Code in Form not working right

[Hibba]

I have a form with 6 combo boxes. All the combo boxes will change rowsource value depending on what value is in the first box, which is a number field, so always a number. I used the code builder to do this:

Private Sub Form_Current()
If Me.Cabin.Value < 10 Then
   Me.Skill_1.RowSource = "Select Skill_ID, Skill From [Lower Skills] Order By Skill_ID"
   Me.Skill_2.RowSource = "Select Skill_ID, Skill From [Lower Skills] Order By Skill_ID"
   Me.Skill_3.RowSource = "Select Skill_ID, Skill From [Lower Skills] Order By Skill_ID"
   Me.Skill_4.RowSource = "Select Skill_ID, Skill From [Lower Skills] Order By Skill_ID"
   Me.Skill_5.RowSource = "Select Skill_ID, Skill From [Lower Skills] Order By Skill_ID"
   Me.Skill_6.RowSource = "Select Skill_ID, Skill From [Lower Skills] Order By Skill_ID"
Else
   Me.Skill_1.RowSource = "Select Skill_ID, Skill From [Upper Skills] Order By Skill_ID"
   Me.Skill_2.RowSource = "Select Skill_ID, Skill From [Upper Skills] Order By Skill_ID"
   Me.Skill_3.RowSource = "Select Skill_ID, Skill From [Upper Skills] Order By Skill_ID"
   Me.Skill_4.RowSource = "Select Skill_ID, Skill From [Upper Skills] Order By Skill_ID"
   Me.Skill_5.RowSource = "Select Skill_ID, Skill From [Upper Skills] Order By Skill_ID"
   Me.Skill_6.RowSource = "Select Skill_ID, Skill From [Upper Skills] Order By Skill_ID"
End If
End Sub

HOWEVER, when I move between records, this doesn't really work. If Cabin.value is 15 and you move to the next record, it will display all the combo boxes as if cabin.value was greater than 10. You can tab through the values and they will change to what they are suppose to be, but I need it to update correctly.

Any suggestions?
I have attached the file just in case.

Thanks!

 

[MStef]

Hello Hibba!
Here it is! It's a little better now, but
I think you have to learn something about
DATABASE NORMALIZATION, PRIMARY KEY,
RELATIONSHIPS, SUBFORMS.
(MStef alias Štef)

 

[Hibba]

Thanks, I'll take a look. And yes, I need to learn about a lot of things...

 

[Pat Hartman]

Here's a sampl db that shows the problem and offers a solution.

http://www.access-programmers.co.uk/forums/showthread.php?t=79602