Using Tempvars in application

[DeanJW2006]

I have a table in my access 2010 application that lists Departments and Executives (for simplicity sake). I use the Executives in many queries throughout the application; however, Executives come and go frequently causing significant changes to my queries.

I was thinking about setting up tempvars or some better solution to initialize at the startup of the application. Each tempvar would be an executive of one of the departments. Instead of using the actual executive name in the queries, I would use the tempvar that was initialized at startup, thus assuring me that I have the correctly updated list.

How would I initialize these tempvars? Each would relate to a Department. Is this possible or is there a better way?

 

[MarkK]

If we are talking about a query, and we are working on how to drive data into that query more efficiently, wouldn't it make sense to see the query? The way you describe it, that you have the actual executive's name saved in the query, seems like the wrong approach, so then replacing that wrong approach with TempVars doesn't see right either. You should be using the LongInteger primary key of the row, not the name.
hth
Mark

 

[DeanJW2006]

I was trying to keep it simple. It is a table with two fields: Department and Executive. I have many queries that use the Executive field's name as filtering criteria for reports. Executives change frequently, so I was looking for a way to dynamically update the queries by changing them from a "name" to a variable that initializes everytime the database starts up.

 

[MarkK]

It is a table with two fields: Department and Executive.

I'm sorry, but I don't see how this can be a useful data structure. What is the overall purpose of the database? Are there other tables? When the executive changes, what happens to the old data? Are any of the changes recorded with a date, so you can track changes over time?
hth
Mark

 

[DeanJW2006]

there are lots of tables and lots of queries, reports and forms. I'll try changing the references to the indexes rather than the names and maybe that simply takes care of it....

 

[jdraw]

DeanJW,

Can you step back and tell us WHAT your database is about (the things involved) in plain English? Once readers understand WHAT you are trying to do, they may have advice/options for HOW to do things in Access.

 

[DeanJW2006]

The database tracks issues to put it simply. It tracks hundreds of issues. Up to 5 departments or Executives could have a primary, secondary, tertiary.....responsibility on each issue. While departments don't change, Executives do. I have hundreds or queries and reports that filter on the Executives names (for better or worse!). I need a way to update the Executives when they change without having to touch a hundred queries. Bad design or not..the option of starting over is not an option.

 

[Gasman]

As mentioned you should be using the key.
However I do the same in one app of mine, where I need the value throughout the app.

Consequently I use Tempvars. The code below gets me the manager logging in. From that I then know his employees to show, etc etc

Since creating this, I have seen mention of Global variables, but this works fine for me.

However, please note my signature though.

TempVars("EmployeeID").Value = Me.cboEmployeeID.Column(0)

 

[DeanJW2006]

Thanks. I'll try something like that. Ideally, I was looking, for a way to assign the 15 department Executives to the tempvars by using a do while not EOF structure or something similar. My idea is to get the tempvars refreshed each time the application starts which will then feed into numerous queries.

 

[Gasman]

Thanks. I'll try something like that. Ideally, I was looking, for a way to assign the 15 department Executives to the tempvars by using a do while not EOF structure or something similar. My idea is to get the tempvars refreshed each time the application starts which will then feed into numerous queries.

That effectively is what I do. I create 3 tempvars in all, at login time and they are there for the duration. One is even just to save multiple DLOOKUPs everywhere to get manager name for the ID.

However I only have one tempvar for the ID and that is set for whoever logs in, even though there are 3 managers, plus myself.

 

[jdraw]

Could you post a jpg (screen capture) of your tables and relationships so we can see your set up? Make sure all fields in all tables are visible.

 

[Cronk]

Let me guess. Say the table with Departments and Executives has one executive to each Department and there are 5 Departments. For each type of report, there are 5 reports, one for each Department, each based on a separate query for each Department with the filter being the Executive's name.


Good design would have the selection of which report based on selecting the Department ie filtering on the DepartmentID, not the Executive's name.

 

[jdraw]

Cronk,

I tend to agree with your guess, but the OP has said
Bad design or not..the option of starting over is not an option.

Reminds me a little of
"I'm too busy chopping wood to sharpen the ax"

 

[DeanJW2006]

In the Executive table, there are 15 Execs/Dept. In the "issues" table there are 5 fields for Executives to possibly occupy depending upon their Department's level of involvement in the issue. All 5 have to be included in all reports to each of the 5 departments. There is only one report based off an openargs with a qry for each primary Dept. In the end, each Department gets a report showing all issues where they are listed in places 1 thru 5.

 

[jdraw]

This may be description of WHAT without database terms.

[COLOR="Blue"][I]In our company, which is comprised of 5 Departments, there are 15 Executives.
 We manage a number of Issues and to each we assign an Executive from each of the 
Departments. The Executives are assigned an Involvement level ( 1 for Primary, 
2 for Secondary...).
From time to time we are required to 
-identify the Status of each issue or 
-Report on the Issues or
-add a new Issue or edit an existing Issue(s).[/I][/COLOR]

Just trying to separate What from How in simple terms. Adjust as necessary to reflect your situation.
Good luck.

 

[DeanJW2006]

In our Company which is comprised of 15 Departments and each Department has an Executive. We manage a number of issues and each issue has at least 1 but up to 5 Executives assigned to it depending upon each Executive's level of involvement (from Primary to 5th level).

Every month we report on the status of all of the issues. The reports are issued by Department, and each Department report shows all of the issues
they were involved with.

 

[MarkK]

Cronk,

I tend to agree with your guess, but the OP has said
Bad design or not..the option of starting over is not an option.

Reminds me a little of
"I'm too busy chopping wood to sharpen the ax"

Now I just bail when people have a table design problem they refuse to correct. No time.
Mark

 

[CJ_London]

In the "issues" table there are 5 fields for Executives to possibly occupy depending upon their Department's level of involvement in the issue

this sounds like bad design. I suspect right design means just one query.