Fiiltering subForm in Tab Control section of main Form

[Rod]

Artist,

You should have no trouble with the link properties. Here is an example that I have just constructed (click on it to enlarge). Make sure there is an entry for Source Object.





What does not work is the Access wizard; don't try to click on the elipsis as Access bitches and then clears the name of the link field. It's the wizard that is at fault, not the underlying subform mechanism.

[Artist.Anon]

Hi Rod,

Yes I was using the '...' wizard, and can enter the data directly.

I am not worried at all about the 3 views of the subforms, whether they are visible etc. I will make three separate subforms and put each one into a separate tab.

All I want to be able to do is to apply a filter to these subforms with criteria from the form level.

Regards,

[Artist.Anon]

Also a quick question, I have defined a public variable in a module called 'CurrentUser'. I would like to use it in a form's properties sheet / data / filter.

Something like:
Filter: Archived = False AND DestinationUserid = CurrentUser

Where 'Archived' and 'DestinationUserid' are fields within the table. The first part works fine, but not the 'CurrentUser' part, so it is not recognizing that it is a public variable. What formatting should I be using?

Cheers

[Rod]

I am not worried at all about the 3 views of the subforms, whether they are visible etc.

They are only blank in the Form Design window; in Form View they all display data.

I will make three separate subforms and put each one into a separate tab.

Up to you of course but you triple the maintenance overhead.

All I want to be able to do is to apply a filter to these subforms with criteria from the form level.

We're coming to that; let's get the subforms installed and linked correctly. Give me the names of the three subform controls (not the form name) and I'll give you some sample code.

I have defined a public variable in a module called 'CurrentUser'

Confusion here: Is 'CurrentUser' a standard module name or a variable name?

Filter: Archived = False AND DestinationUserid = CurrentUser

If 'CurrentUser is a module name then it definitely won't work. You need to write DestinationUserid = CurrentUser.<myvariablename> where the last term is the name of your global variable. Global variables are declared in the declaration section of standard modules using the 'Public' keyword. Do not use form, report or class modules for global variable definition.

[June7]

Re Post15 - That's it! Need declaration in module header.

I just noticed your example code shows setting Filter of mfrm1 twice. I followed that in my code and it still works to open two instances with the different filter criteria. It also works when I change the second Filter code to mfrm3. Can that be explained?

[Rod]

Can that be explained?

Gremlins?

No it had me going for a minute or two but Access is doing exactly what I told it. The second reference to mfrm1 is a mistake; it should be mfrm3. Well spotted. The delights of cut and paste!

Access opens the first form, mfrm1 and filters it by "j*". It then opens the second form, mfrm3, and somehow the filter is carried forward to the new form. The code then resets the filter on the first form to "r*". I wondered when I tested it why the form I expected on top was underneath.

Can't explain why the filter propagated to the second form. I have checked the property sheet and it's not to do with that. Anyway you say you get the same effect. Perhaps FilterOn sets the filter in a working copy which Access is using instaed of a file copy.

[Artist.Anon]

Hi

'CurrentUser' is the variable in my module called 'Globals'

So I have tried DestinationUserid = CurrentUser
and DestinationUserid = Globals.CurrentUser

and neither works. When opening the form I get prompted for CurrentUser (or Globals.CurrentUser) to enter a value.

How do I reference a public declared variable that is set in the login page. If I msgbox (CurrentUser) on form load, it does display the value, so it is there and public.

Confused.

[Rod]

Here is a trivial db showing the use of a global variable in a standard module. See what's different from yours.

MSAFUSeOfGlobal.accdb

Open Form1 first.

[Artist.Anon]

Hi Rod,

Use of a global variable in forms or vba is not where I am having the problem. Its when I try and use it on the form's property sheet under the Filter setting. Open a form in design view, click on property sheet, go down to filter, and try and filter the form where userid=global.currentuser (where userid is a field in the table belonging to the form in question) and currentuser is a public variable declared in global module.

Once more thanks for your input,

[Rod]

Hi Artist,

Yes I get the same symptom when using the Property Sheet - seems to be a bug. It works however in the form's Load event.

Me.Filter = "DestinationUserid = " & CurrentUser
Me.FilterOn = True

Yet the same syntax fails in the Property Sheet - weird.

[Artist.Anon]

That's the piece of code I needed, I can apply that to the Enter event on the tab control.
Thanks for all the help!

[June7]

It's my experience that Access (as in queries and boxes) cannot 'see' VBA declared variables and custom constants and Sub procedures. But VBA custom Function procedure can be called by Access queries and controls, at least can in ControlSource - don't know about other properties. So a UDF could reference the global variable or constant and return the value.