Syntax Error Help

[KLahvic01]

I am trying to get a combobox populate off of a previous combobox selection. I am receiving the syntax error:

Syntax Error (missing operator) in query expression 'Products.Product Name'.

Here is the code I have on the after update event of the first combobox:

Private Sub cboProductCategory_AfterUpdate()
On Error Resume Next
cboProductID.RowSource = "Select Products.Product Name, Products.Description, Products.List Cost, Products.Size, Products.Color " & _
    "FROM Product " & _
    "WHERE Products.Category = '" & cboProductCategory.Value & "' " & _
    "ORDER BY Products.Size;"
End Sub

Any help would be greatly appreciated.

Thank you,
KJ

 

[mdlueck]

I suspect the query blows up due to, I assume, spaces in the names of database objects. If there are spaces in the database objects, wrap [] around the complete names in order that Access understands what is the complete name of the database object.

 

[Beetle]

It's because your Product Name field contains a space (so does your List Cost field). Field names with spaces must be enclosed in brackets [Product Name], which is why it is good practice not to use spaces in field names. ProductName (and ListCost) would be better naming choices.

 

[KLahvic01]

Awesome, I knew that the spaces are a bad idea in field names, but only found that out much later into the development so I decided to not change them. Forgot about the brackets. Now I am getting the following error:

The record source 'Select Products.[Product Name], Products.Description, Products.[List Cost], Products.Size, Products.Color From Product WHERE.....' specified does not exist.

What the heck does this mean, the table exists, the fields exist, I am not sure what it is erroring on.

Any help?

KJ

 

[VilaRestal]

From Products I presume (not Product singular)

 

[Beetle]

Is there a reason you're rebuilding the entire Row Source in the first place? Why not just reference the first combo box in the criteria of the second combo box's embedded Row Source, then simply requery the second in the After Update of the first?

 

[VilaRestal]

Coding it is better imo: More efficient, easier to debug and handle errors and not dependent on form names.

 

[KLahvic01]

From Products I presume (not Product singular)

Thank you, you rock as usual, I completely overlooked that little tidbit...works like a charm now...

Is there a reason you're rebuilding the entire Row Source in the first place?

I just found that little bit of code on the internet telling me how to do a multiselect, and tried to modify it to my needs. Only reason. I would try your suggestion too, I just am new to this and wouldnt know how to do that...If you want to show me I would be appreciative as an alternative...

Thank you again...

KJ

 

[Beetle]

Well, Vila has a point, and if you have it working there's no reason to change it, but if you're interested the alternative is to place a reference to the first combo box in the criteria row of the appropriate column of the Row Source of the second combo box. In your case this would be the Category column. The reference would look like;

Forms!YourFormName!cboProductCategory

Then, in the After Update event of the first combo box you simply requery the second combo box;

Me!cboProductID.Requery