two listboxes

[imuharram]

I have two list boxes
Department listbox and Unit listbox
I want whenever I choose a department from the listbox all the units under that department to appear in the unit listbox. whenever the department value changes only the units under that department appear in the other listbox.
I tried having a query but I couldn't know how to specify the criteria (how to get the value from the form itself ==> dept_no= dept_no).
How can I do it?

 

[missinglinq]

The concept is called "cascading comboboxes." You can google or search here under that term and you should get a ton of hits. The same technique applies to listboxes. Here's one link:

http://bytes.com/forum/thread605958.html

 

[imuharram]

Could anyone help me and tell me where is the mistake in the following code? I can't get it to work

Private Sub units_AfterUpdate()
Me.to_unit.Value = DLookup("unit_no", "unit", "[unit_name]=" & Me.units.Value)
End Sub


units => is an unbound listbox
after a unit is chosen from units listbox i try to select the unit_no for that unit and save it in to_unit where it is a text field that saves the unit number

 

[pono1]

Try this...

DLookup("unit_no", "unit", "[unit_name]='" & Me.units.Value &"'")

A pretty good reference.

Regards,
Tim

 

[imuharram]

the code worked, thanks
but something else came up
attached is snapshot of what's going on
I have the department listbox when i choose a department the unit listbox (unbound) lists the units I have in that department and when I choose a unit its unit_no is saved in to_unit (a field in the table that needs to be saved and will be set to invisible to the user). when I choose a unit in the listbox like "billing" the unit_no is saved correctly, but as you can notice the next row is set to "billing" also without even choosing the department!! and if I choose in the next row for example "database" the row above is changed to "database". Although in the table the values are saved correctly but it appears to the user that there's something wrong.
what can I do?
I hope I was clear enough to know what I mean.

 

[pono1]

Try this...

Me.Requery

Regards,
Tim

 

[imuharram]

where should i put this code?
I tried putting it in the to_department after update it didn't work. I tried to put it in the unit listbox it didn't work as well.