SQL in VBA gets me everytime (1 Viewer)

Everytime I try to place a SQL query in VBA, it doesn't work. I can create queries in the RowSource of ComboBoxes but I cannot get them to work in my code. I have a uniform and PPE database to keep track of issues to employees. I am using 3 cascading comboboxes to help user select the proper clothing item. The first box contains the clothing catagory: work, dress, ppe, and accesories, the second box contains the specific types of items eg: button down shirts, coveralls, etc. The third contains the sizes of the items. I have disabled the second two until the first one is choose. I have a form that shows the the title of the page. Control Source: =[Caption]. Each form has a Caption. Any way, when I run the code, the title says #Error"

txtType and txtPrimary are the first and second ComboBoxes
Primary_Num and Item_Num are actually strings

The code is :
Private Sub txtType_AfterUpdate()
'On Error Resume Next I had this uncommented at first
strSQL = "Select Primary.Item_Num, Primary.Item_Catagory, Primary.Primary_Num "&_
"From Primary " & _
"Where Primary.Primary_Num LIKE '" &m.txtType.Value &"';"
Me.txtPrimary.RowSource = strSQL
Me.txtPrimary.Requery

I am not getting an Error but it shows up in the title

Hi. A quick way to troubleshoot something like this is to do a Debug.Print strSQL and see what your SQL looks like.

I'm confused. What does the form caption have to do with the sql statement?
What does the caption have to do with a control source?

add space to your ampersand (&):

strSQL = "Select Primary.Item_Num, Primary.Item_Catagory, Primary.Primary_Num " & _
"From Primary " & _
"Where Primary.Primary_Num LIKE '" & m.txtType.Value & "';"

Debug print shows " Select Primary.Item_Num, Primary.Item_Catagory, Primary.Primary_Num From Primary Where Primary_Num LIKE '8440'

I mentioned about the caption because it is suppose to show the Form title but is shows Error.

I have spaces between the "&" and it still does not work

gamesprite said:

Debug print shows " Select Primary.Item_Num, Primary.Item_Catagory, Primary.Primary_Num From Primary Where Primary_Num LIKE '8440'

Click to expand...

Hi. I assumed from your topic title that you're having problems with constructing the SQL, so I didn't even read your code. If you copy the result of the Debug.Print and paste it into the query designer and it works, then your problem is somewhere else then. But if the query designer balks at your SQL, then you'll have to fix it first.

Primary_Num suggests it is numeric. If so, ditch the single quotes.

As well as scrapping the text delimiters if its a number field, you should use = instead of LIKE as you aren't using a wildcard.
Value is the default property so can be omitted.
I think you mean Me. not m.

m.txtType, or you mean, Me.txtType?

You don't need to keep the query in code at all. Just bind the combo to a query that references the "parent" combo. In the Click event of the "parent" combo, you need to requery the child:

Me.cbo2.Requery

If you have more than two, then you need to requery all the descendents.
Me.cbo2.Requery
Me.cbo3.Requery
Me.cbo4.Requery

In the query's RowSource:

Select Primary.Item_Num, Primary.Item_Catagory, Primary.Primary_Num From Primary Where Primary.Primary_Num = Forms!YourFormName!txtType

Be sure to fix up the form's name in my example. If you do this using QBE, you'll even get intellisense to help you pick the correct form and correct control.