Populating a field with multiple records based on a combo box

[JustABeginner]

Hi,
I am creating a database in which I currently have a form (Visit), with
several subforms (TestKit1, TestKit2....). What I want to be able to do is
select one of the test kits from a combo box, then subsequently the subform
will be populated with individual records for each of the tests in the
selected kit, so that each kit does not have to be individually selected, and
then the result details can be entered into the other fields. Each visit can
have multiple tests and test kits, and each of the possible tests is
currently in a combo box list. How can I automatically have the test names
entered into the test name field?

 

[Allen Browne]

To answer the question you asked, you can use the AfterUpdate (or
AfterInsert) event of the main form to Execute an Append query statement
that inserts records into the related table that the subform gets its
records from.

Before you get to there, you need to have a suitable relational structure in
place, though. You say that one Visit can have multiple Tests. That means
you need a Visit table, and a Test table, with a one-to-many relationship.

One test can have multiple kits? If so, you have a many-to-many relationship
betwen kits and tests, i.e. one kit can be used in many tests, and one test
can have many kits. You need a junction table to resolve this. this TestKit
table will have fields:
TestID relates to the Test table's primary key;
KitID relates to the Kit table's priamry key.
(Presumably you already have a Kit table that contains one record for each
kit.)

Or, perhaps when you talk about a "Test", you are referring to a pre-defined
test type that the doctor can choose from? If so, you will have a TestType
table (one record for each type of test.) The entries in this table will
become a drop-down list of test types to use in the Test table above (i.e.
each actual test will be one of the items from pre-defined TestType list.)

For any TestType, there will probably be kits that are normally used. You
will therefore have a TestTypeKit table that defines, these. Ultimately,
this will be where the Append query gets its information from to execute the
query to append the actual kits used in a test.

Hope that hasn't confused you: it is *really* important to resolve all these
one-to-many relationships and get the structure right before you strart to
try to solve the questions about the interface.

 

[JustABeginner]

Yes, it is a many to many relationship, one test can have several kits, and
one kit has several tests.
So I made the append query, and was able to append the right records to the
table. But, how can I make it so that each time I select the kit on a form,
all the tests from that kit are shown on the form or subform?
Currently, I have a subform that is in a datasheet form. Each test I select
corresponds with the patient's record I am on, then when I move to the next
patient's record, a blank form appears, and the information I enter adds to
the selected patient in the table. How I have it set up right now, is how I
would like it to work, except I would like all the appended test names to
show each time the kit is selected.
As for the relationships, I think I have them right.
tbl Visit: pkVisit ID; KitID
tbl KitTestJxn: pkKitID; TestID
tbl Test: pkTestID;ResultID
tblResultkResultID

 

[Allen Browne]

I don't think I can answer this question about how to design the interface
to execute the append query, because I don't understand the underlying
structure, nor the logic you are using.

You cannot create the related record(s) at the point when you select the kit
in the main record, because the primary record has not been saved yet. If it
is a new entry in the main record, that record must be saved first, so
Form_AfterInsert or Form_AfterUpdate would be the event to execute the
append query.

If it was NOT a new record, and the user actually changed the kit from its
previous value, do you need to remove the related records you created
previously and create new ones instead? Is this acceptable even if test
results exist for the old ones?

I also suspect that you need several additional tables to teach Access which
kits typically go with what tests so you have the default combinations that
give it the defaults to use for the append query, whereas the results go to
actual instances of those test types associated with a visit of a patient.