CODEWITHSUNDEEP
ms-accessvba
Pangu
Pangu

Reputation: 3819

Access VBA: Closing form causes duplicate entry in table

Let me explain what I have set up to easily describe my issue. I created a child form based on a table, which for illustration looks like this sample:

enter image description here

Each Text Box is bounded, i.e. its Control Source points to the appropriate field name from the table.

There is a save Command Button at the bottom where I have added some vba code to insert the 4 field values into the table.

Once the save button is clicked I do the following to insert into the table, and close the form:

db.Execute "INSERT INTO testTable (Number, Name, OtherNumber, OtherName) " _
        & "VALUES (" & Me.Number & ", '" & Me.Name _ 
        & Me.OtherNumber & "', '" & Me.OtherName & "')"
DoCmd.Close acForm, "frmTest", acSaveNo

The problem is that once the form is closed, it creates a duplicate entry in the table.

Due to my unfamiliarity with vba programming at the moment, I know that it has to do with the bounded textboxes to the table because if I remove the Control Source fields from the textboxes, leaving it blank, and manually fetch the values in the textboxes instead, i.e. Me.textBox_OtherNumber, then insert those values into the table when the save button is clicked, I don't get a duplicate entry in the table when the form is closed.

However, I would prefer the textboxes be bounded to the table because this makes it easier to open the form up in either acFormAdd mode where the field values are empty and waiting to be added as a new record, or the form is opened up in acFormEdit mode where the field values is already populated with the values from the selected row in the table using a WHERE criteria clause parameter in the DoCmd.OpenForm.

For example, I have a parent form that contains a Command Button and subform. The subform's Source Object is linked to a Datasheet whose Record Source is linked to the same table as the child form.

In the field OtherNumber of the Datasheet, I created a hyperlink and programmed its On Click command to be like so:

Dim uniqID As Integer
Dim criteria As String

uniqID = Me.ID
criteria = "ID = " & uniqID 

DoCmd.OpenForm "frmTest", acNormal, , criteria, acFormEdit

When a particular hyperlink of a specific row is clicked, the fields in frmTest is populated with the data based on the criteria, which will be used to update the current record.

However, if the Command Button is clicked in the parent form, I do the following:

DoCmd.OpenForm "frmTest", acNormal, , , acFormAdd

The form is opened but the field values are blank, indicating a new record is about to be added based on the field values.

To me, this feels like it is much easier than not having the textboxes in frmTest bounded to anything, and if it is not bounded, I would have to figure out a way to reference the field values in the Datasheet and pass to the child form frmTest.

Do you see my dilemma? What's the best way to solve this?

Thanks

Upvotes: 0

Views: 315

Answers (1)

Erik A
Erik A

Reputation: 32672

There are many, many ways to solve this problem:

Using a bound form, on your save button, just omit the line to save the record (since Access automatically saves it):

DoCmd.Close acForm, Me.Name, acSaveNo

Or, if you don't trust Access to save it:

Me.Recordset.Update
'Or: DoCmd.RunCommand acCmdSaveRecord
'Or: If Me.Dirty Then Me.Dirty = False
DoCmd.Close acForm, Me.Name, acSaveNo

Note that acSaveNo refers to not saving any changes to the design of the form, and is not related to saving records in any way.

If you want to set values on an unbound form from another form:

If Not Application.SysCmd(acSysCmdGetObjectState, acForm, "frmTest") Then 
    'You probably don't want to run this if the form is already opened
    DoCmd.OpenForm "frmTest"
    frmTest!ID.Value = uniqID
End If

(of course, there are many alternatives to this as well. The most relevant one is you can pass arguments between forms using the OpenArgs parameter of DoCmd.OpenForm, and you can handle those on form load).

Upvotes: 2

Related Questions