GotoControl and Subforms

[Vel]

Hello,

I have a form, with a subform (call it subA), with a
subform (call it subB).

After uptdating a combo box (cboDate) I want to move the
focus from a control in SubA to a control in SubB,
execute a FindRecord action, then move back to SubA. I
get an error stating that the field does not exist in the
current record. Here's my macro:

Action: GotoControl
Control Name: subB

Action: GotoControl
Control Name: txtMSID

^at this point I get the error. In help it says, in
order to move from a form to a control in the subform to
first move to the subform then to the control, which I
did. Anyway, here's the rest of the macro, which I'm
using because I ran into some odd snags with code. If
there are any other errors (or I'm returning to the main
form incorrectly) please point them out.

Action: FindRecord
Find What: forms!frmIntakes!subDetail!cboDate

Action: GotoControl
Control Name: cboDate


Now, there's a bit more, but if I get this part worked
out I'm all set. In my code I had a problem with the
findrecord argument that I could not resolve so I thought
I'd try a macro. Now I can't get the focus to move
around correctly.

Thanks in advance,

Vel.

 

[Ken Snell]

You're already in a subform and want to move to a second subform? Then first
set focus to a control on the main form, then set focus to the second
subform control, then set focus to the control on the second subform.

 

[Monica Lagunes Sempe]

Hi everyone,
Is there a macro that allows you to create a mandatory "text form field"?
That you can not tab through it unless you key something in?
I have been looking everywhere but haven't been able to find anything.
Thanks in advance,
Monica

 

[Van T. Dinh]

Are you asking about Form Field in Microsoft Word or Microsoft Access (a
database appllication)???

Your post in "microsoft.public.access.forms" indicated Microsoft Word and
Ken's answer is for Microsoft Access!

Re-post to an appropriate newsgroup as per my post in "m.p.a.forms".

HTH
Van T. Dinh
MVP (Access)

 

[Van T. Dinh]

Are the 2 Subforms *nested* Subforms or are they 2 separate Subforms on the
main Form?

Where is cboDate in relation to the Form/Subforms?

Van T. Dinh
MVP (Access)

 

[Vel]

Starting in a subform, in a control named cboDate.

Want to move to a nested subform then to that subform's
control. The full pathnames would be:

Moving From: formA!subA!cboDate
To: formA!subA!subB!txtMSID
Find
To: formA!subA!cboDate

Is that any clearer?

 

[Vel]

Van,

The two subforms are nested. In my example, the paths to
the controls would be:

Move From: forms!frmMain!subA!cboDate
To: forms!frmMain!subA!subB!txtMSID
Then: Execute find action
Then Move To: forms!frmMain!subA!cboDate

Unfortunately, when I've tried this, using the following
actions, in this order I get an error that 'there is no
field txtMSID in the current record'

cboDate AfterUpdate

gotoControl
subB

gotoControl
txtMSID

etc....

 

[Ken Snell]

I've done some testing and have learned why the error is occurring. What the
macro is doing in the second GoToControl statement (the one that should be
setting the focus to the control in the sub-subform) is trying to set focus
on a field with that name in the subform!

I have tried various means to get around this problem and have had no
success directly with consecutive GoToControl actions. It appears that the
two, consecutive GoToControl actions will work from a main form to a subform
and then a control on the subform, but not from a main form to a subform to
a sub-subform to a control on the sub-subform, nor from a subform to a
sub-subform to a control on the sub-subform.

The code does work if you open the "subform" as its own form (it's now the
main form) and then click the command button...the control moves to the
"sub-subform" (now the subform) and then to the control on the
"sub-subform".

Thus, I conclude that you'll need to use VBA code to change the focus. Put
code similar to this on the command button's Click event:

Private Sub cmdButton_Click()
Me.subB.SetFocus
Me.subB.Form.txtMSID.SetFocus
End Sub

However, once you've set the focus to the sub-subform, and then try to run
the DoCmd.FindRecord action, it will not do the find on the txtMSID field in
the sub-subform; it still tries to do it on the most recently active field
in the subform.

Thus, the above code would be modified this way:

Private Sub cmdButton_Click()
Me.subB.SetFocus
Me.subB.Form.txtMSID.SetFocus
Me.subB.Form.RecordsetClone.FindFirst "[txtMSID]=" & Me.cboDate
Me.subB.Form.Recordset.Bookmark = Me.subB.Form.RecordsetClone.Bookmark
Me.cboDate.SetFocus
End Sub

 

[Vel]

Ken,

Thanks for your help. I am still getting an error
though. Utilizing the new code I get the error:


Run-time error'3070':

The Microsoft Jet database engine does not recognize
'txtMSID' as a valid fieldname or expression.


Any suggestions?

Vel.

 

[Ken Snell]

Instead of using txtMSID in the "Find" statement, use the name of the field
to which txtMSID control is bound.

Me.subB.Form.RecordsetClone.FindFirst
"[NameOfFieldToWhichtxtMSIDIsBound]=" & Me.cboDate

My apology....I neglected to make that distinction for you in the posted
code example.