Multiselect

D

David

I built a criteria string out of a multi select box and
put it in a text box. I can put the box on a report and I
see the appropriate values; i.e., "Smith" or "Jones".

When I try to use the form in a query, I return no
results. Any ideas?
 
J

John Viescas

It sounds like you're trying to use a parameter query. You can use a
parameter only where you could otherwise use a simple literal. So, you
cannot include any Boolean logic within the parameter value - Access won't
recognize it. It ends up evaluating your parameter something like this:

WHERE LastName = '"Smith" or "Jones"'

You would be better off not using a parameter at all. Build the filter
string in code and use it as the WhereCondition to open the report.

DoCmd.OpenReport "MyReport", WhereCondition:="LastName = 'Smith' Or
LastName = 'Jones'"

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
J

Joe Fallon

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


To de-select all items in a list box try:
Dim lngX As Long

With Me![lstMyListBox]
For lngX = (.ItemsSelected.Count - 1) To 0 Step -1
.Selected(.ItemsSelected(lngX)) = False
Next lngX
End With
 

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

Top