Code Issue

S

Stu

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
 
J

JamesDeckert

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
 

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

Similar Threads

Code for List Boxes 16
List Box and Query 3
Code Help!! 0
List Box Coding Problem 1
Cleanup Code on Form ?? 4
List Box HELP 0
Multiple List Boxes on Form - Coding HELP 0
CODE HELP! 2

Top