Alter Bound Fields from Underlying Query - Control Source

[mous]

Hi

I have a query that links three tables together. All with correct joins.

The last table contains:

Wk1, Wk1Desc, Wk2, Wk2Desc, Wk3, Wk3Desc etc etc

My two fields on my form are called WkNo (check box) and WkDesc (Combo Box) and a bound to Wk1 and Wk2Desc respectively by default.

On a drop down box that stores the values 1, 2, 3, 4 etc etc I want to alter the underlyning Control Source of these two fields, so when the user selects 3, the control Source for WkNo becomes Wk3 and WkDesc becomes Wk3Desc to edit.

This is my code:

Private Sub WeekSelector_AfterUpdate()

If WeekSelector = 1 Then
WkNo.ControlSource = Wk1
WkDesc.ControlSource = Wk1Desc
ElseIf WeekSelector = 2 Then
WkNo.ControlSource = Wk2
WkDesc.ControlSource = Wk2Desc
Me.Requery

End If

End Sub

Any reason why this isn't working. Thanks

 

[Dennisk]

you have a design issue here as the table is not Normalised in that that you have repeating group wk1 wk2 etc. The correct way is create a seperate table containing a generic wk number, use the primary key from the original table as the foreign table.

 

[mous]

Thanks for the reply. I do realise this. Any ideas though.