Declaring a Variable

[jpiscit1]

Hello everyone.

I am trying to find a way that I can set a value to a variable and reference later.

More specifically, I am developing a database that is linked to SQL tables. One table records problems from three different operations.

Here's what i would like to see:

My Welcome form (first to open when Access is opened) pops up. Within that form there would be three selections (to cover the three operations). For example PM1, PM3, and PM4.

If the user selects PM1 (for example) the next form would have some code that says IF somevar = 'PM1" then only show records where operation field = 'PM1'.

Now, i think you set the variable as:
Public Operation As String
in a module.

My problem is setting the variable to = PM1, PM3 or PM4 depending on the selection in the actual Welcome form so that i can reference it later.

Can anyone show me (1) how I set a value to the variable and (2) how I would reference it later.

Thanks.

john


Your limits are only as far as you set your boundries......

 

[jpiscit1]

Under modules:
Created a new Module
Public Machine As String

In opening form:
I created an option group
Added the following

Private Sub Frame35_Click()

Select Case Me.Frame35

Case Is = 1
Machine = "pm1"
Me.Text24.SetFocus
Me.Text24.Value = Machine
Case Is = 2
Machine = "pm3"
Me.Text24.SetFocus
Me.Text24.Value = Machine
End Select

End Sub

Still working on how to resolve the look up option when referencing a form.. will post when I get the answer.

John

Your limits are only as far as you set your boundries......

 

[OnTheFly]

First, you are correct you would define a global variable (one that can be used throughout the application) in a Module using Public

Not knowing what type of control you are using on the Welcome form for you user selection, I can't be specific to that form but here is an example of what can be done.

My global variable is defined as follows in a Module
Public strOperation as String

Let's say the Welcome form has an option group with three option buttons. Opt1=1, Opt2=2 and Opt3=3. The Group control is named grpChoice. In the OnClick event of the Option group (grpChoice) I would do the following:

Select Case Me.grpChoice
Case 1
strOperation="PM1"
Case 2
strOperation="PM2"
Case 3
strOperation="PM3"
End Select

This will set your variable. Now wherever you referance it again it will contain this value until it changes. For Example: If strOperation="PM1" then ...



Hope this helps.

OnTheFly

 

[OnTheFly]

If you are trying to open a form to show only the values of Machine=PM1 you can do something like:

DoCmd.OpenForm "yourformname",acNormal,,"fieldname='" & Machine & "'"

Hope this helps.

OnTheFly

 

[PHV]

only show records where operation field = 'PM1'
Create a public function like this:
Public Function getMachine()
getMachine = Machine
End Function
Now, in any query, you can set a criteria to =getMachine()

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jpiscit1]

Thanks for your responses.

I'm ok with defining the variable. I ended up calling it "operation".

Lets say operation = PM1 based on the users selection. Now, I want to open a form that is already created for data entry, showing only the records whose field "operation" contains, in this case, PM1.

The record source for the form uses a Select statement that looks like this:

SELECT *
FROM [NCR Main Table]

I assume I would need to add something like...

WHERE operation = ????

What would I put in Place of the question marks that would point to my Public variable?



Your limits are only as far as you set your boundries......

 

[PHV]

Have you read my post ?
Replace any occurrence of Machine to operation, as you changed your mind mean while.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[OnTheFly]

DoCmd.OpenForm "yourformname",acNormal,,"Operation='" & Operation & "'"

This code will open the form and filter it for the value of Operation (the field) equal to the value of Operation the variable.


If you are going to do more coding within Access I might suggest that you try to avoid using the exact same names for two different things. It gets very confusing. Try something like strOperation to name your variable. It is a pretty common standard in programming and it also helps identify that the variable is a String (hence str prefix).

Hope this helps.

OnTheFly

 

[jpiscit1]

My Apoligize PHV, I did not read your post as closely as I should have. I was concentrating on OnTheFly's solution.

I managed to get both solutions to work but ended up using OnTheFly's method. That worked quite well. Also thank you for the advise on naming conventions. That is something I need to improve on.

Thanks again to both of you. You were a big help.

John

Your limits are only as far as you set your boundries......