Use a macro to move between subforms on a form

[Peter Fox]

is there a macro action which will move the focus from the main form to a
sub-form, or between one subform and another subform?
I need to provide a requery action so that as the user moves from one record
to another in a datasheet subform, the corresponding records in other tables
are displayed in all other sub-forms.
Have done this with separate buttons on each subform activating a requery,
but would like to streamline this with one button

 

[Steve Schapel]

Peter,

This type of functionality would normally be handled via the Link Master
Fields and Link Child Fields properties of the subforms.

 

[Peter Fox]

This doesn't help. I probably needed to more fully describe the situation,
but I was trying to save space.
I savvy linking subforms to main forms, but what I actually have is a
(blank) main form (no fields) and three forms on this as "subforms". I did
this because I wanted what would have been my main form to be in table
format, but apparently you cannot have a main form in continuous table format
(showing many records) and then have subforms on such a form each showing
only a single record.
So I have three "subforms" on a blank main form , and what I need is to
detect the movement of the cursor through the records in the first "subform"
(lets call this subform A), and for the corresponding related data to change
in the other two subforms (lets call them B and C).
Failing being able to detect this cursor movement, I have tried putting a
"refresh" button on the main, to move the focus to first subform B, and find
its corresponding record to the current record in subform A, then move to
subform C, and find its corresponding record to the current record in subform
A.
I have got around the prob by buttons inside each of the subforms B and C,
with a macro to find their corresponding record based on a common ID field.
But this requires the user to click two buttons. I would like a single
refresh but that would activate a macro to move the cursor first to subform
B, and refresh, then move again to subform C, and refresh.
Wondering whether I can use a Sendkeys action in a macro, but do not know
the proper syntax to do a Ctrl/Tab in the sendkeys argument.
Am not into writing procedures, and wish to avoid a solution using these, as
am comfortable being able to do pretty much everything I need to thru macros,
which I believe I have grasped reasonably well. Not got the time for
tackling learning the structure, language, and syntax of access basic (or is
it now visual basic in Access, no longer the jet database language ?? am
using Access 2003, SP 2)

 

[Steve Schapel]

Peter,

Sorry for lack of clarity in my earlier reply.

On the main form, put an unbound textbox. Set its Control Source
property to the equivalent of:
=[SubformA]![NameOfLinkingField]

Enter the name of this textbox as the Link Master Fields property for
Subform B.

 

[Peter Fox]

That didn't work, as the link Child/master will only allow linkings to
fields, not text-box controls, but I fudged it: using your suggestion as
inspiration, I created a new field in the underlying table, put this on the
form, and used a set-value action to change the value of this field in
response to a Gotfocus event on the main subform (subform A). I also set the
link child/master to this new field for both subforms B and C - and it
works!!!
Your answer didn't directly work, but certainly pointed the way to a solution.
Thanks heaps!

Peter,

Sorry for lack of clarity in my earlier reply.

On the main form, put an unbound textbox. Set its Control Source
property to the equivalent of:
=[SubformA]![NameOfLinkingField]

Enter the name of this textbox as the Link Master Fields property for
Subform B.

--
Steve Schapel, Microsoft Access MVP

This doesn't help. I probably needed to more fully describe the situation,
but I was trying to save space.
I savvy linking subforms to main forms, but what I actually have is a
(blank) main form (no fields) and three forms on this as "subforms". I did
this because I wanted what would have been my main form to be in table
format, but apparently you cannot have a main form in continuous table format
(showing many records) and then have subforms on such a form each showing
only a single record.
So I have three "subforms" on a blank main form , and what I need is to
detect the movement of the cursor through the records in the first "subform"
(lets call this subform A), and for the corresponding related data to change
in the other two subforms (lets call them B and C).
Failing being able to detect this cursor movement, I have tried putting a
"refresh" button on the main, to move the focus to first subform B, and find
its corresponding record to the current record in subform A, then move to
subform C, and find its corresponding record to the current record in subform
A.
I have got around the prob by buttons inside each of the subforms B and C,
with a macro to find their corresponding record based on a common ID field.
But this requires the user to click two buttons. I would like a single
refresh but that would activate a macro to move the cursor first to subform
B, and refresh, then move again to subform C, and refresh.
Wondering whether I can use a Sendkeys action in a macro, but do not know
the proper syntax to do a Ctrl/Tab in the sendkeys argument.
Am not into writing procedures, and wish to avoid a solution using these, as
am comfortable being able to do pretty much everything I need to thru macros,
which I believe I have grasped reasonably well. Not got the time for
tackling learning the structure, language, and syntax of access basic (or is
it now visual basic in Access, no longer the jet database language ?? am
using Access 2003, SP 2)

 

[Steve Schapel]

Peter,

I am pleased to know that you have a working solution.

However, I need to point out that the approach I suggested is a standard
procedure, and will definitely work. Your statement "the link
Child/master will only allow linkings to fields, not text-box controls"
is simply incorrect.

 

[Peter Fox]

Took another look at it. My conclusion that access would only accept a field
from the underlying table as link rather than a text box was because I tried
to use the builder with the Link Child Fields property to set up the link,
and the text box was not listed as being available to select as the Master
field in the builder window's drop-down for the master field choice.
However, after reading your last reply, had another go, this time typing the
text box name directly into the Link Master Field property, instead of trying
to set this up using the builder.
As you say, it does work, and of course it is a much tidier solution than
creating an unnecessary field in the underlying table.
Thanks again!