Subform design with allowances for child tables

[Jaazaniah]

Ok, the gist of this is I'm trying to come up with a way to display
and work with a table that acts as child items to the main table of
the subform.

The setup goes something like this
Main form is a single form.
[MainForm].[CustID]=[Historyform].[CustID]
Historyform is a continuous form listing history of transactions.
[Historyform].[HistoryID]=[Extrasform].[HistoryIDRef]
Extras form is continuous, and full of references to a history item
and extras related to the transaction.

Obviously I can't have Extras as a subform in the History form, so if
I make the form a popup, modal, or some other form of independant
form, what would be the best way to have the two forms linked in such
a way as to:

a) filter existing extras records to those relating to the current
transaction, and
b) constrain input such that HistoryIDRef=HistoyID of the record on
the continuous form that called it?

looking for a little bit of VB help in solving this problem. will
cross-post to VBA and see if it gets me anywhere.

 

[Dirk Goldgar]

Ok, the gist of this is I'm trying to come up with a way to display
and work with a table that acts as child items to the main table of
the subform.

The setup goes something like this
Main form is a single form.
[MainForm].[CustID]=[Historyform].[CustID]
Historyform is a continuous form listing history of transactions.
[Historyform].[HistoryID]=[Extrasform].[HistoryIDRef]
Extras form is continuous, and full of references to a history item
and extras related to the transaction.

Obviously I can't have Extras as a subform in the History form, so if
I make the form a popup, modal, or some other form of independant
form, what would be the best way to have the two forms linked in such
a way as to:

a) filter existing extras records to those relating to the current
transaction, and
b) constrain input such that HistoryIDRef=HistoyID of the record on
the continuous form that called it?

looking for a little bit of VB help in solving this problem. will
cross-post to VBA and see if it gets me anywhere.

You don't need VBA code for this; you can do it entirely in the design of
controls and subforms on a single main form.

Put both subforms on the main form. Suppose that the subform controls are
named "sfHistory" and "sfExtras".

For sfHistory, set the Link Master Fields and Link Child Fields properties
both to "CustID".

Put a new text box on the main form. Name it "txtSubformLink". Set its
Visible property to No, make it as small as you like, and set its Control
Source property to:

=[sfHistory].[Form]![HistoryID]

For sfExtras, set the Link Master Fields and Link Child fields as shown
here:

Link Master Fields: txtSubformLink
Link Child Fields: HistoryIDRef

There, you're done. sfHistory will now automatically show the History
records corresponding to the CustID on the main form, and sfExtras will
automatically show the Extras records pertaining to the current History
record on sfHistory.