Solved Why is it creating 2 records?

[WorkDatabase]

Hello everyone,

why is my form creating 2 records? I have a form that 3 controls are pre populated with data from a subform. The rest of the controls are empty and the form is in add mode. When I save the records via a button it creates the record that I have entered the data for and then a further record from the pre populated controls? I only want the one record? Can anyone tell me why this is happening?

thank you

 

[CJ_London]

unless you do something to prevent it, the form saves automatically when it goes to a new record or you close the form, so most likely this is happening as well as the save from your button.

 

[theDBguy]

Hi. Welcome to AWF!

Bound forms automatically save records in Access, so a Save button could be redundant. Could you show us all the code you are using to prepopulate and save the form?

Edit: Oops, too slow...

 

[WorkDatabase]

The first bit of the code opens the form and pre populates the controls.


Private Sub AddNewComment_Click()



Dim strReference As String

Dim strEmployeeName As String

Dim dtDateTime As Date



' Get the filtered values from the subform

strReference = Me.TblComments_subform.Form.Recordset.Fields("ReferenceAlpha").Value

strEmployeeName = Me.TblComments_subform.Form.Recordset.Fields("Employee Name").Value

dtDateTime = Me.TblComments_subform.Form.Recordset.Fields("Date/Time").Value



' Open the "AddNewComment" form in add mode

DoCmd.OpenForm "AddNewComment", acNormal, , , acFormAdd, , strReference



' Set the values in the "AddNewComment" form

Forms("AddNewComment").ReferenceAlphaTxt.DefaultValue = "'" & strReference & "'"

Forms("AddNewComment").EmployeeNameTxt.DefaultValue = "'" & strEmployeeName & "'"

Forms("AddNewComment").DateTimeTxt.DefaultValue = "#" & Format(dtDateTime, "yyyy-mm-dd hh:mm:ss") & "#"



' Requery the controls to display the prepopulated values

Forms("AddNewComment").ReferenceAlphaTxt.Requery

Forms("AddNewComment").EmployeeNameTxt.Requery

Forms("AddNewComment").DateTimeTxt.Requery

End Sub

Then this is my save button


Private Sub SaveRecordBtn_Click()





' Check if all required information is entered

If Not IsNull(Me.ReferenceAlphaTxt.Value) And Not IsNull(Me.EmployeeNameTxt.Value) And Not IsNull(Me.DateTimeTxt.Value) Then

' Add a new record to the TblComments table

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("TblComments", dbOpenDynaset, dbAppendOnly)

rs.AddNew

rs("ReferenceAlpha").Value = Me.ReferenceAlphaTxt.Value

rs("Employee Name").Value = Me.EmployeeNameTxt.Value

rs("Date/Time").Value = Me.DateTimeTxt.Value

rs.Update

rs.Close



' Close the "AddNewComment" form

DoCmd.Close acForm, Me.Name



' Refresh the subform in the "EditAbsence" form

Forms("EditAbsence").TblComments_subform.Form.Requery

Else

MsgBox "Please enter all required information.", vbInformation + vbOKOnly, "Incomplete Information"

End If

End Sub

 

[WorkDatabase]

Hi. Welcome to AWF!

Bound forms automatically save records in Access, so a Save button could be redundant. Could you show us all the code you are using to prepopulate and save the form?

Edit: Oops, too slow...

The problem is that the blank record isn’t created at the point of opening the form. The 2 records are created when the save button is clicked but I can’t see anywhere in my code where this would happen.

 

[theDBguy]

When a Dirty form gets saved, the BeforeUpdate event fires. Just as a test, try adding a message box line in the BeforeUpdate event and then Debug your code from there by placing a breakpoint on that line.

 

[WorkDatabase]

When a Dirty form gets saved, the BeforeUpdate event fires. Just as a test, try adding a message box line in the BeforeUpdate event and then Debug your code from there by placing a breakpoint on that line.

I have disabled the BeforeUpdate event in the code and it’s still creating that record.

 

[The_Doc_Man]

The problem is that the blank record isn’t created at the point of opening the form.

In "add" mode, yes it is. A new record gets created for a bound form that is designed/set up for insertion. But this new record hasn't been committed yet so you can't see it via any other means. It ain't there until it is saved. It is this mechanism that causes auto-numbered fields to have gaps - when you open the new record but then cancel it. It is the mechanism that consumes an autonumber.

A bound form that has no record would give you the error message "No current record" the moment you tried to do anything to it. Your SAVE routine does an .AddNew, but Access did one for you already when that form opened up for business. Otherwise the first time you had ANY OTHER VBA to touch any bound control's content, you would get the error. The first time you tried to store data, with or without VBA's help, you would get the error. The record HAS to exist (for the bound form) in order for it to be of any use at all.

 

[WorkDatabase]

unless you do something to prevent it, the form saves automatically when it goes to a new record or you close the form, so most likely this is happening as well as the save from your button.

It’s definitely not happening when I close the form without hitting the save button.

 

[WorkDatabase]

In "add" mode, yes it is. A new record gets created for a bound form that is designed/set up for insertion. But this new record hasn't been committed yet so you can't see it via any other means. It ain't there until it is saved. It is this mechanism that causes auto-numbered fields to have gaps - when you open the new record but then cancel it. It is the mechanism that consumes an autonumber.

A bound form that has no record would give you the error message "No current record" the moment you tried to do anything to it. Your SAVE routine does an .AddNew, but Access did one for you already when that form opened up for business. Otherwise the first time you had ANY OTHER VBA to touch any bound control's content, you would get the error. The first time you tried to store data, with or without VBA's help, you would get the error. The record HAS to exist (for the bound form) in order for it to be of any use at all.

Ok, understand, what’s the solution?

 

[WorkDatabase]

Ok, understand, what’s the solution?

Thanks for the information, I solved it using your information on how a form in add mode works. Should have known but just having a tired and lazy day. Thank you very much.

I updated the code to open the form in Add mode but to update the existing record. Problem solved.

 

[Pat Hartman]

I see that the problem is "solved" but you have bigger problems with your schema. It makes no sense to be storing data from ONE subform record in the parent table.

 

[The_Doc_Man]

Ok, understand, what’s the solution?

Either (A) don't bother with an explicit save or (B) after the explicit save, do a "Me.Undo" on the form so that it isn't dirty. BUT even that second option COULD lead to creating blank records under some circumstances. I'd go with (A) and rethink the scenario.