Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 648
  • Last Modified:

Continuous Unbound Form / Save Multiple Records

Hello Experts,

I have a form whose objective is as follows:

1. Allow users to select a record (tblExport.ExportID) and then display that records relating records in tblExportMap (relationship on ExportID)
2. Then allow user to edit any or all of the export fields (tblExportMap.ExportFieldID or tblExportMap.tblColumn)
3. If necessary rollback/cancel all changes made or save all changes


I originally did this with a continuous form and set the recordsource of the form to  
strSQL = "SELECT tblExportMap.ExportNameID, tblExportMap.ExportFieldID, tblExportMap.tblColumn" & _
                     " FROM tblExportMap" & _
                     " WHERE tblExportMap.ExportNameID=" & intExportNameID

Open in new window

after the ExportName was selected from a drop down menu

This allowed users to edit the data and add records as needed without any problem. The problem occured in that if a user clicked the cancel button (which ran Me.Undo) the code only undid the most recent records edits (since access adds/edits/etc one record at a time).

What I believe I need is an unbound form that allows a user to add any records needed, edit any records and then with one command button "save" insert or update all of the changes into the table at once. Likewise, a 'cancel' button that discards any edits or inserts.

In the bound version of this form, a new row auto populates after the record is saved. In the unbound version, I cannot get a new row to generate so that a user can create another row of data on the same form.

What is the best approach to auto-add this new record row?
0
w00tw00t111
Asked:
w00tw00t111
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The simplest way to implement this is to use a temporary or staging table. When the user opens the form, fill the temp table with the data they need to edit, and then use a standard bound form to allow them to edit data in that temp table. You could then run code to "commit" those changes when the user clicks a button, for example, by using standard UPDATE queries - just update the data in the "live" table with the edits in the "temp" table.
0
 
w00tw00t111Author Commented:
Thanks LSM.

Multiple users won't be editing this data at the same time so I wasn't sure if that would be the most effective route or not.

Thank you for the solution!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now