Solved SQL Statement in VBA

[silversun]

Hi everybody.
Please help me on following syntax error.
I am using a string variable as RowSource of combo box: cmb_unit.
As you can see in the attached image it has syntax error and I couldn't figure it out.

Private Sub cmb_items_AfterUpdate()
Dim sSource As String
sSource = "SELECT t_units.[unit_ID], t_units.[unit], t_units.[unit_index] " & _
          "FROM t_units " & _
          "WHERE t_units.unit_index= " & Me.cmb_items.Column(2)
    
Me.cmb_unit.RowSource = sSource

End Sub

I appreciate your help.

 

[MajP]

need a space before the = sign.

 

[Cronk]

Is Me.cmb_items.Column(2) a text field? If so it should be

WHERE t_units.unit_index= '" & Me.cmb_items.Column(2) &"'"

@ MajP I;ve not had issues with no space before or after the = sign

 

[arnelgp]

what is the Rowsource of combo cmb_items?
can you show some records of the combo.

 

[silversun]

need a space before the = sign.

I added the space. It didn't fix the issue.

 

[silversun]

text field

No.
It is a number between 1 and 5

 

[silversun]

Rowsource of combo cmb_items

SELECT t_indexed_items.item_ID, t_indexed_items.item
FROM t_indexed_items
ORDER BY t_indexed_items.[item];

 

[arnelgp]

you have only 2 columns in cmb_items.
on vba, column 1 is refered as 0, column 2 as 1, etc.

so there is No Me.cmb_items.Column(2)
what you have is Me.cmb_items.Column(0) (which is column 1) and Me.cmb_items.Column(1), which is column 2.

you may try (if you are referring to Column 2):

sSource = "SELECT t_units.[unit_ID], t_units.[unit], t_units.[unit_index] " & _
"FROM t_units " & _
"WHERE t_units.unit_index= " & Me.cmb_items.Column(1)

 

[silversun]

you have only 2 columns in cmb_items.
on vba, column 1 is refered as 0, column 2 as 1, etc.

so there is No Me.cmb_items.Column(2)
what you have is Me.cmb_items.Column(0) (which is column 1) and Me.cmb_items.Column(1), which is column 2.

you may try (if you are referring to Column 2):

sSource = "SELECT t_units.[unit_ID], t_units.[unit], t_units.[unit_index] " & _
"FROM t_units " & _
"WHERE t_units.unit_index= " & Me.cmb_items.Column(1)

Thank you very much.
I added the third column in my query design and it started working.
Good catch.
Thank you a lot

 

[Gasman]

If you had put it all into a string variable and used debug.print to display it, it would have been pretty obvious?