Overcoming unbound continuous forms limitations by staging data

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.  When you get a workable but not very efficient form with a lot of comboboxes working you can hear the muttering. "Did I pick all the items from that category already?  Did I skip a category?  Grr, this is a LOT of clicking.  And this job is ALMOST EXACTLY like the one last week!  Why do I have to go through the drudgery of this again and again?  Why can't I just reload this stuff from last week and change these two columns and delete that row?"  Designing a form to do such a job is not as simple as it seems on the surface.

You have data in tables, but you don't want to edit that data.  You want to present it to the user for consideration, editing and finally appending to tables as new records.  That seems like a job for unbound controls!  Unbound controls are the go-to solution for when you want to present data, but not edit it.  Bind a RowSource to a combo or list box, but don't bind a ControlSource.  Use VBA in events to pre-load textboxes.  Navigation and filtering controls get done like this all the time.  One big problem, though.  MS Access does not play nicely with unbound controls on continuous forms.  MS Access does not have control arrays like VB does.

Try it!  Put an unbound control on a continuous form that displays a bunch of records.  Change its value.  The control changes on EVERY record. So, what to do?  We don't want to change the existing data, so binding the controls to the existing data is also out.  We could put multiple unbound controls on the continuous form and do some fancy coding, counting records and making some controls visible and other invisible so that there is a unique unbound control for each record.  But that's a lot of work, and requires that you know in advance how many possible records you could have on the form.  It's cludgey.

Now what?

To work with continuous forms we need bound controls.  The data we'd like to work with is not something we actually want to edit--we want to appended it to our records.  We could append all the data, and then have the user edit it...but what if they change their minds?  Then you have to provide facilities for deletion, which is always a good way to increase the risk for colossal screw-ups.  "Oh, hey, yeah...Um, I just deleted a bunch of stuff out of Bob's last three jobs, by accident, because I thought they were mine.  Can you get that stuff back?"  So while we could go that route, it probably isn't wise.

More time-efficient and user-oriented would be to present the user with all the probable options, allowing any necessary edits, and then committing the data.  How to do that presents a bit of a problem, however.  You have to take that existing data that you don't want to change, put it some place where it can be edited, and then append the edited data to its final storage location.  

My solution is the use of code and a local table whose contents get dumped and replaced with staged data.  If you pull the data that you want to display to the user, and append it some temporary place where it can be edited, the form can then be bound to this volatile data to permit the user to make any necessary changes.  After the user makes any desired changes, code can then append this temporary staged-and-edited data to its permanent location.  This technique also works nicely when you want to create a form that would use/create/edit data from a normally un-updateable query, such as a multi-table query or the recordset returned by a SQL Server stored procedure.

As a simple practical example, every worksite needs to have a hazard assessment done.  Nicely normalized tables of hazard categories and hazard descriptions can be created.  Not every job has every hazard, nor even every category of hazard.  Still, returning to the same jobsite later will probably involve most of the same entries with a few minor changes.  So, you need a form to allow the user to select the appropriate categories and hazards.  You could do so on a bound form with a pair of cascaded combo boxes.  It works.  But it's not time-efficient or user-oriented.  The guts of an elegant solution involves creating a table with the appropriate structure to hold the staged data, and a form to display that staged data.  On the form, there need to be two events.  One to delete any previously staged data, and append new staged data to the 'temptable.'  After editing, another event is needed to  append the staged data to permanent storage.

Note: While a make-table query and deleting the table could be used, I am leery of the long term effects of dropping/recreating tables on a regular basis.  I think tables should be created and persist

While I could have posted screenshots, instead I have created and posted a working sample that demonstrates the technique in a simple and effective manner.
The sample was created in MS Access 2003, with a A2000 file format and should work for all versions of MS Access from A2000 forward.

In the attached sample:
tblJobs is the stub main table.
tblHazards and tblHazardCategories are self-explanatory.
tempHazardsPresent is the table that holds the staged data (I like to prefix table names with temp to signal that their data is not significant in the long-run)
tblHazardsPresent is the permanent location for the desired records

There are two forms.
frmPoorEntryDesign is workable, but a lot of clicking for the end-user
frmAddHazards is much more elegant from a user-friendly point of view
It has a multi-select listbox to pick Hazard Categories
It has a command button to load the 'temptable' and requery the form
It has a command button to append the selected choices to permanent storage.
It has an OnClose event to empty the 'temptable' when the form closes.

The sample simply demonstrates the technique.  I have not extended far beyond basics.  It does not check for previous results in the permanent table.  It just deletes them.  Ways of retaining the past records can be created.  Past records could be loaded with their values marked.  I have not done that here.  In the sample, it is just working with two columns of data.  Imagine if there were 10 or 15 columns and 15 or 20 rows.

Pulling the data from the permanent table based on some criteria (such as reloading from a previous job) could also be accomplished.  That is not done here for the sake of brevity.
Loading the staging table with data from an un-updateable query, and pushing the data back to multiple tables could also be accomplished.
Data could be pulled from a stored procedure pass-through query into the staging table and, using code, pushed back to the appropriate tables.
The possibilities are myriad.  Anytime you would like to pull a significant amount of data that you'd like to edit and append, this is a simple technique for doing so.

That MS Access does not have control arrays like VB does can be annoying.  The demonstrated technique is one way of overcoming the limitations of unbound controls on continuous forms--by binding them to temporary data that can be discarded after its usefulness has ended.

Comments (4)

Hi.  I have a question about your sample database (which is exactly what I needed a demo of - thanks!)  When I choose say 4 hazard items to add to job 1 it works perfectly.  When I change the job number to 2 ( a job number I have added to the tblJobs ) it adds only the last one to job 2 and the first 3 it adds to job 1 again.   Any idea why that is happening?  In the code below it looks like job id 1 is coded right in.  Is that where the problem is?
For Each itm In Me.lstHazardCategoryID.ItemsSelected 'go through each category selected, and add all the related records
    Set rs1 = db.OpenRecordset("Select * from tblHazards where HazardCategoryID =" & Me.lstHazardCategoryID.ItemData(itm) & ";", dbOpenDynaset)
    'Me.lstHazardCategoryID.ItemData(itm) grabs the value from each selected item
    Do Until rs1.EOF
        With rs
            !HazardID = rs1!HazardID
            !HazardDescription = rs1!HazardDescription
            !Present = False
            !JobID = 1
        End With
Next itm
Most Valuable Expert 2014


The sample was created as a stub to demonstrate the process of grabbing records from one job, putting them in a temporary, editable location, and then knocking them out, after editing, into a permanent location.

It was never meant to be built up as a working app.

The form is built upon tempHazardsPresent.  As noted, because the focus was not on creating a polished app, but a demo, cmdLoadHazards_Click() hard-codes the JobID.

If you were going to polish it more toward a production app, you'd conceivably want forms to create new jobs, and something on frmAddHazards to permit you to select a job to save them to. That, of course, would have required building the error-checking to ensure that a valid JobID had been selected.  And, I didn't sew all the relationships up tight either.

You may also want to have functionality to clone an existing job -- and add something to tblJobs to describe each job.

There's also no present functionality to edit things.

Here is the sample with a combobox to select a job

Ah I see.  Thanks so much for clearing that up for me!   Great article!

I have another question about this.  Is it possible to create a command button that would select all hazards listed in a category?  I'm guessing the command button would have to use a recordset but I'm still murky on how those work.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.