Number of items in Combo Box

On May 30, 8:45 pm, "Ken Snell \(MVP\)"
wrote:
I need to spend a bit more time digesting the details of what you posted,
but while I do that, a thought/suggestion for you ---

Think about whether this setup would work for you. Instead of using a
subform that has all the "letter-identified" records showing and awaiting
user input, what if the form popped up a form to the user for the "A" record
so that the user could enter the data; then it changed the info in that
popup form to the "B" record for data entry, and so on, until all data
records have been entered. What this would do for you is make sure that the
user provides data to all records, ensures that the user doesn't skip a
record/data item, avoids problem of adding "empty" records to table that
still need data, and provides a more fluid data entry flow for the user
without having to figure out what to do next.

Let me know, while I give more thought to your process desires.
--

Hey Ken,

Although this idea would work essentially, I'm not sure I (or my
employer) would be happy with it. Where does the
data go after a user enters data onto the popup form? The subform
(child table)? If so, whats the point of doing this instead of just a
subform?

What happens if the user incorrectly enters data, or wants to write
over some data entered this way (for example, he figures out that some
components he thought were good were really bad, or visa versa).
There is also the possibility that it is decided that another
dimension should be added. Right now I have a command button that
opens the Components form (which has a subform of the Dimensions) so
the user can add Dimensions, then go back to the Shipments form and
add the data for that newly added Dimension. This possibility is
extremely rare, but I don't think it should be ruled out just because.

Also, new shipments are inspected and entered as new records into the
database, but if a user wants to look up data about an old shipment,
he will need to know about all the inspected dimensions (a list of
them), which would be the perfect job for a subform.

You'll have to explain more of the benefits of this method before you
sell me, but I appreciate your desire to help.

Is there anything I can do to better explain my previous post? There
was a lot I laid out and wouldn't be a surprise to me if it was hard
to understand .



Ken Snell
MS ACCESS MVP

"Garret" wrote in message

oups.com...



Hey Ken, thanks for posting back. I hope you enjoyed your
(hopefully!) extended weekend.

On May 26, 5:46 pm, "Ken Snell \(MVP\)"
wrote:
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.- Hide quoted text -

- Show quoted text -

ok, am tied up all day but will definitely work to get you a reply tonite..

--

Ken Snell
MS ACCESS MVP


"Garret" wrote in message
oups.com...
On May 30, 8:45 pm, "Ken Snell \(MVP\)"
wrote:
I need to spend a bit more time digesting the details of what you posted,
but while I do that, a thought/suggestion for you ---

Think about whether this setup would work for you. Instead of using a
subform that has all the "letter-identified" records showing and awaiting
user input, what if the form popped up a form to the user for the "A"
record
so that the user could enter the data; then it changed the info in that
popup form to the "B" record for data entry, and so on, until all data
records have been entered. What this would do for you is make sure that
the
user provides data to all records, ensures that the user doesn't skip a
record/data item, avoids problem of adding "empty" records to table that
still need data, and provides a more fluid data entry flow for the user
without having to figure out what to do next.

Let me know, while I give more thought to your process desires.
--

Hey Ken,

Although this idea would work essentially, I'm not sure I (or my
employer) would be happy with it. Where does the
data go after a user enters data onto the popup form? The subform
(child table)? If so, whats the point of doing this instead of just a
subform?

What happens if the user incorrectly enters data, or wants to write
over some data entered this way (for example, he figures out that some
components he thought were good were really bad, or visa versa).
There is also the possibility that it is decided that another
dimension should be added. Right now I have a command button that
opens the Components form (which has a subform of the Dimensions) so
the user can add Dimensions, then go back to the Shipments form and
add the data for that newly added Dimension. This possibility is
extremely rare, but I don't think it should be ruled out just because.

Also, new shipments are inspected and entered as new records into the
database, but if a user wants to look up data about an old shipment,
he will need to know about all the inspected dimensions (a list of
them), which would be the perfect job for a subform.

You'll have to explain more of the benefits of this method before you
sell me, but I appreciate your desire to help.

Is there anything I can do to better explain my previous post? There
was a lot I laid out and wouldn't be a surprise to me if it was hard
to understand .

On May 31, 1:19 pm, "Ken Snell \(MVP\)"
wrote:
ok, am tied up all day but will definitely work to get you a reply tonite..

--

Ken Snell
MS ACCESS MVP

"Garret" wrote in message

oups.com...



On May 30, 8:45 pm, "Ken Snell \(MVP\)"
wrote:
I need to spend a bit more time digesting the details of what you posted,
but while I do that, a thought/suggestion for you ---

Think about whether this setup would work for you. Instead of using a
subform that has all the "letter-identified" records showing and awaiting
user input, what if the form popped up a form to the user for the "A"
record
so that the user could enter the data; then it changed the info in that
popup form to the "B" record for data entry, and so on, until all data
records have been entered. What this would do for you is make sure that
the
user provides data to all records, ensures that the user doesn't skip a
record/data item, avoids problem of adding "empty" records to table that
still need data, and provides a more fluid data entry flow for the user
without having to figure out what to do next.

Let me know, while I give more thought to your process desires.
--

Hey Ken,

Although this idea would work essentially, I'm not sure I (or my
employer) would be happy with it. Where does the
data go after a user enters data onto the popup form? The subform
(child table)? If so, whats the point of doing this instead of just a
subform?

What happens if the user incorrectly enters data, or wants to write
over some data entered this way (for example, he figures out that some
components he thought were good were really bad, or visa versa).
There is also the possibility that it is decided that another
dimension should be added. Right now I have a command button that
opens the Components form (which has a subform of the Dimensions) so
the user can add Dimensions, then go back to the Shipments form and
add the data for that newly added Dimension. This possibility is
extremely rare, but I don't think it should be ruled out just because.

Also, new shipments are inspected and entered as new records into the
database, but if a user wants to look up data about an old shipment,
he will need to know about all the inspected dimensions (a list of
them), which would be the perfect job for a subform.

You'll have to explain more of the benefits of this method before you
sell me, but I appreciate your desire to help.

Is there anything I can do to better explain my previous post? There
was a lot I laid out and wouldn't be a surprise to me if it was hard
to understand .- Hide quoted text -

- Show quoted text -

Sure. I finish work at 4:30 EST though, but I'll try to remind myself
to check the forums when I go home tonight. If not, I'll reply first
thing tomorrow morning.

OK - based on what you desire, here is some sample code that will add a new
record to the subform for each item in a combo box -- replace generic names
with real names --- SubformName is the name of the subform control on the
main form (the control that holds the subform object):


Dim lngItem As Long
DoCmd.Echo False
With Me.SubformName.Form.RecordsetClone
For lngItem = 0 To Me.ComboBoxName.ListCount - 1
.AddNew
.Fields("Component_No").Value = Me.Component_No.Value
.Fields("Dimension_No").Value =
Me.ComboBoxName.ItemData(lngItem)
.Update
Next lngItem
End With
Me.SubformName.Requery
DoCmd.Echo True


--

Ken Snell
MS ACCESS MVP



"Garret" wrote in message
oups.com...
On May 30, 8:45 pm, "Ken Snell \(MVP\)"
wrote:
I need to spend a bit more time digesting the details of what you posted,
but while I do that, a thought/suggestion for you ---

Think about whether this setup would work for you. Instead of using a
subform that has all the "letter-identified" records showing and awaiting
user input, what if the form popped up a form to the user for the "A"
record
so that the user could enter the data; then it changed the info in that
popup form to the "B" record for data entry, and so on, until all data
records have been entered. What this would do for you is make sure that
the
user provides data to all records, ensures that the user doesn't skip a
record/data item, avoids problem of adding "empty" records to table that
still need data, and provides a more fluid data entry flow for the user
without having to figure out what to do next.

Let me know, while I give more thought to your process desires.
--

Hey Ken,

Although this idea would work essentially, I'm not sure I (or my
employer) would be happy with it. Where does the
data go after a user enters data onto the popup form? The subform
(child table)? If so, whats the point of doing this instead of just a
subform?

What happens if the user incorrectly enters data, or wants to write
over some data entered this way (for example, he figures out that some
components he thought were good were really bad, or visa versa).
There is also the possibility that it is decided that another
dimension should be added. Right now I have a command button that
opens the Components form (which has a subform of the Dimensions) so
the user can add Dimensions, then go back to the Shipments form and
add the data for that newly added Dimension. This possibility is
extremely rare, but I don't think it should be ruled out just because.

Also, new shipments are inspected and entered as new records into the
database, but if a user wants to look up data about an old shipment,
he will need to know about all the inspected dimensions (a list of
them), which would be the perfect job for a subform.

You'll have to explain more of the benefits of this method before you
sell me, but I appreciate your desire to help.

Is there anything I can do to better explain my previous post? There
was a lot I laid out and wouldn't be a surprise to me if it was hard
to understand .



Ken Snell
MS ACCESS MVP

"Garret" wrote in message

oups.com...



Hey Ken, thanks for posting back. I hope you enjoyed your
(hopefully!) extended weekend.

On May 26, 5:46 pm, "Ken Snell \(MVP\)"
wrote:
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.- Hide quoted text -

- Show quoted text -

Actually, as I think about it, the Requery step isn't needed:

Dim lngItem As Long
DoCmd.Echo False
With Me.SubformName.Form.RecordsetClone
For lngItem = 0 To Me.ComboBoxName.ListCount - 1
.AddNew
.Fields("Component_No").Value = Me.Component_No.Value
.Fields("Dimension_No").Value =
Me.ComboBoxName.ItemData(lngItem)
.Update
Next lngItem
End With
DoCmd.Echo True

--

Ken Snell
MS ACCESS MVP




"Ken Snell (MVP)" wrote in message
...
OK - based on what you desire, here is some sample code that will add a
new record to the subform for each item in a combo box -- replace generic
names with real names --- SubformName is the name of the subform control
on the main form (the control that holds the subform object):


Dim lngItem As Long
DoCmd.Echo False
With Me.SubformName.Form.RecordsetClone
For lngItem = 0 To Me.ComboBoxName.ListCount - 1
.AddNew
.Fields("Component_No").Value = Me.Component_No.Value
.Fields("Dimension_No").Value =
Me.ComboBoxName.ItemData(lngItem)
.Update
Next lngItem
End With
Me.SubformName.Requery
DoCmd.Echo True


--

Ken Snell
MS ACCESS MVP



"Garret" wrote in message
oups.com...
On May 30, 8:45 pm, "Ken Snell \(MVP\)"
wrote:
I need to spend a bit more time digesting the details of what you
posted,
but while I do that, a thought/suggestion for you ---

Think about whether this setup would work for you. Instead of using a
subform that has all the "letter-identified" records showing and
awaiting
user input, what if the form popped up a form to the user for the "A"
record
so that the user could enter the data; then it changed the info in that
popup form to the "B" record for data entry, and so on, until all data
records have been entered. What this would do for you is make sure that
the
user provides data to all records, ensures that the user doesn't skip a
record/data item, avoids problem of adding "empty" records to table that
still need data, and provides a more fluid data entry flow for the user
without having to figure out what to do next.

Let me know, while I give more thought to your process desires.
--

Hey Ken,

Although this idea would work essentially, I'm not sure I (or my
employer) would be happy with it. Where does the
data go after a user enters data onto the popup form? The subform
(child table)? If so, whats the point of doing this instead of just a
subform?

What happens if the user incorrectly enters data, or wants to write
over some data entered this way (for example, he figures out that some
components he thought were good were really bad, or visa versa).
There is also the possibility that it is decided that another
dimension should be added. Right now I have a command button that
opens the Components form (which has a subform of the Dimensions) so
the user can add Dimensions, then go back to the Shipments form and
add the data for that newly added Dimension. This possibility is
extremely rare, but I don't think it should be ruled out just because.

Also, new shipments are inspected and entered as new records into the
database, but if a user wants to look up data about an old shipment,
he will need to know about all the inspected dimensions (a list of
them), which would be the perfect job for a subform.

You'll have to explain more of the benefits of this method before you
sell me, but I appreciate your desire to help.

Is there anything I can do to better explain my previous post? There
was a lot I laid out and wouldn't be a surprise to me if it was hard
to understand .



Ken Snell
MS ACCESS MVP

"Garret" wrote in message

oups.com...



Hey Ken, thanks for posting back. I hope you enjoyed your
(hopefully!) extended weekend.

On May 26, 5:46 pm, "Ken Snell \(MVP\)"
wrote:
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.- Hide quoted
text -

- Show quoted text -

On Jun 1, 12:13 am, "Ken Snell \(MVP\)"
wrote:
Actually, as I think about it, the Requery step isn't needed:

Dim lngItem As Long
DoCmd.Echo False
With Me.SubformName.Form.RecordsetClone
For lngItem = 0 To Me.ComboBoxName.ListCount - 1
.AddNew
.Fields("Component_No").Value = Me.Component_No.Value
.Fields("Dimension_No").Value =
Me.ComboBoxName.ItemData(lngItem)
.Update
Next lngItem
End With
DoCmd.Echo True

Hey Ken,
I'm a bit confused with the code but it looks like a good start. For
one thing, the Inspection_Dimension table (the subform's data) does
not have a Component_No field. It could, and probably should for data
design sake, but isn't necessary. What it does have is a Shipment_No,
so it knows which Shipment the Inspection belongs to. So I modified
the code to look like this:

'I added in the bit about the new Record since thats the only
'time I wanted this code to run
If Me.NewRecord = True Then
Dim lngItem As Long
DoCmd.Echo False
With Me.sbfInspection.Form.RecordsetClone
For lngItem = 0 To Me.sbfInspection.Form!
cboDimension_No.ListCount - 1
.AddNew
.Fields("Shipment_No").Value = Me.Shipment_No.Value
.Fields("Dimension_No").Value =
Me.sbfInspection.Form!cboDimension_No.ItemData(lng Item)
.Update
Next lngItem
End With
DoCmd.Echo True
End If

Right now, I get the "Run time error 3101", saying "The Microsoft Jet
Database Engine cannot find a record in the table 'tblShipments' with
the key matching field(s) 'Shipment_No'."

This seems silly to me, for I clearly have the field Shipment_No in
the tblShipments (its the primary key!).



--

Ken Snell
MS ACCESS MVP

"Ken Snell (MVP)" wrote in l...



OK - based on what you desire, here is some sample code that will add a
new record to the subform for each item in a combo box -- replace generic
names with real names --- SubformName is the name of the subform control
on the main form (the control that holds the subform object):

Dim lngItem As Long
DoCmd.Echo False
With Me.SubformName.Form.RecordsetClone
For lngItem = 0 To Me.ComboBoxName.ListCount - 1
.AddNew
.Fields("Component_No").Value = Me.Component_No.Value
.Fields("Dimension_No").Value =
Me.ComboBoxName.ItemData(lngItem)
.Update
Next lngItem
End With
Me.SubformName.Requery
DoCmd.Echo True

--

Ken Snell
MS ACCESS MVP

"Garret" wrote in message
roups.com...
On May 30, 8:45 pm, "Ken Snell \(MVP\)"
wrote:
I need to spend a bit more time digesting the details of what you
posted,
but while I do that, a thought/suggestion for you ---

Think about whether this setup would work for you. Instead of using a
subform that has all the "letter-identified" records showing and
awaiting
user input, what if the form popped up a form to the user for the "A"
record
so that the user could enter the data; then it changed the info in that
popup form to the "B" record for data entry, and so on, until all data
records have been entered. What this would do for you is make sure that
the
user provides data to all records, ensures that the user doesn't skip a
record/data item, avoids problem of adding "empty" records to table that
still need data, and provides a more fluid data entry flow for the user
without having to figure out what to do next.

Let me know, while I give more thought to your process desires.
--

Hey Ken,

Although this idea would work essentially, I'm not sure I (or my
employer) would be happy with it. Where does the
data go after a user enters data onto the popup form? The subform
(child table)? If so, whats the point of doing this instead of just a
subform?

What happens if the user incorrectly enters data, or wants to write
over some data entered this way (for example, he figures out that some
components he thought were good were really bad, or visa versa).
There is also the possibility that it is decided that another
dimension should be added. Right now I have a command button that
opens the Components form (which has a subform of the Dimensions) so
the user can add Dimensions, then go back to the Shipments form and
add the data for that newly added Dimension. This possibility is
extremely rare, but I don't think it should be ruled out just because.

Also, new shipments are inspected and entered as new records into the
database, but if a user wants to look up data about an old shipment,
he will need to know about all the inspected dimensions (a list of
them), which would be the perfect job for a subform.

You'll have to explain more of the benefits of this method before you
sell me, but I appreciate your desire to help.

Is there anything I can do to better explain my previous post? There
was a lot I laid out and wouldn't be a surprise to me if it was hard
to understand .

Ken Snell
MS ACCESS MVP

"Garret" wrote in message

egroups.com...

Hey Ken, thanks for posting back. I hope you enjoyed your
(hopefully!) extended weekend.

On May 26, 5:46 pm, "Ken Snell \(MVP\)"
wrote:
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.

On Jun 1, 8:22 am, Garret wrote:
On Jun 1, 12:13 am, "Ken Snell \(MVP\)"

wrote:
Actually, as I think about it, the Requery step isn't needed:

Dim lngItem As Long
DoCmd.Echo False
With Me.SubformName.Form.RecordsetClone
For lngItem = 0 To Me.ComboBoxName.ListCount - 1
.AddNew
.Fields("Component_No").Value = Me.Component_No.Value
.Fields("Dimension_No").Value =
Me.ComboBoxName.ItemData(lngItem)
.Update
Next lngItem
End With
DoCmd.Echo True

Hey Ken,
I'm a bit confused with the code but it looks like a good start. For
one thing, the Inspection_Dimension table (the subform's data) does
not have a Component_No field. It could, and probably should for data
design sake, but isn't necessary. What it does have is a Shipment_No,
so it knows which Shipment the Inspection belongs to. So I modified
the code to look like this:

'I added in the bit about the new Record since thats the only
'time I wanted this code to run
If Me.NewRecord = True Then
Dim lngItem As Long
DoCmd.Echo False
With Me.sbfInspection.Form.RecordsetClone
For lngItem = 0 To Me.sbfInspection.Form!
cboDimension_No.ListCount - 1
.AddNew
.Fields("Shipment_No").Value = Me.Shipment_No.Value
.Fields("Dimension_No").Value =
Me.sbfInspection.Form!cboDimension_No.ItemData(lng Item)
.Update
Next lngItem
End With
DoCmd.Echo True
End If

Right now, I get the "Run time error 3101", saying "The Microsoft Jet
Database Engine cannot find a record in the table 'tblShipments' with
the key matching field(s) 'Shipment_No'."

This seems silly to me, for I clearly have the field Shipment_No in
the tblShipments (its the primary key!).





--

Ken Snell
MS ACCESS MVP

"Ken Snell (MVP)" wrote in l...

OK - based on what you desire, here is some sample code that will add a
new record to the subform for each item in a combo box -- replace generic
names with real names --- SubformName is the name of the subform control
on the main form (the control that holds the subform object):

Dim lngItem As Long
DoCmd.Echo False
With Me.SubformName.Form.RecordsetClone
For lngItem = 0 To Me.ComboBoxName.ListCount - 1
.AddNew
.Fields("Component_No").Value = Me.Component_No.Value
.Fields("Dimension_No").Value =
Me.ComboBoxName.ItemData(lngItem)
.Update
Next lngItem
End With
Me.SubformName.Requery
DoCmd.Echo True

--

Ken Snell
MS ACCESS MVP

"Garret" wrote in message
roups.com...
On May 30, 8:45 pm, "Ken Snell \(MVP\)"
wrote:
I need to spend a bit more time digesting the details of what you
posted,
but while I do that, a thought/suggestion for you ---

Think about whether this setup would work for you. Instead of using a
subform that has all the "letter-identified" records showing and
awaiting
user input, what if the form popped up a form to the user for the "A"
record
so that the user could enter the data; then it changed the info in that
popup form to the "B" record for data entry, and so on, until all data
records have been entered. What this would do for you is make sure that
the
user provides data to all records, ensures that the user doesn't skip a
record/data item, avoids problem of adding "empty" records to table that
still need data, and provides a more fluid data entry flow for the user
without having to figure out what to do next.

Let me know, while I give more thought to your process desires.
--

Hey Ken,

Although this idea would work essentially, I'm not sure I (or my
employer) would be happy with it. Where does the
data go after a user enters data onto the popup form? The subform
(child table)? If so, whats the point of doing this instead of just a
subform?

What happens if the user incorrectly enters data, or wants to write
over some data entered this way (for example, he figures out that some
components he thought were good were really bad, or visa versa).
There is also the possibility that it is decided that another
dimension should be added. Right now I have a command button that
opens the Components form (which has a subform of the Dimensions) so
the user can add Dimensions, then go back to the Shipments form and
add the data for that newly added Dimension. This possibility is
extremely rare, but I don't think it should be ruled out just because.

Also, new shipments are inspected and entered as new records into the
database, but if a user wants to look up data about an old shipment,
he will need to know about all the inspected dimensions (a list of
them), which would be the perfect job for a subform.

You'll have to explain more of the benefits of this method before you
sell me, but I appreciate your desire to help.

Is there anything I can do to better explain my previous post? There
was a lot I laid out and wouldn't be a surprise to me if it was hard
to understand .

Ken Snell
MS ACCESS MVP

"Garret" wrote in message

egroups.com...

Hey Ken, thanks for posting back. I hope you enjoyed your
(hopefully!) extended weekend.

On May 26, 5:46 pm, "Ken Snell \(MVP\)"
wrote:
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,

...

read more »- Hide quoted text -

- Show quoted text -

I think I figured out why there was an error - I was running the code
in the forms Before_Update event, so I think it was trying to add
records to a record which wasn't in the database yet.

This brings the question - where DOES it go? I put the code in a
command button on the Form and tried it out (without the NewRecord
check) and it worked perfectly. One other thing...

Note before, I mentioned how each Dimension also has an
Inspection_Tool that is used to measure the dimension. The user needs
to know this so he/she can do the measuring. I think to do this I
would need a field (bound, unbound, or calculated) that links to the
same record that draws the Dimension data and now also draw the
Inspection_Tool data. Not exactly sure how to do that but I think I
can figure it out with more thought.

My last post can't be read very well, so reposting:

I think I figured out why there was an error - I was running the code
in the forms Before_Update event, so I think it was trying to add
records to a record which wasn't in the database yet.

This brings the question - where DOES it go? I put the code in a
command button on the Form and tried it out (without the NewRecord
check) and it worked perfectly. One other thing...

Note before, I mentioned how each Dimension also has an
Inspection_Tool that is used to measure the dimension. The user
needs
to know this so he/she can do the measuring. I think to do this I
would need a field (bound, unbound, or calculated) that links to the
same record that draws the Dimension data and now also draw the
Inspection_Tool data. Not exactly sure how to do that but I think I
can figure it out with more thought.




On Jun 1, 8:22 am, Garret wrote:
On Jun 1, 12:13 am, "Ken Snell \(MVP\)"

wrote:
Actually, as I think about it, the Requery step isn't needed:

Dim lngItem As Long
DoCmd.Echo False
With Me.SubformName.Form.RecordsetClone
For lngItem = 0 To Me.ComboBoxName.ListCount - 1
.AddNew
.Fields("Component_No").Value = Me.Component_No.Value
.Fields("Dimension_No").Value =
Me.ComboBoxName.ItemData(lngItem)
.Update
Next lngItem
End With
DoCmd.Echo True

Hey Ken,
I'm a bit confused with the code but it looks like a good start. For
one thing, the Inspection_Dimension table (the subform's data) does
not have a Component_No field. It could, and probably should for data
design sake, but isn't necessary. What it does have is a Shipment_No,
so it knows which Shipment the Inspection belongs to. So I modified
the code to look like this:

'I added in the bit about the new Record since thats the only
'time I wanted this code to run
If Me.NewRecord = True Then
Dim lngItem As Long
DoCmd.Echo False
With Me.sbfInspection.Form.RecordsetClone
For lngItem = 0 To Me.sbfInspection.Form!
cboDimension_No.ListCount - 1
.AddNew
.Fields("Shipment_No").Value = Me.Shipment_No.Value
.Fields("Dimension_No").Value =
Me.sbfInspection.Form!cboDimension_No.ItemData(lng Item)
.Update
Next lngItem
End With
DoCmd.Echo True
End If

Right now, I get the "Run time error 3101", saying "The Microsoft Jet
Database Engine cannot find a record in the table 'tblShipments' with
the key matching field(s) 'Shipment_No'."

This seems silly to me, for I clearly have the field Shipment_No in
the tblShipments (its the primary key!).





--

Ken Snell
MS ACCESS MVP

"Ken Snell (MVP)" wrote in l...

OK - based on what you desire, here is some sample code that will add a
new record to the subform for each item in a combo box -- replace generic
names with real names --- SubformName is the name of the subform control
on the main form (the control that holds the subform object):

Dim lngItem As Long
DoCmd.Echo False
With Me.SubformName.Form.RecordsetClone
For lngItem = 0 To Me.ComboBoxName.ListCount - 1
.AddNew
.Fields("Component_No").Value = Me.Component_No.Value
.Fields("Dimension_No").Value =
Me.ComboBoxName.ItemData(lngItem)
.Update
Next lngItem
End With
Me.SubformName.Requery
DoCmd.Echo True

--

Ken Snell
MS ACCESS MVP

"Garret" wrote in message
roups.com...
On May 30, 8:45 pm, "Ken Snell \(MVP\)"
wrote:
I need to spend a bit more time digesting the details of what you
posted,
but while I do that, a thought/suggestion for you ---

Think about whether this setup would work for you. Instead of using a
subform that has all the "letter-identified" records showing and
awaiting
user input, what if the form popped up a form to the user for the "A"
record
so that the user could enter the data; then it changed the info in that
popup form to the "B" record for data entry, and so on, until all data
records have been entered. What this would do for you is make sure that
the
user provides data to all records, ensures that the user doesn't skip a
record/data item, avoids problem of adding "empty" records to table that
still need data, and provides a more fluid data entry flow for the user
without having to figure out what to do next.

Let me know, while I give more thought to your process desires.
--

Hey Ken,

Although this idea would work essentially, I'm not sure I (or my
employer) would be happy with it. Where does the
data go after a user enters data onto the popup form? The subform
(child table)? If so, whats the point of doing this instead of just a
subform?

What happens if the user incorrectly enters data, or wants to write
over some data entered this way (for example, he figures out that some
components he thought were good were really bad, or visa versa).
There is also the possibility that it is decided that another
dimension should be added. Right now I have a command button that
opens the Components form (which has a subform of the Dimensions) so
the user can add Dimensions, then go back to the Shipments form and
add the data for that newly added Dimension. This possibility is
extremely rare, but I don't think it should be ruled out just because.

Also, new shipments are inspected and entered as new records into the
database, but if a user wants to look up data about an old shipment,
he will need to know about all the inspected dimensions (a list of
them), which would be the perfect job for a subform.

You'll have to explain more of the benefits of this method before you
sell me, but I appreciate your desire to help.

Is there anything I can do to better explain my previous post? There
was a lot I laid out and wouldn't be a surprise to me if it was hard
to understand .

Ken Snell
MS ACCESS MVP

"Garret" wrote in message

egroups.com...

Hey Ken, thanks for posting back. I hope you enjoyed your
(hopefully!) extended weekend.

On May 26, 5:46 pm, "Ken Snell \(MVP\)"
wrote:
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,

...

read more »- Hide quoted text -

- Show quoted text -

Where it goes would depend upon how you want the form to work. If you assume
that the user will never mistakenly choose the wrong item in the combo box,
you could use its AfterUpdate event. However, I think I'd be inclined to use
the Click event of a command button that the user would click to confirm
that the choice was made correctly.

For the Dimension item, you probably could add the dimension table to the
subform's RecordSource query and join it in the query's design so that you
could include the desired field. Sometimes, adding more tables can make a
query nonupdatable, meaning that the subform then could not be used to enter
data -- if that occurs, then don't add the dimenstion table, and instead use
a calculated field in the query that uses DLookup in an expression to look
up the desired dimension value.
--

Ken Snell
MS ACCESS MVP




"Garret" wrote in message
oups.com...
My last post can't be read very well, so reposting:

I think I figured out why there was an error - I was running the code
in the forms Before_Update event, so I think it was trying to add
records to a record which wasn't in the database yet.

This brings the question - where DOES it go? I put the code in a
command button on the Form and tried it out (without the NewRecord
check) and it worked perfectly. One other thing...

Note before, I mentioned how each Dimension also has an
Inspection_Tool that is used to measure the dimension. The user
needs
to know this so he/she can do the measuring. I think to do this I
would need a field (bound, unbound, or calculated) that links to the
same record that draws the Dimension data and now also draw the
Inspection_Tool data. Not exactly sure how to do that but I think I
can figure it out with more thought.




On Jun 1, 8:22 am, Garret wrote:
On Jun 1, 12:13 am, "Ken Snell \(MVP\)"

wrote:
Actually, as I think about it, the Requery step isn't needed:

Dim lngItem As Long
DoCmd.Echo False
With Me.SubformName.Form.RecordsetClone
For lngItem = 0 To Me.ComboBoxName.ListCount - 1
.AddNew
.Fields("Component_No").Value = Me.Component_No.Value
.Fields("Dimension_No").Value =
Me.ComboBoxName.ItemData(lngItem)
.Update
Next lngItem
End With
DoCmd.Echo True

Hey Ken,
I'm a bit confused with the code but it looks like a good start. For
one thing, the Inspection_Dimension table (the subform's data) does
not have a Component_No field. It could, and probably should for data
design sake, but isn't necessary. What it does have is a Shipment_No,
so it knows which Shipment the Inspection belongs to. So I modified
the code to look like this:

'I added in the bit about the new Record since thats the only
'time I wanted this code to run
If Me.NewRecord = True Then
Dim lngItem As Long
DoCmd.Echo False
With Me.sbfInspection.Form.RecordsetClone
For lngItem = 0 To Me.sbfInspection.Form!
cboDimension_No.ListCount - 1
.AddNew
.Fields("Shipment_No").Value = Me.Shipment_No.Value
.Fields("Dimension_No").Value =
Me.sbfInspection.Form!cboDimension_No.ItemData(lng Item)
.Update
Next lngItem
End With
DoCmd.Echo True
End If

Right now, I get the "Run time error 3101", saying "The Microsoft Jet
Database Engine cannot find a record in the table 'tblShipments' with
the key matching field(s) 'Shipment_No'."

This seems silly to me, for I clearly have the field Shipment_No in
the tblShipments (its the primary key!).





--

Ken Snell
MS ACCESS MVP

"Ken Snell (MVP)" wrote in
l...

OK - based on what you desire, here is some sample code that will add
a
new record to the subform for each item in a combo box -- replace
generic
names with real names --- SubformName is the name of the subform
control
on the main form (the control that holds the subform object):

Dim lngItem As Long
DoCmd.Echo False
With Me.SubformName.Form.RecordsetClone
For lngItem = 0 To Me.ComboBoxName.ListCount - 1
.AddNew
.Fields("Component_No").Value = Me.Component_No.Value
.Fields("Dimension_No").Value =
Me.ComboBoxName.ItemData(lngItem)
.Update
Next lngItem
End With
Me.SubformName.Requery
DoCmd.Echo True

--

Ken Snell
MS ACCESS MVP

"Garret" wrote in message
roups.com...
On May 30, 8:45 pm, "Ken Snell \(MVP\)"
wrote:
I need to spend a bit more time digesting the details of what you
posted,
but while I do that, a thought/suggestion for you ---

Think about whether this setup would work for you. Instead of using
a
subform that has all the "letter-identified" records showing and
awaiting
user input, what if the form popped up a form to the user for the
"A"
record
so that the user could enter the data; then it changed the info in
that
popup form to the "B" record for data entry, and so on, until all
data
records have been entered. What this would do for you is make sure
that
the
user provides data to all records, ensures that the user doesn't
skip a
record/data item, avoids problem of adding "empty" records to table
that
still need data, and provides a more fluid data entry flow for the
user
without having to figure out what to do next.

Let me know, while I give more thought to your process desires.
--

Hey Ken,

Although this idea would work essentially, I'm not sure I (or my
employer) would be happy with it. Where does the
data go after a user enters data onto the popup form? The subform
(child table)? If so, whats the point of doing this instead of just
a
subform?

What happens if the user incorrectly enters data, or wants to write
over some data entered this way (for example, he figures out that
some
components he thought were good were really bad, or visa versa).
There is also the possibility that it is decided that another
dimension should be added. Right now I have a command button that
opens the Components form (which has a subform of the Dimensions) so
the user can add Dimensions, then go back to the Shipments form and
add the data for that newly added Dimension. This possibility is
extremely rare, but I don't think it should be ruled out just
because.

Also, new shipments are inspected and entered as new records into the
database, but if a user wants to look up data about an old shipment,
he will need to know about all the inspected dimensions (a list of
them), which would be the perfect job for a subform.

You'll have to explain more of the benefits of this method before you
sell me, but I appreciate your desire to help.

Is there anything I can do to better explain my previous post? There
was a lot I laid out and wouldn't be a surprise to me if it was hard
to understand .

Ken Snell
MS ACCESS MVP

"Garret" wrote in message

egroups.com...

Hey Ken, thanks for posting back. I hope you enjoyed your
(hopefully!) extended weekend.

On May 26, 5:46 pm, "Ken Snell \(MVP\)"
wrote:
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,

...

read more »- Hide quoted text -

- Show quoted text -

On Jun 1, 10:07 am, "Ken Snell \(MVP\)"
wrote:
Where it goes would depend upon how you want the form to work. If you assume
that the user will never mistakenly choose the wrong item in the combo box,
you could use its AfterUpdate event. However, I think I'd be inclined to use
the Click event of a command button that the user would click to confirm
that the choice was made correctly.

Well since the records automatically are added, theres no longer a
need for the user to click on the combo box, making it sort of a waste
of a control instead of a text box, but without the rows in the combo
box it wouldn't be possible to add all the records. So its sort of a
bad route but it gets to the end goal.

Please specify whether you mean the events for the main form or the
events for the subform. I'm a little confused.

For the Dimension item, you probably could add the dimension table to the
subform's RecordSource query and join it in the query's design so that you
could include the desired field. Sometimes, adding more tables can make a
query nonupdatable, meaning that the subform then could not be used to enter
data -- if that occurs, then don't add the dimenstion table, and instead use
a calculated field in the query that uses DLookup in an expression to look
up the desired dimension value.

Whenever I try and add [Inspection_Dimension] to the query used for
the Record Source of the Subform, it ends up showing every single
dimension for every shipment of that component in the subform, AND I
can't add any new records.
So I tried it with DLookup:

InspectTool: DLookUp([Inspection_Tool],[tblValidComponentDimensions],
[tblValidComponentDimensions].[Dimension_No]=[Forms]!
[frmInspection].Dimension_No)

And I get prompted with message boxes whenever I open up the
frmShipments form to input data for these fields used in the DLookup,
and then followed by an error of some sort that makes the subform
either blank, or have #error on all the fields where InspectTool would
be. Ah!