RecordSetClone Error

A

AKphidelt

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!
 
A

AKphidelt

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?
 
K

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
 
M

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.


AKphidelt said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top