Solved

Continuous Unbound Form / Save Multiple Records

Posted on 2011-09-12
2
586 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now