Prev: cmd button to open small form when value is null
Next: Concanotating Date() in a form issue
From: John Keith on 22 Jan 2009 14:56
How do I give the string name of a subform in the following:
DoCmd.GoToRecord acDataForm, "Subform name string", acGoTo, recnum

Or if this is not possible, here is what I am trying...

I have 3 forms,
Subform A is a continuous form w/ Name and Title only (onClick routines
position the form B record).
Form B includes Subform A and has all fields for one row.
Popup Data Entry form C is modal, has all fields for adding data.
All 3 have the same underlying updatable query.

Main form A/B allows clicking on a row in A which selects that row and
displays it in the fields of B.
A/B has an Add button and a Delete button.

When adding, an input box asks for the key and if that key already exists
then B shows that record. Otherwise C pops up to add new data. Once C is
closed, the data is committed and Form A/B gets focus w/ B showing the new
record.

In each case above, I'd like to make the subform also GoTo that record.

What are some alternatives?
I did try to swap A and B but a "continous" main form can not have a sub-form.

--
Regards,
John
From: Marshall Barton on 23 Jan 2009 09:19
John Keith wrote:

>How do I give the string name of a subform in the following:
>DoCmd.GoToRecord acDataForm, "Subform name string", acGoTo, recnum
>
>Or if this is not possible, here is what I am trying...
>
>I have 3 forms,
>Subform A is a continuous form w/ Name and Title only (onClick routines
>position the form B record).
>Form B includes Subform A and has all fields for one row.
>Popup Data Entry form C is modal, has all fields for adding data.
>All 3 have the same underlying updatable query.
>
>Main form A/B allows clicking on a row in A which selects that row and
>displays it in the fields of B.
>A/B has an Add button and a Delete button.
>
>When adding, an input box asks for the key and if that key already exists
>then B shows that record. Otherwise C pops up to add new data. Once C is
>closed, the data is committed and Form A/B gets focus w/ B showing the new
>record.
>
>In each case above, I'd like to make the subform also GoTo that record.


A lot of the DoCmd operations are limited in their ability
to manipulate specific objects. In this case you can use
the LinkMaster/Child properties to sync the two subforms.

Add a hidden text box (named txtLinkA) to the main form.
Then add a line of code to subformA's Current event
procedre:
Parent.txtLinkA = Me.[the primary key field name]

Now set the subformB control's LinkMasterFields property to
txtLinkA and set the LinkChildFields property to the primary
key field name.

--
Marsh
MVP [MS Access]
From: John Keith on 23 Jan 2009 10:35
Thanks for the reply:

I tried linking the form/sub-form via the master/child properties but this
caused an undesired effect. It forced the continous sub-form to filter to
the main form's single record.

I discovered that I did not need an explicit gotorecord for the sub form.
All that was required was to position the record set then the sub-form
automatically positioned itself.

In Form C's btnAdd_Click routine after the newSSN has been stored using the
Close form action. F is a ref to formB and SF is a ref to sub-formA:

F.Requery
SF.Requery
F.Recordset.FindFirst ("[SSN] = '" & NewSSN & "'")
DoCmd.GoToRecord acDataForm, "Empl", acGoTo, F.Recordset.AbsolutePosition
+ 1
SF.Recordset.FindFirst ("[SSN] = '" & NewSSN & "'")

I am wondering if there is a trick to using the link fields that will
preserve the continous feature of the sub-form. Perhaps this way could get
around having to requery the recordsets.

--
Regards,
John


"Marshall Barton" wrote:

> John Keith wrote:
>
> >How do I give the string name of a subform in the following:
> >DoCmd.GoToRecord acDataForm, "Subform name string", acGoTo, recnum
> >
> >Or if this is not possible, here is what I am trying...
> >
> >I have 3 forms,
> >Subform A is a continuous form w/ Name and Title only (onClick routines
> >position the form B record).
> >Form B includes Subform A and has all fields for one row.
> >Popup Data Entry form C is modal, has all fields for adding data.
> >All 3 have the same underlying updatable query.
> >
> >Main form A/B allows clicking on a row in A which selects that row and
> >displays it in the fields of B.
> >A/B has an Add button and a Delete button.
> >
> >When adding, an input box asks for the key and if that key already exists
> >then B shows that record. Otherwise C pops up to add new data. Once C is
> >closed, the data is committed and Form A/B gets focus w/ B showing the new
> >record.
> >
> >In each case above, I'd like to make the subform also GoTo that record.
>
>
> A lot of the DoCmd operations are limited in their ability
> to manipulate specific objects. In this case you can use
> the LinkMaster/Child properties to sync the two subforms.
>
> Add a hidden text box (named txtLinkA) to the main form.
> Then add a line of code to subformA's Current event
> procedre:
> Parent.txtLinkA = Me.[the primary key field name]
>
> Now set the subformB control's LinkMasterFields property to
> txtLinkA and set the LinkChildFields property to the primary
> key field name.
>
> --
> Marsh
> MVP [MS Access]
>
From: Marshall Barton on 23 Jan 2009 15:23
John Keith wrote:
>I tried linking the form/sub-form via the master/child properties but this
>caused an undesired effect. It forced the continous sub-form to filter to
>the main form's single record.
>
>I discovered that I did not need an explicit gotorecord for the sub form.
>All that was required was to position the record set then the sub-form
>automatically positioned itself.
>
>In Form C's btnAdd_Click routine after the newSSN has been stored using the
>Close form action. F is a ref to formB and SF is a ref to sub-formA:
>
> F.Requery
> SF.Requery
> F.Recordset.FindFirst ("[SSN] = '" & NewSSN & "'")
> DoCmd.GoToRecord acDataForm, "Empl", acGoTo, F.Recordset.AbsolutePosition
>+ 1
> SF.Recordset.FindFirst ("[SSN] = '" & NewSSN & "'")
>
>I am wondering if there is a trick to using the link fields that will
>preserve the continous feature of the sub-form. Perhaps this way could get
>around having to requery the recordsets.


Use the LinkMaster/Child links of the single subform (B),
not the continuous subform (A)

The requeries are needed to pick up a new/edited record.

--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: cmd button to open small form when value is null
Next: Concanotating Date() in a form issue