Multiple Criteria In FindFirst

  • Thread starter Thread starter JeffG
  • Start date Start date
J

JeffG

Hello all-

If have a form that filters a recordset based on two drop down boxes.
Here's the problem...The first drop down box is Country. The second is
Year. When I select the Country, it selects the Country. When I select any
other Year in the Year dropdown box, it goes back to the first item in the
Country set with the first year. What I thought I could do is use multiple
criteria in the rs.findfirst, but I am going nuts trying to figure it out.
Here's the sub in its entiretly...

Private Sub Year_Lookup_AfterUpdate()
Set rs = Me.Recordset.Clone
rs.FindFirst "[Country] = '" & Me![Country_Lookup] & "'"
rs.FindFirst "[Year] = " & Me![Year_Lookup]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

What I want to do is to be able to filter the recordset down to the Country
and if the Country has multiple years in the table, select the country and
the year to input the data. For example, in my table, there is Argentina,
year 2005, Bahamas, year 2005, Japan, years 2005 and 2006. I want to be
able to select Japan, year 2006 so that I can enter the data.

Any help would be greatly appreciated.

JG
 
This may not be exactly what you're looking for, but the way I've always
handled a "multiple filtering" is to base my recordset call on it.
e.g.

rs = dbs.openrecordset("Select * from
where ([field 1] ='" &
string_condition & "' and [field 2] =#" & date_condition & "#)")

Hope that helps (or at least rules out one possiblility!)
 
JeffG said:
If have a form that filters a recordset based on two drop down boxes.
Here's the problem...The first drop down box is Country. The second is
Year. When I select the Country, it selects the Country. When I select any
other Year in the Year dropdown box, it goes back to the first item in the
Country set with the first year. What I thought I could do is use multiple
criteria in the rs.findfirst, but I am going nuts trying to figure it out.
Here's the sub in its entiretly...

Private Sub Year_Lookup_AfterUpdate()
Set rs = Me.Recordset.Clone
rs.FindFirst "[Country] = '" & Me![Country_Lookup] & "'"
rs.FindFirst "[Year] = " & Me![Year_Lookup]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

What I want to do is to be able to filter the recordset down to the Country
and if the Country has multiple years in the table, select the country and
the year to input the data. For example, in my table, there is Argentina,
year 2005, Bahamas, year 2005, Japan, years 2005 and 2006. I want to be
able to select Japan, year 2006 so that I can enter the data.
Click to expand...


FindFirst accepts ,ultiple conditions by combining them with
AND and/or OR

rs.FindFirst "[Country] = '" & Me![Country_Lookup] _
& "' AND [Year] = " & Me![Year_Lookup]
 
I want to be
able to select Japan, year 2006 so that I can enter the data.
Click to expand...

The argument to FindFirst is just a valid SQL WHERE clause without the
word WHERE - so it can include multiple AND/OR expressions, anything
which could be used in a query. Frex

rs.FindFirst "[Country] = '" & Me![Country_Lookup] & "' AND " _
& "[Year] = " & Me![Year_Lookup]

Depending on the values in the form controls, this could evaluate to

[Country] = 'Japan' AND [Year] = 2006

John W. Vinson[MVP]
 
That did it! I knew it was something easy, but couldn't figure it out.

The help is greatly appreciated!

Jeff G
John Vinson said:
I want to be
able to select Japan, year 2006 so that I can enter the data.
Click to expand...

The argument to FindFirst is just a valid SQL WHERE clause without the
word WHERE - so it can include multiple AND/OR expressions, anything
which could be used in a query. Frex

rs.FindFirst "[Country] = '" & Me![Country_Lookup] & "' AND " _
& "[Year] = " & Me![Year_Lookup]

Depending on the values in the form controls, this could evaluate to

[Country] = 'Japan' AND [Year] = 2006

John W. Vinson[MVP]
Click to expand...
 
This helped me out too!
Debbie

| On Wed, 9 Mar 2005 16:35:34 -0500, "JeffG"
|
| >I want to be
| >able to select Japan, year 2006 so that I can enter the data.
|
| The argument to FindFirst is just a valid SQL WHERE clause without the
| word WHERE - so it can include multiple AND/OR expressions, anything
| which could be used in a query. Frex
|
| rs.FindFirst "[Country] = '" & Me![Country_Lookup] & "' AND " _
| & "[Year] = " & Me![Year_Lookup]
|
| Depending on the values in the form controls, this could evaluate to
|
| [Country] = 'Japan' AND [Year] = 2006
|
| John W. Vinson[MVP]
|
 
Back
Top