Public Variables

[ListO]

I'm having trouble using public variables.

I have declared several public variables to be used throughout my database, using:
Public pChar1 as String, pChar2 as String

I have tried declaring them in the general declarations area of class modules, also in the general declarations area of a regular module (whatever that's called), and have the same problem. I have also checked that "Option Private Module" is not active.

Trouble is that queries do not seem to recognize these variables when I try to use them as criteria, such as

Criteria: <> pChar1

Is there some secret to using them in queries, or using them at all?

Hoping for relief...

 

[Pat Hartman]

This is not an error. SQL is not a programming language (although it does support the use of functions) and therefore would not recognized any VBA constructs. You can get around this limitation by turning the variable references into functions:

1. Open a new code module and define a global variable to hold the parameter (I like to keep any globals in a module all by themselves so I don't have to go looking for them).

Public gVariableX As Integer

2. Create a public function to return that variable. You will need a separate function for each variable unless you want to get fancy and pass a parameter to specify which variable you want this time. If you go the fancy route, I would make a function for each data type so you don't return varients.

Public Function ReturnVariableX() AS Integer
ReturnVariableX = gVariableX
End Function

3. Reference the function in the query.

Select YourFields, ReturnVariablX() as VariableValX
From YourTable
Where YourField = ReturnVariableX();

 

[ListO]

Thank you, thank you, thank you.

-Curt

 

[groupy]

Hi Pat or others,

I really want to go that fancy way. Because this will make my code must smaller and in my case I can use a loop in stead of using the same code 10 times (setting 10 public variables)

But I have no idea how I can refer to a public variable on a variable way.

I fancy functions like this

Module basTest
-----------------------------

Public test1 As STring
Public test2 As String
-----------------------------

Public Function getVariable(varName As STring)

getVariable = basTest("test1")

End function

OR

Public Function setVariable(varName As STring, newData As String)

basTest("test1") = newData

End function

So the only question I have is, how can I refer to public variable on a variable way?

Thanks a lot

 

[Mile-O]

Groupy,

You'll need to create an array.

Dim strArrays(1 To 10)

You put the values in to the separate values:

strArrays(1) = 100
strArrays(2) = 56

To loop through (e.g ...)

Dim intCounter As Integer
For intCounter = 1 To 10
    MsgBox strArrays(intCounter)
Next intCounter

 

[groupy]

Thanks for your reply.

But it is not exactly what I looking for, because it still not possible to make the following methode.

Public Function getVariable(varName As STring)

getVariable = basTest("test1")

End function


I want to pass a varName As String and not a number.

Is this possible?

 

[Mile-O]

I want to pass a varName As String and not a number.

Is this possible?

No. I don't believe so.

Why don't you just declare it as Public?

 

[groupy]

Hi,

I'm sorry but maybe I not clear enough.

I have some code like this:

Module basTest
---------------------------
Public test1 As STring
Public test2 As String
etc until 15

Function getTestV(varName As String) As String

'This part is not working yet
If(basTest(varName) = "") Then
getTestV = basTest(varName)
Else
'This function is not important so is not written down
call setTestV(varName)
End if

End function

As you can see with this function I can manage all the 15 variables. If this is not working than I need to make 15 exact the same functions for all the 15 variables.

Hopefully you can help! I also understand that I can use a table instead of public variables, but I don't prefer this.

 

[Mile-O]

I know exactly what you mean and I'm telling you that I don't believe it is possible.

Now rather than defining 15 individual variables with the same name; I'm saying you dimension that variable with 15 instances which you can loop through.

 

[Mile-O]

Public test(1 To 15) As String

Public Function getTestV(intName As Integer) As String

    If test(intName) = "" Then
        ' etc..
    End If

End function

And all you need to do is pass the number of the relevant instance.

 

[groupy]

OK,

Thanks for your replies. I will use the suggestion you came with, in fact I already using it. But it is a stupid that my suggested solotion is not possible. Maybe in a newer version of Access (Access 2006 ????).

Best regards