RecordSetClone Error

[Guest]

Hello all... im trying to get to my RecordSetClone of a form, so if someone
is looking for a certain record it can show up on this form without having to
do tons of Dlookups and stuff. I did a little research on RecordSetClone but
still can't get it to work... here is my current code

Sub rsc()
Dim rst As DAO.Recordset
Dim Response As String

Response = InputBox("Enter the Report Number")
Set rst = Forms![frmIncident].RecordsetClone

rst.FindFirst Response

If rst.NoMatch Then
MsgBox "NO entry found", vbInformation
Else
Forms![frmIncident].Bookmark = rst.Bookmark
End If

End Sub

I get the Runtime Error 3001 "Invalid Argument" on this line

rst.FindFirst Response

If anyone has any suggestions that would be awesome. Thanks a lot!

 

[Guest]

Alright, after some more research I figured out that my FindFirst Response
was a problem because it didn't verify what field to look in. So I changed it
to

rst.FindFirst "[Record Number]= Response"

However, now I get an error message that says Run-time error '3070'- The
microsoft Jet database engine does not recognize 'Response' as a valid field
name or expression.

Any help?

 

[Ken Snell \(MVP\)]

You must give a "WHERE" clause (without the WHERE word) for your Respone
variable's value. And I've simplified the code for you. And I've assumed
that the field on which you're searching is ReportNum (change it to its real
name if that is not the correct name) and that the field is numeric (not
text):

Sub rsc()
Dim Response As String
Response = "ReportNum=" & InputBox("Enter the Report Number")
With Forms![frmIncident].RecordsetClone
.FindFirst Response
If .NoMatch Then
MsgBox "NO entry found", vbInformation
Else
Forms![frmIncident].Bookmark = .Bookmark
End If
End With
End Sub

 

[Marshall Barton]

First, you should check that the user actually entered a
number. Then you can check for a matching record this way:

Response = InputBox("Enter the Report Number")
If Repsonse Like "*[!0-9]*" Or Repsonse = "" Then
MsgBox "Invalid Report Number", vbInformation
Else
WIth Forms![frmIncident].RecordsetClone
.FindFirst "[Record Number]=" & Response
If .NoMatch Then
MsgBox "NO entry found", vbInformation
Else
Forms![frmIncident].Bookmark = .Bookmark
End If
End With
End If
End Sub
--
Marsh
MVP [MS Access]

Alright, after some more research I figured out that my FindFirst Response
was a problem because it didn't verify what field to look in. So I changed it
to

rst.FindFirst "[Record Number]= Response"

However, now I get an error message that says Run-time error '3070'- The
microsoft Jet database engine does not recognize 'Response' as a valid field
name or expression.

Hello all... im trying to get to my RecordSetClone of a form, so if someone
is looking for a certain record it can show up on this form without having to
do tons of Dlookups and stuff. I did a little research on RecordSetClone but
still can't get it to work... here is my current code

Sub rsc()
Dim rst As DAO.Recordset
Dim Response As String

Response = InputBox("Enter the Report Number")
Set rst = Forms![frmIncident].RecordsetClone

rst.FindFirst Response

If rst.NoMatch Then
MsgBox "NO entry found", vbInformation
Else
Forms![frmIncident].Bookmark = rst.Bookmark
End If

End Sub

I get the Runtime Error 3001 "Invalid Argument" on this line

rst.FindFirst Response