Need help for using SQL in VBA

[Muzi&Con]

I want use SQL statement in VBA.

1. Update a combo box value to "Potato"
2. Run SQL statement in VBA
Docmd.RunSQL "Seletct ColA from Table WHERE ColB=" & Me.Combo_box.value
3. Input the result to Text Box

I failed it last 3 hours.
Thank you for your interest.

 

[NauticalGent]

If the value in the ComboBox is indeed "Potato" (string), try:

Docmd.RunSQL "Seletct ColA from Table WHERE ColB=""" & Me.Combo_box.value & """

All that being said, I am assuming that "Potato" is an example and NOT the only value that will be available...?

 

[arnelgp]

what "column number" is potato and what is the Bound Column Number.
if the bound column number is the same as the column number for "potato", simply assigning "potato" to the combobox will do:

Me.combo1 = "potato"

otherwise, you need to loop through each item member of the combo to find "potato":

Dim i As Long
For i = 0 To Me.Combo1.ListCount - 1
    If Me.Combo1.Column(columnNumberOfpotato, I) = "potato" Then
        Me.Combo1 = Me.Combo0.Column(boundColumnNumber, i)
        Exit For
    End If
Next i

 

[sonic8]

1. Update a combo box value to "Potato"
2. Run SQL statement in VBA
Docmd.RunSQL "Seletct ColA from Table WHERE ColB=" & Me.Combo_box.value
3. Input the result to Text Box

DoCmd.RunSql is meant to run action queries only, not to retrieve data.
If you just want to retrieve a single value, the DLookup-Function would be an easy solution. Otherwise you would need to open a recordset to retrieve multiple fields/records from the table.

 

[Pat Hartman]

If you are trying to show a value related to the value in the combo, the simplest solution is to change the query used as the RecordSource of the form to left-join to the table containing the data you want to display. Just make sure to set the locked property of the control you bind this field to to locked because you don't want the user to accidentally update it.

Here'a sample of the three commonly used methods. Look at the next to last item on the switchboard - Auto-Populate and Cascade - 3 methods