Search function with two combos and "and"

[Opal]

I am using two combo boxes so that the user can search a large
recordset.

Initially I used:

Private Sub cboMove1_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMove1) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[CassetteID] = " & Me.cboMove1 _
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
Me.Refresh
End Sub

for my first combo box and the same for my second except:

rs.FindFirst "[CassetteNoID] = " & Nz(Me![cboMove2], 0)

Both boxes work fine on their own, but when I combine then
as follows:

rs.FindFirst "[CassetteID] = " & Me.cboMove1 _
And "[CassetteNoID] = " & Nz(Me![cboMove2], 0)

I get type mismatch errors and I am stumped as to how to resolve.

BTW, there are over 2800 records to search, so I need simple way
for the user to search. Any help would be appreciated.

 

[Douglas J. Steele]

rs.FindFirst "[CassetteID] = " & Me.cboMove1 & _
" And [CassetteNoID] = " & Nz(Me![cboMove2], 0)

Note, though, that if nothing's been selected in cboMove2, you're going to
be looking for CassetteNoID 0. Is that what you want? If what you want is to
select records regardless of what the value of CassetteNoID is when
nothing's selected in the combo, use

If IsNull(Me![cboMove2]) = True Then
rs.FindFirst "[CassetteID] = " & Me.cboMove1
Else
rs.FindFirst "[CassetteID] = " & Me.cboMove1 & _
" And [CassetteNoID] = " & Me![cboMove2]
End If

 

[Opal]

rs.FindFirst "[CassetteID] = " & Me.cboMove1 & _
        " And [CassetteNoID] = " & Nz(Me![cboMove2], 0)

Note, though, that if nothing's been selected in cboMove2, you're going to
be looking for CassetteNoID 0. Is that what you want? If what you want is to
select records regardless of what the value of CassetteNoID is when
nothing's selected in the combo, use

  If IsNull(Me![cboMove2]) = True Then
    rs.FindFirst "[CassetteID] = " & Me.cboMove1
  Else
    rs.FindFirst "[CassetteID] = " & Me.cboMove1 & _
       " And [CassetteNoID] = " & Me![cboMove2]
  End If

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

I am using two combo boxes so that the user can search a large
recordset.

Initially I used:

Private Sub cboMove1_AfterUpdate()
Dim rs As DAO.Recordset

   If Not IsNull(Me.cboMove1) Then
       'Save before move.
       If Me.Dirty Then
           Me.Dirty = False
       End If
       'Search in the clone set.
       Set rs = Me.RecordsetClone
       rs.FindFirst "[CassetteID] = " & Me.cboMove1 _
       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
   Me.Refresh
End Sub

for my first combo box and the same for my second except:

rs.FindFirst "[CassetteNoID] = " & Nz(Me![cboMove2], 0)

Both boxes work fine on their own, but when I combine then
as follows:

rs.FindFirst "[CassetteID] = " & Me.cboMove1 _
              And "[CassetteNoID] = " & Nz(Me![cboMove2], 0)

I get type mismatch errors and I am stumped as to how to resolve.

BTW, there are over 2800 records to search, so I need simple way
for the user to search.  Any help would be appreciated.- Hide quoted text -

- Show quoted text -

Thanks, Doug, that got rid of the type mismatch error. At first I
only applied
the change to cboMove1 but now that I have it in both they appear to
be in
sync. I was worried that would be my next problem. Your help has
been
appreciated.