Macro to open the main form before the subforms

[louise]

Hello,
I have an Intro-Form that is used to create a new record and manually assign the "Event Number" which is the linking field between several tables/forms.
A button on the Intro-Form opens another MainForm with two SubForms -- where more information on the Event is entered.

My button on the Intro-Form correctly opens the MainForm, to the correct record with the correct Event Number.
But the Subforms don't immediately show that they are also on the correct records and have the correct Event Number -- the EventNumber doesn't show up until you click on one of the subform's fields. Then the Event Number shows up.

I think that this is because the subforms are opening first and don't yet have the main form's information. But I don't know what to do about it. I would prefer to handle the issue through a macro, rather than directly entering coding. Can you give me some leads?

(I could be wrong that this is the reason for the problem....)

Thanks very much!

[mike60smart]

Hi Louise

Can you post a screen shot of your Relationship Diagram?

[Welshgasman]

Yes, subforms open before main form?

[Micron]

As wgm says, subforms load first. Suggest you
- try opening the main form, which will automatically load your subforms, then on the latest main form event (Current) set the subform control values. If the main form contains navigation controls, likely the current event is the one you'd need so that this occurs on every record. Also, the link Master and Child fields should take care of this. Did you set those properties?
- or if those subform fields are bound, try refreshing the subforms
- or open the main form, allow that to retrieve the data for the bound main form fields, then assign the subform control source to the desired subform. That will load the subform after the main form. The main form event could be Current or Load, I think.

[orange]

Louise,

Posting a copy of your database may be the most efficient way to a solution.
We don't fully understand your set up, but you might try google "synchronize forms/subforms" or similar and see if any responses apply.
As others have said in Access subforms load first - that's the way it as built. It isn't an option.

[louise]

Thank you for these suggestions!
Yes, I have set the Master and Child fields.
For suggestion #1: Can you tell me more about what you mean by "set the subform control values" as it would look in a macro? I'm not sure what the control value is in this case.
For suggestion #2: I can't see how to refresh the subforms, since the event options for the subform is either OnEnter or OnExit.
For suggestion #3: Again, could you be more specific about "then assign the subform control source to the desired subform" -- as it would look in a macro, and is this On Current for the mainform?
Thank you so much!

[mike60smart]

Hi Louise

If you are opening a Main Form to add a new Record, and start entering date into the Main Form,

then the related Subform will automatically display the related Foreign Key.

Then when you enter data into the subform the primary Key for that record will be displayed automatically.

Does this happen?

[Micron]

- by control value I meant input whatever value it is that you need to be in there.
- if you're saying those are the only 2 events, that's because you're selecting the subform control, not the subform. Click on subform a second time.
- the subform control source property refers to what form you want to be displayed in it. You can set that after the main form opens.

I don't use macros and didn't know you were so not sure how much help I can be with that. As an example, the macro step SetValue is probably what you'd need to make a control hold a value.

[Gicu]

Not sure it can be done with a macro but it is very easy with VBA in the Current event of the Main form. I think all of these would work (adjust for your form names):

'setting the SourceObject
Me.sfrmSubform1.SourceObject="sfrmSubform1" 'name of the form to load in sfrmSubform1 contro
Me.sfrmSubform2.SourceObject="sfrmSubform2" 'name of the form to load in sfrmSubform2 control

'setting the subform recordsource
Me.sfrmSubform1.Form.recordsource="qrySourceForSubform1" 'name of the query that is the record source of sfrmSubform1 form
Me.sfrmSubform2.Form.recordsource="qrySourceForSubform2" 'name of the query that is the record source of sfrmSubform2 form

'requery the subforms
Me.sfrmSubform1.Form.Requery 'requery the first subform
Me.sfrmSubform2.Form.Requery 'requery the second subform

Cheers,
Vlad