Repost:gotorecord

[Daniel]

Why is it that when I set my focus on a subform and then use the GotoRecord
command it is still trying to modify the record of the main form and not the
subform? I set the focus on a control my subform and then use the following
command:

DoCmd.GoToRecord acActiveDataObject, , acGoTo, 7

but it won't work and when I try to debug it using the:

MsgBox Screen.ActiveForm.Name

It returns the name of my main form as the active form instead of the
subform that has the focus. Where have I gone wrong in my approach?

How can I change the record of a subform?

If I have the following subform:

Forms![Drawing Management Frm]![Drawing History Frm
sous-formulaire]![Drawing Status Tbl subform]

How can I programmatically goto record 7?

Thank you,

Daniel

 

[Dirk Goldgar]

Why is it that when I set my focus on a subform and then use the
GotoRecord command it is still trying to modify the record of the
main form and not the subform? I set the focus on a control my
subform and then use the following command:

DoCmd.GoToRecord acActiveDataObject, , acGoTo, 7

but it won't work and when I try to debug it using the:

MsgBox Screen.ActiveForm.Name

It returns the name of my main form as the active form instead of the
subform that has the focus. Where have I gone wrong in my approach?

How can I change the record of a subform?

If I have the following subform:

Forms![Drawing Management Frm]![Drawing History Frm
sous-formulaire]![Drawing Status Tbl subform]

How can I programmatically goto record 7?

Thank you,

Daniel

Screen.ActiveForm will always return the name of the active *main*
form -- it knows nothing about subforms. It appears to me that the
subform you want to manipulate is actually a subsubform; that is, a
subform on a subform. Try setting the focus to a specific control on
the subsubform. To do that you must use a series of calls to SetFocus.
The following code assumes it is being executed from the main form:

Me![Drawing History Frm sous-formulaire].SetFocus

Me![Drawing History Frm sous-formulaire]![Drawing Status Tbl
subform].SetFocus

Me![Drawing History Frm sous-formulaire]![Drawing Status Tbl
subform]![NameOfControlOnSubSubForm].SetFocus

DoCmd.GoToRecord acActiveDataObject, , acGoTo, 7

Note that (1) those lines will have been wrapped by the newsreader, but
they were intendend as four separate statements, and (2) you must
replace "NameOfControlOnSubSubForm" with the name of a control on the
subsubform that is capable of receiving the focus.

 

[Dirk Goldgar]

Dirk,

In my case when I'm trying to run this code I am in the subform ...
trying to control the subsubforms. My code based on your previous
email is:

Me![Drawing Status Tbl subform].SetFocus
Me![Drawing Status Tbl subform]![Lead Signature].SetFocus
DoCmd.GoToRecord acActiveDataObject, , acGoTo, RecordPos

Although it does set the focus on the "Lead signature" control when it
proceeds to the Docmd statement it insists on control the record
position of the subform rather than that of the subsubform. Any more
ideas?

I don't think you did anything wrong. It works from the main form, but
not from the subform -- I think that's a bug in Access. Try this
workaround, which acts directly on the recordset of the subsubform:

Me![Drawing Status Tbl subform].Form.Recordset.AbsolutePosition =
RecordPos - 1

(Note: the above line was probably wrapped by the newsreader.)

Be aware that, with this approach, the focus will not be moved to the
subsubform unless you explicitly move it there. So you may well want to
include a SetFocus call to put the focus where you want it.

Also, it's up to you to make sure that RecordPos contains a valid record
number for the subsubform. If it doesn't, an "invalid argument" error
will be raised.

 

[Dirk Goldgar]

Firstoff, it seems to work (that's the good news)

unfortunately I don't get it! could you explain. why -1? On a
seperate note, why is there no help on this subject? Is there any
way to use the gotorecord statement with a subform (I couldn't)?

Thanks again, I've been trying to get this to work for almost three
days. One of those last little details that needed to be fixed.

The GoToRecord method didn't work, IMO, because of a bug in Access. I
have seen no documentation for this, and I haven't taken it up with MS
Product Support Services, but it looks to me as if Access isn't
recognizing that changing the focus from a subform to a subsubform is
changing the "active data object". I note that it does recognize the
change when you start from the main form, change it to the subform, and
from there to the subsubform.

Anyway, since using GoToRecord on a subform requires that subform to be
the active data object, this failure to recognize the new active data
object leaves you stuck, as far as that method is concerned. There may
be a way around it, maybe by setting the focus first up to a control on
the main form and then back down to the subform and subsubform again,
but I haven't tested that yet.

As for why manipulating the subsubform's recordset directly works,
that's only logical. Since Access 2000, the form's Recordset property
has been made available to developers. The property returns a reference
to the same recordset object that the form is using to display, update,
and navigate among records. The form is wired up to this recordset in
such a way that changes to the recordset are directly reflected on the
form, and vice versa. So if you move that recordset to a specific
record, the form goes along.

Working directly on the form's recordset involves using the properties
and methods of that recordset object. Because you're working in an
Access .mdb file, the recordset will be a DAO recordset, so you can use
the methods and properties of a DAO recordset. These are documented in
the help file for DAO, which is included with your installation of
Acces -- "Microsoft Data Access Object 3.6", in the help table of
contents. So there is help on this subject, but alas! you have to know
what to look for before you can find it. You could also try searching
the Microsoft KnowledgeBase at

http://support.microsoft.com/default.aspx?scid=fh;EN-US;KBHOWTO

or even Google Groups (http://groups.google.com), but again, knowing
what keywords to search for is the real trick.

 

[Dirk Goldgar]

Dirk,

Every answer you give me bring up another question!

If the help exists, why is it when I perform a search for let say
"absoluteposition" I get nothing. Should I be able to perform such a
search?

<sigh> It's because the help system, especially the cross-referencing,
has been broken since Access 2000. If you search in the "Access
application" environment, you find nothing, but if you search in the VB
Editor environment you get appropriate hits.

My experience is that the best way to search for a known object,
property, or method name is to type it in the Immediate window or a code
module, then position the text caret on or adjacent to what you typed,
and press F1.

Is there a typical location where these help files are located so
that I might go and retrieve them myself?

They're there, I expect -- the help just isn't finding them.

Finally, on a totally seperate note, I wanted to Thank you for all
your help in the past. Without your help, and the help of many
others, I would never have been able to get to where I am today! You
guys are great!!! As for me tomorrow will be my last day working on
database development, at least for now (it a sickness I tell you...
now that I've started I actually crave programming)!

What? Quit now, just when you're going good?! <gasp!>

You're welcome, and good luck.