Solved DoCmd.OpenForm with a plethora of criteria, how to manage it?

[stumpf]

Hi!

I have an unbound "search form" with controls, some of which are: Combo boxes (text data) for gender, profession and language, text boxes (numeric data) for upper and lower age limit. The user selects some, all or none of the criteria and then the opening other form should show records based on a query generated by the choices.
The DoCmd.OpenForm Where criteria with reference to combo box gives the wanted result as long as the combo box field is not empty. Problems start when it is empty.

I'm thinking of maybe having a block of Cases with all the permutations but that gives me (if I only had those 3 combo boxes) 7 different where criteria.
I figured the short way could possibly be assigning string variables a value according to each combo box and then concatenating the where criteria. That way I should end up with only one criteria which is built of the values of those combo boxes. This is where the single and double quotes haunt me to grave. I just do not get it right.

I might be totally lost here. So if I am, please show me to the right path or give another angle.

I have tried a bunch of things but this is what I'm trying to build now:

If Me.cboProfession = "" Then
            strWhereProfession = "LIKE '*'"
        Else: strWhereProfession = "Profession = me.cboProfession"
        End If

My idea is to have a similar if block for every combo box and to concatenate the values with something like this (I know this is not even close to correct syntax):
strWhere = strWhereGender & strWhereProfession & strWhereLanguage &

DoCmd.OpenForm "tempFrm", acNormal, , strWhere, acFormReadOnly, acWindowNormal

Whatever version I've tried, I haven't got it right (except for only strWhereAge. I guess it is because of numeric values). So what to try next?

 

[Ranman256]

you can filter before you open the form, or in the form:

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string 

sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

If sWhere = "1=1" Then   'no filters
  docmd.openform "fMyForm"
Else
  docmd.openform "fMyForm",,,sWhere
End If

end sub

 

[arnelgp]

Private Sub button1_Click()

    Dim strWhere As String
    strWhere = vbNullString
    '
    'Note: just add " And " to the end of the criteria
    'and later we will deal with it
    '
    If Me!cboGender.ListIndex > -1 Then
        strWhere = strWhere & "[Gender] = '" & Me!cboGender & "' And "
    End If
    If Me!cboProfession.ListIndex > -1 Then
        strWhere = strWhere & "[Profession] = '" & Me!cboProfession & "' And "
    End If
    If Me!cboLanguage.ListIndex > -1 Then
        strWhere = strWhere & "[Language] = '" & Me!cboLanguage & "' And "
    End If
    If Len(Me!txtLowAge & vbNullString) > 0 Then
        strWhere = strWhere & "[Age] >= " & Me!txtLowAge & " And "
    End If
    If Len(Me!txtHighAge & vbNullString) > 0 Then
        strWhere = strWhere & "[Age] <= " & Me!txtHighAge & " And "
    End If
    '
    ' other conditions
    ' put below
    '
    ' ...
    ' ...
    ' now test our string
    If Len(strWhere) > 0 Then
        'remove the last "And"
        strWhere = Left$(strWhere, Len(strWhere) - 5)
    End If
    
    'check if the form is already open
    If SysCmd(acSysCmdGetObjectState, acForm, "tempFrm") <> 0 Then
        'close the form
        DoCmd.Close acForm, "tempFrm", acSaveNo
    End If
    'open the form (again?)
    DoCmd.OpenForm "tempFrm", acNormal, , strWhere, acFormReadOnly, acWindowNormal

End Sub

 

[stumpf]

you can filter before you open the form, or in the form:

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string

sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

If sWhere = "1=1" Then   'no filters
  docmd.openform "fMyForm"
Else
  docmd.openform "fMyForm",,,sWhere
End If

end sub

you can filter before you open the form, or in the form:

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string

sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

If sWhere = "1=1" Then   'no filters
  docmd.openform "fMyForm"
Else
  docmd.openform "fMyForm",,,sWhere
End If

end sub

This looks very efficient. Please elaborate why does sWhere get the value "1=1"?

 

[stumpf]

Private Sub button1_Click()

    Dim strWhere As String
    strWhere = vbNullString
    '
    'Note: just add " And " to the end of the criteria
    'and later we will deal with it
    '
    If Me!cboGender.ListIndex > -1 Then
        strWhere = strWhere & "[Gender] = '" & Me!cboGender & "' And "
    End If
    If Me!cboProfession.ListIndex > -1 Then
        strWhere = strWhere & "[Profession] = '" & Me!cboProfession & "' And "
    End If
    If Me!cboLanguage.ListIndex > -1 Then
        strWhere = strWhere & "[Language] = '" & Me!cboLanguage & "' And "
    End If
    If Len(Me!txtLowAge & vbNullString) > 0 Then
        strWhere = strWhere & "[Age] >= " & Me!txtLowAge & " And "
    End If
    If Len(Me!txtHighAge & vbNullString) > 0 Then
        strWhere = strWhere & "[Age] <= " & Me!txtHighAge & " And "
    End If
    '
    ' other conditions
    ' put below
    '
    ' ...
    ' ...
    ' now test our string
    If Len(strWhere) > 0 Then
        'remove the last "And"
        strWhere = Left$(strWhere, Len(strWhere) - 5)
    End If
   
    'check if the form is already open
    If SysCmd(acSysCmdGetObjectState, acForm, "tempFrm") <> 0 Then
        'close the form
        DoCmd.Close acForm, "tempFrm", acSaveNo
    End If
    'open the form (again?)
    DoCmd.OpenForm "tempFrm", acNormal, , strWhere, acFormReadOnly, acWindowNormal

End Sub

This is exactly what I'm looking for. Unbelievable. Million thanks!

 

[Uncle Gizmo]

Private Sub button1_Click()

    Dim strWhere As String
    strWhere = vbNullString
    '
    'Note: just add " And " to the end of the criteria
    'and later we will deal with it
    '
    If Me!cboGender.ListIndex > -1 Then
        strWhere = strWhere & "[Gender] = '" & Me!cboGender & "' And "
    End If
    If Me!cboProfession.ListIndex > -1 Then
        strWhere = strWhere & "[Profession] = '" & Me!cboProfession & "' And "
    End If
    If Me!cboLanguage.ListIndex > -1 Then
        strWhere = strWhere & "[Language] = '" & Me!cboLanguage & "' And "
    End If
    If Len(Me!txtLowAge & vbNullString) > 0 Then
        strWhere = strWhere & "[Age] >= " & Me!txtLowAge & " And "
    End If
    If Len(Me!txtHighAge & vbNullString) > 0 Then
        strWhere = strWhere & "[Age] <= " & Me!txtHighAge & " And "
    End If
    '
    ' other conditions
    ' put below
    '
    ' ...
    ' ...
    ' now test our string
    If Len(strWhere) > 0 Then
        'remove the last "And"
        strWhere = Left$(strWhere, Len(strWhere) - 5)
    End If
   
    'check if the form is already open
    If SysCmd(acSysCmdGetObjectState, acForm, "tempFrm") <> 0 Then
        'close the form
        DoCmd.Close acForm, "tempFrm", acSaveNo
    End If
    'open the form (again?)
    DoCmd.OpenForm "tempFrm", acNormal, , strWhere, acFormReadOnly, acWindowNormal

End Sub

Thanks for this ArnelGP. I should be able to use that -1 list index idea in my Search Form product with combo boxes, efficiently.