Record is Inserted Twice
This is a form with a subform The main form displays rows from the "People" table. The subform displays rows from the Class Registration table, linked by the Email field, which is the primary key for the People table, and part of the key for the Registration table.
The subform contains a control cmdAddRecord1, which copies data from the main form and inserts a row into the Registration table. The VBA code runs OK, but when I use the navigation button to move the main form to another record, it attempts to write a second row to the Registration table, and I get the error message about a duplicate record. With he primary key removed, it actually writes two identical rows.
Here's the VBA for the subform: BTW, cmdDelete works OK
Option Compare Database
Private Sub cmdAddRecord1_Click()
On Error GoTo Err_cmdAddRecord1_Click
Dim lngRecordsFound As Long
Dim txtAddSQL As String ' Define INSERT Statement
txtAddSQL = "INSERT INTO Registration (ShortName, ClassNumber, Email, First, Last) " & _
"VALUES (txtShortNameS, txtClassNumberS, txtEmailS, txtFirstS, txtLastS);"
Dim txtFindSQL As String ' Define SELECT Statement
txtFindSQL = "SELECT * FROM Registration WHERE ShortName = txtShortNameS AND " & _
"ClassNumber = txtClassNumberS AND Email = txtEmailS;"
txtFirstS = Forms![Registration Update Form].txtFirst 'move person data from main form
txtLastS = Forms![Registration Update Form].txtLast
txtEmailS = Forms![Registration Update Form].txtEmail
txtShortNameS = Forms![Registration Update Form].txtShortName
txtClassNumberS = Forms![Registration Update Form].txtClassNumber
txtGo = MsgBox("OK to Update?", 1, "Is All Data Correct")
If txtGo = vbOK Then
txtAction = "Cancel"
txtAction = "Cancel"
Private Sub cmdDelete_Click()
' Delete the Registration Record
' This will delete both records if there are duplicate keys
On Error GoTo Err_cmdDelete_Click
Dim txtDeleteSQL As String
txtDeleteSQL = "DELETE FROM Registration " & _
"WHERE ShortName = txtShortNameS AND ClassNumber = txtClassNumberS " & _
"AND Email = txtEmailS;"