Synchronise two combo boxes

[Sandy]

I am trying to synchronise two combo boxes on a form but get no list in the
second.
Details are:-

tblRepairCategory
RepairCategoryID (Primary Key - Autonumber)
RepairCategory (Text)

tblRepairType
RepairTypeID (Autonumber)
RepairCategoryID (Number - Long)
RepairType (Text)
BuyPrice (Currency)
SellPrice (Currency)

These two tables are linked by RepairCategoryID - One to Many.

My form and combos:-
FormName = DependentCombos

First Combo Box
Name = Categories
RowSourceType = Table/Query
RowSource = tblRepairCategory
ColumnCount = 2
ColumnWidths = 0cm; 2.54cm
BoundColumn = 1
AfterUpdate - Event =

Private Sub Categories_AfterUpdate()
Me.RepairTypes.RowSource = "SELECT RepairType FROM" & _
" tblRepairType WHERE RepairCategoryID = " & Me.Categories &
_
" ORDER BY RepairType"
Me.RepairTypes = Me.RepairTypes.ItemData(0)
End Sub

Second Combo Box
Name = RepairTypes
RowSourceType = Table/Query
ColumnWidths = 5cm
Width = 3cm

I don't fully understand the line
"Me.RepairTypes = Me.RepairTypes.ItemData(0)"
in the 'AfterUpdate. If someone were to explain it that would be good.

Thanks in advance
Sandy

 

[Jim Bunton]

On the first combo
the after update procedure will run when you click the combo

what this need to do is to reset the Rowsource of the second query to return
rowns relative to the first [which you seem to have done].

me.secondcombo.rosource = !someappropriatesql!
'THE NEXT line -
Me.SecondCombo.requery 'should do the trick!

Initially the second combo should show nothing - you can use an appropriate
query to do this or alterenatively just leave the rowsource blank ) I
think!)

Not sure what "Me.RepairTypes = Me.RepairTypes.ItemData(0)" does at all!

Jim Bunton

 

[Ken Snell \(MVP\)]

See this article at The ACCESS Web:
http://www.mvps.org/access/forms/frm0058.htm

 

[Sandy]

Hi Jim,

Adding the line:-
"Me.RepairTypes.Requery" to the AfterUpdate did the trick.

Incidently I got the code and method from here
http://support.microsoft.com/kb/289670 and as far as I can see there is no
mention of "requery", strange!!

After the list was visible I messed about with the line:-
"Me.RepairTypes = Me.RepairTypes.ItemData(0)"

It appears that if the line is not there at all the second combo box remains
empty until a selection is made.
Including the line with the "ItemData(0)" displays the first item in the
list automatically in the combo box; changing (0) to (1) i.e. ItemData(1)"
displays the second etc. Bit like a default value!

Anyway problem solved - thanks Jim
Sandy

On the first combo
the after update procedure will run when you click the combo

what this need to do is to reset the Rowsource of the second query to
return
rowns relative to the first [which you seem to have done].

me.secondcombo.rosource = !someappropriatesql!
'THE NEXT line -
Me.SecondCombo.requery 'should do the trick!

Initially the second combo should show nothing - you can use an
appropriate
query to do this or alterenatively just leave the rowsource blank ) I
think!)

Not sure what "Me.RepairTypes = Me.RepairTypes.ItemData(0)" does at all!

Jim Bunton

I am trying to synchronise two combo boxes on a form but get no list in
the second.
Details are:-

tblRepairCategory
RepairCategoryID (Primary Key - Autonumber)
RepairCategory (Text)

tblRepairType
RepairTypeID (Autonumber)
RepairCategoryID (Number - Long)
RepairType (Text)
BuyPrice (Currency)
SellPrice (Currency)

These two tables are linked by RepairCategoryID - One to Many.

My form and combos:-
FormName = DependentCombos

First Combo Box
Name = Categories
RowSourceType = Table/Query
RowSource = tblRepairCategory
ColumnCount = 2
ColumnWidths = 0cm; 2.54cm
BoundColumn = 1
AfterUpdate - Event =

Private Sub Categories_AfterUpdate()
Me.RepairTypes.RowSource = "SELECT RepairType FROM" & _
" tblRepairType WHERE RepairCategoryID = " & Me.Categories
& _
" ORDER BY RepairType"
Me.RepairTypes = Me.RepairTypes.ItemData(0)
End Sub

Second Combo Box
Name = RepairTypes
RowSourceType = Table/Query
ColumnWidths = 5cm
Width = 3cm

I don't fully understand the line
"Me.RepairTypes = Me.RepairTypes.ItemData(0)"
in the 'AfterUpdate. If someone were to explain it that would be good.

Thanks in advance
Sandy

 

[Sandy]

Thanks Ken your article explains it well
Sandy

See this article at The ACCESS Web:
http://www.mvps.org/access/forms/frm0058.htm