Access 2007 Combo Box Search

[Matthew Scheperle]

I have two combo boxes on a form. I want to be able to select Status such as
OPEN from one box and a year from the next combo box. Then click on Search
and pull up all records based on one or both selections. The results would
display in the detail section of the form. I can do this with the
DoCmd.ApplyFilter method in VBA for one box. However, how do I get it to
search by both criteria?

Example: This works BTW in a Private Sub event procedure...
If Me.year = "2009" Then
DoCmd.ApplyFilter , "[qryfrmViewAllRecords].[year]='2009'"

I then want a second box for Status so that they could find all OPEN,
CLOSED, or simply ALL status for 2009. I am trying to think of how I would
declare a variable, or combine the two "Where" clauses in VBA.

Thanks,

 

[Jeanette Cunningham]

There is an excellent sample database for searching on this website.

http://www.allenbrowne.com//ser-62.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Matthew Scheperle]

Jeanette,

I used the code from Allen Browne's website and have the two combo boxes
working however I have one more question to add on now. I use the code as
follows

Private Sub search_Click()
Dim strWhere As String
Dim lnglen As Long
If Not IsNull(Me.combostatus) Then
strWhere = strWhere & "([Status] = """ & Me!combostatus & """) AND "
End If
If Not IsNull(Me.year) Then
strWhere = strWhere & "([LegYear] = """ & Me!year & """)"
End If
Me.Filter = strWhere
Me.FilterOn = True
End Sub

What happens if I want them to have the choice of ALL so that they could
choose ALL of the Status field and one year. Or ALL of both? It also does not
always work if one is left blank however that I could probably fix with a
validation rule...

Thanks,

--
Matt Scheperle
(e-mail address removed)

There is an excellent sample database for searching on this website.

http://www.allenbrowne.com//ser-62.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have two combo boxes on a form. I want to be able to select Status such
as
OPEN from one box and a year from the next combo box. Then click on Search
and pull up all records based on one or both selections. The results would
display in the detail section of the form. I can do this with the
DoCmd.ApplyFilter method in VBA for one box. However, how do I get it to
search by both criteria?

Example: This works BTW in a Private Sub event procedure...
If Me.year = "2009" Then
DoCmd.ApplyFilter , "[qryfrmViewAllRecords].[year]='2009'"

I then want a second box for Status so that they could find all OPEN,
CLOSED, or simply ALL status for 2009. I am trying to think of how I would
declare a variable, or combine the two "Where" clauses in VBA.

Thanks,

.

 

[Jeanette Cunningham]

If you follow Allen's code more closely in building the where clause,
including the " AND " with every
If Not IsNull ....strWhere and using his code to chop off the trailing "
AND " at the end, you will find that it will always work when a combo is
blank.

If you want to include "all", you can make a union query for the combo, and
change the If Not IsNull .... strWhere like this
If Not IsNull(Me.combostatus) Then
If Not Me.combostatus <> "All" Then
strWhere = strWhere & "([Status] = """ & Me!combostatus & """) AND "
End If
End If



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette,

I used the code from Allen Browne's website and have the two combo boxes
working however I have one more question to add on now. I use the code as
follows

Private Sub search_Click()
Dim strWhere As String
Dim lnglen As Long
If Not IsNull(Me.combostatus) Then
strWhere = strWhere & "([Status] = """ & Me!combostatus & """) AND "
End If
If Not IsNull(Me.year) Then
strWhere = strWhere & "([LegYear] = """ & Me!year & """)"
End If
Me.Filter = strWhere
Me.FilterOn = True
End Sub

What happens if I want them to have the choice of ALL so that they could
choose ALL of the Status field and one year. Or ALL of both? It also does
not
always work if one is left blank however that I could probably fix with a
validation rule...

Thanks,

--
Matt Scheperle
(e-mail address removed)

There is an excellent sample database for searching on this website.

http://www.allenbrowne.com//ser-62.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have two combo boxes on a form. I want to be able to select Status
such
as
OPEN from one box and a year from the next combo box. Then click on
Search
and pull up all records based on one or both selections. The results
would
display in the detail section of the form. I can do this with the
DoCmd.ApplyFilter method in VBA for one box. However, how do I get it
to
search by both criteria?

Example: This works BTW in a Private Sub event procedure...
If Me.year = "2009" Then
DoCmd.ApplyFilter , "[qryfrmViewAllRecords].[year]='2009'"

I then want a second box for Status so that they could find all OPEN,
CLOSED, or simply ALL status for 2009. I am trying to think of how I
would
declare a variable, or combine the two "Where" clauses in VBA.

Thanks,

.