Solved

Declare global variable and assign value globally (for all functions on a form)

Posted on 2013-10-29
9
1,419 Views
Last Modified: 2013-10-30
I have a form that sends emails when different fields are triggered. I want to assign an address list to a string by using global variables (so I can edit the list @ one location).

I tried adding this just below the "options compare database" section but it is not recognizing the address list when the email is triggered. I know the email list is formatted properly because I added it directly to the .to  section and it worked perfectly. How do I declare this list globally so i can use the EmailsAddressList string anywhere in the code?

Public EmailsAddressList As String
EmailsAddressList = "test@test.com; test1@test.com"

Thanks in advance!
Dan
0
Comment
Question by:filtrationproducts
  • 4
  • 3
  • 2
9 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Several ways to do this.

I prefer to use TempVars.  I believe these were new with 2007.  You can add or change TempVars at any time in your application using code similar to:

tempvars.Add "EmailTo", "test@test.com;test1@test.com"

And can then recall those values using:

tempvars("EmailTo")

---------------------
Another method I used to use (before TempVars), was to create a function:

Public Function fnEmailTo(Optional EmailTo as variant = Null) as String

    Static myEmailTo as variant

    if isnull(EmailTo) = False then myEmailTo = EmailTo

    fnEmailTo = NZ(myEmailTo, "")

End Function

This method allowed me to set the value of the function with code like:

fnEmailTo "test@test.com;test1@test.com"

and to recall the value with

fnEmailTo()

When you pass the value to the function, it stores that value in a static variable.  When you call the function without a value, it returns the value stored in the static variable.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
It isn't easy to edit a global (or any) variable.  It might work better to save the emails list to a field in a table, where you could easily edit them as needed.  I use a little one-row table called tblInfo for such data.
0
 
LVL 1

Author Comment

by:filtrationproducts
Comment Utility
Fyed,

I have Access 2007 but this file is an .mdb. Will tempvar's work with this file?

The reason I ask is I added the following code below the "Option Compare Database" section but I am receiving an error that says "Invalid Outside Procedure".

TempVars.Add "EmailTo", "test@test.com;test1@test.com"

Open in new window


Thanks,
Dan
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
If you are running A2007, then TempVars are available.

You do not declare a tempvar, like you would a global variable.

The TempVars collection already exists.  All you have to do is set that value somewhere, I would do it in a form or standard code module.  If this is going to be global, then I would generally put that line of code in the Open even of my startup form.

But you could also have an options form, or something like that, that uses a table (like Helen recommended) to allow you to edit this value.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Another neat thing about tempvars, is that you can set the value in the immediate window using the same syntax, which allows you to easily test forms, queries, reports, and modules that use the value.
0
 
LVL 1

Author Comment

by:filtrationproducts
Comment Utility
I like the idea of using a table to add the contacts. If I put the email addresses in a table, how would I call that table data and format it to send to those using the Outlook.Application?

Currently I just dim a string and call it using

.To = whatevermystringis 

Open in new window

0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 150 total points
Comment Utility
With tempvars, you would use your splash (or menu) form to set the value:

Private Sub Form_Load

    TempVars.Add "EmailTo", "test@test.com; test1@test.com"

End Sub

Then in your email code you would just use:

.To = TempVars("EmailTo")

To store that in a table, you would either need to have a form, linked to a table (Helen indicates she uses tblInfo), and have a textbox for editing those values.  You would obviously need a way to open that form, but to use those values, you could do something like:

.To = DLOOKUP("EmailTo", "tblInfo")
0
 
LVL 1

Author Closing Comment

by:filtrationproducts
Comment Utility
Using the tempvars function worked to solve my problem as described in my original question.

Thanks for your help!
Dan
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
If the data is stored in a table, you can retrieve it using a function like the following:

Function GetTemplatePath() As String
'Created by Helen Feddema 17-Nov-2005
'Modified by Helen Feddema 24-Jul-2006

On Error GoTo ErrorHandler

   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("tblInfo", dbOpenDynaset)
   rst.MoveFirst
   GetTemplatePath = Nz(rst![TemplatePath]) & "\"
   rst.Close
   
ErrorHandlerExit:
   Set rst = Nothing
   Set dbs = Nothing
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Open in new window

0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

728 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

9 Experts available now in Live!

Get 1:1 Help Now