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.
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.