Cascading Combo Boxes

[Steven P via AccessMonster.com]

Repost of earlier, no response, Thanks.
Hello,
I have constructed combo boxes as previously instructed, have worked through
debugging the code but the combo box is not updating. Here is the code as
last ran.
Private Sub Combo8_AfterUpdate()
On Error Resume Next
Select Case Combo8.Value
Case "CSO"
Forms!INPUTSKYONGRINDING!cboMachine.RowSource = "tblCSO MACHINE"
Case "KYON"
Forms!INPUTSKYONGRINDING!cboMachine.RowSource = "tblKyon Machine List"
Case "MILLING"
Forms!INPUTSKYONGRINDING!cboMachine.RowSource = "tblMILLING MACHINE"
Case "KENDEX"
Forms!INPUYSKYONGRINDING!cboMachine.RowSource = "tblKENDEX MACHINE"
Case "MOLDED"
Forms!INPUTSKYONGRINDING!cboMachine.RowSource = "tblMOLDED MACHINE"
Case "ADV. MATLS"
Forms!INPUTSKYONGRINDING!cboMachine.RowSource = "tblADV MATLS Machine"
End Select

End Sub

Before arriving at this format I tried what was suggested with the following.
Private Sub Combo8_AfterUpdate()
On Error Resume Next
Select Case Combo8.Value
Case "CSO"
cboMachine.RowSource = "tblCSO MACHINE"
Case "KYON"
cboMachine.RowSource = "tblKyon Machine List"
etc.....
I received an error for the cboMachine portion, assuming it did not recognize
this since it was located in another form, I added the previous form
statement. There is no error message now, but it does not work.

Thanks,
Steven

 

[Guest]

The basic problem is that once you change a row source, you must requery the
control to see the new value. The minimum you need to do is to add this line
after your End Select statement:

Forms!INPUTSKYONGRINDING!cboMachine.Requery

The way the statment below is contstucted, once executed, your combo will
have only one row and no other items is the list will be available. Is that
what you want? My guess is you really want to select a specific row in
INPUTSKYONGRINDING!cboMachine based on what is selected in Combo8. If this
is the case, then what you want is something like this:

Private Sub Combo8_AfterUpdate()
Dim VarItem as Variant

For Each varItem In Forms!INPUTSKYONGRINDING!cboMachine
If Forms!INPUTSKYONGRINDING!cboMachine(varItem) = Me.Combo8 Then
Forms!INPUTSKYONGRINDING!cboMachine.SetFocus
Forms!INPUTSKYONGRINDING!cboMachine.ListIndex = varItem
Exit For
Next varItem
End Sub

 

[John Spencer]

Since you have spaces in your table names, I think you may have to surround
them with [] to make things work.

Try

Forms!INPUTSKYONGRINDING!cboMachine.RowSource = "[tblCSO MACHINE]"

You may have to force the combobox on the other form to requery
Forms!INPUTSKYONGRINDING!cboMachine.Requery

Test that and see as just assigning the rowSource is usually enough to cause
a requery to occur. No need have the combobox requery itself twice and eat
up time, network bandwidth, etc.

 

[Steven P via AccessMonster.com]

Thanks John,
How and where do I insert the requery, it does not work without it.
Thanks,
Steve

Since you have spaces in your table names, I think you may have to surround
them with [] to make things work.

Try

Forms!INPUTSKYONGRINDING!cboMachine.RowSource = "[tblCSO MACHINE]"

You may have to force the combobox on the other form to requery
Forms!INPUTSKYONGRINDING!cboMachine.Requery

Test that and see as just assigning the rowSource is usually enough to cause
a requery to occur. No need have the combobox requery itself twice and eat
up time, network bandwidth, etc.

Repost of earlier, no response, Thanks.
Hello,

[quoted text clipped - 38 lines]

Thanks,
Steven

 

[John Spencer]

Put the requery directly under the End Select.

Thanks John,
How and where do I insert the requery, it does not work without it.
Thanks,
Steve

Since you have spaces in your table names, I think you may have to
surround
them with [] to make things work.

Try

Forms!INPUTSKYONGRINDING!cboMachine.RowSource = "[tblCSO MACHINE]"

You may have to force the combobox on the other form to requery
Forms!INPUTSKYONGRINDING!cboMachine.Requery

Test that and see as just assigning the rowSource is usually enough to
cause
a requery to occur. No need have the combobox requery itself twice and
eat
up time, network bandwidth, etc.

Repost of earlier, no response, Thanks.
Hello,

[quoted text clipped - 38 lines]

Thanks,
Steven