Combo Boxes and Duplicate Entries

[Wong]

Hello

I am trying to get a 2nd combo working from the first .

I have a table called Keystage (tblKeystage) and I have also named my 1st combo cboKeystage. then by following the vba below I created another 6 tables with corresponding names and combo's.

Private Sub cboKeystage_AfterUpdate()

On Error Resume Next

Select Case cboKeystage.Value
Case "Foundation"
cboYear.RowSource = "tblFoundation"
Case "KS1"
cboYear.RowSource = "tblKSOne"
Case "KS2"
cboYear.RowSource = "tblKSTwo"
Case "KS3"
cboYear.RowSource = "tblKSThree"
Case "KS4"
cboYear.RowSource = "tblKSFour"
Case "Post16"
cboYear.RowSource = "tblPost"

End Select

End Sub

The combo boxes both work, brilliant. My problem is that when I enter another record and make a selection from the 1st combo box, it changes all the records.

Could somebody please help.
Thanks
Wong

 

[dopedealer]

Use the Search function on this forum. Type in Cascading Comboboxes. Your approach is a bit off. You don't need 6 tables.
hth
Chris

 

[Wong]

Thanks for your reply.

I have had a good look at the Search function and this how I was directed to adding a further 6 tables.

Originally I had 1 table for Keystage, 1 for year with the ID from Keystage in this table.

Unfortunately this set up did not work.

I will have another look
Thanks again

 

[dopedealer]

Attached is an example i just made, have a look, it should guide you to what you want to achieve.
hth
chris