Solved Multiple Record Creation (1 Viewer)

[BusyBeeBiker]

I have a main form (frmPersonnel) with multiple tabs and sub-forms

When I select Add New Record, it opens a nice new shiny blank record for completion.

As you would expect I add a Save or Abandon Record Option, which is where the problem is occurring.

1. On Save Option - saves the record BUT also generates to FIVE additional blank records in the main form table tblPerson.
2. on Abandon option - doesn't save BUT generates TWO blank records in the main form table tblPerson.

This one has me stumped .

 

[pbaldy]

What is the code behind the buttons? Is the form bound?

 

[BusyBeeBiker]

What is the code behind the buttons? Is the form bound?

Yes the form frmPersonnel is bound to the underlying table tblPerson (see Attached) with a Primary Key pkPersonID.

With regards to validation code on Main form BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If glbHandleErrors Then On Error GoTo ErrHandler ' Set Error Handling

Dim strMessage As String, Response

If Me.NewRecord = True Then ' Validates if New Record
' Validate that Surname has been entered.
    If IsNull(txtSurname) Then
    
    ' Initialise Message String.
        strMessage = "Cannot Save Record Without Surname." & vbCrLf _
                   & "Select YES to return to Surname Field." & vbCrLf _
                   & "Select NO to Abandon Change(s). Return to Previous Record."
    
       Response = MsgBox(strMessage, vbYesNo)
        If Response = vbNo Then
           Cancel = True ' Cancel Record Save/Update
           Me.Undo
           Me.Bookmark = strBookMark ' Return to Previous Record
           Me.Refresh
           Me.txtSearch.SetFocus
           GoTo ExitHere:

        Else
            Me.txtSurname.SetFocus
            GoTo ExitHere:
        End If
    End If

' Asks User to Save Record or Not!
    Response = MsgBox("Save New Record?", vbYesNo)
    If Response = vbNo Then
       Cancel = True
       Me.Undo
    End If
End If

ExitHere:   ' Any Clean Up Code
   Err.Clear
   Exit Sub
 
ErrHandler: ' ERROR HANDLING ROUTINE.
   If Err.Number <> 0 Then
       Call LogError(Err.Number, Err.Description, Forms!frmLoginScreen!fkID, Environ("UserName"), Environ("ComputerName"), "", glbHandleErrors)
      Resume ExitHere
   End If
End Sub

 

[Pat Hartman]

We need to see all the code in the module. Something is dirtying the records causing records to be added when they shouldn't be. OR you are running an append query despite using a bound form.

 

[BusyBeeBiker]

As requested code for whole of module frmPersonnel. Will keep a weather eye for something dirtying the records.

File too big to fit between code tags (see attached file)

 

[Pat Hartman]

FYI, when someone asks you to post code, please do it using the code tool. pdf's are dead. I can't search, I can't get a list of the procedures, I can't compile the code, etc.

1. you are dirtying the record in the current event. This will force Access to always save the current record even if nothing else has been entered/changed.
2. your validation is not being done correctly. It belongs in the Form's BeforeUpdate event and you have to cancel the event if validation fails.
3. too many procedures have meaningless names
4. Significant amount of recordset processing that makes no sense in a bound form.

 

[BusyBeeBiker]

FYI, when someone asks you to post code, please do it using the code tool. pdf's are dead. I can't search, I can't get a list of the procedures, I can't compile the code, etc.

1. you are dirtying the record in the current event. This will force Access to always save the current record even if nothing else has been entered/changed.
2. your validation is not being done correctly. It belongs in the Form's BeforeUpdate event and you have to cancel the event if validation fails.
3. too many procedures have meaningless names
4. Significant amount of recordset processing that makes no sense in a bound form.

Initially tried to put code between code tags, but came back with error message saying too large, hence PDF file.

With regards to point 1. you initially mentioned this point in a previous post and have worked through the code and found the specific line of code that was dirtying the record in 4 separate places. Once in the main form and 3 times in sub forms, for some reason I was populating a bound control in the main form with a message rathe rthan using a message box! - won't be doing that again.

Thanks for the help

 

[Pat Hartman]

Did that fix the problem with generating duplicate records?

Also, FYI, recordset processing is significantly slower than action queries. If you want to update an entire recordset, best to do it with an update query.

nitially tried to put code between code tags, but came back with error message saying too large, hence PDF file.

The site still has some residual bugs from the last update. Post the code in a text file if you can't get the tags to work properly.

 

[Uncle Gizmo]

Initially tried to put code between code tags, but came back with error message saying too large,

I would suggest next time either place the code into to a brand new MS Access database, into a module say, alternatively into a text document.