Cancel record insert and using bookmark

[karenpc]

The following code works fine until I set the bookmark, and I get the run-time error: 2108 - You must save the field before you execute the GoToControl, or setfocus method. Then it goes to the record if it exists. Can someone help me get rid of this error? Thanks very much for your help!!! Karen

Private Sub PatientID_BeforeUpdate(Cancel As Integer)
Dim rst As DAO.Recordset
Dim strSearch As String
Set rst = Me.RecordsetClone
strSearch = "[Patient ID] = """ & Forms!FrmPatientDetail![Patient ID] & """"

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

End Sub

 

[RickSpr]

It looks to me like you're trying to use the Patient ID control both to display the patient ID of the current record and to search for a record matching an entered patient ID. This won't work, because when the user keys data into a bound control, Access updates the underlying record. By trying to change records in the BeforeUpdate event, you're interfering with Access's attempt to update the current record.

There are two ways to solve this:

1. You can use a separate, unbound control into which the user can key a patient ID to perform a search. It's a good idea to place this control in the form header section in order to visually separate it from the other control. You would typically give it a label saying something like "Enter a patient ID to search for:". You should also consider clearing the control after the record is found, to avoid confusing the user. By the way, a combo box works very well for this purpose.

2. You can use the same unbound control to both search for a patient ID and to display (but not update) the current record data. This works well for data that the user shouldn't change, and I assume your patient ID is like this. Since the control is unbound, it would be empty by default, so you have to fill in the value for each record in the form's Current event. If you allow additions on the form, you may also have to save new values in the record, using the form's BeforeInsert event--but watch out: if the user has turned off confirmations (in Tools>Options) I don't think the BeforeInsert event is fired. Rick Sprague