Hey Ken, thanks for posting back. I hope you enjoyed your
(hopefully!) extended weekend.
Let's stop and rethink your form's design before we add more code to your
setup.
If you want to have one record added for each item that is in the first
combo box's list, you certainly can add new records to the subform and then
have the user enter data into each record. But why would you then want to
leave the second combo box in that subform, where the user could actually
change the desired value in the record from what you wanted it to be? In
other words, if you want the first record in the subform to be for "A", then
don't put "A" in a combo box control for that record because the user could
change it to "B", thereby making your data wrong or messed up.
So perhaps what you really want is to generate a series of records with
"protected" values ("A", "B", etc.) -- one for each record -- and have the
user enter the data for that item in each record. No combo box at all --
just a locked textbox that would display the "A", "B", etc. value for the
record. Make sense?
Yes, this idea does make more sense. The way it is right now has a
combobox that fills with values depending on the record on the main
form. If there were a way to just fill locked textboxes with the
values, instead of the combobox, it would be more efficient, although
at present the user gets an error if he tries to save a record in the
subform with "A", if there already exists an "A" in that subform
(because the combobox field is part of the primary key).
Perhaps it will clarify more about why I had the combo box initially
if I tell you the whole story going on here.
I've got a table called tblComponents. Its got a Component_No as the
primary key. Belonging to each component are dimensions, in a table
called tblValidCompDimensions. You'll see why its called this in a
minute. These dimensions are the parts of the component that, when a
shipment of the component comes in, are inspected to make sure the
component is good to use. Each dimension also has a corresponding
Tool that is used to measure that Dimension, so whoever is doing the
measuring knows how to measure. So we have:
tblComponents
Component_No (PK)
....other fields
tblValidCompDimensions (child table)
Component_No (PK)
Dimension_No (PK)
Inspection_Tool
Note that these tables aren't the ones that I have been referencing on
the forms, but you need to know this to understand what comes next.
On the main form that I've been talking about, it displays information
from tblShipments. Shipments just have an Autonumber PK, and
Shipments contain a Component (A Shipment will never contain multiple
Components..that would be a different Shipment). So here's where all
the plans unfold.
On the form, the user selects a Component from a combobox that draws
its values from tblComponents, representing what Component was in the
Shipment. Then the user fills out date, lot size, and other data
about the Shipment. After the main form is complete, representing
what came in the Shipment, the user fills out the subform,
representing the Shipment Inspection. When the user had selected the
Component, the combobox on the subform's Rowsource property changed to
only contain the values (Dimensions) that belongs to that Component
selected, as can be found in the tblValidCompDimensions. Hence, each
record in tblValidComponentDimensions is a valid matchup of a
Component and a Dimension, because I don't want to see Dimensions in
that combobox that do not belong to that Component. Then the user
just has to select a Dimension, fill out how well the Components look
for this Dimension (in tolerance), record results, and do the same for
the next Dimension. After all the Dimensions are measured, the record
is complete.
One thing I don't have right now that I would need to add is the
Inspection_Tool to the subform. I assume if its possible to add a
record with a specific field already filled (the Dimension), then it
would easy to do the same for the Tool that goes along with that
Dimension.
I devised this method with some trouble, for I had a hard time trying
to design tables and forms with the idea that a given Component had
the same Dimensions to be measured every Shipment, but the Shipment
Inspection is an "instance" of that Component, and so the tolerance of
its Dimensions were always different, and must be recorded so someone
can look back upon Shipments and see how well they passed the
tolerance tests.
Then, think about the user's process for entering the data. You envision
adding all the needed records to the subform at the beginning and having the
user enter data into each record. This certainly can be done -- if the
subform's record can be saved to the table with just the "A", "B", etc.
value in that record and without any user-entered data at that point. Check
your table's fields to be sure none are set to Required if the field will be
"empty" at the time you create the record for the user and then go on to
create the next record, etc. Then, how will you require that the user
entered data for each subform record before the form is closed or the main
form moves to a new record? Do you plan to run validation checking at some
point to ensure that each record in the subform has a value? Or will an
"empty" record be ok for your data?
It would be nice if it could be a guarentee that all fields are filled
in.
Another way to do this is to generate just a single record in the subform at
a time. When the user selects an item in the first combo box, generate a new
record in the subform for the first "A" (or whatever) value. Let the user
enter data into it, and then use the saving of that record as the trigger to
create the next record (for the "B" or whatever value); and continue until
all necessary records have been entered. You'd need some validation checking
to be sure the user doesn't stop entering data before all of the needed
subform records have been entered if you indeed need all data to be
entered -- this could be done n the Exit event of the subform control to
cancel leaving the subform until all data have been entered.
This idea would work as well. As long as all the Dimensions are
filled so that the Inspection is "complete".
Either way, before you program an approach, give thought to the entire
process -- what you want to happen, what you don't want to happen, etc. Post
back with your ideas/desires and then we can get into programming that will
accomplish what you seek.
Thanks for the help so far. Let's hope the programming is the easy
part now that we've established what we need to do.
.