Recordset.Clone - Type Mismatch

[PL01]

I get a type mismatch error when running the recordset.clone code below.

Both INVvar and VNvar are strings, but Invar is a number and VNvar is text

Is the "and" syntax correct for the rs.FindFirst line of code? If I delete everything after "and" I get no mismatch error. (rs.FindFirst "[Inventory #] = " & INVvar)

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Inventory #] = " & INVvar And "[Vendor Name] = " & VNvar
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

 

[fneily]

rs.FindFirst "[Inventory #] = " & INVvar & " And [Vendor Name] = " & Chr(34) & VNvar & Chr(34)

In INVvar and VNvar are textbox names, then change to Me![INVvar] and Me![VNvar]

 

[MajP]

Dim rs As Object
Set rs = Me.Recordset.Clone

Probably not the issue, but this is a poor declaration of a variable and can cause similar errors. Always be explicit.

me.recordset returns a DAO.recordset
therefore

dim rs as DAO.recordset
and never
dim rs as recordset

this is ambigous since there is a dao.recordset and an aodb.recordset and you will also get similar errors.

 

[PHV]

My preferred way:

rs.FindFirst "[Inventory #]=" & INVvar & "AND [Vendor Name]='" & Replace(VNvar, "'", "''") & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, sorry for the typo:

rs.FindFirst "[Inventory #]=" & INVvar & " AND [Vendor Name]='" & Replace(VNvar, "'", "''") & "'"

 

[PL01]

This is all way beyond me but...thank you once again Tek Tips Guy!