Search function with two combos and "and"

  • Thread starter Thread starter Opal
  • Start date Start date
O

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.
 
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
 
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.
Click to expand...
Initially I used:
Click to expand...
Private Sub cboMove1_AfterUpdate()
Dim rs As DAO.Recordset
Click to expand...
   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
Click to expand...
for my first combo box and the same for my second except:
Click to expand...
rs.FindFirst "[CassetteNoID] = " & Nz(Me![cboMove2], 0)
Click to expand...
Both boxes work fine on their own, but when I combine then
as follows:
Click to expand...
rs.FindFirst "[CassetteID] = " & Me.cboMove1 _
              And "[CassetteNoID] = " & Nz(Me![cboMove2], 0)
Click to expand...
I get type mismatch errors and I am stumped as to how to resolve.
Click to expand...
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 -
Click to expand...

- Show quoted text -
Click to expand...

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.
 
Back
Top