AddNew inserts duplicate records

[exaccess]

My Access database has one big table. Each record holds data about a member of a club. Some fields are connected to small lookup tables. Thus it is basically a straightforward design. The main table does not have any primary key. Instead the combination of first name and last name is unique. I use an input form that is switched into Add Mode by the user. The user enters the data and presses the Add Record key. At that moment the record is added to the table. The code showing all this is below. The problem is that the system adds the record twice. Please help.
Code to switch to add mode:

Private Sub SetAddModeBt_Click()
    If G_ReadOnly = "true" Then GoTo Stay_ReadOnly33:
    If Me.Mode <> "Add Mode" Then
        Me.AllowEdits = False
        Me.AllowAdditions = True
        Me.AllowDeletions = False
        Me.DataEntry = True
        Me.NOM.Locked = False
        Me.PRENOM.Locked = False
        Me.Mode = "Add Mode"
    End If
Stay_ReadOnly33:
End Sub

Code for adding record:

Private Sub AddNewBt_Click()
On Error GoTo Err_AddNewBt_Click
    Dim UF_Rec1, UF_Rec2, UF_Rec3 As String
    UF_Rec1 = Me.NOM
    UF_Rec2 = Me.PRENOM
    If Me.Mode = "Read Only" Or Me.Mode = "Edit Mode" Then
        MsgBox "To add a new record change to Add Mode"
        Exit Sub
    End If
    Dim rstarget As Recordset
    Set rstarget = CurrentDb.OpenRecordset("MembersTbl")
    
    rstarget.FindFirst "[NOM] = '" & UF_Rec1 & "'" & " And " & _
                                    "[PRENOM] = '" & UF_Rec2 & "'" 
    If rstarget.NoMatch = False Then
        MsgBox "Member exists - nothing added"
        Set rstarget = Nothing
        Exit Sub
    End If
  ' MsgBox "adding record NOM = " & Me.NOM
    With rstarget
    .AddNew
    .Fields("NOM").Value = Me.NOM.Value
    .Fields("PRENOM").Value = Me.PRENOM.Value
    .Fields("STATUT").Value = Me.STATUT.Value
    .Fields("NAT").Value = Me.NAT.Value
    .Fields("LANG").Value = Me.LANG.Value
    .Fields("ENTRORG").Value = Me.ENTRORG.Value
    .Fields("DEPORG").Value = Me.DEPORG.Value
    .Fields("DNAISS").Value = Me.DNAISS.Value
    .Fields("DateCreated").Value = Now()
    .Fields("LastUpdate").Value = Now()
    MsgBox "adding record NOM = " & Me.NOM
    .Update
    .Close
    End With
    Set rstarget = Nothing
    MsgBox "Record added to database", , GC_Title
    DoCmd.Close
Exit_AddNewBt_Click:
    Exit Sub

Err_AddNewBt_Click:
    MsgBox Err.Description
    Resume Exit_AddNewBt_Click
    
End Sub

 

[pbaldy]

It sounds like the form is bound to the table. You either want code to add the record, or a bound form; not both.

 

[exaccess]

Thanks for the prompt answer. But what is the practical solution? What code should I change to solve the problem?

 

[pbaldy]

The practical solution is to use one method or the other.

The first code you posted is appropriate to a bound form. The second is appropriate for an unbound form. Simplest is to use bound forms, but it's your call.

http://www.baldyweb.com/BoundUnbound.htm

 

[exaccess]

Does this mean that I should drop the second code para and replace it by the following:

Private Sub AddNewBt_Click()
On Error GoTo Err_AddNewBt_Click
    Dim UF_Rec1, UF_Rec2, UF_Rec3 As String
    UF_Rec1 = Me.NOM
    UF_Rec2 = Me.PRENOM
    If Me.Mode = "Read Only" Or Me.Mode = "Edit Mode" Then
        MsgBox "To add a new record change to Add Mode"
        Exit Sub
    End If
    Dim rstarget As Recordset
    Set rstarget = CurrentDb.OpenRecordset("MembersTbl")
    
    rstarget.FindFirst "[NOM] = '" & UF_Rec1 & "'" & " And " & _
                                    "[PRENOM] = '" & UF_Rec2 & "'" 
    If rstarget.NoMatch = False Then
        MsgBox "Member exists, nothing added"
        GoTo CloseExit
    End If
  ' MsgBox "adding record NOM = " & Me.NOM
    Me.DateCreated = Now
    DoCmd.Save acForm, Me.Name
    MsgBox "Record added to database", , GC_Title
: CloseExit
    DoCmd.Close
    rstarget.Close
    Set rstarget = Nothing
Exit_AddNewBt_Click:
    Exit Sub
Err_AddNewBt_Click:
    MsgBox Err.Description
    Resume Exit_AddNewBt_Click
    
End Sub

I am opting to use bound form. In the meantime I have briefly tested the code it works. Is this right way to do it? Thanks.

 

[Mihail]

No. That mean that you should remove the AddNew button at all.

 

[exaccess]

OK Mihail. In that case should I just use the save button after going to Add Mode?
One more point the DoCmd.Close seems to save the record without executing the DoCmd.Save. How can I avoid saving the record while closing the form?

 

[Mihail]

Access attempt to automatically save a record when you exit from that record. This is happen for example when you move (jump) to other record, when you move the focus to an unbound control, when you close the form etc. This happen for BOUNDED forms (see again post #4).

Because you have duplicate record after you hit the AddNewBt, we know that you have a bounded form - yours controls are bounded to a field in a table.
So, you edit the values of this controls. Nothing happen yet. The table field are still as the beginning. The new values (what you have now in yours controls) are not (yet) saved in the table.
Now you hit the button. What is happen ?

Private Sub AddNewBt_Click()
  On Error GoTo Err_AddNewBt_Click
Dim UF_Rec1, UF_Rec2, UF_Rec3 As String
  UF_Rec1 = Me.NOM
  UF_Rec2 = Me.PRENOM
  If Me.Mode = "Read Only" Or Me.Mode = "Edit Mode" Then
    MsgBox "To add a new record change to Add Mode"
Exit Sub
  End If
Dim rstarget As Recordset
  Set rstarget = CurrentDb.OpenRecordset("MembersTbl")
  rstarget.FindFirst "[NOM] = '" & UF_Rec1 & "'" & " And " & _
    "[PRENOM] = '" & UF_Rec2 & "'"
  If rstarget.NoMatch = False Then
    MsgBox "Member exists - nothing added"
    Set rstarget = Nothing
Exit Sub
  End If
' MsgBox "adding record NOM = " & Me.NOM
  With rstarget
   [B] .AddNew[/B]

The very last instruction from here say to Access to "jump" from the current record to a new one. So, Access will save the current record before the "jump". This is the first instance of your record.
Then Access move the focus to the new record.
Now the rest of your code write (again), in this new record, all the values => the second instance of your record.

Hope this allow you to debug yourself your code.

One more point:

Dim UF_Rec1, UF_Rec2, UF_Rec3 As String

This instruction (as it is) declare 3 variables:
UF_Rec1 and UF_Rec2 as variant and UF_Rec3 as string.
In order to be all strings, the correct declaration is:

Dim UF_Rec1 As String, UF_Rec2 As String, UF_Rec3 As String

 

[exaccess]

OK. Now it is clear. I am almost there by debugging the code. Thanks.