Updating multiple records at once on a form

[cjg.groups]

Hello, please advise if the following form design idea is even
possible, or if there is a better way. I am trying to add/update/view
multiple records at once on a single form.

Use:
Multiple laboratory tests are conducted on a sample (from Samples
table). Each test result (2 data points) is stored separately in the
Results table. I would like to enter and view all of the results for
the sample on one form.

Design:
Create a form with many unbound text boxes, one pair for each possible
test result. After data is entered, click a "Save" button which runs
VB code to store the data. The code would create new Results table
records for all of the data entered, then copy the data from the form
to those records. To view the data, the form's OnCurrent could run VB
code to build a record set of Results for the current sample, then
copy the data onto the form.

Is this reasonably possible? Is there a better way to add/update/view
multiple records at once on a single form? Thank you.

 

[Albert D. Kallal]

Why bother to write all of that code?

You best build a form that allows you to enter the data..and you likely
don't need to write any code.

So, you likely would use a classic form + sub-form.

the main from would be based on a table called

tblTests

In tblTests, you would have things like date, time of day, who did the test
etc.

then, a sub-form (likely a continues one) would have the restless of this
test (this tale would be related back to the above tblTest).

We could call this table

tblTestResults

Here is some pictures and ideas of forms that display data:

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

so, tblTestResults would be where you enter your two data points.....

I don't see the need for a whole bunch of un-bound boxes...as ms-access is
well able to display and edit the data as you need, and no code will need be
written...

 

[Guest]

Oh Yuck... Yes, what you describe is possible, but probably not what you
want to be doing!!

Have you considered having a continuous form? To do this, have a Main form
with a subform that is a continuous form - in your main form, have the Sample
details, in the continuous subform have the Test results. Link the forms
using the link child/master fields property and Access will ensure it stays
in sync. This assumes that you are storing your Samples in a table, linked
to Test results for those samples in another table.

Hope this helps.

Damian.

 

[cjg.groups]

Thank you for your suggestions Albert and Damian. I do plan to use a
subform. However, I need the Results subform to show 20 records from
the Results table at once. There are 20 results associated with each
Sample.

Albert's grid display looks promising but does not provide the layout
potential of text boxes on a form. The Results subform requires
various text labels and it helps the user if I group similar results
into columns. The crux is the user needs to see all possible results,
each with their own record, at once.

I will practice with the grid display, but editing multiple records at
once on the same form using laid out text boxes is the ultimate goal.
Can forms do that?

 

[Albert D. Kallal]

I will practice with the grid display, but editing multiple records at

once on the same form using laid out text boxes is the ultimate goal.
Can forms do that?

If you use continue forms as I pointed out, then yes. If you don't use that
type of form, then you have to hand code this, and not only is that a lot
work, it also likely means that you data designs are wrong (not normalized).
If you design your data structures (tables) correctly, then you should well
be able to display and edit those many records on a form you design (that
form will likely need some continues sub-forms).

 

[cjg.groups]

Thank you for your help. I will use the grid subform as you
described.