Define Global/Public variables from a Table? (1 Viewer)

[Alastair]

New to these forums so I hope I am posting this to the right area?

I am in the process of learning Access again after an absence of around 9 years off and on.

My new project is to adapt a DB I designed in Alpha Five to work in Access I have version 2013 but at work we are still using 2000!

I would like to declare Global or Public variables from a table so they can be added to or edited easily. I had the following function to do this with Alpha Five but at the moment my lack of knowledge of Access VBA is making this task difficult can anyone point me in the right direction or advise a better approach?

Here is the function that I use in Alpha:

FUNCTION udVars AS A (udTabName AS C )

dim codeStr as c

t=table.open_session(udTabName,file_ro_shared)
t.fetch_first()
while .not. t.fetch_eof()
'add all lines to a single crlf() delimited string
codeStr = codeStr + "dim global "+trim(t.My_var)+" as "+alltrim(t.My_type)+crlf()
codeStr = codeStr + alltrim(t.My_var) + " = convert_type(\""+alltrim(t.My_value)+"\",\""+alltrim(t.My_type)+"\")"+crlf()
t.fetch_next()
end while

t.close()

if codeStr <> ""

codestr = strtran(codeStr,chr(34)+chr(34),chr(34)) ' replace double quotes chr(34)+chr(34) with a single quote chr(34)

evaluate_template(codeStr)
end if

END FUNCTION

It opens a table reads in the records and then makes them into a string the string would look something like this:

"Public EuroRate as Single = 0.885"

I would then need to use this string to declare the variables but not sure what command to use - I was looking at the Eval function last night but couldn't get this to work...

The table would hold strings for all the above so you would have:

My_Var (variable name)
My_Typ (type of variable e.g. Single)
My_Val (value e.g. 0.885)

The table would hold as many variables as required, some would be dates, some paths for making directories and some would be numbers.

Anyone got any idea if this would work in Access or would an alternative way of doing this be better?

 

[TJPoorman]

Not sure if this is what you're looking to do, but perhaps you could adapt it to your needs:

http://www.cpearson.com/excel/vbe.aspx

 

[spikepl]

Use tempvars - you can set them up from VBA as needed. Google access tempvars. Your suggested method is a very roundabout and complex and I suspect not at all viable way to get to more or less the same result.

BTW: What you were attempting to do is to define constants at runtime - I doubt Access allows that. The only instance I can think of is perhaps when using compiler directives.

 

[MarkK]

Yeah, I agree with spike, don't write code that writes code.

What I do is write a settings class with public properties. This is a bit laborious but since a custom class's properties are available in intellisense it's a nice feature if you go back to work on a system you deployed long enough ago that you forgot everything about it.

A settings class might have features like . . .

private m_euroRate as single

property get EuroRate() as single
  EuroRate = m_euroRate
end property

private sub Class_Initialize()
  with currentdb.openrecordset("Settings")
    m_euroRate = .fields("EuroRate")
    .close
  end with
end sub

. . . and an instance of that class will show EuroRate in the intellisense list.
hth

 

[Alastair]

Thanks for the responses, I wasn't sure that the way I did it in Alpha Five would work with Access.

TJPoorman - Thanks for the link but I think that is too advanced for me at the moment!!!

spikepl - I suspected after a few attempts to reproduce my Alpha Five code the other night that this may not work with Access. But it worked very well in A5 and allowed ease of access to the variables when I wanted to change them. Which we sometimes need to do. See attached graphic of A5 Global Variables screen. I will have to read up about tempVars I did come across these the other night but ran out of time!

lagbolt - I have never used classes in Access can you explain a bit more how your code/class would work? I am not even sure how to start entering a class into VBA?

Could anyone recommend some reference material or a book to help me learn?

 

[Pat Hartman]

Since the variables are held in a table, why not use a bound form to access them. You can open the form hidden from the start up form and reference the controls whenever you need a variable. The nice thing about this method is that it works for ALL versions of Access. It also has the advantage of allowing you to keep the form visible while you're testing so you can easily see/change values.

 

[Alastair]

Hi Pat,

Thanks for your reply, sorry for my late response I have just got back from a weeks holiday.

Can you explain a bit more how a bound form would do this? I haven't heard of this before.