Lookup Record in Form

[Guest_imported]

I have an index set on two fields in my database, SLIC and DATE. Therefore for every SLIC entry there can only be one date. On my form, the first two fields they enter are the SLIC and DATE. If they enter a SLIC and DATE that already exist I want it to pull the record and display it for editing etc. I'm using the On Exit event handler. This is the code I have so far.

I know that it is finding the record after trial and error, however I'm getting the following error:

"Microsoft Access Run-time Error '2105':
You can't go to the specified record.

You may be at the end of a recordset."

Here is my code:

Private Sub DATE_Exit(Cancel As Integer)
Dim db As DAO.Database, rst As DAO.Recordset
Dim strSearchName As String

Set rst = Me.RecordsetClone
strSearchName = "[SLIC] = " & Me![SLIC] & "AND [DATE] = #" & Me![DATE] & "#"

rst.FindFirst strSearchName
If rst.NoMatch Then
Exit Sub
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close
End Sub

 

[KyleS]

Hi,
Do you have a unique identifier (eg Primary Key) on this table? If so, instead of Me.Bookmark = rst.Bookmark try DoCmd.GoToRecord , , acGoTo, rst!UNIQUEIDFIELD

Kyle

 

[Guest_imported]

I just tried that. No luck, I'm still getting that error 2105. I made sure that on my form you can edit/add records because I thought that might be the problem. This is confusing me...

 

[KyleS]

Ok,
Is your form bound to the table of are you using an unbound form and recordsets?

I would assume it's bound, since you're using Me.RecordsetClone, but you gotta ask...

So, with that assumption I assume you're also having the form open up to "NewRecord" right?. So we need to ditch the "New Record" which was created as soon as the user typed into the fileds and then use GoTo

Out of curiosity, try this...

Me.Undo
DoCmd.GoToRecord , , acGoTo, rst!UNIQUEIDFIELD

Kyle :

 

[Guest_imported]

BWAKAKAKKAK. Thank you so much. That worked.