simple question, i'm a vba newbe

[tw]

Where would I put code for a constant that I want available throughout the
entire access program, not just a specific module or form?

 

[Graham R Seach]

You can put the constant in any standard module and Access will find it.
Because Access loads the entire module into memory when anything in it is
referenced, standard practice is (if you have many such constants, or global
variables) to put them all into their own module.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[tw]

by any "standard module" what do you mean. I put a constant at the
beginning of a module and access wouldn't compile because I have option
explicit at the top of all my modules and form modules and it could not see
it. I took it out because it didn't work. Is a standard module the same as
a module in the "modules" section of the database window? If so, that is
where I put it, I didn't put it by itself though, I put it in the module
that has the functions called first from the "splash" form like this
=================
option compare database
option explicit
const someconst as sometype = somevalue
public somevar as sometype
===================

Neither one of these worked when referenced anywhere else in my code, I had
to change everything to fill a private var with the data that should have
been available once and always, I have to get every time I need it.

 

[Graham R Seach]

A "standard" module is a module that is not a class module. Class modules
also appear in the Modules section of the Database Window, which is why I
was specific.

Constants don't have declarative datatypes, therefore, you can't say:
Public Const MYCONSTANT As Integer

Also, you cannot assign the value of a constant to a variable, like this:
Public Const MYCONSTANT = intMyvariable

Also, you cannot declare a constant as Public in a class module (which
includes form modules).

Instead, you explicitly set its value, and declare it without any datatype,
like so:
Public Const MYCONSTANT = 3
...or...
Public Const MYCONSTANT = "abc"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Dirk Goldgar]

Constants don't have declarative datatypes, therefore, you can't say:
Public Const MYCONSTANT As Integer

Graham, are you saying you can't say

Public Const MYCONSTANT As Integer = 33

? Because you can. Or are you just talking about a constant
declaration that doesn't contain a value for the constant?

 

[Graham R Seach]

Dirk,

That's what I was saying, but after your post, I went and checked it out,
and it seems you can. I made an assumption (never having done it before),
which was wrong. Oops!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[tw]

I didn't do any of those things that you suggested, the public statement is
for a variable not a contant.
Remember, I'm a newbe. How do I tell the differece between a class module
and a standard module?

I put the code in a module that I have my initialization functions in. Is
there anything wrong with the statements that I wrote? My questions are
still unanswered.

 

[Albert D.Kallal]

You can use the following, and simply place it in a standard module.

Option Compare Database
Option Explicit

Public Const OneDozen As Integer = 12


A standard module is a module that you create in the "modules" tab in
ms-access. There is also what is called class modules, and you can make
those by going:

insert->Class module.

however, you do NOT want to use a class module.

In addition, there is also a module associated with EACH form that you make.
Again, you do NOT want to use the forms module, as they go out of scope, and
thus again the constant would not be available.

So, simply create a standard module (or user one of your existing), and put
the constant in that module.

Then, all code can use this constant.

Do note, that forms, and reports controls and sql expressions will NOT be
able to use this constant, but any code you write will be able to...

You can trick, or make expressions in sql, or controls also see the
constant, and you do this by placing a PUBLIC function in a standard module.


Public Function MyDozen() as integer

MyDozen = 12

End function

So, public functions placed in a standard module can be used in expressions,
and also in controls (and, in addition of course be used everywhere in code
like constants can also).

if I defined the above function, then I can put a control text box on a
form, and go;

=([qty] * MyDozen())

The above will work, where as I can't use the constant I define in the above
expression:

= ([qty] * OneDozen)

So, constants, and variables for that matter can't be used in controls
expressions, but functions can. However, both functions, and constants can
be used in VBA code expressions everywhere...

 

[tw]

thanks, I'll try again.

If I put
public const OneDozen as Integer = 12 in a standard module
(which is where I had it orig when it didn't work, but I think I was
trying to access it on a form control)
then I put in the form's code on open or on load

me.controlname.value = OneDozen

will that work?

 

[Douglas J. Steele]

thanks, I'll try again.

If I put
public const OneDozen as Integer = 12 in a standard module
(which is where I had it orig when it didn't work, but I think I was
trying to access it on a form control)
then I put in the form's code on open or on load

me.controlname.value = OneDozen

will that work?

It should.