"tweaking" an Allen Browne Tip w/combo boxes

[Guest]

The following code is adapted from Allen Browne’s “Using a Combo Box to Find
Records†Tip.

Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[DistrictID] = " & Me.cboMoveTo
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

It’s working fine and I understand it. But, I want to add a second variable
to look for. In other words, I want to “find the first record where the
DistrictID = Me.cboMoveTo AND the SeasonID = Me.cboSeasonPicker.â€

I’m stuck on how and where to add that second search string. I’ve tried
adding it at the end of the rs.FindFirst line, but it doesn’t accept it.

Anyone help me?

 

[Brian Bastl]

Will this work?

Dim rs As DAO.Recordset
Dim stWhat as String

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If

stWhat = "[DistrictID]=" & Me.cboMoveTo
stWhat = stWhat & " And "
stWhat = stWhat & "[SeasonID]=" & Me.cboSeasonPicker

'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst stWhat
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

HTH,
Brian

 

[Guest]

Sorry, didn't work. I get this error: "The Microsoft Jet database engine
does not recognize 'Fall' as a valid field name or expression." (If I pick
Spring as the season, I get the same error message only it says "Spring"
instead of Fall.)

Next idea?

Jerry

Will this work?

Dim rs As DAO.Recordset
Dim stWhat as String

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If

stWhat = "[DistrictID]=" & Me.cboMoveTo
stWhat = stWhat & " And "
stWhat = stWhat & "[SeasonID]=" & Me.cboSeasonPicker

'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst stWhat
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

HTH,
Brian

The following code is adapted from Allen Browne's "Using a Combo Box to Find
Records" Tip.

Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[DistrictID] = " & Me.cboMoveTo
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

It's working fine and I understand it. But, I want to add a second variable
to look for. In other words, I want to "find the first record where the
DistrictID = Me.cboMoveTo AND the SeasonID = Me.cboSeasonPicker."

I'm stuck on how and where to add that second search string. I've tried
adding it at the end of the rs.FindFirst line, but it doesn't accept it.

Anyone help me?

 

[Brian Bastl]

In your last post, you stated that the following worked:
"[DistrictID]=" & Me.cboMoveTo

so, since we know that works, try changing the last line in stWhat from
stWhat = stWhat & "[SeasonID]=" & Me.cboSeasonPicker
to:
stWhat = stWhat & "[SeasonID]='" & Me.cboSeasonPicker & "'"

See if that works.
Brian

 

[Guest]

That did it, Brian! Thanks a bunch!

Jerry