Fiiltering subForm in Tab Control section of main Form

[Artist.Anon]

I have a Form 'fUser' that has a Tab Control section with 3 Tabs. In each Tab I have the same subForm 'sfLogs'. What I need to do is to apply a different filter to each Tab/subform. I do not know where to do this.

Example:

Table 'tUser' with a field 'Userid'
Table 'tLogs' with fields 'Userid1', 'Userid2' & 'Status'
(These tables are unlinked in relationship)

Form 'fUser'
Form 'sfLogs' (continuous list)



In form 'fUser' I have some other data and a section for a control tab box 'cTabBox' that has 3 tabs 'A', 'B', 'C'.

In tab 'A' I want to display the subForm 'sfLogs' WHERE (sfLogs.Userid1 = tUser.Userid) AND (sfLogs.Status = "A")
In tab 'B' I want to display the subForm 'sfLogs' WHERE (sfLogs.Userid2 = tUser.Userid) AND (sfLogs.Status = "B")
In tab 'C' I want to display the subForm 'sfLogs' WHERE (sfLogs.Userid1 = tUser.Userid) OR (sfLogs.Userid2 = tUser.Userid)

I can set everything up but do not know how to or where to apply the filters I need on each subForm of each Tab. I suspect I need to write some VBA code but also do not know where to put the code.

If anyone can help please?

[June7]

Never heard of anything like this. You have 3 subform container controls that all have the same SourceObject. I doubt can do what you want. Code would set properties of sfLogs. This would affect all instances of the subform because there really is only one subform.

[Rod]

A challenge - I'm working on it!

[Artist.Anon]

Thanks. Look forward to hearing if you have any success

[Rod]

What I thought may work, doesn't.

Dim frm1 as Form_Form1
Dim frm2 as Form_Form1
Set frm1 = New Form_Form1
Set frm2 = New Form_Form1
frm1.Visible = True
frm2.Visible = True

produces only one instance of Form1. D--n! I always thought you could get multiple instances this way - but I thought wrong - I'm sure I've done it in the past for side-by-side comparisons. (I shall complain to the OOP standards authority.) Even one of my reference books, admittedly for v2003, says this works. Did it change with v2007?

I then played with trying to automatically copy the form design to another class - a kind of Save As. The documentation suggests this is possible and the help for the 'Save' macro action even explains how to do it. The idea was to temporarily create two copies for the duration of the requirement. I could not get this to work.

So it was back to the sledgehammer: I physically created a copy of the form and renamed it - Form5 is an exact copy of Form4.

    Set mfrm1 = New Form_Form4
    mfrm1.Visible = True
    Set mfrm2 = New Form_Form5
    mfrm2.Visible = True

and here's the result with different filters - as expected - no rocket science.



If you must do this then at present the only way I know is to create copies of the design.

[Rod]

I don't believe it! Now my original idea is working! I need to play with it more to make sure its stable.

[Rod]

It now seems stable.

Set mfrm1 = New Form_Form4
    mfrm1.Filter = "CustomerName like 'j*'"
    mfrm1.FilterOn = True
    mfrm1.Visible = True
    Set mfrm3 = New Form_Form4
    mfrm1.Filter = "CustomerName like 'r*'"
    mfrm1.FilterOn = True
    mfrm3.Visible = True

Couldn't be anything to do with the fact I'm making the first form visible before opening the second? Nah! Here's the result - note the caption for both is Form4 and I didn't cheat!



Now the tricky part is imbedding these forms as subforms. You need to design your tabbed control with subform container controls that are empty. When your main form loads you need to create three instances of the subform and assign one to each of the subform controls, set the filters, links, etc. It should work. You may need to go back to the form's Open event but I don't think so.

If the subform assignation doesn't work - I see no reason why it shouldn't - you may need to toggle the forms visible/hidden as required to simulate the tabbed control.

Rather you than me.

[Rod]

Ouch, I've fallen at the last hurdle. It seems that the SourceObject property of a subform requires the name of a class. Unfortunately my scheme generates three instances from the same class so they all have the same class name. Anyway I suspect the subform goes away and creates its own instance of the class.

So subforms are ruled out.

OK, questions:

Do you wish to see all three filtered forms simultaneously or is it that you are looking at one or the other? If you wish to see all three at once, are floating windows acceptable? You can still synchronise data between windows but it requires some effort as there is no built-in Access functionality for this.

[Artist.Anon]

Hi,

No they do not need to be visible simultaneously. Each is in a tab and when that tab is pressed, then that subform becomes visible in the tab control.

I am thinking that the easiest may just to have 3 subforms (the same), but I am still faced with how do I apply a filter on load of the subform in a tab, dependent on a variable of the main form?

[Rod]

Well that was an interesting excursion into Never Never Land. The answer of course is straightforward and staring me in the face! It was my comment that the subform generates its own instance of the class; three subforms, thus three separate instances each of which may be filtered independently.

Artist, if you're still following this thread make a post and tell us the names of the three subform controls - I guarantee only one of them is called 'sfLogs,' perhaps none. We'll reply with some sample code to filter the subforms and tell you where to put the code.

[Artist.Anon]

Hmmmmm. I only got as far as adding the subform into the first tab when I hit the problem of how to filter it. Thats when I researched the filtering and couldn't find the answer and posted the issue here.

I did not then try and add the same subform into the 2nd and 3rd tabs. I have now tried to do so but just get blank pages instead of the subform that I see in the first tab.

Thanks for all your efforts Rod!

[Rod]

Don't worry about the 'blank pages.' This is characteristic of the Access Form Design window when adding second or more instances.

Let me give you some steps to follow, then I have to leave it until tomorrow.

Open your main form in the form design window.
Click on each tab in turn and delete the subform control.
Click on tab A and drag the sfLogs form onto the tab page (it turns black at one point). The subform will be displayed in all its glory.
With the subform control selected go to the Property Sheet and the Other tab. Name this control sfrA.
Click on tab B and drag the sfLogs form onto it. This time it will appear blank.
With the subform control selected go to the Property Sheet and the Other tab. Name this control sfrB.
Click on tab C and drag the sfLogs form onto it. This time it will appear blank.
With the subform control selected go to the Property Sheet and the Other tab. Name this control sfrC.
Select sfrA on tab A.
In the Property Sheet tab Data, make sure the Link Master Fields entry is Userid and the Link Child Fields is Userid1.
Select sfrB on tab B.
In the Property Sheet tab Data, make sure the Link Master Fields entry is Userid and the Link Child Fields is Userid2.
Select sfrC on tab C.
In the Property Sheet tab Data, make sure the Link Master Fields entry is Userid and the Link Child Fields is Userid1.

You may need to qualify the link fields with table/query names but try without first. I am assuming there is a control on the master form named Userid and that there are controls on the subform called Userid1 and Userid2. Not strictly necessary for them to be controls but they must be in the result set.

Now is a good time to test that the synchronisation is working on all three subform instances.

Filters tomorrow.

[Artist.Anon]

Hi Rod,

That is pretty much what I had, except the Link Master/Child fields. I can't enter anything into these- I get "Can't build a link between unbound forms".

For each tab there is an On Enter event. This is where I thought I would need to put the filter requirements for the subform? If so what would the code be?

Regards,

[June7]

Interesting, I tested the code from post7 in a general module. The code executes but the forms won't stay open. I step debug and watch each form open with correct filtering but as soon as I step through the End Sub line, some or all of the forms close.

If you don't care about seeing all 3 forms side-by-side, why not toggles (or radio buttons) on one form that will execute code to modify the filter criteria. So either click tabs or click toggles, whichever will be easier in the long run.

Artist, do you want to edit data in each subform? If not, another alternative is 3 listboxes side-by-side to show the different filtered data.

[Rod]

Hi June,

The code executes but the forms won't stay open. I step debug and watch each form open with correct filtering but as soon as I step through the End Sub line, some or all of the forms close.

Here are extracts from a document I'm considering publishing.

When dimensioning variables for referring to form object instances, it is important to remember that when a final reference to an object is destroyed, the object itself is destroyed. Thus, using the above as an example, if I had dimensioned the variable at the procedure level then, the moment the procedure ends, the variable is destroyed and takes the form with it! Variables used for these purposes should be declared at the module or global level.

Could this be the cause? My duplicate instances are stable and fully functional. (Please ignore the preceding spaces on each line of the code.)