TempVars unusable in field default value

[markmarko]

Hello,

I'm trying to use a temporary variable to keep track of which CSR is
inputting data.

I have a macro which prompts user for ID code, which is stored in the temp
variable TempUser.

On a form control default value property, I can use the expression
[TempVars]![TempUser], which will populate that user's ID code into the
control.

However, I cannot use that same expression in the tables field default value
property. If I try, when I save the changes to the table, I get the error
message "Could not find the field 'TempVars]![TempUser'. "

Any ideas why I can't use that in the table field default property?

Thanks!

 

[George Nicholson]

At the table level, Field Default values are confined to expressions that
can be resolved 100% of the time.
That form might not be open, or even exist, therefore it is not allowed.

 

[markmarko]

Wow, ok thanks!

Out of curiosity, how do you know that?

 

[Dale Fye]

A workaround to this situation is to create a function in a code module that
is accessable to the entire application (I usually create one just for these
types of variables). Once created, this function can be accessed from
anywhere in Access (forms, reports, queries). Set this function up so that
you can pass it an Optional value. If you pass it something, it sets the
functions value and returns that value, if you don't pass it the value, it
returns the most recent value passed to it.

Public Function fnTempUser(Optional TempUser As String = "") As String

Static myTempUser As String

If Len(TempUser) > 0 Then myTempUser = TempUser
fnTempUser = myTempUser

End Function

HTH
Dale
Now you can call fnTempUser anytime you want. To pass it a value just use:

Call fnTempUser("MarkMarko")

 

[George Nicholson]

lol, probably from asking the same question years ago on this forum

When you think about it, it makes sense. The table-level Default value (and
Validation rules, etc.) has to be something that can evaluate if you try to
add a record to the table directly, even if working in the backend, so it
has to be independent of any user-created objects/code.

 

[George Nicholson]

Once created, this function can be accessed from anywhere in Access

Not quite anywhere. You can't use a user-defined function for the Default
value of a field at Table level, which was the OP's question. You can use
it at Form level, but not Table. It just isn't allowed.

 

[markmarko]

Well, I suppose that does make sense. Actually, not really. I'm brand new to
Access & database building, and I've been thrown in the deep end to build
this one for our company. I guess I don't see why it would be able to supply
that default expression, and if TempUser has a value, use it, if not don't.

Frankly, I'm glad of your answer, cause I don't feel inclined to learn that
VBA code Dale mentioned! I've got some experience writing VB code for excel,
but not access, and learning the nuances is insanity inducing!

Thanks, both of you!

 

[George Nicholson]

I use variations of Dale's suggestion *a lot* in my apps. I use to pass
"live" values to queries, code, forms, reports rather than refering to
control values and then worrying about whether the form with that control is
open. A lot of that is simply personal preference though.

But it can't be used to set the DefaultValue of a field at the Table level.
Believe me, I've tried

 

[Allen Browne]

Only a very limited subset of the functionality in Access can be used in the
properties in table design (such as Default Value or Validation Rule.)

It really has to be engine-level stuff to work there. Without testing, I
would be really surprised if TempVars did work in that context.

 

[Pat Hartman]

Keep in mind also that other applications can access tables in your Access
application. Jet could not manage enforcing engine level rules if they
depended upon code outside of the running application. Remember that
NOTHING happens on the server with an Access database. Everything happens
locally on the PC running the code that is accessing the database.

An alternative to global variables that I prefer is a hidden form. I open
the form when the application opens. This hidden form hides itself and
opens the main switchboard. This gives me several advantages. Among them:
1. For debugging, I can unhide the form so I have easy access to the
"variable" values and can even change them manually.
2. Values are not lost if my application encounters an error which can
happen with global variables.
3. The hidden form will be the last object closed when the user closes the
database. This gives me a place to put any clean up code I might need.

 

[Dale Fye]

Got me.

Not quite anywhere. You can't use a user-defined function for the Default
value of a field at Table level, which was the OP's question. You can use
it at Form level, but not Table. It just isn't allowed.