List Box and Query

[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

 

[Dale Fye]

Stu

To use the OpenQuery method, the SQL must actually reside in a saved query,
so you need to do something like

currentdb.querydefs("qry_SuppliersTest").SQL = strSQL
docmd.openquery "qry_SuppliersTest", acnormal, acEdit

The down side of this, is that in your cmdOK_Click function, you will need
to strip the WHERE clause out of the previous version of qrySupplierTest
before appending the new where clause. As an alternative (the way I usually
do this) is to have two queries "qry_Suppliers" and "qry_SuppliersFiltered",
and I do


currentdb.querydefs("qry_SuppliersFiltered") =
currentdb.querydefs("qry_Suppliers").SQL & WhereString( )

This way, you don't have to worry about messing with qry_Suppliers.


HTH
Dale

 

[Stu]

I like your approach much better. When building QrySuppliersFiltered. do I
use the exact same criteria as QrySuppliers? And when the code runs, it will
add the where string. If so, I guess I could just Save As (QrySuppliers AS
Qry SupplerFiltered) rather than building again.

 

[Dale Fye]

Usually, I would not put any criteria in qry_Suppliers. It would basically
be a select statement that only pulls in the necessary fields from
tbl_Suppliers.

HTH