global variables?

[Guest]

I have a database where all of the data input / reports is based on which
TEAM they are currently using.

So, when the user first enters the database, I need them to select which
TEAM to use and have this as the TEAM for their entire session. For example,
whichever team they select, all the reports, forms, etc need to be filtered
to this TEAM.

How do I do this? How are the variables passed between forms?

I've tried using a combo box to select the TEAM. I get the list of teams
okay. But how to I bind this to subsequent forms / reports/ etc.

Thanks.

 

[SusanV]

In a module, declare a global variable; ie, Global TeamName as String
Set their startup form to populate this global variable via a dropdown or
whatever. On all the forms, reports etc, use the global variable for the
WHERE clause; ie, for a report:

DoCmd.OpenReport "YourReportName", acViewPreview, , "Team = " & "'" &
TeamName & "'"

Or a form:

DoCmd.OpenForm "YourFormName", acNormal, , "Team = " & "'" & TeamName & "'"
-- hth,SusanV

 

[Dirk Goldgar]

I have a database where all of the data input / reports is based on
which TEAM they are currently using.

So, when the user first enters the database, I need them to select
which TEAM to use and have this as the TEAM for their entire session.
For example, whichever team they select, all the reports, forms, etc
need to be filtered to this TEAM.

How do I do this? How are the variables passed between forms?

I've tried using a combo box to select the TEAM. I get the list of
teams okay. But how to I bind this to subsequent forms / reports/
etc.

Thanks.

One way would be to do the following:

1. Define a global variable, and use to store the selected TEAM.

2. Define a public function in a standard module; all the function
does is return the value of the TEAM global variable. If you want, you
can have the function check whether the TEAM variable has not been set
yet, or has somehow lost its value, and prompt the user for it if
necessary, storing the selected TEAM in the variable.

3. In all your forms and reports, set the RecordSource query to call
that function as a criterion.

So, for example, you might have a standard module "modTEAMSelect",
containing code like this:

'----- start of module code -----
Option Compare Database
Option Explicit

Public gTEAM As String ' or Long, or whatever

Function CurrentTeam() As String ' or Long, or whatever

' If we don't have a team selected, make the user choose one.

If Len(gTeam) = 0 Then
DoCmd.OpenForm "frmSelectTeam", _
WindowMode:=acDialog
End If

' Return the current team, as stored in the global variable.

CurrentTeam = gTEAM

End Function
'----- end of module code -----

Now your various recordsource queries will use this function; e.g.,

SELECT * FROM SomeTable
WHERE SomeTable.Team = CurrentTeam();