Getting a 3rd dependent combo box to work

[Cerian]

Hi there,

I'm having trouble getting my 3rd dependent combo box to work in Access 2003.

I have three dependent boxes named cbogroup, cbosection and cbofunction. Each one is dependent on the selection made in the previous box. All of these appear on one form called 'records subform'.

The information for each box is taken from a different table. The first from 'functional group' which has only 2 fields: 'group' and 'group code'. The second box is populated from table 'functional section' which also contains the field 'group code' and this information is used to filter the choices for the second box. This is the row source for cbosection:

SELECT [Functional Section].[Section Code], [Functional Section].Section, [Functional Section].[Group Code] FROM [Functional Section] WHERE ((([Functional Section].[Group Code])=[cbogroup])) ORDER BY [Functional Section].[Section Code];

This works fine. However, my 3rd box won't play ball. The information is taken from table 'functional list' which is linked to 'functional section' using the field 'section code'. I assumed you could use the same row source, i.e. :

SELECT [Functional List].Code, [Functional List].[Section Code], [Functional List].Function FROM [Functional List] WHERE (([Functional List].Code)=[cbosection])) ORDER BY [Functional List].Code;

But when I put the criteria =[cbosection] in, the box just comes up blank.

This is the code I have used:

Private Sub cbogroup_AfterUpdate()
Me.cbosection = Null
Me.cbosection.Requery
End Sub

Private Sub cbosection_AfterUpdate()
Me.cbofunction = Null
Me.cbofunction.Requery
End Sub

Private Sub Form_Current()
Me.cbosection.Requery
Me.cbofunction.Requery
End Sub


Any ideas on where I'm going wrong?

Thanks!

[puppydogbuddy]

Hi there,

I'm having trouble getting my 3rd dependent combo box to work in Access 2003.

I have three dependent boxes named cbogroup, cbosection and cbofunction. Each one is dependent on the selection made in the previous box. All of these appear on one form called 'records subform'.

The information for each box is taken from a different table. The first from 'functional group' which has only 2 fields: 'group' and 'group code'. The second box is populated from table 'functional section' which also contains the field 'group code' and this information is used to filter the choices for the second box. This is the row source for cbosection:

SELECT [Functional Section].[Section Code], [Functional Section].Section, [Functional Section].[Group Code] FROM [Functional Section] WHERE ((([Functional Section].[Group Code])=[cbogroup])) ORDER BY [Functional Section].[Section Code];

This works fine. However, my 3rd box won't play ball. The information is taken from table 'functional list' which is linked to 'functional section' using the field 'section code'. I assumed you could use the same row source, i.e. :

SELECT [Functional List].Code, [Functional List].[Section Code], [Functional List].Function FROM [Functional List] WHERE (([Functional List].Code)=[cbosection])) ORDER BY [Functional List].Code;

But when I put the criteria =[cbosection] in, the box just comes up blank.

This is the code I have used:

Private Sub cbogroup_AfterUpdate()
Me.cbosection = Null
Me.cbosection.Requery
End Sub

Private Sub cbosection_AfterUpdate()
Me.cbofunction = Null
Me.cbofunction.Requery
End Sub

Private Sub Form_Current()
Me.cbosection.Requery
Me.cbofunction.Requery
End Sub


Any ideas on where I'm going wrong?

Thanks!

Try commenting out what you have in the current event and test. If that leaves something out of synch, then requery the main form>>>>>Me.Requery<<<<<to bring everything in synch.

[Cerian]

Try commenting out what you have in the current event and test. If that leaves something out of synch, then requery the main form>>>>>Me.Requery<<<<<to bring everything in synch.

I'm sorry, I don't know what you mean by 'commenting out'!??

[puppydogbuddy]

I'm sorry, I don't know what you mean by 'commenting out'!??

Commenting out means place a single quote in the left margin to turn an executable statement into a non-executable comment as show below.

Private Sub Form_Current()
'Me.cbosection.Requery
'Me.cbofunction.Requery
End Sub

The above 2 requery statements will not be executed because they are commented out.

[Cerian]

Commenting out means place a single quote in the left margin to turn an executable statement into a non-executable comment as show below.

Private Sub Form_Current()
'Me.cbosection.Requery
'Me.cbofunction.Requery
End Sub

The above 2 requery statements will not be executed because they are commented out.

Thanks for your help on this. But no luck I'm afraid! Any other ideas?

[Cerian]

I'm not sure what I did, but I've somehow managed to solve the problem! Thanks for your help!

C

[puppydogbuddy]

I'm not sure what I did, but I've somehow managed to solve the problem! Thanks for your help!

C

Please post the code you have now, so that other newbies can see what the final working code looks like in comparison to your prior posts. Thanks.

[Cerian]

I don't think it was changing the code that solved the problem, I think it was down to a glitch with the relationship between my second and third tables.

I did have to tweek the code slightly so that when you change the first box, the third box becomes a blank again. It now reads:

Private Sub cbogroup_AfterUpdate()
Me.cbosection = Null
Me.cbofunction = Null
Me.cbosection.Requery
Me.cbofunction.Requery
End Sub

Private Sub cbosection_AfterUpdate()
Me.cbofunction = Null
Me.cbofunction.Requery
End Sub

Private Sub Form_Current()
Me.cbosection.Requery
Me.cbofunction.Requery
End Sub

[puppydogbuddy]

Thanks. This will give any interested party another idea of where to look for a possible cause for combo box problems.