FindRecord Multiple Fields

[HomeGrowth]

I use a combo with 3 fields. upon user selected his/her choice, it go out and find the record. I used the codes below but return an error message 'Type mismatch (Error 13)'. All 3 fields are text datatype.

' Find the record that matches the control.
Dim rs As Object
Dim strRev As String
Dim strStatus As String

strRev = Me![cmbSOPList].Column(1)
strStatus = Me![cmbSOPList].Column(2)

Set rs = Me.Recordset.Clone
rs.FindRecord "[Doc Number] = '" & Me![cmbSOPList] & "'" And "[Rev] = " & strRev And "[Status] = " & strStatus
Me.Bookmark = rs.Bookmark

Can someone shed some lights on this??

 

[RoyVidar]

If all fields are text, they will all need text delimiters (single quotes), also have a look at the concatenation, the .findfirst would be the same as an sql where clause, without the keyword where.

[tt]rs.FindRecord "[Doc Number] = '" & Me![cmbSOPList] & "' And [Rev] = '" & strRev & "' And [Status] = '" & strStatus & "'"[/tt]

I'm also a fan of testing for match:

[tt]if not rs.nomatch then me.bookmark=rs.bookmark[/tt]

Roy-Vidar

 

[randy700]

First -- change this...
Set rs = Me.Recordset.Clone
to this...
Set rs = Me.RecordsetClone



Randy

 

[HomeGrowth]

Roy-Vidar and Randy

Thank you for your helps!

It works fine with FindFirst method but not FindRecord.

So I changed to

rs.FindFirst "[Doc Number] = '" & Me![cmbSOPList] & "' And [Rev] = '" & strRev & "' And [Status] = '" & strStatus & "'"

For some reasons, it doesn't matter if I use
Set rs = Me.Recordset.Clone
or
Set rs = Me.RecordsetClone

I keep the original Set rs = Me.Recordset.Clone

Thanks again, it is a great help!

 

[FancyPrairie]

I don't believe rst.FindRecord is a valid method. Rather it's Docmd.FindRecord. And I believe it only works for the control that has focus.