Re: Dynamically populating Combo Box .?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Cheers Doug

but not sure I explained clearly the problem..

Because I do not always want to filter my select statement on the the same
field (fields) I need to also store the variable name in the SQL table.

So
"Select * From Transactions Where AccNo = '" & strAccNo & "'"
could just as easily be
"Select * From Transactions Where Surname = '" & strSurname & "'"
or even
"Select * From Transactions Where AccNo = '" & strAccNo & "' and Surname =
'" & strSurname & "'"

In the last example therefore I would save the following text within my SQL
field (strAccNo and strSurname are both variables within my VB app)
"Select * From Transactions Where AccNo = '" & strAccNo & "' and Surname =
'" & strSurname & "'"
but when grabbed by my VB app, I want it to work as
"Select * From Transactions Where AccNo = X1234567 and Surname = Smith"


Code example below:

Sub PopulatecbBox()
......Code to open ADO recordset
With myRecordset
SetUpCombo(cbArg1, .Fields("Arg1cbSelect").Value,
..Fields("Arg1cbField").Value)
End With
End Sub

Private Sub SetUpCombo(ByVal cbComboBox As ComboBox, ByVal strSelect As
String, ByVal strField As String)
Dim oleConn As New System.Data.OleDb.OleDbConnection(strConnADO)
Dim oleAdapter As New System.Data.OleDb.OleDbDataAdapter()
Dim myDataset As DataSet
Dim intCounter As Integer
myDataset = New DataSet()
oleAdapter.SelectCommand = New System.Data.OleDb.OleDbCommand(strSelect,
oleConn)
oleAdapter.Fill(myDataset)
For intCounter = 0 To myDataset.Tables(0).Rows.Count - 1
cbComboBox.Items.Add(myDataset.Tables(0).Rows(intCounter).Item(strField))
Next
End Sub

In this instance strSelect is being passed in as
"Select * From Transactions Where AccNo = '" & strAccNo & "'"
and so obviously my combo box is not being populated.

Cheers

"Doug Bell" <PoorSupport@xxxxxxxxxxxxxxxx> wrote in message
news:OiPzlutnFHA.3304@xxxxxxxxxxxxxxxxxxxxxxx
> Hi Chubbly,
> What you have:
> "Select * From Transactions Where AccNo = '" & strAccNo & "'"
>
> Will (if strAccNo="X5235982") send:
> Select * From Transactions Where AccNo = 'X5235982 '
>
> You can test this by:
> st=
> "Select * From Transactions Where AccNo = '" & strAccNo & "'"
>
> console.writeline (st)
> and looking at the output window.
>
> Some databases prefer double quotes.
> Some need an end character ";"
>
> Doug
>
> "Chubbly Geezer" <chubbly_geezer@xxxxxxxxxxxxxxxx> wrote in message
> news:OzhMnXonFHA.1204@xxxxxxxxxxxxxxxxxxxxxxx
>> I am trying to populate a combo box on a form based on the contents of a
>> field within SQL.
>>
>> i.e.
>>
>> I read the data from the SQL field. In this case I return the string
>> "Select * From Transactions"
>> I populate my dataset and set the dataset as the datasource of my combo
> box.
>> This works fine.
>>
>> However what I want to do is to use a where clause in my select statement
>> such as "Select * From Transactions Where AccNo = '" & strAccNo & "'",
> where
>> strAccNo is a variable within my VB app.
>> Now not surprisingly my combo box is empty as the the datasource is
> exactly
>> as my select statement reads and it is trying to return records where
> AccNo
>> matches strAccNo and not as I had hoped which is to match AccNo to the
>> contents of strAccNo (X5235982 for example).
>>
>> What I have:
>> Select * From Transactions Where AccNo = 'strAccNo'
>>
>> What I want:
>> Select * From Transactions Where AccNo = 'X5235982 '
>>
>> Any ideas appreciated.
>>
>>
>
>


.

Relevant Pages

  • RE: Text box validation
    ... Server 2000 with ADO to populate the txtCurrent_user box as follows. ... Private Sub Form_Open ... Dim rsLOGIN_ID As Recordset ... eventhough both boxes have the ...
    (microsoft.public.access.formscoding)
  • Re: command for listing documents in a Lisbon
    ... ' Populate the listbox, lstSelectDocument, with the documents paths here ... Dim MyFile As String ... > Sub OpenFileSelectionForm() ...
    (microsoft.public.word.vba.beginners)
  • Re: Excel and Visual Basic
    ... populate. ... Sub ClearWorksheet() ... Dim adoConnection As ADODB.Connection ...
    (microsoft.public.excel.programming)
  • Re: populate combo box from excel
    ... I've taken your suggestion and used the code to populate from another word ... > Dim rs As DAO.Recordset ... > Private Sub ComboBox1_Change ... > the first combobox - one to a cell in the first column of a table and the ...
    (microsoft.public.word.vba.userforms)
  • Project Error
    ... Private Declare Sub Sleep Lib "Kernel32" ... Dim strDataSrc As String ...
    (microsoft.public.vb.bugs)