Can I cursor through a Master form and display a synchronized Detail form?

[Shayne G. Wright]

[This followup was posted to microsoft.public.access.forms and a copy was sent to the cited author.]

Hi

I was wondering if the following is possible in Access97

I want to create a master form as a datasheet that displays all records
in my PROPERTY table. As I navigate through the master form datasheet,
and set cursor focus on individual records in the datasheet, I would
like my detail subform(s) to display synchonized data from child tables
of the PROPERTY table.

That is I want a master form for PROPERTY and detail subforms for each
of my related CUSTOMER, PROPERTY_LEGAL, PROPERTY_USE, CUSTOMER_OWNER
child tables. All tables have a FK/PK relationship with the parent
PROPERTY table using an ACCT_ID field.

PROPERTY - PROPERTY_LEGAL 1:M
PROPERTY - PROPERTY_USE 1:M
PROPERTY - CUSTOMER 1:1
PROPERTY - CUSTOMER_OWNER 1:M

So if I have my cursor on record in the master datasheet form, for say
ACCT_ID 12345, the detail subforms will display all PROPERTY_LEGAL,
PROPERTY_USE, CUSTOMER, and CUSTOMER_OWNER records for ACCT_ID 12345.
When I cursor down one record, to select ACCT_ID 12346, all subforms
will be synchronized and display all records for ACCT_ID 12346. That is,
whichever record is selected in master datasheet, will determine the
records displayed in each of my subforms according to the ACCT_ID of the
master record.

Oh yeah, I am not doing any data entry into my base tables...just
querying. So my tables should be protected from accidental updates.

Thanks and take care,
Shayne

 

[Albert D. Kallal]

yes, I would use continues forms in place of datasheets. You can see some
nice shots of grids, and several show a screen that syncs two forms as you
ask.

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

You don't mention how many "child" forms you plan to have, but lets assume
two.

So, you got the main form + two child forms you need to display.

So, I would built 3 grids using continues forms, or 3 forms in data sheet
view.

You then create a 4th form - unbound, and just a plain form...
Now drop in the above 3 forms.

In the master form (which is now a subform), in the on current event, you
place the following command to make the child forms follow this form.

me.Parent.Child1.Requery
me.Parent.Child2.Requery.


In the link child/master settings for child 1, you place:

linkChildFields main_id (whatever is the name of the field in
this sub-form that is used to relate back to the parent table)
LinkMasterFields [MasterForm].[form].[ID]

In the link child/master settings for child 2 form you place

linkChildFields main_id (whatever is the name of the field in
this sub-form that is used to relate back to the parent table)
LinkMasterFields [MasterForm].[form].[ID] ("masterForm" is the name of
the contorl you used to hold the master form).

You are done....