Multiple linked Datasheet subforms

[cpnet]

I'm trying to create a master-detail form that links records with one master
table, to 2 detail tables. The basic way of doing this is easy enough, but
I want to use data sheets for the master as well as the 2 detail forms. In
other words, rather than just showning one record at a time from my master
table, I want to show all records from the master table all of the time, and
only the detail records corresponding to the selected master record. Here's
what I'm trying to do:

- Create 3 subforms (all using Datasheet view), linked to my master and 2
detail tables respectively
- Create a 'base' form that will host my 3 subforms
- When the user changes the current record in the "master" subform, the
"detail" subforms should be filtered to reflect the changed record.

I haven't done a ton with VBA, so I'm not clear on how the "On Current"
event of my master subform can change the filters for my 2 detail subforms
(when all 3 are placed on the same 'base' form).

 

[Ken Snell [MVP]]

No need for VBA.

Ue the LinkChildFields and LinkMasterFields properties of the subform
controls to tie the subforms to the main form. ACCESS then will filter the
subforms' records for you as you change the main form's data.

 

[cpnet]

Unfortunately, I don't think that deals with my situation. The main (base)
form has no data, so I can't link any subforms to is. It is strictly to
hold the 3 subforms that contain my data.

To try to be more concrete, I have the following 4 forms:

BaseForm
SubFormMaster
SubFormDetail1
SubFormDetail2

BaseForm has no data. SubFormMaster, SubFormDetail1, and SubFormDetail2 are
all subforms that I've placed on BaseForm. SubFormMaster, SubFormDetail1,
and SubFormDetail2 are all displayed in Datasheet view. When the current
record of SubFormMaster changes, then I want to filter the records on
SubFormDetail1 and SubFormDetail2 so that they only relate to the one
current record in SubFormMaster.

I know this isn't the "normal" way to do things, but I want to display my
master form as a DataSheet, which is why I'm trying to set things up this
way. I just don't know what code to put in the "On Current" event for the
SubFormMaster subform, to set filters on the SubFormDetail1 and
SubFormDetail2 subforms.

I think with your approach, then only a single Master record is displayed.
I want to display ALL master records, but still filter the detail records on
the single current master record.

Thanks,
cpnet

 

[Ken Snell [MVP]]

You could use the Current event of the SubFormMaster source object form to
set the filters on the other subforms' source objects' forms.

Code would look something like this (this code assumes that the subform
controls [the controls that hold the "details" subforms] are named
SubFormDetail1 and SubFormDetail2):


Private Form_Current()
Me.Parent.SubformDetail1.Form.FilterOn = False
Me.Parent.SubformDetail2.Form.FilterOn = False
Me.Parent.SubformDetail1.Form.Filter = "[LinkingFieldNameInDetail1Subform]="
& Me.[LinkingFieldNameInSubFormMaster].Value
Me.Parent.SubformDetail1.Form.Filter = "[LinkingFieldNameInDetail2Subform]="
& Me.[LinkingFieldNameInSubFormMaster].Value
Me.Parent.SubformDetail1.Form.FilterOn = True
Me.Parent.SubformDetail2.Form.FilterOn = True
End Sub

 

[Joan Wild]

Unfortunately, I don't think that deals with my situation. The main
(base) form has no data, so I can't link any subforms to is. It is
strictly to hold the 3 subforms that contain my data.

BaseForm
SubFormMaster
SubFormDetail1
SubFormDetail2

As an alternative to Ken's method, you could put a textbox on the BaseForm
(called txtLink) and set it's control source to =SubFormMaster!KeyField

Then set the link master/child properties for the SubFormDetail1 and 2 as:
Master - txtLink
Child - whatever the matching field is on the subform.

No code needed.

 

[cpnet]

I'm having problems with the first line:

Me.Parent.SubformDetail1.Form.FilterOn = False

It's giving me an error about, "...invalid reference to the property
Form/Report".


It is the actual "Form" property that seems to be the issue. I tried:

Dim myForm as Form
Set myForm = Me.Parent.SubformDetail1

And I get an error about wrong type (as I'd expect). When I change it to:

Dim myForm as Form
Set myForm = Me.Parent.SubformDetail1.Form

I again get the, "...invalid reference to the property Form/Report" error.
I've also tried other things like:

"Me.Parent!SubFormDetail1.Form", and
"Me.Parent.Controls("SubFormDetail1").Form", all with the same results.
It's probably
something simple, but I'm stuck.

 

[Ken Snell [MVP]]

When I use SubformDetail1 in the code step, I am assuming that that is the
actual name of the subform control that holds the form that you're calling
SubformDetail1.

Check what the real name of the subform control on the main form is that
holds the SubformDetail1 form, and use that name. Do this by opening the
main form in design view, clicking on top edge of the subform control that
holds the SubformDetail1 form, open Properties window, click on Other tab,
and read the name there.

If you have the correct name, then try these variations for the code:
Me.Parent!SubformDetail1.Form.FilterOn = False
or
Me.Parent.Form.SubformDetail1.Form.FilterOn = False

Note also that this code will work *only when* you're running the code while
the form that holds the code is serving as the subform of the main form.

 

[cpnet]

I did some checking, and confirmed I have the name right. I think it may
actually be a security or scope issue of some sort.

I placed a button on BaseForm. When the button is clicked, it calls the
code:

Public Sub Test()
Me.SubFormDetail1.Form.OnFilter = False
End Sub

....with no problems.

I can call this code with no problems from within BaseForm's module.
However, if I try to call this from another sub-form's module (or a new
module), I can call the sub, but get the same old error when it get's to the
code about, " Me.SubFormDetail1.Form.OnFilter = False"


I may have to use the hidden text box trick posted by "Joan Wild" to get
this to work. I'm used to C# and Delphi, so the scope and security with VBA
isn't that clear to me.

 

[Ken Snell [MVP]]

Joan's idea is much easier to implement, so that is probably a good way to
go.

I don't see security being any issue with the code giving you the error. If
it's working from the base form, then it should work from the subform as
well.