Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBA for Access - 2 listbox 'query' not returning results

Status
Not open for further replies.

TatesK

Programmer
Oct 26, 2012
2
US
Hello all!

Let me just preface this with the fact that I am a complete newbie. I majored in showtunes. I am learning as I go, but considering I'd never looked at Access till 3 weeks ago I think I'm catching on.

There is a long story leading to this point, but I've been asked to create a query where people can multi select parameters. I made 2 listboxes - one for 'Subjects' and one for 'Categories.'

I found a VBA code that I could read, and modified it to my purposes.

Option Compare Database


Private Sub Okay_2_Click()
Dim Q As QueryDef, DB As Database
Dim Criteria As String
Dim Crit As String
Dim ctl As Control
Dim ctl2 As Control
Dim Itm As Variant
Dim Itm2 As Variant


' Build a list of the selections.
Set ctl = Me![LstSubject]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm

If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more items in the" & _
" list box!", 0, "No Selection Made")
Exit Sub
End If

' Build a list of the selections.
Set ctl2 = Me![LstCategory]

For Each Itm2 In ctl2.ItemsSelected
If Len(Crit) = 0 Then
Crit = Chr(34) & ctl2.ItemData(Itm2) & Chr(34)
Else
Crit = Crit & "," & Chr(34) & ctl2.ItemData(Itm2) _
& Chr(34)
End If
Next Itm2

If Len(Crit) = 0 Then
Itm2 = MsgBox("You must select one or more items in the" & _
" list box!", 0, "No Selection Made")
Exit Sub
End If

' Modify the Query.
Set DB = CurrentDb()
Set Q = DB.QueryDefs("MultiSelect")
Q.SQL = "Select * From BabyData Where [SUBJECT] In(" & Criteria & _
") And [Category] In(" & Crit & _
");"

Q.Close

' Run the query.
DoCmd.OpenQuery "MultiSelect"

End Sub

This does not give me any errors- however, it also doesn't give me any results. I'm not sure what I am missing here- if someone could let me know what I should be looking at, I'd be very grateful.

Thank you!
Tatum
 
Welcome to Tek-Tips.

You don't need the Q.Close in your code since you are never opening it. You can replace the line with
Code:
[s]Q.Close[/s]
Set Q = Nothing

You should find out what the final SQL equals. Either place a debug.Print statement in your code
Code:
	' Modify the Query.
	Set DB = CurrentDb()
	Set Q = DB.QueryDefs("MultiSelect")
	Q.SQL = "Select * From BabyData Where [SUBJECT] In(" & Criteria & _
		") And [Category] In(" & Crit & _
		");"
        Debug.Print Q.SQL
	Set Q = Nothing
or open the debug window (press Ctrl+G) and enter
Code:
? Currentdb.QueryDefs("MultiSelect").SQL
Press enter after SQL

This should give you a good idea what might be wrong.

Please use the TGML code tag since it makes your code much easier to read :)


Duane
Hook'D on Access
MS Access MVP
 
Oh, gosh, sorry! I just took a look at the FAQ- I will use the TGML tag moving forwarding.

Thanks for the tip- it turns out I had bound the Category list to the wrong column. Of course it wasn't working! Much appreciated!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top