Code Issue

[Guest]

I have an unbound form with a multiple select list box (lstClass) and a
command button (cmdOK). I am trying to select row(s) from the list box and
build up a Where string and then apply that "WHERE" criteria to a Select
Query (qrySupplierTEST) to get my desired records. Once I have these
records, I want to use this query linked to a table in an Update Query and
run qupdInstallerTEST. I created a Function WhereString() thinking I would
be using this in possible several forms. Can anyone assist me and straighten
out my code in applying the "WHERE" to a query and forward. I know the
WhereString() is working. Thanks

Private Function WhereString() As String
Dim strWhere As String
Dim varItem As Variant

On Error Resume Next

' ... build "Class" criterion expression
If Me.lstClass.ItemsSelected.Count > 0 Then
strWhere = strWhere & "Class IN ("
For Each varItem In Me.lstClass.ItemsSelected
strWhere = strWhere & "'" & _
Me.lstClass.ItemData(varItem) & "', "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND "
End If

WhereString = strWhere
If Len(WhereString) > 0 Then
WhereString = " WHERE " & Left(WhereString, Len(WhereString) - 5)

End If

End Function

Private Sub cmdOK_Click()
Dim strSQL As String
Dim strRecordSource As String

On Error Resume Next

strRecordSource = "qrySupplierTEST"

' build sql string for form's RecordSource
strSQL = "SELECT * FROM " & strRecordSource & _
WhereString()

DoCmd.OpenQuery "qupdInstallerTEST", acNormal, acEdit

End Sub

 

[Guest]

Use debug to stop execution after the query string is built and look at the
query string to determine if there is a formatting error in the string, or a
logic problem with the SQL.
James