Requery All the Subforms in 1 command?

[SteveC24]

Hello all,

I have a form, which contains about 10 subforms....each subform is for different TYPES of equipment, and so each contains a field called "Manufacturer".

What I would ideally like to do is be able to double-click the dropdown box for Manufacturer, and be able to edit/delete/add the items in that list. When the form is closed, the combo box would be updated.

I have done this all over the database, but because these are all subforms, the only way I can see to do it is to individually requery each manufacturer field when the manufacturer form closes. This would be a REAL pain in the proverbial. I would like to do the same elsewhere....but hit the same problem.

I thought I could simply requery the whole form, as in, the main form that all the subforms rest in, but that doesn't work.

Any suggestions?

 

[Fizzio]

hmmm,

The simple answer to your question is no, you will need to requery every subform unless you have the Master/Child links set for every form then as the manufacturer changes, the subforms can change as well to keep sync

However.....

My main concern about your position is that your data structure may not be optimal.
Is there a reason that you are displaying 10 subforms?
a) Do you have a different table for each piece of equipment?
b) Do you have 1 table (with equipment type and Manufacturer in ) but display subsets of this table in the subforms.

The answer to this will indicate if you need to re-evaluate your data structure (for your benefit, hope the answer is (b))

 

[SteveC24]

Hello,

Thanks for your input.

No, there is a different table for each different TYPE of equipment. I had to do it this way, as each different type of equipment has different information that needs to be entered for it. The only common fields between them all is NAME and MANUFACTURER.

Guess I will have to go and individually requery them....urgh!

 

[Fizzio]

It depends on your form structure and how you use the dropdown box.

If the manufacturer dropdown changes the main form data, setting the master/child links will force all the subforms to requery as well.

If the combo only affects the subforms, you could 'cheat' and contain all the subforms in a subform on the main form (if that makes sense )
Then on the change of the combo, requery the 1st subform (you will probably need to add a textbox to the 1st subform to keep this and the main form in sync)

If you cannot sus it, post a cut-down sample of your Db.

 

[Rich]

Hello,

Thanks for your input.

No, there is a different table for each different TYPE of equipment.

Why not just normalise the data?

 

[SteveC24]

Well, I bit the bullet this afternoon, and added an individual requery for each combo....good lord it was boring, but didn't take as long as I thought it would! A lot of copying and pasting went on!

Thanks for your input people!

 

[Ian Mac]

Well, I bit the bullet this afternoon, and added an individual requery for each combo....good lord it was boring, but didn't take as long as I thought it would! A lot of copying and pasting went on!

Thanks for your input people!

You should use the advice provided, the Subforms should have the Parent/Child links set correctly and Me.Requery will requery the Subforms.

 

[Fizzio]

Well, I bit the bullet this afternoon, and added an individual requery for each combo....good lord it was boring, but didn't take as long as I thought it would! A lot of copying and pasting went on!

Thanks for your input people!

It sounds like your data structure and form structure are just looking for trouble!
Take another look at how you are structuring your Data and Forms as suggested - it will cause a hell of a lot less headavhes in the long run

You cannot beat double Geordie advice!