使用VBA SQL查找基于列表框选择的多条记录

Question

我有一个表单，其中有一个包含各种产品的列表框和一个组合框，当从列表框中选择产品时，该组合框应该包含一个或多个工艺编号(同一产品可以有多个工艺编号)。进程号包含在另一个表(进程顺序)，我一直在玩SQL试图找出它，但它不断出现语法错误或告诉我，我有一个缺失的操作符。

下面的代码是我尝试基于另一个例子，我发现在这里，但这是我第一次使用SQL。

Dim strMyString As Variant
'lstProduct is the listbox containing the products
strMyString = lstProduct.Value
'Process Order is the table name, Process No the field containing the Process Numbers and Product Description is the field I want to match the listbox value
strSQL = "SELECT [Process Order].[Process No] FROM [Process Order] WHERE [Product Description]= '" & strMyString
'cmbProcNo is the combobox where I want the process numbers to be displayed
cmbProcNo.RowSource = strMyString

抱歉，如果这是一个愚蠢的问题，但任何帮助是非常感激的谢谢!

Answer 1

这是正确的代码!

Dim strSQL As String
'lstProduct is the listbox containing the products'
'Process Order is the table name, Process No the field containing the Process Numbers and Product Description is the field I want to match the listbox value'
strSQL = "SELECT [Process Order].[Process No] FROM [Process Order] WHERE [Product Description]= '" & Me.lstProduct & "'"
'cmbProcNo is the combobox where I want the process numbers to be displayed'
Me.cmbProcNo.RowSource = strSQL
Me.cmbProcNo.Requery

忘记关闭SQL语句。然后需要将SQL分配给ComboBox行源，而不是Listbox的值。