Modify Query Criteria Via Form (1 Viewer)

maximus76

New member
Local time
Today, 16:34
Joined
Oct 14, 2019
Messages
3
I have a form (Form_1) which has a number of combo boxes (combo1, combo2, combo3). As users make selections in the combo boxes I want to take the selected text from the combo box and add this to criteria in my Query (Query_1) to filter the results.

Form_1 will have a button which will launch a report (Report_1) which is based on Query_1 which should show results with the filtered criteria.

Struggling to work out how to do this :banghead:

Any help would be appreciated.

Cheers
Max
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:34
Joined
Oct 29, 2018
Messages
21,357
Hi Max. Welcome to AWF! Can you show us what you've tried so far? What you're trying to do sounds very doable. For example, you can use the WhereCondition argument of the OpenReport method to apply the criteria when opening your report.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:34
Joined
May 7, 2009
Messages
19,169
replace fieldN with the name of your field:
Code:
Private Sub buttonName_Click()
Dim sWhere As Variant
sWhere = Null
If [combo1].ListIndex > -1 Then
    sWhere = "Field1 = '" & [combo] & "'"
End If
If [combo2].ListIndex > -1 Then
    sWhere = (sWhere + " And ") & "Field2 ='" & [combo2] & "'"
End If
If [combo3].ListIndex > -1 Then
    sWhere = (SWhere + " And ") & "Field3 ='" & [combo3] & "'"
End If
If IsNull(sWhere) Then sWhere = "(1=1)"
DoCmd.OpenReport "Report_1",acViewPreview,, sWhere
End Sub
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:34
Joined
May 21, 2018
Messages
8,463
I basically do the same as Arnelgp. I usually add a way to change my search from and AND to an OR. I usually make it a function so it can be called externally, or you can pass the string as Arnelgp did.

Code:
Public Function GetFilter() As String
  'need variable for each partial filter
  Dim strName As String
  Dim strDate As String
  Dim strState As String
  Dim strCountry As String
  'Need final filter
  Dim strFilter As String
  Dim AndOr As String
  
  'Optional Code for 'and or'. If not remove code for this.
  Select Case Me.frmAndOr
    Case 1
      AndOr = " AND "
    Case 2
      AndOr = " OR "
  End Select
  
  'need code for each combo. Just copy and past. Need to handle dates and strings
  If Not Trim(Me.cmboName & " ") = "" Then
    strName = "Full_Name = '" & Me.cmboName & "'" & AndOr
  End If
  If IsDate(Me.cmboDate) Then
    strDate = "Created_Date = #" & Format(Me.cmboDate, "MM/DD/YYYY") & "#" & AndOr
  End If
  If Not Trim(Me.cmboState & " ") = "" Then
    strState = "State = '" & Me.cmboState & "'" & AndOr
  End If
  If Not Trim(Me.cmboCountry & " ") = "" Then
    strCountry = "Country = '" & Me.cmboCountry & "'" & AndOr
  End If
  
  'Use plus to avoid the nulls
  strFilter = strName + strDate + strState + strCountry
  'Get rid of last And or
  If Not strFilter = "" Then
    strFilter = Left(strFilter, Len(strFilter) - Len(AndOr))
  End If
  GetFilter = strFilter
End Function
 

Attachments

  • MajP SimpleSearch.accdb
    636 KB · Views: 103

maximus76

New member
Local time
Today, 16:34
Joined
Oct 14, 2019
Messages
3
Thank you arnelgp and MajP, I greatly appreciate this. it works perfectly.
 

Users who are viewing this thread

Top Bottom