Hello,
I'm creating a database from scratch, was nearing completion, and have run into 2 issues that the users have requested. The scenario is that I have a database with 5 tables that need to be edited for each new sample result from lab equipment, the tables
being
- The status of the lab (such as barometric pressure.)
- Status of the instrument (which instrument was being used) which references the 1st table.
- The type of sample being analyzed.
- A many-to-many table joining tables 2 and 3 so that each instrument and sample are recorded as well as data pertaining to that current sample test (such as time test was performed), with this form referencing tables 2 and 3.
- A sample will be run on a single instrument usually 2 times to make sure that a single test didn't happen to give a wrong value due to make sure the test results are repeatable, but sometimes it is done 1 to 4 times due to different
circumstances, so there is a result table which references table 4. This is done so that I don't have table 4 with 4 columns to enter data in, with most of the time the last 2 columns having no data in them which I was taught violates normal
form for databases.
Given this, here are my problems. First, I have setup a form to allow lab personnel to enter data into Microsoft Access through one form instead of 5 different forms, on for each table. This is done by having a form for the 5th table have a subform
for table 4, which has subforms for tables 2 and 3, and then table 2 having a subform for table 1, with combo boxes allowing a user to choose data from previous records for all the subforms. So I have a form that is 4 levels deep due to subforms.
The problem is that to enter NEW data, a user currently has to:
- Open the form for table 5 that has the subreports,
- right-click on the combo box that changes the data for the subform that is for table 4,
- select "Edit List", then has to edit list again for the other combo boxes for the forms for tables 2 and 3,
- for the form for table 2 need to repeat steps to reach the form for table 1,
- select New Record,
- enter the new data,
- click save,
- then back in the subform for table 4 has to click refresh all, (Note: If an instrument has already been used to analyze a sample once during a day then the user will only have to select the value from the combo box for instrument status for that day so
steps 3 - 7 can be skipped which does save some time for entering new data.)
- then click new record,
- enter the new data,
- save,
- go back up to the main form,
- refresh again,
- select from the combo box the primary key value for the new value entered for the form for table 4,
- FINALLY enter the first result from the test,
- and then for tests done on the same sample, just like how Steps 3-7 can be skipped, steps 2-14 can be skipped when if more than one test is being done on the same sample by copying the record into a New Record for the form for table 5.
I've talked with the users and we agree that this is ridiculous to have to be done every time a new sample arrives in the lab that needs to be analyzed. So the first question is, is there anyway to setup a form so that a user can just select everything
without going through multiple forms, refreshing, etc. and have as few steps as possible, preferable (not needing to be this short, but anything shorter is better) something like this:
- Open the form for table 5,
- create a new record,
- allow values to be entered for the lab condition for table 1, instrument status for table 2, type of sample for table 3, conditions of instrument when analyzed for table 4, and then the actual results for table 5,
- and then copy that record for each time the sample needs to be re-tested and change the result so each result is recorded.
Again, if more steps than that are needed, that's fine, but having to do 16 steps for each new sample at the beginning of each day (10 steps if the instrument has been run the same day) is ridiculous, so anything shorter than that would be great.
The second things that the users have requested, and this is 2nd in priority to the 1st problem mentioned above) is to instead of having to copy the final form each time the sample is rerun on an instrument is to have another box to enter data appear in
each time a result box is filled in. For example, when you go to an self-checkout at a store you don't need to get a new transaction for each item. You scan an item, keep scanning items until you have nothing left in your cart, and then select
checkout. Likewise, the users want once one result has been entered for another box to appear (which I know how to do with events for labels and text boxes so they will only show if there is data in another box) and then to be able to enter values
into the boxes until all test result have been entered. This way all the results for a sample being tested can be entered and reviewed on the form at the same time so there will be no chance of entering the same result twice or forgetting to input one.
Again, the users really need the steps being cut down from 16 steps for new samples, that is the top priority, while being able to enter multiple sample results at a time so New Record doesn't need to be selected for each retest is the bottom priority.
-Tommy
Allow a Form to Add Multiple Records at a Time
