Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Continuous Unbound Form / Save Multiple Records

Posted on 2011-09-12
2
Medium Priority
?
639 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 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

721 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