RecordSetClone Error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AKphidelt - 23 Jun 2007 20:32 GMT

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!

Reply to this Message

AKphidelt - 23 Jun 2007 20:50 GMT

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?

> 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
[quoted text clipped - 23 lines]
>
> If anyone has any suggestions that would be awesome. Thanks a lot!

Reply to this Message

Marshall Barton - 23 Jun 2007 23:53 GMT

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

Signature

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
[quoted text clipped - 31 lines]
>>
>> rst.FindFirst Response

Reply to this Message

Ken Snell (MVP) - 23 Jun 2007 20:54 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

> Hello all... im trying to get to my RecordSetClone of a form, so if
> someone
[quoted text clipped - 26 lines]
>
> If anyone has any suggestions that would be awesome. Thanks a lot!

Reply to this Message