Option Group Variables (from Form to Module)

emanuele.musa · July 20th, 2007, 06:08 AM

Hello all, I am a new user of VBA for both Excel and Access.

*BACKGROUND*
I have created for the Hedgefund in which I am working, the database to store all the useful information relative to the company business, and the reports which show test results and ratios.
THe database now works with the information of ONE Portfolio investment.
Now I need to shape the database in a way that it is possible to manage more than one "portfolios investment", storing all the data in the same database and leaving the user the opportunity to select at the begnning the Portfolio to be analysed.

*SOLUTION*
The main idea to solve this problem was the creation of an extra coloumn in every Table in which are stored the Portfolio's data.
This coloumn will contain the "number of the portfolio" from which the data has been uploaded (eg. "1" = Portfolio #1 etc...)
Everytime the user chooses the portfolio to be analysed by clicking on the right "option group cell", a variable called "P" gets the value that will be compared with the one of the "Portfolio's number coloumn" during each test...and the database will keep calculating the tests for the particular portfolio until the user will change again the value "P" from the Option Group.

*THE PROBLEM*
I positioned the option group on the FIRST Form of my database.
If a person does not touch the option group, The Portfolio #1 will be automatically selected, with the value of the variable P = "1".
The problem arises as I am not able to create "P" as a Global Variable.
If P is global: despite its value is saved in the VBA code of this FIRST FORM, it would be possible to read the P value also in all the other forms and Modules ( because in the modules I created the algorhitms to calculate the tests, and without the right value of P the database would show me the info about the wrong Portfolio).

*HELP*
Does someone knows how to declare P as Global Variable, or how to let the functions which run the test, take in input the P variable from the current value of the FRONT FORM?
Thankyou in advance!

Private Sub Frame19_AfterUpdate()
P = Me.Frame19.Value
End Sub

BrianWren · July 20th, 2007, 03:58 PM

If you put into any module (except a Class module)

Code:

Public P As Integer  ' (or whatever type fits your need)

then all code in your project (including objects) will have access to it to read it or change it.

You could add to your 1st form

Code:

Private Sub Form_Activate()
    P = Me.Frame19.Value
End Sub

This might go better in _Load(). The idea is that it will run one time to initialize your variable.