Setting focus to tabbed subforms

[Nyanko]

Setting focus and linking to tabbed subforms

Hi,

I'm chasing my tail with an Access problem, and can't get my head around solutions I have found on the net. I dabbled in Access quite a bit, about 5 years ago, I've never touched the VBA side, macros or used the OnEvents.

I have a database set up to record answers to a large employee survey. I've set up a master form containing a tab control with 10 tabs each containing a separate subform all linked by an interview number.

My structure looks like :

FrmMaster - name of form containing all subforms
Tabctl1 - name of tab control
Tab1, Tab2, Tab3 - name of each tab within the tab control
FrmSection1, FrmSection2, FrmSection3 - name of each subform on appropriate tab
Comment1, Comment2, Comment3 - name of final control on each subform

The name of the first control on each subform is a question number : Q7, Q17, Q24, Q36 and does not follow any set pattern.

How would I go about moving from Tab1.FrmSection1.Comment1 to Tab2.FrmSection2.Q7

Any help (in newbie terms) would be most gratefully appriciated

 

[RuralGuy]

Tab controls do not affect control referencing in any way. It is as if they are not even there. Setting focus to a control on a SubForm is a two step process. First you set the focus to the SubFormControl that displays the SubForm on your Main Form. Then you set the focus to the control on the SubForm. The name of a SubFormControl defaults to the name of the Form it is displaying, but it is not manditory and can be changed. Remember, first the SubFormControl and then the SubForm. The code in the Exit event of FrmSection1.Comment1 will look something like:

Forms!FrmMaster!FrmSection2.SetFocus
Forms!FrmMaster!FrmSection2.FORM!Q7.SetFocus

You can also use:

Me.Parent!FrmSection2.SetFocus
Me.Parent!FrmSection2.FORM!Q7.SetFocus

 

[Nyanko]

Thank you. You're a star !

I've been getting my brain in a knot on that one, always seems so simple when explained !

 

[Nyanko]

Not sure if this is related to this issue, but every time I tab through my controls using this code it sets a new record for each form as soon as I enter the first control.

I have no other VBA code, however I have the interview number (autonumber) field on each sheet - although it's not enabled and greyed out.

Why do my sub forms create a new record every time I tab to them ?

 

[RuralGuy]

Setting the focus to a control in a SubForm does not create a new record. There must be something else going on behind the scenes.

 

[Nyanko]

I have used an autonumber to link all the subforms and masterform together. I included this field on all subforms, although I have disabled it and made it non-tabbable ... do you think this has anything to do with it ?

 

[RuralGuy]

As long as the Linking field is in the RecordSource of the SubForm it need not be displayed for the LinkChild/MasterFields to function correctly. I assume you mean that the MainForm AutoNumber is used as a ForeignKey in the RecordSource of the SubForms.

 

[Nyanko]

Hi,

I tried removing the InterviewNumber (primary key) field from all the tabbed subforms however it's still creating a new record everytime I switch to a different subform.

I have no other VBA code or anything complicated... I have no idea why it's doing this ?

I've attached my current database in the hope that this makes more sense If you look at the table you can see that a new record has been created after each section has changed ... I'm also getting Null Value errors when trying to enter in the first control of each subform ??

I'm well and truely baffled !


[cross posted to http://www.ozgrid.com/forum/showthread.php?p=409718&posted=1#post409718 as dealine looming and desperate ]

 

[RuralGuy]

MainForm/SubForm combinations are usually designed to display a 1:m relationship. Your MainForm and your SubForms are all dealing with the same record of the same table. There is no reason to have SubForms in this case since all of the fields you are presenting are available in the current record of the MainForm.

 

[RuralGuy]

As a follow up: When you move the focus from a SubForm to a MainForm or another SubForm, Access saves the current record of the SubForm. This would explain the mysterious new records you were talking about. Another reason to not use SubForms for this particular application.

 

[RuralGuy]

You may want to look at MVP Duane Hookom's At Your Survey application for ideas.

 

[Nyanko]

MainForm/SubForm combinations are usually designed to display a 1:m relationship. Your MainForm and your SubForms are all dealing with the same record of the same table. There is no reason to have SubForms in this case since all of the fields you are presenting are available in the current record of the MainForm.

Thank you for the time you have taken to look over this for me.

I see now where the issue has arisen, however I've left with a quandry... the survey I need to produce is huge and doesn't fit on a single page, I thought that by splitting it into subforms it would solve my problem. But I guess it's created more issues than it solved !

Any suggestions as how I can go about this ?

 

[RuralGuy]

The tabbed form is a good idea. You just do not need SubForms on the tabs. Put the questions and fields directly on each tab without the SubForm.

 

[Nyanko]

The tabbed form is a good idea. You just do not need SubForms on the tabs. Put the questions and fields directly on each tab without the SubForm.

...well that seems like the most logical solution and a whole lot less grief than what I came up with. I seem to have a knack for over complicating matters.

I am very grateful for your assistance and patience

 

[RuralGuy]

Glad I could be of some help. For the future, you may want to look at the link I provided for ideas. I have not looked at it myself but I'll bet the Duane has a separate record for each question/answer pair. That design would allow any number of questions/answers without calling the programmer. Your design requires modifying the form each time there is an additional question. Just a thought. What you have will probably work just fine for your situation.