Using MultiSelect with a Listbox as criteria for a report

  • Thread starter Thread starter David Frick
  • Start date Start date
D

David Frick

I have a form that displays a report in Print Preview view when a command
button is clicked.

The records dispalyed in the form are filtered by a WHERE clause that is
specified by a user's selection in a combo box.

The code in the VB module looks like this:

stDocName = "rpt_Customers"
Cmd.OpenReport stDocName, acPreview, , "StateID = " & Me.[cboStateID]

This returns all the customers from the state selected in the combobox.
But I want to allow the user to select cuistomers from _more than one_
state.

I can allow multiple selects from a listbox but I am not sure how to code
the criteria in my VB module.

This is what I would like to do but does not work:

Cmd.OpenReport stDocName, acPreview, , "StateID IN " & Me.[lstStateID]

Can someone help me with the syntax?

Thanks
Dave
 
I have tried this and I get the same error every time = Run-time error
'2001'

What am I doing wrong?

Thanks
Suzanne
 
A Multi-Select ListBox can not be used directly in the Query Grid Criteria.
e.g. Forms![FormName]![ListBoxName] will NOT work.
Why?
Simply examine the output of the Selection and you will see that the string
produced is different from the one the Query Grid requires:
"MyText1 Or MyText2 Or MyText3" from Listbox
"MyText1" Or "MyText2" Or "MyText3" from query grid.

The solution to the multi-select listbox problem is to write the query in
code so that it looks the same way the query grid does.
Build the criteria based on selected values that you can determine by
looping through the selected property of the list box.

I usually write the query in the Grid and use multiple criteria and then
copy the SQL to code and work it into this format:


Private Sub btnCreateInvoice_Click()
On Error GoTo Err_btnCreateInvoice_Click

Dim frm As Form, ctl As Control, varItm As Variant, strCriteria As
String
Dim dbs As Database, qdf As QueryDef
Set dbs = CurrentDb
Set frm = Forms![CreateInvoiceData]
Set ctl = frm!ListPOs
strCriteria = ""

For Each varItm In ctl.ItemsSelected
strCriteria = strCriteria & "(pohdr.ponumber)='" &
Trim(ctl.ItemData(varItm)) & "' Or "
Next varItm

If strCriteria = "" Then
MsgBox "Select one or more PO's."
Exit Sub
End If

strCriteria = Left(strCriteria, Len(strCriteria) - 4)

strSQL = "SELECT [invnumber], Date() AS invdate, pohdr.vendno "
strSQL = strSQL & "FROM pohdr INNER JOIN poln ON pohdr.pokey =poln.pokey
"
strSQL = strSQL & "WHERE (" & strCriteria & ");"

If QueryExists("Inv1") = True Then
dbs.QueryDefs.Delete "Inv1"
End If

Set qdf = dbs.CreateQueryDef("Inv1")
qdf.SQL = strSQL

Set ctl = Nothing
Set frm = Nothing
Set dbs = Nothing

Exit_btnCreateInvoice_Click:
Exit Sub

Err_btnCreateInvoice_Click:
MsgBox ("Error # " & str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_btnCreateInvoice_Click

End Sub


Function QueryExists(strQueryName As String) As Boolean
On Error Resume Next
QueryExists = IsObject(CurrentDb.QueryDefs(strQueryName))
End Function


--
Joe Fallon
Access MVP
 
Back
Top