Adding Combo Box to Dynamic Multi Search

[isotopes]

Hello all,

I am new to the forum and an Access novice. Not too long ago, I stumbled on to a Dynamic Multi Search form on this site and have been tailoring it to my organization's directory of contacts. Everything was going good until I was asked to include a search by groups to which each individual may belong. The data in the table is contained in Yes/No check boxes for around 30 different groups. I am hoping to add a combo box to the Dynamic Search as a means to pull up individuals in any 1 category.

Below are two attempts at what I thought might work, however, neither performs any filtering. Any and all help is greatly appreciated.

Private Sub Groupbox_Change()  
Dim db As Database     
Dim qdf As QueryDef     
Dim strSQL As String     
Set db = CurrentDb     
Set qdf = db.QueryDefs("qryGroup")          

strSQL = "Select Snap_Contact_master.* FROM SNAP_Contact_master"     

Select Case Me.Groupbox         
Case "cboUsed"             strSQL = "where SNAP_Contact_master.groupbox.Text & " = vbTrue     
End Select       

Me.SearchResults = Me.SearchResults.ItemData(1)       Me.SearchResults.SetFocus     
DoCmd.Requery  
End Sub

Private Sub Groupbox_Change()  
Dim db As Database     
Dim qdf As QueryDef     
Dim strSQL As String     
Set db = CurrentDb     
Set qdf = db.QueryDefs("qryGroup")          
sqlQry = "Select SNAP_Contact_Master.*"         

Select Case Me.Groupbox             
Case "Urban 14"                 
sqlQry = Me.Groupbox.SelText & " WHERE Urban14 = True"             

Case "Claims"                 
sqlQry = sqlQry & " WHERE Claims = vbtrue"            

Case "EBT"                 
sqlQry = sqlQry & " WHERE EBT = vbtrue"             

Case Else                 
sqlQry = sqlQry & " WHERE Urban14 = vbtrue Or Claims = vbtrue Or EBT = vbtrue"         
End Select         

Me.SearchResults = Me.SearchResults.ItemData(1)     Me.SearchResults.SetFocus      
DoCmd.Requery  
End Sub

 

[Ranman256]

your vbTrue is not needed INSIDE the dbl-quotes
either:
sqlQry = sqlQry & " WHERE Claims = " & vbtrue
or
sqlQry = sqlQry & " WHERE Claims = True"

 

[isotopes]

Thanks Ranman. I have corrected the code, however, the combo box selections are still not filtering the the listbox. What else am I missing?

 

[Ranman256]

once you build a where for the list box,
you must requery the list....
lstBox.requery

normally you dont need code for this...the query
qsListBoxData
where lstBox.rowsource = qsListBoxData

the query whould look at the form and pull data, via
select * from tbl where [group] = forms!frmMain!cboGroup

 

[isotopes]

I believe that I have it set up to properly requery the list box. I am not quite sure I follow the last part though.

Any further ideas how to get this to work?

Thanks!

 

[isotopes]

Any other thoughts on how to make this work?

Thanks!

 

[jdraw]

I just looked at you sample database --with no records it's difficult to test.
Traditional approach would have tblGroup with fields such as GroupID PK and GroupName.

Then relationships

tblContacts-->ContactIsInGroup<----Group

ContactIsInGroup is a junction table.

Your set up with GroupNames in the Contact record is problematic. If you add a new group, you have to change your records (add a field). If you delete a Group you have to manually change your combo value list, and change fields in the Contact records???