Criteria Form

  • Thread starter Thread starter kryszystof via AccessMonster.com
  • Start date Start date
K

kryszystof via AccessMonster.com

I was wondering what is the way i can have blank text fields in a form, and
have them filled out and act as criteria to opne another form. this will be
much easier for users to use instead of filters. I know it can be done, i
see it all the time, but iI can't remember how to do it. Please Help! TIA
 
Hi Kryszystof
I think you'ree thinking of building a WHERE clause to pass as the
WhereCondition argument when you open the form. E.g (if the table has
fields FirstName and LastName and the form has textboxes txtFirstName,
txtLastName) something like:

Dim strWhere As String

If Not IsNull(Me.txtFirstName.Value) Then
'add condition to match first name or initial
strWhere = "(FirstName LIKE """ & Me.txtFirstname.Value _
& "*"") AND "
End If

If Not IsNull(Me.txtLastName.Value) Then
'exact match for last name
strWhere = strWhere & "(LastName=""" & Me.txtLastName.Value _
& """) AND "
End If

'... and so on

'Remove superfluous AND
If Right(strWhere, 5) = " AND " Then
strWhere = Left(strWhere, Len(strWhere) - 5)
End If

DoCmd.OpenForm ... , strWhere, ...
 
Thanks,

I understand clearly the logic, but nothing happens when this code gets
executed. i have this driven by a button:

Private Sub Open_Criteria_Click()

Dim strWhere As String

If Not IsNull(Me.txtJob.Value) Then
strWhere = "([Job] LIKE """ & Me.txtJob.Value _
& "*"") AND "
End If

If Not IsNull(Me.txtStatus.Value) Then
strWhere = strWhere & "([Status]=""" & Me.txtStatus.Value _
& """) AND "
End If

If Right(strWhere, 5) = " AND " Then
strWhere = Left(strWhere, Len(strWhere) - 5)
End If

DoCmd.OpenForm frm_Main, acNormal, , stWhere

End Sub

what am i not doing right?
 
Back
Top