Macro: Calling value from VBA Variable

[rreddy]

Hi,

I'm trying to put together a database with a user login and audit trail. I am able to get the used login bit to work with a table and passwords and have the user's name stored in a global variable.

I have kind of figured out how to get the audit trail to work with data macro's but am struggling to figure out how to get the user name pulled from the global variable into the macro set field value box properly.

Note I'm not super experienced with coding, but have a little knowledge.

Thanks in advance for any advice you may be able to offer.

 

[pbaldy]

To my knowledge the only way to get the value of a VBA variable outside VBA is to have a public function that returns it.

 

[rreddy]

Thanks pbaldy, how would you code that function?

 

[pbaldy]

One line:

FunctionName = VariableName

 

[rreddy]

Ok, so I tried doing that and the problem I came across was that the below error.

"The function 'MsgUser' is not valid for expressions used in data macros."

I tried doing a macro in the form after update field but that won't let me create new records.

Would I be better off figuring how to do this at form level using VBA. Can someone advise what the code would look like for creating a new record and adding values to the fields.

Thanks

 

[pbaldy]

I'm not a macro user, so not sure I can help with that. You might look at TempVar's, which may be accessible from the data macro. You can certainly do audit trails in code; I've got several db's with audit trails running. Here's one method:

http://allenbrowne.com/AppAudit.html