Open form to proper record

[Jay Oken]

I have a form (fm_menu) with a subform (sf_subform). I have it set up so
that the subform opens with DataSource1. When the user double clicks on a
record in the subform I post the [Link_id] field value for the active
subform record to a field on the main form and then change the datasource
for the subform and refresh the subform. When the new record source opens I
want the subform to find the record for the [link_id]. What VBA code would I
use to do this or is there a different (more preferred) way to do this.

[Jeanette Cunningham]

Use the Link Master and Link Child fields to sync the subform with the main
form.
On the properties dialog for the subform, choose the Data tab and find the
Link Master and Link Child fields properties.
Click the button to the right to open the dialog where you choose the link
fields.

With that set, the main form and subform will stay in sync automatically.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

"Jay Oken" <okencon...@yahoo.com> wrote in message
news:%23woceRE...@TK2MSFTNGP06.phx.gbl...

[Jay Oken]

Thanks but the problem with this solution is that the subform changes
datasources. The main form does not contain data per se but is a "menu" form
that opens up different forms in the subform.

"Jeanette Cunningham" <n...@discussions.microsoft.com> wrote in message
news:%233He5bO...@TK2MSFTNGP05.phx.gbl...

[Dirk Goldgar]

"Jay Oken" <okencon...@yahoo.com> wrote in message
news:%23woceRE...@TK2MSFTNGP06.phx.gbl...

It's not entirely clear to me what you're doing, Jay. What is your current
code for the subform's DblClick event (or whatever event it is that you're
using to change datasources)? "Datasource" isn't a well-defined term in
Access, so I don't know if you are changing the RecordSource of the
subform's Form object, or the SourceObject of the subform control itself.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

[Jay Oken]

Hi Dirk,

Let me clarify and simplify.

Main form = fm_menu
This has no RecordSource and is simply a "menu" form
This also has a text box called txtFamilySelect

Subform = fm_subform
This recordsource is selected based on buttons on fm_menu. Basically, based
on the button clicked, I change the recordsource and then refresh the form.

I then want to find (and make current) the record in the fm_subform where
Family_Link_ID (a field in the fm_subform) = txtFamilySelect (from the
fm_menu form).

Thanks,
J

"Jay Oken" <okencon...@yahoo.com> wrote in message

news:Oqe8A8Qg...@TK2MSFTNGP02.phx.gbl...

[Jay Oken]

Hi Dirk,

Let me clarify and simplify.

Main form = fm_menu
This has no RecordSource and is simply a "menu" form
This also has a text box called txtFamilySelect

Subform = fm_subform
This recordsource is selected based on buttons on fm_menu. Basically, based
on the button clicked, I change the recordsource and then refresh the form.

I then want to find (and make current) the record in the fm_subform where
Family_Link_ID (a field in the fm_subform) = txtFamilySelect (from the
fm_menu form).

Thanks,
J

"Dirk Goldgar" <d...@NOdataSPAMgnostics.com.invalid> wrote in message
news:FF5F745A-CD64-40CF...@microsoft.com...

[Dirk Goldgar]

"Jay Oken" <okencon...@yahoo.com> wrote in message

news:%23mFaM2%23gKH...@TK2MSFTNGP02.phx.gbl...

> Hi Dirk,
>
> Let me clarify and simplify.
>
> Main form = fm_menu
> This has no RecordSource and is simply a "menu" form
> This also has a text box called txtFamilySelect
>
> Subform = fm_subform
> This recordsource is selected based on buttons on fm_menu. Basically,
> based
> on the button clicked, I change the recordsource and then refresh the
> form.

If you change the subform's RecordSource property, you don't need to do
anything else to refresh or requery the subform -- when you change a form's
RecordSource, the form is automatically requeried.

> I then want to find (and make current) the record in the fm_subform where
> Family_Link_ID (a field in the fm_subform) = txtFamilySelect (from the
> fm_menu form).

But you don't want to restrict the subform to show only that record, just
find that record among the records now displayed? Then you could use code
along these lines:

------ start of example code ------
'*** I'm assuming this code is running on the main form ***

With Me.fmSubform.Form
.RecordSource = "Your new recordsource"
.Recordset.FindFirst "Family_Link_ID = " & Me.txtFamilySelect
End With
------ end of example code ------