Public Variable into DataEntry form for Table

  • Thread starter Thread starter mma40
  • Start date Start date
M

mma40

I have tried everything that I could find on these threads but it is
not working. I have a public variable myRecord that is a number that I
want to be entered into the UserNumber field when opened. I also have
the default value set to myRecord. I put a Msgbox in the on open of the
form to display my variable and it shows correctly but I get #Name??
error in the box when the form is opened. Can anyone help?
 
You cannot assign a varialbe as the default value for a control. There is a
way, however you can do this. One way I know works, because I use it, is to
create a Static Function. You can use a user defined function as the Default
Value of a control. So, here is an example of a static function:

Static Function GetMyNumber(Optional varTheNumber As Variant) As Variant

Dim varSaveNumber As Variant

If Not IsMissing(varTheNumber) Then
varSaveNumber = varTheNumber
End If
GetMyNumber = varSaveNumber

End Function

How it works: If nothing is passed to the function, it returns the current
value of the variable. If you pass it a number, then it stores it in the
varialbe and returns it.

This needs to be in a Standard Module so it will retain its value as long as
the app is running.

Where you currently assign the value of your public variable, call this
function with the value you would put in the public variable as it's argument.

GetMyNumber(usernumber)

It will assign the value of the usernumber

Make GetMyNumber() the Default Value of your control.

In fact, I never use Public varialbes. I always use this technique instead.
 
I must be doing something wrong. Here is what I built:

Option Compare Database
Public mRecordID As Long

Static Function GetMyNumber(Optional mRecordID As Variant) As Variant

Dim varSaveNumber As Variant


If Not IsMissing(mRecordID) Then
varSaveNumber = mRecordID
End If
GetMyNumber = varSaveNumber


End Function

and included GetMyNumber() as the default value
 
If that is all you did, you did not do enough. Did you change the code where
you assign the value to the variable to assign it to the function as I
demonstrated?
Also, what did you name your module. It cannot be the same name as the name
of the function or Access will get confused.

Also, you posted what you did, but not the results. What happened when you
tried to use it?
 
Sorry, I am new to this and don't understand. I assigned the variable
as public because I get the number from the Main form before I close it
and open this add form. I don't know about Static Functions and left
the Public mRecordID as Long or it does not store my variable. I may
be approaching it altogether wrong. Also my module is called GetID and
the results I got was the same #Name? in the field. Thank you for your
help.
 
A normal function's variables only maintain their values as long as the
function itself is running. A Static function's variables maintain their
value as long as the module they are in is being used. So if you put a
static function in a form's module, then it's variables only maintain their
value while the form is open. When the form is closed, their values are
lost. If you open the form a second time while your app is running, the
static function's variables will revert to their default value, depending on
their data types.

When you put a static function in a standard module, it's variables maintain
their value as long as the application is running. They work much like a
Global variable. The difference is, static functions can manipulate their
own varialbes.

If you want to put your user's ID into the table your form is based on, the
form has to have visibility to the ID. Normally, a Global variable could be
used. The reason you can't use it the way you want to do it is that a
variable, regardless of scope, can not be the Default Value of a control, but
a user defined function can.

In your case, The ID is determined outside the form and the form does not
know what that ID is. Based on your original post, it is evident you, at
some point in your app, are assigning a value to the variable. Instead, you
can assign the value to the static variable instead:

So if it is something like:

UserID = SomeValue

You can, instead do it like this:

GetMyNumber(SomeValue)

The way the function is written, it will now store whatever you passed it in
its internal variable varSaveNumber. It does not matter that the variables
are typed as variant. An optional variable as an argument to a function must
be variant. I write these functions with variant as internal variables so
they can be used with any data type. If you want the return value to be a
specific type, you can use a type casting function to do that. For example,
if you want it to be Long, you could retreive the value like this:

lngSomeThing = CLng(GetMyNumber())

It is not absolutely necessary as Access will convert types for you when and
where it can.

So now we have the ID saved in the function by assigning it a value. Now we
want to use that value as the Default Value for a control on our form. All
we need to do is put the function name in the Default Vaule property of the
control. In design view of the form, select the control, select Properties
from the tool bar or by right clicking and selecting Properties. In the text
box labeled Default Value, type in:
GetMyNumber()

So when you open the form and create a new record, you will see the user's ID.

Please post back if you need any clarification.
If your module name is GetId, then it cannot have a function or sub named
GetId. That will confuse Access. What you need to do is paste the code I
posted into your module.
 
Back
Top