Help with an Error

[pfm721]

Can anyone see something wrong with this code?

I am getting an error @ the Me.RecordsetCol e line in the AfterUpdate event.

Run-time error 7951

You entered an expression that has an invalid reference to the RecordsetClone property.

Private Sub Combo0_AfterUpd ate()
With Me.RecordsetClo ne
.FindFirst "[VetSSN] = """ & Me.Combo0 & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub


Private Sub Combo0_NotInLis t(NewData As String, Response As Integer)
Dim strSQL As String

If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton 2, "Not Found") = vbYes Then

Me.Combo0.Undo
strSQL = "INSERT INTO tblVeteran ( VetSSN )SELECT """ & NewData & """ AS Dummy;"
CurrentDb.Execu te strSQL, dbFailOnError
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClo ne
.FindFirst "[VetSSN] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.Combo0.Undo
Response = acDataErrContin ue
End If
End Sub

Any ideas??

Thanks

[Stewart Ross]

Hi. There is no obvious error with what you are doing, in that your use of recordsetclone is valid.

I don't get a compile error when trying to replicate the With me.recordsetclo ne statement (using Access 2003).

As a double check, could you define an explicit recordset variable and refer to that instead? If you get the same compilation failure it would suggest you have a problem with the project library references - a missing reference for the Microsoft DAO or ADO object libraries (from VB editor select Tools menu, then References, and check that at least one of these two is ticked).

Dim RS as Recordset
set RS = Me.Recordsetclone
with RS
.findfirst ... etc
end with

When you define a variable of type recordset you will find that the editor will list the properties available when you use the dot separator. Type RS. to list the properties available - as long as the library reference is in place.

-Stewart

[missinglinq]

I think Stewart's on the right track! My reference material, which I think originated from Allen Browne, indicates that
Microsof DAO 3.6 Object Library has to be set in references (Tools - References) and you have to have the recordset dimmed specifically as

Dim db As DAO.Database
Dim rs As DAO.Recordset

Linq ;0)>