Using combo boxes to affect the list in another combo box

[rconnell]

Folks,

I have a table which has 4 columns.

ID (Primary Key) Machine Name Machine subsection Room
1 MPP1 St12 - PnP
XP03
2 MPP1 St22 - Reject
XP03
3 MPP2 St03 - Vision
XP16
4 MPP2 St07 - Splice
XP16
5 Converter System die
XP12
6 Converter Lane Die
XP12
7 Casting Line Anvil Die
XP07



I want to be able to have another table which will be populated using a form
which will allow the user to select the machine name in one field on this
form and depending on the selection it will change a list available in
another field which will allow the user to enter a machine subsection. I
want the second field to be limited to the options associated with that
machine. There is approx 30 machines and each might have up to 20 sub parts
which the user will be able to select as they wish. In the finished list
there will
be 600 Rows (30 X 20). I want the combo box to only show each entry once
and depending on this selection I want the list of the subsections of that
particular machine to appear in another combo box.

They will also be typing in to other text fields which will have problems
and
work carried out on that machine. This will be used as a handover database
from shift to shift and also for management to query as they wish.

Any ideas or help or examples.

Regards

RC

 

[Michel Walsh]

Hi,


Whatever it is two or more combo box, the technique is the same. Say ComboB
has to have its list "updated" accordingly to the value in ControlA. I
assume you don't mind to wait until the list is to be displayed to indeed
update it, so, in the GotFocus event of ComboB,

Dim str AS String
str="SELECT whatever FROM somewhere "
If IsNull( Me.ControlA ) Then
'no more job to do
Else
str=str & " WHERE toBeRestricted= FORMS!FormNameHere!ControlA "
End IF

If str <> Me.ComboB.RowSource Then
Me.ComboB.RowSource = str
End if
' since modifying the RowSource FORCES a requery,
' we tested before, to avoid to requery, if it was
' required, instead of doing it unconditionally.
' Just some optimization.



And if ComboC depends on ComboB, you do similar stuff for ComboC, dependant
of ComboB, rather than ControlA, and so, and so.



Hoping it may help,
Vanderghast, Access MVP