MS access VBA update creating duplicate entries

Question

I tied this code to a command button in a MSAccess form. The purpose is to insert an update to an existing record and add a date to the TIME OUT field. Whenever I run this code block it updates the record but also creates a duplicate entry in the same table with the same unique ID. So it looks like this on the table side view.

It looks like this on the form:

I'm trying to understand why this happening. Just for some extra info. Here is my table structure:

Private Sub CheckOutBtn_Click()
    Dim rst As Recordset
    Dim employeeInt As Integer
    Dim strSQL As String
    employeeInt = Int(Me!EmployeeID)
    
    With CurrentDb.OpenRecordset("TimeTable")
    .Edit
    ![EmployeeID] = employeeInt
    ![TImeOut] = Now()
    .Update
    End With
    
    Me.Refresh

End Sub

Any help would be appreciated!

Like your last (deleted) question, this question only has half the info. Deleting questions that people have made effort on, not accepting answer that people have made effort on is generally considered ungrateful behaviour. — Nick.Mc, Dec 10 '20 at 00:12
I realized I messed up on trying to phrase it and set it up. I figured I would try again with a different angle. I am not trying to be ungrateful as you put it. I am EXTREMELY frustrated and stuck on this problem because there seems to be no reason its happening. I am not sure how to explain it any better or make things clearer to someone reading it. I am of course, happy to provide any additional information but I don't know what would help. — Cvrpapc, Dec 10 '20 at 00:17
It's still unclear. For example it would be helpful to label the screenshot of the table with what the actual table name is. Anyway I think the issue is that you have to use something like `.Find` to go to the required record (i.e. matching on EmployeeID) _before_ you update it. But I'm not sure - all the code samples I see are very lame. Here some really dumb code you could start with. https://learn.microsoft.com/en-us/office/vba/access/concepts/data-access-objects/modify-an-existing-record-in-a-dao-recordset It literally steps through each individual record to find the one to edit. — Nick.Mc, Dec 10 '20 at 00:27
You could just run an update query using docmd.runsql passing the two required parameters. https://stackoverflow.com/a/39214415/1521579. If you create the query through the access interface, you can switch to see the sql. Then follow the steps mentioned in the link. — Ricardo Diaz, Dec 10 '20 at 00:39
If you want to edit record that has focus on form then there is no need to open and edit a recordset. As it is, code is simply editing first record of the recordset and changes the EmployeeID that was already in that record. The 'empty' record is because user started data entry on the form. Simply populate field on form: `Me.TimeOut = Now()`. Otherwise, use an UNBOUND form where user selects an employee from combobox and code runs an UPDATE action SQL. — June7, Dec 10 '20 at 02:12
The problem of how this all started was because it doesn't update the original entry. It creates a new record. I will try the SQL approach. — Cvrpapc, Dec 10 '20 at 02:36

mazoula · Answer 1 · 2020-12-13T08:40:10.490

There are 3 problems with your code. First you are going to need to use TimeClockID rather than EmployeeID and you should include TimeClockID in your forms DataSource.

Next include an invisible textbox linked to TimeClockID on your form so you can use the me object to get the TimeClockID of the forms current record.
Second don't change Employee number. I'm assuming that is leftover code from the form used to insert a record when the employee started their shift. Changing the employee number probably created the duplicate record somehow. Third use TimeClockID to update the correct record.

Private Sub CheckOutBtn_Click()

     Dim rst As Recordset
     Set rst = Me.Recordset.Clone
     With rst
        .FindFirst "TimeClockID = " & Me.txtTimeClockID 'subroutine to move rst to current form record.  
        .Edit
        ![TimeOut] = Now()
        .Update
        End With
    Me.Refresh
End Sub

Now adding Timeout does not create a duplicate record:

MS access VBA update creating duplicate entries

1 Answers1