Using multi-select list box data in a Query

S

Stu

I have used the following multi-select List Box data to open a report with no
problems. I am subswtituting report name with a Query name using my list box
data as criteria for a Make Table Query. I get ERROR message (Wrong number
of arguments or invalid property assignment) at point where it tries to run
the Query. Maybe strDoc = "Query Name" is incorrect OR my DoCmd.OpenQuery is
incorrect ????? Can anyone help me with the proper coding?

Private Sub OK_Click()
On Error GoTo OK_Click_Err
Dim varItem As Variant
Dim strWhere As String
Dim strWhere1 As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim strDoc1 As String

strDoc = "qryMakeTableSalesRankings"
strDoc1 = "qupdOrderRankings"

With Me.lstBrand
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & .ItemData(varItem) & strDelim & ","
End If
Next varItem
End With

lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[SupplierID] IN (" & Left$(strWhere1, lngLen) & ") "
End If

strWhere = strWhere1
DoCmd.SetWarnings False

'FAILURE AT THIS POINT on OPENQUERY

DoCmd.OpenQuery "qryMakeTableSalesRankings", acNormal, acEdit, _
WhereCondition:=strWhere
DoCmd.OpenQuery strDoc1, acNormal, acEdit
DoCmd.Close acForm, "frmSelectCriteria"
DoCmd.SetWarnings True

OK_Click_Exit:
Exit Sub

OK_Click_Err:
MsgBox Err.Description
Resume OK_Click_Exit

End Sub
 
D

Douglas J. Steele

Unlike the OpenForm and OpenRecord methods, the OpenQuery method doesn't
accept a WhereCondition.

You'll need to determine the actual SQL of the query, and then use the
Execute method of the Database object to run your Make Table.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top