Trouble with SetFocus property and SubForms in Access

Question

I have a MainForm with 4 other subforms on it. These subforms are visible and invisible based on user selection on the main form. The visible/invisible works like a charm but I am having trouble with setting the focus on these subforms. what I am doing:

on change event of a combobox makes a subform visible, then I wanted to set my focus on that form but I kept getting the runtime 2110 error. I noticed this was because all the fields on my main form are required fields and access could not transfer the focus unless these fields were filled up (setfocus worked when all the fields were filled in the main form). Now to fill up all the fields before going to the subform is counterintuitive because its not how the data to be entered will be flowing.

So my question is ----- How do I let my focus transfer to my subform before having to fill out all the required fields in the mainform!?

All help is highly appreciated as always! thanks!

Answer 1

IMHO that is not possible if the main form is bound to a recordsource. Whenever Access moves the focus away from a form it's default behaviour is to save the changes if there are any. It will therefore always want a valid record (i.e. no changes or valid changes).

If the subform record has a recordsource with a foreign key relationship to the recordsource of the main form you will also need the PK of the main form to have been created - which usually is a serial generated on save. If you do not have a record, you do not have an PK to pass on as FK.

Alas, if there is NO relationship between the main form and the subforms (i.e. you don't need the PK), a possibility would be to make the main form unbound and save it's contents via a button click in order to allow users to enter half of the data before switching to the subforms.

Of course you would have to handle the case of users inserting data in the subforms and then not finishing the fields on the main form.