Access form overwriting table entry

Question

I have created a table that has two forms based off of it - one form is for data entry, the other one is for recalling the data and potentially editing the entry. The problem occurs with the second form when I call up the data via a combo box; it will populate all of the fields just fine the first time, but if I try to successively use the box to pull up additional entries it will overwrite the fields of the other entries with the data from the first entry.

Here is the code I am using for the combo box in the form:

Private Sub EmployeeID_AfterUpdate()

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone

    rst.FindFirst "EmployeeID = " & Me!EmployeeID

    Me.Bookmark = rst.Bookmark

    Forms!frm_HourlyRatingEdit!Position = DLookup("Position", "dbo_Employees", "EmployeeID = Forms!frm_HourlyRatingEdit!EmployeeID")

    Forms!frm_HourlyRatingEdit!txtFirstName = DLookup("FirstName", "dbo_Employees", "EmployeeID = Forms!frm_HourlyRatingEdit!EmployeeID")

    Forms!frm_HourlyRatingEdit!txtLastName = DLookup("LastName", "dbo_Employees", "EmployeeID = Forms!frm_HourlyRatingEdit!EmployeeID")

    Forms!frm_HourlyRatingEdit!Department = DLookup("Dept", "dbo_Employees", "EmployeeID = Forms!frm_HourlyRatingEdit!EmployeeID")

    Forms!frm_HourlyRatingEdit!DateHire = DLookup("HireDate", "dbo_Employees", "EmployeeID = Forms!frm_HourlyRatingEdit!EmployeeID")

End Sub

What are some possible ways of fixing this?

Answer 1

The code snippet:

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone

    rst.FindFirst "EmployeeID = " & Me!EmployeeID

    Me.Bookmark = rst.Bookmark

is venerable code. Though now the Search combo generate an embedded macro, I have used that code snippet in countless applications.

Did you make the Search combo bound? It should not be. and that could cause that error.

Answer 2

You might like to take a look at the file FindRecord.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo includes two forms, one to go to a record, the other to filter the form to the selected record(s).  The first operates in the same way as yours, by synchronizing bookmarks.  In this form you'll see that the combo box to go to a record is unbound, i.e. has an empty ControlSource property, while the first and last name controls on the form are bound.  You need to replicate this sort of set-up in your own form.

Answer 3

To be totally honest I'm really not sure if it's bound or not. There is a line for the EmployeeID combo box preferences that says "Bound Column: 1". Should that be changed?

When I debug the error code is points specifically to the "Me.bookmark = rst.Bookmark" line of code.

Answer 4

Scott,

Thanks for the tips. I am actually trying out your suggestion, but now I have run into another problem -

Run-time error '3022':

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

I get the funny feeling that the form is trying to write to the database as soon as I recall the data and I'm not sure why.

Answer 5

You are using a Bound form with Unbound techniques. The RecordsetClone method only works with a bound form. So when you use

Me.Bookmark = rst.Bookmark

you place the record pointer to the record that matches the employeeID selected. Then when you populated the controls with your DLookups, you are doing the same thing the Bookmark does, but if the values are different in the dbo_Employees table as the forms Recordsource, then they will be overwritten to the form's Recordsource. Its also not clear from your code whether Forms!frm_HourlyRatingEdit!EmployeeID is the same control you are using the select an employee.

Bottom line, if you are using a search combo to select a record, then it is redundant to populate controls on the form.