1

I have a form in Access where I'd like to create a button that adds a new record with information to the table.

I think part of the issue is the way I'd like the user to interact with the form.

From any given record I'd like the user to be able to use that record for data entry, but not overwrite the data in that record.

In record 47 out of 100 the current data is:

(private) ID: 47
Customer Name: John Smith
City: Las Vegas

I'd like the user to be able to alter the public data:

(private) ID: 47
Customer Name: Kate Doe
City: New York

then click the Add New Record Button

The desired result is a new record at the bottom of the table with the updated information and the record that was used for data entry remains the same.

So the table after the click should be

(private) ID: 47
Customer Name: John Smith
City: Las Vegas

...

(private) ID: 101
Customer Name: Kate Doe
City: New York

The button right now just uses

DoCmd.GoToRecord , , acNewRec

This method overwrites the current data in the data entry record. As far as I can tell all the AcRecord parameters don't solve this issue and I think I may need to generate a query?

June7
  • 19,874
  • 8
  • 24
  • 34
Watson221
  • 73
  • 7
  • Have user select a record to replicate, code copies data to new record and sets focus to that record, user edits new record. – June7 Sep 29 '22 at 03:59

2 Answers2

1

this ends up being a duplicate of: MS Access: Copy/Paste selected record programmatically? but I don't like the accepted answer as if you implement it you find you have to turn off error messages. Easy enough to do but a good sign there is a better way.
Creating forms to help enter data faster is a common topic here is an example using a listbox:

MS Access 2016 - Multi column listbox to add values to multiple fields in a table

As June7 said create the new record and let the user edit the new record. This is more natural and hence easier for the form user to understand. Here is code that handles nulls and doesn't require turning error messages on and off.

Private Sub cmdAppend_Click()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("YourFormsTable")
rs.AddNew 'most declaritive way to add records
rs![Customer Name] = Nz(Me.[Customer Name], "unknown")
rs!City = Nz(Me.City, "unknown")
rs.Update
Me.Requery
Set rs = Nothing
'DoCmd.GoToRecord , , acLast
'Me.Refresh
End Sub
mazoula
  • 1,221
  • 2
  • 11
  • 20
1

The way to this in Access, it to let your button copy the record to a new record, then let the user edit this as needed.

Here is the code to do that:

Copy a record from a button click

This is very fast and requires no requery of the form, and if the user wish to cancel, he/she can just delete the new record as the original record is left untouched.

Gustav
  • 53,498
  • 7
  • 29
  • 55