Solved

Continuous Unbound Form / Save Multiple Records

Posted on 2011-09-12
2
630 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 85

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

624 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