CODEWITHSUNDEEP
ms-accessvba
Jared H
Jared H

Reputation: 5

Concatenate SQL in Access VBA

I'm trying to run an after update query in a field on my form to populate another ComboBox in the form with a concatenated value. I can make the query work in SQL itself, but am having trouble getting the syntax right in VBA. If anyone has any advice I would greatly appreciate it. The Query is below.

[Prod_List].RowSourceType = "Table/Query"
[Prod_List].RowSource = "SELECT ProductID, Make '" & " - " & "' Model '" " - " & "' ProductNum AS product FROM dbo_ProductInfo " & _
"WHERE ProductID = "SELECT ProductID from dbo_ProductInfo " & _
                 "WHERE Make = '" & Make.Value & "'""

Upvotes: 0

Views: 1380

Answers (1)

Gustav
Gustav

Reputation: 56016

Try to reduce it a bit:

[Prod_List].RowSource = "SELECT ProductID, Make & '" - "' & Model & '" - "' & ProductNum AS Product FROM dbo_ProductInfo " & _
"WHERE ProductID IN (SELECT ProductID from dbo_ProductInfo " & _
                    "WHERE Make = '" & Make.Value & "')"

It may only work with IN, as the query may not know if more than one record could be returned from the subquery. Using IN, it can be any count, though it probably always will be only one.

Upvotes: 1

Related Questions