Solved

Continuous Unbound Form / Save Multiple Records

Posted on 2011-09-12
2
602 Views
Last Modified: 2012-05-12
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
Comment
Question by:w00tw00t111
2 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 36526188
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
 

Author Comment

by:w00tw00t111
ID: 36526267
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question