Combo box selection based on another Combo box disappears?

[RCSNC]

I have two combo boxes,divisionCombo and classTypeCombo, on a multi record form named schedules

I am trying (as many other newbies before me have) to have the contents of the classCombo based upon the selection made in divCombo.

Here's my record source for divisionCombo:

SELECT [divisionID], [divisionName] FROM Division ORDER BY [divisionName]

and for classTypeCombo:

SELECT [classTypeID], [classTypeName] FROM classType WHERE [classType].[divisionID]=[Forms]![schedules]![divisionCombo];

Note: divisionID and classTypeID are hidden

and finally here's the code I have for the AfterUpdate event for divisionCombo:

Private Sub divisionCombo_AfterUpdate()
classTypeCombo.Requery
End Sub

Here's my problem: As I move through the records, everything is fine as long as the division does not change. As soon as I change the division the classtype for all different divisions disappears.

Also, if I just click through the classtype fields from record to record (as I imagine a user might) I only see the records for the last division I entered (not necessarily the one selected for the record.

Any help is appreciated! Thanks!

 

[Eupher]

Okay, you've got your combo boxes synchronized, but what happens (or what is supposed to happen) when you make a selection in classTypeCombo?

Ken S.

 

[RCSNC]

Eupher,

Once I make a selection in classTypeCombo it should stay selected unless I change the divisionCombo. For example, if I select "DivisionA" and my choices in classTypeCombo are "TypeA1", "TypeA2", etc. I want to be able to make a selection, go on to the rest of the fields and have everything stay the same if the next record happens to be "DivisionB".

Thanks!

 

[KenReay]

Hi

I think your problem is that while moving through the records the value of Division (may) change, but the other cobo is not requeries in that case, a requery of TypeCombo in the onCurrent event of the form, should fix it

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RCSNC]

Thanks, this worked.