Help with an Error

[pfm721]

Can anyone see something wrong with this code?

I am getting an error @ the Me.RecordsetCole 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_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[VetSSN] = """ & Me.Combo0 & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub


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

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

Me.Combo0.Undo
strSQL = "INSERT INTO tblVeteran ( VetSSN )SELECT """ & NewData & """ AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[VetSSN] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.Combo0.Undo
Response = acDataErrContinue
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.recordsetclone 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)>