Having problem to use SELECT query in vba with Datasheet subform

[Jo..]

Hello, it is me again.



I am having problem with getting Combobox value with Select Query in vba.

This is happening to my subform. The subform is shown as a datasheet. When I type in the ProductID. I want the combobox to lookup value base on the ProductID i have input to the datasheet.

It kept popping up to ask me for the ProductID which I have input already.

This is my code:

    Me.QuoteID.RowSource = _        "SELECT ProductID, FactoryName, Casepack, ID FROM [" & Me.SubCategoryName & "]" & _
        "WHERE ProductID =  [Forms]![Subform].[ProductID]" & _
        "ORDER BY ProductID;"

I am guessing that I have to somehow put CurrentRecord between the lines?

Please help me on this~

Thank you so much in advance.
Jo

[ItsMe]

I do not fully understand your objective here. But, you have some syntax issues with your SQL for your RowSource. You are placing a variable (in this case referencing a control) in your SQL where there should be the name of a table or query object.

The red text should be replaced with the name of a table or query.

    Me.QuoteID.RowSource = _        "SELECT ProductID, FactoryName, Casepack, ID FROM [" & Me.SubCategoryName & "] " & _
        "WHERE ProductID =  [Forms]![Subform].[ProductID]" & _
        "ORDER BY ProductID;"

Maybe something like this. I also added some white space before WHERE and ORDER ...

    Me.QuoteID.RowSource = "SELECT ProductID, FactoryName, Casepack, ID FROM MyTable" & _
        " WHERE ProductID =  [Forms]![Subform].[ProductID]" & _
        " ORDER BY ProductID;"

[nick404]

Is it asking you to input a parameter for ProductID after you already have?

[Rawb]

Try moving the reference to your Product ID outside the quotes, like so:

    Me.QuoteID.RowSource = _
        "SELECT ProductID, FactoryName, Casepack, ID FROM [" & Me.SubCategoryName & "] " & _
        " WHERE ProductID = " & [Forms]![Subform].[ProductID] &  _
        " ORDER BY ProductID;"

Also, I would strongly suggest you review your Tables, the fact that you have to look in different Tables for a product just because it's in a different category, implies that you are using a non-normalized Table layout.

Ultimately you would end up with an easier to maintain/update database if you used a design similar to the following:

Table Categories:

• CategoryID - Primary Key, Autonumber
• CategoryName - Text, 32 characters

Table Factories:

• FactoryID - Primary Key, Autonumber
• FactoryName - Text, 32 characters

Table Products:

• ProductID - Primary Key, Autonumber
• CategoryID - Index, Foreign Key (Categories.CategoryID), Long Integer
• FactoryID - Index, Foreign Key (Factories.FactoryID), Long Integer
• Casepack - ???

This would allow you to use a single Table to store all of your Products and save you from having to update your VBA code every time a new category is added or an old one removed.

[CJ_London]

[Forms]![Subform].[ProductID]

doesn't look right - if the ProductID control is in a subform on the same form as Me.SubCategoryName then you would use

me.subform.form.productID

And just to check - is your subform control really called subform?

[Jo..]

I do not fully understand your objective here. But, you have some syntax issues with your SQL for your RowSource. You are placing a variable (in this case referencing a control) in your SQL where there should be the name of a table or query object.

The red text should be replaced with the name of a table or query.

    Me.QuoteID.RowSource = _        "SELECT ProductID, FactoryName, Casepack, ID FROM [" & Me.SubCategoryName & "] " & _
        "WHERE ProductID =  [Forms]![Subform].[ProductID]" & _
        "ORDER BY ProductID;"

Maybe something like this. I also added some white space before WHERE and ORDER ...

    Me.QuoteID.RowSource = "SELECT ProductID, FactoryName, Casepack, ID FROM MyTable" & _
        " WHERE ProductID =  [Forms]![Subform].[ProductID]" & _
        " ORDER BY ProductID;"

Thank you for your reply, I have actually got the solution by myself in the end.

[" & Me.SubCategoryName & "] The table name is actually variable. I have different tables with different names which matches the SubCategoryName textbox.

And the white spaces is not helping anything with this situation. Anyway, thank you for your help!

Jo

[Jo..]

Try moving the reference to your Product ID outside the quotes, like so:

    Me.QuoteID.RowSource = _
        "SELECT ProductID, FactoryName, Casepack, ID FROM [" & Me.SubCategoryName & "] " & _
        " WHERE ProductID = " & [Forms]![Subform].[ProductID] &  _
        " ORDER BY ProductID;"

Also, I would strongly suggest you review your Tables, the fact that you have to look in different Tables for a product just because it's in a different category, implies that you are using a non-normalized Table layout.

Ultimately you would end up with an easier to maintain/update database if you used a design similar to the following:

Table Categories:

• CategoryID - Primary Key, Autonumber
• CategoryName - Text, 32 characters

Table Factories:

• FactoryID - Primary Key, Autonumber
• FactoryName - Text, 32 characters

Table Products:

• ProductID - Primary Key, Autonumber
• CategoryID - Index, Foreign Key (Categories.CategoryID), Long Integer
• FactoryID - Index, Foreign Key (Factories.FactoryID), Long Integer
• Casepack - ???

This would allow you to use a single Table to store all of your Products and save you from having to update your VBA code every time a new category is added or an old one removed.

Thank you for your reply. My current design does have the IDs you are suggesting. I will see how it go. But thank you for your suggestion

[Jo..]

doesn't look right - if the ProductID control is in a subform on the same form as Me.SubCategoryName then you would use

me.subform.form.productID

And just to check - is your subform control really called subform?

Thank you for your reply. My solution is actually very close to your. My code is

    Me.QuoteID.RowSource = _    "SELECT ProductID, CasePack, ID, FactoryName FROM [" & Me.SubCategoryName & "]" & _
    "WHERE Factoryname =  [Forms]![MainForm]![Subform].Form![factory] " & _
    "ORDER BY CasePack;"

the subform is not actually subform, but to show you guys what I am doing here. Thank you for your help!