Number of items in Combo Box

G

Garret

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.
Click to expand...


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.
Click to expand...


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!
 
G

Garret

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.
 
K

Ken Snell \(MVP\)

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.
 
G

Garret

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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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.
 
K

Ken Snell \(MVP\)

Comments inline.

--

Ken Snell
<MS ACCESS MVP>


Garret said:
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.
Click to expand...

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

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
Click to expand...

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


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.
Click to expand...

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] & "'")
 
K

Ken Snell \(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) said:
Comments inline.

--

Ken Snell
<MS ACCESS MVP>


Garret said:
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.
Click to expand...

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

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
Click to expand...

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


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.
Click to expand...

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] & "'")
Click to expand...
 
G

Garret

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) said:
Comments inline.

Ken Snell
<MS ACCESS MVP>
Click to expand...
OK, so you can use the AfterUpdate event of that first combo box on the
Main Form to run the code:
Click to expand...
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
Click to expand...
Note that using the code on the AfterUpdate event assumes a couple of
things:
Click to expand...
(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.)
Click to expand...
(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:
Click to expand...
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
Click to expand...
OK, so building on the code noted above, we'd change it to use a recordset
instead of the second combobox:
Click to expand...
Private Sub MainFormItemComboBox_AfterUpdate()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngItem As Long
Dim strSQL As String
Click to expand...
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
Click to expand...
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):
Click to expand...
=DLookup("NameOfToolFieldInTable", "tblDimensions", "Dimension_No='" &
[NameOfTextBoxHoldingDimensionNumberValue] & "'")
--
Click to expand...

Ken Snell
<MS ACCESS MVP>
Click to expand...
Click to expand...

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!
 
K

Ken Snell \(MVP\)

Garret said:
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!
Click to expand...

You're very welcome < blush > ...
Good luck.
 
Top