Code Help!!

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 (qrySupplier) 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 qupdInstaller. 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 = "qrySupplier"

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

DoCmd.OpenQuery "qupdInstaller", acNormal, acEdit
 

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

List Box and Query 3
List Box Coding Problem 1
List Box HELP 0
Multiple List Boxes on Form - Coding HELP 0
Code Issue 1
Code for List Boxes 16
Simplify Code-Delete Query 3
Create Report On the Fly 1

Top