Help! How can I insert mulitple records?

[JustABeginner]

I have asked this question so mnay ways on several sites, and I have yet to
recieve a relevant answer. I have a form Called Patients. On that form, I
have a subform VisitInformation. That form has a subform called tests, which
is in datasheet view. I want to select the kit that the test belongs to, and
have the test form display all the tests that are in that kit, so that I can
fill in the results, etc. for each test. Right now it is set up so that I
have to select each test individually from a combobox. I tried an append
query, but I was unable to find a way to make it populate the records
correctly.

 

[Rob Oldfield]

No offense, but the reason that you've had responses might be that your
question is badly phrased. Where's the combo? Main form or subform? Which
records do you want to update in what way? (...records don't update on a
form, they update on a table, which may or may not be the basis for any
master/child forms)

 

[JustABeginner]

The patient information form has a subform VisitInformation. The combo box
that has the kit name is on the VisitInformation form. Each patient will use
several kits, each containing several tests. I want to be able to select the
name of the kit on the visitinformation form, and then automatically have all
the tests of the kit inserted into the Test Name field. Once the names
insert, I can fill in the rest of the test details. After I complete the
test details, I want the information I just entered to update on the table,
then when I go to the next patient record, I want a blank visit and test
subform to start with the next patient. I have the tests subform in a
datasheet view, so that all the tests for a single patient can be viewed at
once.

 

[Duane Hookom]

I assume your test subform might be similar to the subform used in At Your
Survey found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. Check to
see how questions are appended in the subform for results.

 

[JustABeginner]

That is sort of what I was looking for. However, all I want is for the
subform to automatically list the values in the combo box (one per record),
so that each value does not have to be individually selected. So if I select
Kit A from the combo box, I want to see an individual record in the subform
for every test included in Kit A. I don't think that this model quite fits
what I want to do, but I am not the best at figuring these things out, so I
may be wrong.

 

[John Vinson]

The patient information form has a subform VisitInformation. The combo box
that has the kit name is on the VisitInformation form. Each patient will use
several kits, each containing several tests. I want to be able to select the
name of the kit on the visitinformation form, and then automatically have all
the tests of the kit inserted into the Test Name field. Once the names
insert, I can fill in the rest of the test details. After I complete the
test details, I want the information I just entered to update on the table,
then when I go to the next patient record, I want a blank visit and test
subform to start with the next patient. I have the tests subform in a
datasheet view, so that all the tests for a single patient can be viewed at
once.

It sounds like you want to run an Append query appending all the tests
in a kit to the test table, when the kit is selected.

Don't try to do this on a form - insert the records directly into the
table, with an append query.

John W. Vinson[MVP]

 

[JustABeginner]

If I put it directly on the table, how can I requery to get the same choices
for the next patient?

 

[Nick Coe \(UK\)]

I'm wondering what your table design and relations (ER
diagram if that rings any bells) looks like. From reading
the posts I think you may need to refine the design a bit
before getting back to the form(s).

What are the actual mechanics here; does someone visit a
given patient and monitor the tests?
If the tests are carried out by the patients themselves,
what purpose does a visit serve, what actually happens?
Is the visit to collect the results perhaps?

I assume Test contains instructions or procedures and each
Kit contains a variety of Tests - there's a one to many I
think.

Patient to VisitInfo is probably a one to many.

But what data goes into VisitInfo that relates it to Kit (I
don't mean keys but the subject matter itself)? Define that
and we know what sort of relation to try and then how to
model it on the form(s).

--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.pjandcoe.co.uk/ Online Store
http://www.mrcomputersltd.com/ Repairs Upgrades

In JustABeginner typed:

 

[John Vinson]

If I put it directly on the table, how can I requery to get the same choices
for the next patient?

I'm sorry, this seems to make no sense. Does every patient get the
same kit of tests?

As Nick asks... what are your table structures? How are you storing
the "kits", the list of tests available, and the list of tests that
pertain to a patient?

John W. Vinson[MVP]

 

[JustABeginner]

Each kit contains several tests. Multiple patients can have the same kit,
but not all patients will have the same kit. For example, Kit A contains
tests 1, 2, 3 and 4. Patients 5, 10 and 30 may all use tests from Kit A, but
they may also each use kits B and C. (So this all makes a many to many
relationship). I have a table tblVisits, which contains the date of the
visit, Physician and the kit(s) used. When a kit is selected on the form
Visits, I would like the subform TestDetails (which has the fields from the
table TestDetails, like result, length of time, etc.) To show all the tests
in the selected kit. If there are several Kits used, then I would like the
tests fromm those kits also shown. Right now, I have the names of the tests
in a table titled tests, and I use this table as the source for the Test
Field in the TestDetail Table. What I really want to do is save the time
from selecting each table in each kit, and instead, have all the test names
entered as new records conected to the patient.
I have a junction table between the visit and Test Details tables, so that a
many to many relationship can be made. The table has: pkVisitTestID;
VisitID; and TestDetailID.