Solved

global variable for access reports

Posted on 2013-11-25
7
422 Views
Last Modified: 2013-11-25
I've inherited an access database.  it has lots of reports.  I have to make changes to some of the reports.  That changes I have to make are things like changing the names and titles of employees, like "Joe Smith, Director of Residential Service".    This is currently hardcoded in the reports.  Is there an easy way to feed this data from an access table or a global-type variable, so that the next time I only need to change the data in one place?
0
Comment
Question by:HLRosenberger
7 Comments
 
LVL 4

Assisted Solution

by:AccessGuy1763
AccessGuy1763 earned 125 total points
ID: 39674741
Yep!  You just need to declare them in a global Module.

If you have one already, you can put the global variables there or you can open the VB Editor and do Insert > Module.

Although it's not required, global variable declarations typically are found at the top of the Module, above any global functions or subroutines in the same Module.

So your declaration for the above variable might look like this:
Dim strDirOfResServ as string

Then you can set it in the same global module or anywhere else, since it's global:
strDirOfResServ = "Joe Smith, Director of Residential Service"

Now the part that might be a little tricky is that I think you'll need to use code to set the captions:
me.lblDirOfResServ.Caption = strDirOfResServ
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 125 total points
ID: 39674743
you need to create a table with the names and title of the people.
tblPeople
ID
Name
Title

use at least three fields as mentioned above
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 250 total points
ID: 39674745
If this is a  titles/heading,  for your reports you can include it in a table and use the following as a controlsource property:

 = DLookup("HeaderInfo", "YourTable")


However, if you have an employees table and their positions/title are included in the employeeInfo table you might be able to work that information into the recordsource query of your report.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:HLRosenberger
ID: 39674947
For all 3 of these ideas, how do I then reference the data/field/variable inside the reports, in a simple label object?
0
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
ID: 39675026
Use a textbox instead of a label.  The DLookup syntax goes in the Control Source property, including the = sign like this:



 = DLookup("HeaderInfo", "YourTable")
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39675031
Any of these ideas would use textboxes, I think.  The textbox control source gives you more flexibility than the caption property of a label.
0
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 39675041
Thanks!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Syntax Error in Query 7 31
ms access 2013, running .mdb 2 31
Set focus on next field when character count = 5 9 12
MS Access VBA Print PDF just Current Page 9 12
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now