Microsoft Access is inserting wrong values in table

Question

I am fairly new at Microsoft Access and I came upon a error which I can't seem to resolve. I create 2 seperate form which are LOGIN and LOGOUT, which essentially will handle all the employee interactions throughout the day with basic functionality.

Incase you needed to see my relationships or login form to get a idea of what I am doing.

My login button on click

Private Sub cmdLogin_Click()
    'add record to database
    Dim empID As Integer
    Dim logDate As Date
    Dim logTime As String
    logTime = Time
    logDate = Date
    empID = Me.cboEmployeeNo.Column(0)

    CurrentDb.Execute "INSERT INTO tbl_workShift(employeeID, logTypeID, logDate, logTime) " & _
     " Values(" & empID & ", 1, #" & logDate & "#, '" & logTime & "')"
End Sub

My problem is whenever I login or logout with a employee it seem to create a duplicate of somekind with a different employee number but same first and last name.

As you can see, employee ID 38 and 40 should not exist, but they were duplicated from 2 and 4? any reason why this is happening? any help will be appreciated

Answer 1

If the DataEntry property of a form is set to true or if you open the form with DataMode:=acFormAdd the form will open on a new, empty record, and every entry will be made to this new record.

Make sure to open the form on the right record by selecting the right employee, before editing an employee.

If you are using a combo-box for the selection of employees, make sure NOT to set the Control Source of this control, because you don't want to save the selected value. You want to use it only programmatically.

If you want to have a "pure" data editing and entry form set its properties as follows:

• Default View: Single Form
• Allow Form View: Yes
• Allow Datasheet View: No
• Allow PivotTable View: No
• Allow PivotChart View: No
• Scroll Bars: Neither
• Record Selectors: No
• Navigation Buttons: No

• Border Style: Thin

• Record Source: tbl_employee

• Allow Filters: No
• Allow Edits: Yes
• Allow Deletions: No
• Allow Additions: No
• Data Entry: No

And open it like this

DoCmd.Close acForm, "frm_employee"
DoCmd.OpenForm "frm_employee", WhereCondition:="employeeID=" & _
    Me.cboEmployeeNo.Column(0), DataMode:=acFormEdit

These properties make the form look neat and make sure that you cannot navigate to another record by mistake.

Also you should make a distinction between login and editing employees. The login form should not be allowed to change the employee records.