Number of items in Combo Box

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_Tool] 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. Ahh!

I have an idea for the Tools, and for making the subform appear more
efficient. I could move the combobox onto the main form, and put text
boxes in the subform. Then I could make the combobox invisible, but
it would still store all the data I need. That way it will still add
all the new records without the combobox as the holder of the data.

For adding Tools, I could do the same thing as the Dimensions, except
make Tools the data to draw from instead of Dimensions. I could make
a combobox on the main form right alongside the Dimensions combobox,
this one invisible too, and just add another line of code in the new
record loop. The only problem though, is that I would have to add a
field to tblInspection_Dimensions to hold Tools, which would be
holding duplicate data then since it would be stored in two tables. I
don't think that this would impact the database too hard though.

Still thinking about where the code would actually go though.

It's not clear to me what action the user is making in the main form that
"identifies" what the subform's records should be, so I cannot recommend
which event should run the code other than the one I've already made -- use
a command button to run the code, and let the user click the button to
populate the subform with records. But I'm sure there is some other action
that would be just as useful - - perhaps the main form's AfterUpdate
event... but I cannot say more without more info.

If you would use a combo box just to get a list of values that would be read
by the code and used to know which values to add to the subform as new
records, then I would eliminate the use of the combo box and instead let the
code get those values directly via a recordset that is based on the same SQL
statement that is used for the combo box's RowSource property. If you post
the SQL statement of the combo box's row source, I can suggest how to change
the code to do this.

I am not completely clear about the relationship (or lack thereof) between
Tools and what you are seeking to do here with the Dimension values.
However, in rereading the earlier posts where you've described the setup, I
believe you just would want to display the value of the corresponding Tools
item with each Dimension record; is this correct? If yes, then again, we can
use code to fill a textbox with that value and you would not need to add
Tools field to the subform's RecordSource query. Again, provide more details
about how one would get the Tools value for a given Dimension value.
--

Ken Snell
MS ACCESS MVP





"Garret" wrote in message
oups.com...
I have an idea for the Tools, and for making the subform appear more
efficient. I could move the combobox onto the main form, and put text
boxes in the subform. Then I could make the combobox invisible, but
it would still store all the data I need. That way it will still add
all the new records without the combobox as the holder of the data.

For adding Tools, I could do the same thing as the Dimensions, except
make Tools the data to draw from instead of Dimensions. I could make
a combobox on the main form right alongside the Dimensions combobox,
this one invisible too, and just add another line of code in the new
record loop. The only problem though, is that I would have to add a
field to tblInspection_Dimensions to hold Tools, which would be
holding duplicate data then since it would be stored in two tables. I
don't think that this would impact the database too hard though.

Still thinking about where the code would actually go though.

On Jun 3, 4:54 pm, "Ken Snell \(MVP\)"
wrote:
It's not clear to me what action the user is making in the main form that
"identifies" what the subform's records should be, so I cannot recommend
which event should run the code other than the one I've already made -- use
a command button to run the code, and let the user click the button to
populate the subform with records. But I'm sure there is some other action
that would be just as useful - - perhaps the main form's AfterUpdate
event... but I cannot say more without more info.

Alright, let me try and explain the idea again. Shipments contain
Components, and Components have to have certain Dimensions inspected
to make sure the Components of the Shipment are in tolerance, so they
are good to use. The Dimensions to be inspected are different for
each Component (because "Components" covers a wide range of objects),
so the Dimensions that each Component has to have inspected are
represented in the parent-child tables of tblComponents and
tblDimensions (form and subform for frmComponents).

Now, to mimic a Shipment, there is a main form with a combobox at the
top that looks up all the Components from tblComponents. So the user
first selects an item from this combobox to determine which Component
came in the Shipment, and so as a result, is indirectly selecting
which Dimensions should appear in the subform to be inspected.
Currently, code in the After_Update of this combobox limits the values
(changes the Rowsource) of the combobox of the subform to only those
Dimensions that belong to the Component selected. Does this make
sense?

So this code has to go somewhere after the user selects the Component
on the main form, but it cannot run on a record that is not new, or
there will be many duplicate records, and hence errors, in the
subform.

If you would use a combo box just to get a list of values that would be read
by the code and used to know which values to add to the subform as new
records, then I would eliminate the use of the combo box and instead let the
code get those values directly via a recordset that is based on the same SQL
statement that is used for the combo box's RowSource property. If you post
the SQL statement of the combo box's row source, I can suggest how to change
the code to do this.

This is what I wanted to do but didn't know how, so I kept on
struggling with the monster I had created. Here is the code I use to
populate the combobox with values after the Component is selected on
the main form. (Code is used in the combobox's After_Update)

Private Sub cboComponent_No_AfterUpdate()
strComponent_No = Me.cboComponent_No
Me!sbfInspection.Form!cboDimension_No.RowSource = _
"SELECT Component_No, Dimension_No " & _
"FROM tblValidComponentDimensions " & _
"WHERE Component_No= '" & strComponent_No & "'" & _
"ORDER BY Dimension_No;"
'Update the combobox
Me!sbfInspection.Form!cboDimension_No.Requery
Me!sbfInspection.Form.Refresh
End Sub

I am not completely clear about the relationship (or lack thereof) between
Tools and what you are seeking to do here with the Dimension values.
However, in rereading the earlier posts where you've described the setup, I
believe you just would want to display the value of the corresponding Tools
item with each Dimension record; is this correct? If yes, then again, we can
use code to fill a textbox with that value and you would not need to add
Tools field to the subform's RecordSource query. Again, provide more details
about how one would get the Tools value for a given Dimension value.

Yes, Ken, that sounds right, and thats exactly what I want to do.
Each Dimension has a corresponding Tool that is used to measure it.
The Tool data is stored as another field in the same table where the
Dimensions are stored (tblDimensions). In the Inspection subform of
the Shipments form, it would be useful to display this Tool value so
that the user can do the measuring without having to look it up on the
Components form + Dimensions subform.

Post back if you have any other questions.

Comments inline.

--

Ken Snell
MS ACCESS MVP


"Garret" wrote in message
oups.com...
Alright, let me try and explain the idea again. Shipments contain
Components, and Components have to have certain Dimensions inspected
to make sure the Components of the Shipment are in tolerance, so they
are good to use. The Dimensions to be inspected are different for
each Component (because "Components" covers a wide range of objects),
so the Dimensions that each Component has to have inspected are
represented in the parent-child tables of tblComponents and
tblDimensions (form and subform for frmComponents).

Now, to mimic a Shipment, there is a main form with a combobox at the
top that looks up all the Components from tblComponents. So the user
first selects an item from this combobox to determine which Component
came in the Shipment, and so as a result, is indirectly selecting
which Dimensions should appear in the subform to be inspected.
Currently, code in the After_Update of this combobox limits the values
(changes the Rowsource) of the combobox of the subform to only those
Dimensions that belong to the Component selected. Does this make
sense?

So this code has to go somewhere after the user selects the Component
on the main form, but it cannot run on a record that is not new, or
there will be many duplicate records, and hence errors, in the
subform.

OK, so you can use the AfterUpdate event of that first combo box on the Main
Form to run the code:

Private Sub MainFormItemComboBox_AfterUpdate()
Dim lngItem As Long
If Me.NewRecord = True Then
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
End If
End Sub


Note that using the code on the AfterUpdate event assumes a couple of
things:

(1) The user will never select the wrong item in that combo box, which would
cause a lot of bad records to be added to the subform for that mistaken
choice. (This is why I strongly suggest that you put a command button on the
main form and let the user click it to show that the selection is the one
desired, and then use the Click event of the button to run the above code.)

(2) The main form's record will be saved to the table before the code runs,
else you'll get relational integrity errors because there is no parent
record yet for the records being added to the subform. If you need to ensure
that this happens, the code should be changed to this:

Private Sub MainFormItemComboBox_AfterUpdate()
Dim lngItem As Long
If Me.NewRecord = True Then
Me.Dirty = False ' save main form's record
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
End If
End Sub



If you would use a combo box just to get a list of values that would be
read
by the code and used to know which values to add to the subform as new
records, then I would eliminate the use of the combo box and instead let
the
code get those values directly via a recordset that is based on the same
SQL
statement that is used for the combo box's RowSource property. If you
post
the SQL statement of the combo box's row source, I can suggest how to
change
the code to do this.

This is what I wanted to do but didn't know how, so I kept on
struggling with the monster I had created. Here is the code I use to
populate the combobox with values after the Component is selected on
the main form. (Code is used in the combobox's After_Update)

Private Sub cboComponent_No_AfterUpdate()
strComponent_No = Me.cboComponent_No
Me!sbfInspection.Form!cboDimension_No.RowSource = _
"SELECT Component_No, Dimension_No " & _
"FROM tblValidComponentDimensions " & _
"WHERE Component_No= '" & strComponent_No & "'" & _
"ORDER BY Dimension_No;"
'Update the combobox
Me!sbfInspection.Form!cboDimension_No.Requery
Me!sbfInspection.Form.Refresh
End Sub

OK, so building on the code noted above, we'd change it to use a recordset
instead of the second combobox:

Private Sub MainFormItemComboBox_AfterUpdate()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngItem As Long
Dim strSQL As String

If Me.NewRecord = True Then
Me.Dirty = False ' save main form's record
DoCmd.Echo False
strSQL = _
"SELECT Component_No, Dimension_No " & _
"FROM tblValidComponentDimensions " & _
"WHERE Component_No= '" & strComponent_No & "'" & _
"ORDER BY Dimension_No;"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
Do While rst.EOF = False
With Me.SubformName.Form.RecordsetClone
.AddNew
.Fields("Component_No").Value = Me.Component_No.Value
.Fields("Dimension_No").Value = rst!Dimension_No.Value
.Update
End With
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
DoCmd.Echo True
End If
End Sub




I am not completely clear about the relationship (or lack thereof)
between
Tools and what you are seeking to do here with the Dimension values.
However, in rereading the earlier posts where you've described the setup,
I
believe you just would want to display the value of the corresponding
Tools
item with each Dimension record; is this correct? If yes, then again, we
can
use code to fill a textbox with that value and you would not need to add
Tools field to the subform's RecordSource query. Again, provide more
details
about how one would get the Tools value for a given Dimension value.

Yes, Ken, that sounds right, and thats exactly what I want to do.
Each Dimension has a corresponding Tool that is used to measure it.
The Tool data is stored as another field in the same table where the
Dimensions are stored (tblDimensions). In the Inspection subform of
the Shipments form, it would be useful to display this Tool value so
that the user can do the measuring without having to look it up on the
Components form + Dimensions subform.

To show the Tool data on the subform, add a textbox to the subform. Use this
expression as its Control Source (replace my generic names with real names):

=DLookup("NameOfToolFieldInTable", "tblDimensions", "Dimension_No='" &
[NameOfTextBoxHoldingDimensionNumberValue] & "'")

--

Ken Snell
MS ACCESS MVP

Sorry, error in the last code example:


Private Sub MainFormItemComboBox_AfterUpdate()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngItem As Long
Dim strSQL As String

If Me.NewRecord = True Then
Me.Dirty = False ' save main form's record
DoCmd.Echo False
strSQL = _
"SELECT Component_No, Dimension_No " & _
"FROM tblValidComponentDimensions " & _
"WHERE Component_No= '" & Me.Component_No.Value & "'" & _
"ORDER BY Dimension_No;"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
Do While rst.EOF = False
With Me.SubformName.Form.RecordsetClone
.AddNew
.Fields("Component_No").Value = Me.Component_No.Value
.Fields("Dimension_No").Value = rst!Dimension_No.Value
.Update
End With
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
DoCmd.Echo True
End If
End Sub

--

Ken Snell
MS ACCESS MVP



"Ken Snell (MVP)" wrote in message
...
Comments inline.

--

Ken Snell
MS ACCESS MVP


"Garret" wrote in message
oups.com...
Alright, let me try and explain the idea again. Shipments contain
Components, and Components have to have certain Dimensions inspected
to make sure the Components of the Shipment are in tolerance, so they
are good to use. The Dimensions to be inspected are different for
each Component (because "Components" covers a wide range of objects),
so the Dimensions that each Component has to have inspected are
represented in the parent-child tables of tblComponents and
tblDimensions (form and subform for frmComponents).

Now, to mimic a Shipment, there is a main form with a combobox at the
top that looks up all the Components from tblComponents. So the user
first selects an item from this combobox to determine which Component
came in the Shipment, and so as a result, is indirectly selecting
which Dimensions should appear in the subform to be inspected.
Currently, code in the After_Update of this combobox limits the values
(changes the Rowsource) of the combobox of the subform to only those
Dimensions that belong to the Component selected. Does this make
sense?

So this code has to go somewhere after the user selects the Component
on the main form, but it cannot run on a record that is not new, or
there will be many duplicate records, and hence errors, in the
subform.

OK, so you can use the AfterUpdate event of that first combo box on the
Main Form to run the code:

Private Sub MainFormItemComboBox_AfterUpdate()
Dim lngItem As Long
If Me.NewRecord = True Then
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
End If
End Sub


Note that using the code on the AfterUpdate event assumes a couple of
things:

(1) The user will never select the wrong item in that combo box, which
would cause a lot of bad records to be added to the subform for that
mistaken choice. (This is why I strongly suggest that you put a command
button on the main form and let the user click it to show that the
selection is the one desired, and then use the Click event of the button
to run the above code.)

(2) The main form's record will be saved to the table before the code
runs, else you'll get relational integrity errors because there is no
parent record yet for the records being added to the subform. If you need
to ensure that this happens, the code should be changed to this:

Private Sub MainFormItemComboBox_AfterUpdate()
Dim lngItem As Long
If Me.NewRecord = True Then
Me.Dirty = False ' save main form's record
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
End If
End Sub



If you would use a combo box just to get a list of values that would be
read
by the code and used to know which values to add to the subform as new
records, then I would eliminate the use of the combo box and instead let
the
code get those values directly via a recordset that is based on the same
SQL
statement that is used for the combo box's RowSource property. If you
post
the SQL statement of the combo box's row source, I can suggest how to
change
the code to do this.

This is what I wanted to do but didn't know how, so I kept on
struggling with the monster I had created. Here is the code I use to
populate the combobox with values after the Component is selected on
the main form. (Code is used in the combobox's After_Update)

Private Sub cboComponent_No_AfterUpdate()
strComponent_No = Me.cboComponent_No
Me!sbfInspection.Form!cboDimension_No.RowSource = _
"SELECT Component_No, Dimension_No " & _
"FROM tblValidComponentDimensions " & _
"WHERE Component_No= '" & strComponent_No & "'" & _
"ORDER BY Dimension_No;"
'Update the combobox
Me!sbfInspection.Form!cboDimension_No.Requery
Me!sbfInspection.Form.Refresh
End Sub

OK, so building on the code noted above, we'd change it to use a recordset
instead of the second combobox:

Private Sub MainFormItemComboBox_AfterUpdate()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngItem As Long
Dim strSQL As String

If Me.NewRecord = True Then
Me.Dirty = False ' save main form's record
DoCmd.Echo False
strSQL = _
"SELECT Component_No, Dimension_No " & _
"FROM tblValidComponentDimensions " & _
"WHERE Component_No= '" & strComponent_No & "'" & _
"ORDER BY Dimension_No;"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
Do While rst.EOF = False
With Me.SubformName.Form.RecordsetClone
.AddNew
.Fields("Component_No").Value = Me.Component_No.Value
.Fields("Dimension_No").Value = rst!Dimension_No.Value
.Update
End With
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
DoCmd.Echo True
End If
End Sub




I am not completely clear about the relationship (or lack thereof)
between
Tools and what you are seeking to do here with the Dimension values.
However, in rereading the earlier posts where you've described the
setup, I
believe you just would want to display the value of the corresponding
Tools
item with each Dimension record; is this correct? If yes, then again, we
can
use code to fill a textbox with that value and you would not need to add
Tools field to the subform's RecordSource query. Again, provide more
details
about how one would get the Tools value for a given Dimension value.

Yes, Ken, that sounds right, and thats exactly what I want to do.
Each Dimension has a corresponding Tool that is used to measure it.
The Tool data is stored as another field in the same table where the
Dimensions are stored (tblDimensions). In the Inspection subform of
the Shipments form, it would be useful to display this Tool value so
that the user can do the measuring without having to look it up on the
Components form + Dimensions subform.

To show the Tool data on the subform, add a textbox to the subform. Use
this expression as its Control Source (replace my generic names with real
names):

=DLookup("NameOfToolFieldInTable", "tblDimensions", "Dimension_No='" &
[NameOfTextBoxHoldingDimensionNumberValue] & "'")

--

Ken Snell
MS ACCESS MVP

On Jun 4, 9:59 pm, "Ken Snell \(MVP\)"
wrote:
Sorry, error in the last code example:

Private Sub MainFormItemComboBox_AfterUpdate()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngItem As Long
Dim strSQL As String

If Me.NewRecord = True Then
Me.Dirty = False ' save main form's record
DoCmd.Echo False
strSQL = _
"SELECT Component_No, Dimension_No " & _
"FROM tblValidComponentDimensions " & _
"WHERE Component_No= '" & Me.Component_No.Value & "'" & _
"ORDER BY Dimension_No;"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
Do While rst.EOF = False
With Me.SubformName.Form.RecordsetClone
.AddNew
.Fields("Component_No").Value = Me.Component_No.Value
.Fields("Dimension_No").Value = rst!Dimension_No.Value
.Update
End With
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
DoCmd.Echo True
End If
End Sub

--

Ken Snell
MS ACCESS MVP

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

Comments inline.

--

Ken Snell
MS ACCESS MVP

"Garret" wrote in message
roups.com...
Alright, let me try and explain the idea again. Shipments contain
Components, and Components have to have certain Dimensions inspected
to make sure the Components of the Shipment are in tolerance, so they
are good to use. The Dimensions to be inspected are different for
each Component (because "Components" covers a wide range of objects),
so the Dimensions that each Component has to have inspected are
represented in the parent-child tables of tblComponents and
tblDimensions (form and subform for frmComponents).

Now, to mimic a Shipment, there is a main form with a combobox at the
top that looks up all the Components from tblComponents. So the user
first selects an item from this combobox to determine which Component
came in the Shipment, and so as a result, is indirectly selecting
which Dimensions should appear in the subform to be inspected.
Currently, code in the After_Update of this combobox limits the values
(changes the Rowsource) of the combobox of the subform to only those
Dimensions that belong to the Component selected. Does this make
sense?

So this code has to go somewhere after the user selects the Component
on the main form, but it cannot run on a record that is not new, or
there will be many duplicate records, and hence errors, in the
subform.

OK, so you can use the AfterUpdate event of that first combo box on the
Main Form to run the code:

Private Sub MainFormItemComboBox_AfterUpdate()
Dim lngItem As Long
If Me.NewRecord = True Then
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
End If
End Sub

Note that using the code on the AfterUpdate event assumes a couple of
things:

(1) The user will never select the wrong item in that combo box, which
would cause a lot of bad records to be added to the subform for that
mistaken choice. (This is why I strongly suggest that you put a command
button on the main form and let the user click it to show that the
selection is the one desired, and then use the Click event of the button
to run the above code.)

(2) The main form's record will be saved to the table before the code
runs, else you'll get relational integrity errors because there is no
parent record yet for the records being added to the subform. If you need
to ensure that this happens, the code should be changed to this:

Private Sub MainFormItemComboBox_AfterUpdate()
Dim lngItem As Long
If Me.NewRecord = True Then
Me.Dirty = False ' save main form's record
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
End If
End Sub

If you would use a combo box just to get a list of values that would be
read
by the code and used to know which values to add to the subform as new
records, then I would eliminate the use of the combo box and instead let
the
code get those values directly via a recordset that is based on the same
SQL
statement that is used for the combo box's RowSource property. If you
post
the SQL statement of the combo box's row source, I can suggest how to
change
the code to do this.

This is what I wanted to do but didn't know how, so I kept on
struggling with the monster I had created. Here is the code I use to
populate the combobox with values after the Component is selected on
the main form. (Code is used in the combobox's After_Update)

Private Sub cboComponent_No_AfterUpdate()
strComponent_No = Me.cboComponent_No
Me!sbfInspection.Form!cboDimension_No.RowSource = _
"SELECT Component_No, Dimension_No " & _
"FROM tblValidComponentDimensions " & _
"WHERE Component_No= '" & strComponent_No & "'" & _
"ORDER BY Dimension_No;"
'Update the combobox
Me!sbfInspection.Form!cboDimension_No.Requery
Me!sbfInspection.Form.Refresh
End Sub

OK, so building on the code noted above, we'd change it to use a recordset
instead of the second combobox:

Private Sub MainFormItemComboBox_AfterUpdate()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngItem As Long
Dim strSQL As String

If Me.NewRecord = True Then
Me.Dirty = False ' save main form's record
DoCmd.Echo False
strSQL = _
"SELECT Component_No, Dimension_No " & _
"FROM tblValidComponentDimensions " & _
"WHERE Component_No= '" & strComponent_No & "'" & _
"ORDER BY Dimension_No;"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
Do While rst.EOF = False
With Me.SubformName.Form.RecordsetClone
.AddNew
.Fields("Component_No").Value = Me.Component_No.Value
.Fields("Dimension_No").Value = rst!Dimension_No.Value
.Update
End With
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
DoCmd.Echo True
End If
End Sub

I am not completely clear about the relationship (or lack thereof)
between
Tools and what you are seeking to do here with the Dimension values.
However, in rereading the earlier posts where you've described the
setup, I
believe you just would want to display the value of the corresponding
Tools
item with each Dimension record; is this correct? If yes, then again, we
can
use code to fill a textbox with that value and you would not need to add
Tools field to the subform's RecordSource query. Again, provide more
details
about how one would get the Tools value for a given Dimension value.

Yes, Ken, that sounds right, and thats exactly what I want to do.
Each Dimension has a corresponding Tool that is used to measure it.
The Tool data is stored as another field in the same table where the
Dimensions are stored (tblDimensions). In the Inspection subform of
the Shipments form, it would be useful to display this Tool value so
that the user can do the measuring without having to look it up on the
Components form + Dimensions subform.

To show the Tool data on the subform, add a textbox to the subform. Use
this expression as its Control Source (replace my generic names with real
names):

=DLookup("NameOfToolFieldInTable", "tblDimensions", "Dimension_No='" &
[NameOfTextBoxHoldingDimensionNumberValue] & "'")

--

Ken Snell
MS ACCESS MVP

Hey Ken,

I just implemented all of your suggestions, and they work great!
Before with the Inspection Tool I had been putting that code in the
query as a derived field rather than the control source of the text
box, and it was not working before.

I also ended up taking your suggestion of putting the code in a
command button instead of the After_Update event; this made more sense
from a safety point of view. All I had to do to get the code to work
was change around a few of the names but the code's procedure I left
alone.

I'm going to have to do some reading up on Recordsets and Database
objects -- they seemed to really be of help here and I've never used
them before.

Well this is the end of our little adventure. You've helped me more
than I could have imagined. I thank you, and truly see why they call
you an MVP!

"Garret" wrote in message
ups.com...
On Jun 4, 9:59 pm, "Ken Snell \(MVP\)"
Hey Ken,

I just implemented all of your suggestions, and they work great!
Before with the Inspection Tool I had been putting that code in the
query as a derived field rather than the control source of the text
box, and it was not working before.

I also ended up taking your suggestion of putting the code in a
command button instead of the After_Update event; this made more sense
from a safety point of view. All I had to do to get the code to work
was change around a few of the names but the code's procedure I left
alone.

I'm going to have to do some reading up on Recordsets and Database
objects -- they seemed to really be of help here and I've never used
them before.

Well this is the end of our little adventure. You've helped me more
than I could have imagined. I thank you, and truly see why they call
you an MVP!


You're very welcome blush ...
Good luck.
--

Ken Snell
MS ACCESS MVP