Having to press Esc Key to continue with sub form

[Ant_Snell]

I have an enquiry form with several sub forms. Each sub form is set a visible or not depending on the enquiry type selected in the main form. E.g. if enquiry type combo is “Job Opportunity” then frm_Job_Opportunity sub form is set to visible and all other sub forms visibility is set to false enabling the user to just complete questions relating to that type of enquiry ‘Job Opportunity’.

This all works fine however when entering an enquiry form, the main form is filled in e.g. Enquiry Type, Name, Contact Number etc. then tab to the sub form but it appears not to be editable (with the word calculating briefly showing on the status bar) If you click the Esc button the sub form then becomes editable and works fine. Why is this and how can I avoid having to press Esc key partway through filling out the form/sub form?

The link Master Filed & Link Child field of the sub form is lined by ID . I am fairly confident that side of things is correct.



Thanks

[orange]

Can you post a copy of the database? Preferably in zip format, with only enough data to highlight the issue and instructions to show the issue.

[Micron]

You're loading a bunch of subforms but only showing the one that pertains to mainform combo? I would not.
In combo AfterUpdate event, set the subform control source to be the one that applies to the selection. Your form is likely in the middle of creating or editing a record because of some code or property you've used, and Esc is cancelling the edit.

[Ant_Snell]

The reason for hiding sub forms not relating to the enquiry type is to maintain reasonable DB normalisation. each sub form is based on a table with a one to many relationship with the main enquiry form otherwise if in one table you would have dozens of fields that are rarely filled in. This design seemed to be the best best user experience, e.g. select the enqiry type then see the questions that only relate to that type.

[Ant_Snell]

The Enquiry bit is only a small part of a very large DB so will take some time to pull out the relevant components but will do and Zip up to post. Thanks

[Micron]

The reason for hiding sub forms not relating to the enquiry type is to maintain reasonable DB normalisation.

Sorry, but that is irrelevant to my suggestion and has nothing to do with normalization. I simply said load the relevant subform, don't load every enquiry subform.

[Ant_Snell]

Ahh, so you are saying that on 'After update' of the Enquiry Type combo use load "relevant" sub form. rather than make visible = True ?? Sorry I am just a self taught novice . I'll give that a go and see if it help. Thanks

[Micron]

Yes. You can load 6 subforms (assuming you have that many) in 6 subform controls and hide all but the relevant one but that's very inefficient. It requires a subform control for every form and each other form will load records that you don't need. Instead, you set the subform control sourceobject property based on the choice. Then you only load 1 form and only need one subform control for this.

[Ant_Snell]

Thanks, what you suggest makes sense but I am struggling a bit with the code. If the subforms are called “frm_Enq_Type_1” 2,3,4etc. could you help with a line or so of the code I should put in the after update of the combo if option one is selected, just to get me started?

[CJ_London]

you have one subform called say mySubform

in the after update code of the combo (called say myCombo) you have

mysubform.sourceobject=myCombo

this assumes that the bound column of the combo is the name of the form

[moke123]

here's a crude example which also sets the link master and child fields.

It can be a little tricky referencing any fields in the subform from the main form, but I included a message box to show the syntax.