Parameter Lookup

[sharrell]

I am creating queries based on tables linked to our accounting package.

I have a query for which the user will need to choose parameters. I need to have a lookup feature of some sort for this criteria using a field in a query. I am thinking a list box.

I know how to add a parameter based on user input, i.e. [Start Date], but how do I make a parameter a list of options?

Thanks,
Stephanie

 

[Tiro Cupidus]

You need to make a form with all the little gizmos you want and when the user presses the submit button (or whatever it is to say they're finished), a query runs based on the information provided on the form.

 

[sharrell]

How do I get the query to run based on the options the user chooses?

Nevermind, I think I figured this out.

But, is it possible for more than one option in the list box to be chosen?

Nevermind, I figured this out too.

Thanks for your help.

 

[sharrell]

So now my lack of patience has made a fool of me.

I have gotten the "gizmos" created. I have figured out how to choose multiple options in the same list box. I have linked these list boxes to their corresponding fields in my query.

When I run the query from the form, it returns no records. What am I doing wrong?

I promise to have more patience this time.

 

[Tiro Cupidus]

Hmmm... I was thinking I had done something very similar to this, but it turns out I never actually passed parameters to the query. I'm trying to figure it out now It's so much easier in server-side script Perhaps I can help a bit more if I see what you put in the criteria for you query.

 

[sharrell]

Forms![frmLookup]![lbxLocationLookup] is the criteria for the field Location. It refers to a list box.

I also have <[Forms]![frmLookup]![SinceDate] for field LastSaleDate. It refers to an unbound text box. This seems to work fine. It's the list boxes that are the problem.

 

[sharrell]

I have gotten it to work correctly in MultiSelect is set to None.

From what I have found elsewhere on line, MultiSelect will require some code which I have NO experience at. I tried using an article just now that explains it but I failed.

If anyone has code that will run a query based on two Multiselect List Boxes, please help!!

 

[Tiro Cupidus]

I fiddled around a bit with it before I left work. I'll try to look at it some more tomorrow.

 

[Jon K]

Take a look at this thread for criteria using list box:-
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=35260

Hope it helps.

 

[sharrell]

Thanks, Jon, but I am using 2002 and it has a problem opening the sample database. I can't convert it because it's read-only.

Also, it occurs to me that I'll need two multiselect list boxes, one for location and one for item class. Will it be possible to do this, as I all the examples I've seen have contained only one list box?

 

[Jon K]

Attached is a demo DB in Access 2000 that uses two list boxes. Hope you can open it in Access 2002.

It still used DAO, so if you write your own code, you must make a reference to Microsoft DAO 3.6 Object Library.

The list boxes lstLocation and lstItemClass are populated by text fields "Location" and "Item Class" from table tblLocationItemClass.

The code is contained in the On Click Event of the Retrieve button on the form. When the button is clicked, a query qryDataType is created and run.


I copy the code below in case you can't open the DB in Access 2002.
-------------------------------------
Private Sub cmdRetrieve_Click()
' if nothing is selected, display message and exit.
If Me.lstLocation.ItemsSelected.Count = 0 And _
Me.lstItemClass.ItemsSelected.Count = 0 Then
MsgBox "Select location/item class first"
Exit Sub
End If

Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim SQL As String
Dim sLocation As String
Dim sItemClass As String
Dim sCriteria As String
Dim varItem As Variant

' build criteria string for selected locations.
For Each varItem In Me.lstLocation.ItemsSelected
sLocation = sLocation & ",'" & Me.lstLocation.ItemData(varItem) & "'"
Next varItem
sLocation = Mid(sLocation, 2) ' remove leading comma.
sLocation = " Location in (" & sLocation & ")"

' build criteria string for selected item classes.
For Each varItem In Me.lstItemClass.ItemsSelected
sItemClass = sItemClass & ",'" & Me.lstItemClass.ItemData(varItem) & "'"
Next varItem
sItemClass = Mid(sItemClass, 2) ' remove leading comma.
sItemClass = " [Item Class] in (" & sItemClass & ")"

' build SQL Statement.
If Me.lstLocation.ItemsSelected.Count > 0 And _
Me.lstItemClass.ItemsSelected.Count > 0 Then
sCriteria = sLocation & " AND " & sItemClass
Else
sCriteria = IIf(Me.lstLocation.ItemsSelected.Count > 0, sLocation, sItemClass)
End If
SQL = " SELECT * " & _
" FROM tblLocationItemClass " & _
" WHERE " & sCriteria

Set db = CurrentDb

' delete query qryDataType if exists.
On Error Resume Next
db.QueryDefs.Delete "qryDataType"
On Error GoTo 0

' create and run query qryDataType.
Set qDef = db.CreateQueryDef("qryDataType", SQL)

DoCmd.OpenQuery "qryDataType"

End Sub
------------------------------------

Hope it helps.

 

[sharrell]

This is great. The query runs. I managed to get all the field/listbox reference replaced with my specific names.

However, the query returns no results.

Here is the modified Code. lbxLocationLookup is the Location List Box. lbxClassCodeLookup is the Class List Box. qryResult contains all the fields that I need in the final qryInvAnalysis.


Private Sub cmdRunResults_Click()
' if nothing is selected, display message and exit.
If Me.lbxLocationLookup.ItemsSelected.Count = 0 And _
Me.lbxClassCodeLookup.ItemsSelected.Count = 0 Then
MsgBox "Select location/item class first"
Exit Sub
End If

Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim SQL As String
Dim sLocation As String
Dim sItemClass As String
Dim sCriteria As String
Dim varItem As Variant

' build criteria string for selected locations.
For Each varItem In Me.lbxLocationLookup.ItemsSelected
sLocation = sLocation & ",'" & Me.lbxLocationLookup.ItemData(varItem) & "'"
Next varItem
sLocation = Mid(sLocation, 2) ' remove leading comma.
sLocation = " [Forms]![frmLookup]![lbxLocationLookup] in (" & sLocation & ")"

' build criteria string for selected item classes.
For Each varItem In Me.lbxClassCodeLookup.ItemsSelected
sItemClass = sItemClass & ",'" & Me.lbxClassCodeLookup.ItemData(varItem) & "'"
Next varItem
sItemClass = Mid(sItemClass, 2) ' remove leading comma.
sItemClass = " [Forms]![frmLookup]![lbxClassCodeLookup] in (" & sItemClass & ")"

' build SQL Statement.
If Me.lbxLocationLookup.ItemsSelected.Count > 0 And _
Me.lbxClassCodeLookup.ItemsSelected.Count > 0 Then
sCriteria = sLocation & " AND " & sItemClass
Else
sCriteria = IIf(Me.lbxLocationLookup.ItemsSelected.Count > 0, sLocation, sItemClass)
End If
SQL = " SELECT * " & _
" FROM qryResult" & _
" WHERE " & sCriteria

Set db = CurrentDb

' delete query qryInvAnalysis if exists.
On Error Resume Next
db.QueryDefs.Delete "qryInvAnalysis"
On Error GoTo 0

' create and run query qryDataType.
Set qDef = db.CreateQueryDef("qryInvAnalysis", SQL)

DoCmd.OpenQuery "qryInvAnalysis"
End Sub

 

[Jon K]

You need to replace [Forms]![frmLookup]![lbxLocationLookup] and [Forms]![frmLookup]![lbxClassCodeLookup] with the field names to make the SQL Syntax correct.

Put a field name in square brackets if it contains space.

In fact, for a multi-select list box, the expression [Forms]![FormName]![ListBoxName] returns Null and therefore should never be used.

 

[sharrell]

That worked great. Thank you very much!