Combo boxes - Changing behaviours/locking combo boxes

[robtyketto]

I have Three combo boxes that ideally I want the the users to access in the correct sequence as they depend on the value within the combo box before them.

#1 Facility
#2 Area
#3 Sport

I.e. If Facility is blank the vba code behind Form (On current) updates the Area combo box fails as its expects a value.

Private Sub Form_Current()
Me.comboArea.RowSource = "Select Area.Area_ID, Area.Area, Facility.Facility " & _
"FROM Facility INNER JOIN Area ON Facility.Facility_ID = Area.Facility_ID " & _
"WHERE Area.Facility_ID = " & Me.combofacility.Value & ";"

Me.comboSport.RowSource = "Select Sport_ID, Sport " & _
"FROM Sport " & _
"WHERE Sport.Area_ID = " & Me.comboArea.Value & ";"
End Sub

So is there a method to force them to enter values in the combo boxes in that set order/locking the others out?

Also another issue which is confusing me is that If I change say the facility after all the other combo boxes are populated, it wont impact the values in the other combos making some data incorrect.

I expected this but even though I forced 'Enforced Referential Integrity'
I didnt expect the data to be saved, expected database error.

I know this would be hard to diagnose without detailed info,
Ive attached piccy of my relationships, hopefully easier to understand than my mad ramblings.

[NeoPa]

So is there a method to force them to enter values in the combo boxes in that set order/locking the others out?

You could start with them all (except the first) being disabled and enable them in the AfterUpdate event of the previous control to handle the order.

Also another issue which is confusing me is that If I change say the facility after all the other combo boxes are populated, it wont impact the values in the other combos making some data incorrect

It will affect the data selected in your form (each change) so a requery will be required after each (all) of the combo boxes is changed.
It's not clear that it will change the data in the other combo boxes, but if it does then this, also, should be requeried. In this case, the form data should not really be displayed until all boxes are reselected. The form could perhaps be hidden unless all the combo boxes are correctly selected.