How to filter records based on Select stmt

[RenaG]

I am using Access 2007. I am also a very new Access programmer.

I have a UserIdentification form that saves in a global variable the users' programID. I would like to use that global variable in the Select statement on the next form to select only those patients involved in the same program. The Where clause as I have it currently written is:

WHERE (((Patient.ProgramID)=[Forms]![Menu]![gloProgID]))

But it isn't working. What do I put to the right of the '='?

TIA

~RLG

 

[boblarson]

I am using Access 2007. I am also a very new Access programmer.

I have a UserIdentification form that saves in a global variable the users' programID. I would like to use that global variable in the Select statement on the next form to select only those patients involved in the same program. The Where clause as I have it currently written is:

WHERE (((Patient.ProgramID)=[Forms]![Menu]![gloProgID]))

But it isn't working. What do I put to the right of the '='?

TIA

~RLG

If this is in code then you would use this to refer to the global variable (subject to the right name being used):

"WHERE (((Patient.ProgramID)= " & gloProgID & "))"

But if it is in an actual query then you can't reference it. You need a FUNCTION to return the value

Function RetGlobal() As Long
    RetGlobal = gloProgID
End Function

Then you can refer to it in the query by

WHERE (((Patient.ProgramID)=RetGlobal()))

 

[RenaG]

Hi Bob,

Thanks for taking a look at this.

Not knowing the best place to put the function, I put it in the UserIdentification code but I created it as Public so I thought it would be ok. When I ran it, the following error occurred:

Undefined function 'RetGlobal' in expression

So then I moved it to the top of the Patient code. When I ran it again, I got this error:

Property not found

Where do I put it?

Or alternatively, you mentioned putting the filter in the code. How do I write that and where does it go?

Thanks!

~RLG

 

[boblarson]

The function would go in a STANDARD MODULE, not a form, report or class module. The module can't be named the same as the function.

 

[RenaG]

It worked - yea!!!
(I had to also move the declaration for the gloProgID to the general module.)

Thanks so much for your help!

~RLG

 

[boblarson]

It worked - yea!!!
(I had to also move the declaration for the gloProgID to the general module.)

Yes, that is true. It isn't really a global variable unless it is there. If it is in a form, report or class module then it is only a module level variable. Variable scope is one thing you definitely want to get down.

 

[RenaG]

Do I need to declare a variable in the general module if I want to access it in different modules / forms?

~RLG

 

[boblarson]

Do I need to declare a variable in the general module if I want to access it in different modules / forms?

~RLG

Yes, you do.

Public variableNameHere As Datatype

 

[RenaG]

Okey dokey! Got it. Thanks!

~RLG