Combo Boxes on a sub form

[JackD]

Hi,

My problem is with 2 combo boxes in a subform. One combo box has a list of Services, and the other has a list of different Groups which change depending on what value is selected in the Services combo.
I just cannot get the Groups combo to refresh it's contents whenever the Services combo is changed. I have tried a load of variations in the After Update event but to no avail.

Ideally, I'd also like the Group combo to be set to null whenever the Service combo is changed, just in case an un-associated value remains from a previous selection.

Please see attached.

Thanks for any advice!

 

[boblarson]

You currently have nothing to identify which Groups go with which services. You need a connection. You are currently MISTAKENLY using the Service ID under GROUP ID as criteria and that is wrong. You would need to have some field in the Groups table to identify which Service it belongs to.

 

[JackD]

Hi Bob,

the row source on the 2 combo boxes provide the link between the values Services and Groups?

combo.TransfromService = SELECT ServiceID_Table.Service_ID, ServiceID_Table.Services FROM ServiceID_Table;

combo.TransfromGroup = SELECT GroupID_Table.Groups FROM GroupID_Table WHERE (((GroupID_Table.Group_ID)=Forms!data_entry!hist_sub.form!TransfromService));


It works when the combos are on a main form (provided I take out the reference to the main form in the transfromgroup row source of course).

I can't get it to work as soon as I put this on a subform and try to do it. Interestingly, it has the correct options when you first open the form at the first record. Then the connection seems to be broken thereafter.

Clearly my level of expertise is minimal as I can't see why this doesn't work. Going from a main form to a subform and getting this to work is so much more mysterious than I imagined!

Thanks

 

[boblarson]

Hi Bob,

the row source on the 2 combo boxes provide the link between the values Services and Groups?

No, I don't think you understand. You don't have any ServiceID stored in the Groups table and you need the ServiceID stored there as a FOREIGN KEY to relate the two. Right now they are UNRELATED. All that is happening is that the PRIMARY KEYS from each table are being used and that is NOT CORRECT!

It works when the combos are on a main form

It may APPEAR to work but it will not continue that way as you have provided NO LINK between the two tables. Sorry, but this is 14 years of Access experience talking. You might want to listen.

I can't get it to work as soon as I put this on a subform and try to do it. Interestingly, it has the correct options when you first open the form at the first record. Then the connection seems to be broken thereafter.

That's because you do NOT have it set up properly. You need to have the ServiceID stored in the Groups table as a FOREIGN KEY if you are going to relate them. Now, that is if a group can only belong to one service. If it can belong to multiples then we need to add a third table (a JUNCTION table) to be able to add those relations.