How do i link combo boxes together

[murphysdad]

Help. I am trying to figure out how to have a combo box show me information on a form but change if I select a different field from the previous combo box. CONFUSION. It is like this: I have different departments and in these departments they each have different areas. I want to choose a department and have that dept's areas come up on another combo box(not all areas like now). I know it is as easy as having queries or an "if, then" statement. I do not use this program much!

thanks for any help.
Jeremy.link@unilever.com

 

[Ouch]

look up "query by form" in the help

if you need detail i will help

 

[Gazer44]

Try this

'combo1' is department combo
'combo2' is area combo

Point combo1 to the Table which contains the Departments
& in it's After update property put in :-

Private Sub combo1_AfterUpdate()
Me!combo2.Requery

End Sub

Point combo2 to a NEW query :-

SELECT [AreaTable].[Department],
FROM [AreaTable]
WHERE [AreaTable].[Department])=[YourFormName]![DepartmentTable]![combo1]));

 

[murphysdad]

dude, I don't know much about visual basic...could you guide me on how to make this vb or let me know how to do it without vb?? Thank you for all your help.

 

[Gazer44]

Give me the Table names and their fields which the combo boxes get the info from and each combo box name.

Also- do you want people to use Combo1 BEFORE combo 2 or doesn't it matter.

 

[murphysdad]

Combo box #1 is named: Department
Combo box #1 get info from: Department Query

Combo box #2 is named: Line Number
Combo box #2 gets info from: All Areas

Combo Box #1 must be first.

Again, thank you for the help.

 

[Gazer44]

Give me the Field names in Department, Department Query, All Areas & the relationship (if any)

 

[murphysdad]

Department Query has just one field: Department
All Areas has each line and area listed.
I have also made separate queries for each department(certain lines and areas relate to different departments) I think that would be more helpful.
I can email or call in the info...could be sensitive.
jeremy.link@unilever.com

 

[Gazer44]

Basically -
1. Department Combo looks OK. It's getting it's info from the Department Query.

Put into Department Combo AFTER UPDATE :-

Private Sub Department_AfterUpdate()
me!Line Number.Enabled = True
me!Line Number.Requery
End Sub

This will refresh the Line Number Combo

2. Line No. Combo

Set it's Enabled Property to NO

This needs to look at a query which is obtained from another Table/Query containing both the Departments & the Areas.

Table/Query example -

DEPT AREA
Meat sausage
Meat bacon
Meat chicken
Dairy milk
Dairy eggs
Dairy cheese

And so on.

Create a NEW query based on the Table/Query above. The Line No. combo will have it's Row Source pointing to this Query:-

Put in [YourFormName]![Table/Query the form looks at]![DepartmentComboName] under the DEPT field and set AREA field to Ascending.

Set Bound column to 2 - i.e. the Area field


That should do it.

 

[murphysdad]

vb is not letting me say "Line Number" It is forcing me to write LineNumber. Which means I have to change the combox name too. I tried all that you wrote and it not working...Got any other ideas??

 

[mustangirl]

WHAT I HAVE DONE IS CREATED THREE COMBO BOXES MY FIRST IS SUPPLIER, THEN RTE AND RN.

THIS GOES IN MY ROWSOURCE OF THE FIRST COMBO BOX - SUPPLIER:
SELECT DISTINCT [TBL_NEW_LEWIS_TRACKING].[SHORT NAME] FROM TBL_NEW_LEWIS_TRACKING;

IN MY BUILD EVENT I HAVE THE FOLLOWING:

Private Sub Form_Load()
RTE.Value = ""
RN.Value = ""
SUPPLIER.Value = ""
End Sub

Private Sub SUPPLIER_AfterUpdate()
RTE.RowSource = "SELECT RTE FROM TBL_NEW_LEWIS_TRACKING WHERE [SHORT NAME] = '" & SUPPLIER & "' GROUP BY RTE ORDER BY RTE"
End Sub
BE CAREFULL WITH THE QUOTES AROUND SUPPLIER IT IS A SINGLE THEN DOUBLE AND DOUBLE THEN SINGLE.

(WHAT THIS DOES IS AFTER THE SUPPLIER HAS BEEN SELECTED IT RUNS A QUERY BASED ON THE WHAT WAS ENTERED IN THE FIRST COMBO BOX, AND ONLY LISTS ROUTES THAT A SPECIFIC SUPPLIER IS ON)

Private Sub RTE_AfterUpdate()
RN.RowSource = "SELECT RN FROM TBL_NEW_LEWIS_TRACKING WHERE RTE = '" & RTE & "' GROUP BY RN ORDER BY RN"
End Sub
(WHAT THIS DOES IS AFTER THE ROUTE HAS BEEN SELECTED IT RUNS A QUERY BASED ON THE WHAT WAS ENTERED IN THE SECOND COMBO BOX, AND ONLY LISTS RUNS ON THAT SPECIFIC ROUTE)

HOPE THIS HELPS, LET ME KNOW.

[LIPSTICK] SHAWNDRA CREE JONES,
DATABASE DEVELOPER
TOYOTA MOTOR MANUFACTURING NORTH AMERICA
ERLANGER, KY

 

[Gazer44]

Have sent you Email Re: this

 

[Gazer44]

sorry, my mistake- the Line No combo should point to the Query with:-

[Forms]![YourFormName]![DepartmentComboName] under the DEPT field and set the AREA field to Ascending.

Also VB doesn't like spaces, either change name to -

Line_Number or LineNumber

or put code in as [Line Number]

 

[murphysdad]

RESOLVED! Thank you Gary!