Access 2010: Data macro to log current user via TempVars

B

barnacleboy

New member
Local time
Today, 16:17
Joined
Jul 28, 2010
Messages
2
Hi there!

I have some troubles using TempVars here. I want to log the current user when updating a record by using a data macro. The macro already works fine when logging the change date, but I can't get it working to log the user name. For logging the date I'm using the German "FestlegenFeld" function - I guess it's called "SetField" in the English version - and use "Now()" as parameter for the value. For logging the user name I wanted to use the same function and set the value parameter to the TempVar name which saves the logged-in user's ID: I assume the syntax for getting the previously saved user ID is "[TempVars]![tmpUserID]". When running the data macro on update, the error message "ID [TempVars]![tmpUserID] can't be found." is getting displayed, user logging isn't working, but the change date gets correctly saved.

Using the same TempVar access syntax for "tmpUserID" works fine when using it in a messagebox function of another macro - which is not a data macro. Are TempVars probably not available in data macros? (SetTempVar isn't available too btw.)

Thanks for answering my question!

Cheers
P
 
Hi,

You're correct that you cannot reference/use TempVars in the data macro context. You can add a parameter to the named data macro and then pass in the value of the TempVar to the named data macro when you call the RunDataMacro UI macro action. You can then use that parameter value (which contains the TempVar value now) within the context of the data macro execution.

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
SDET II - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------
 
Thanks for your comment. I somehow assumed that, but wasn't sure if TempVars are not supported in data macros, because the error message isn't really helpful in this case and way too generic.

To solve the problem I have to add a RunDataMacro macro to every single input field of a table? That's not very convenient when doing this on 20-30 forms with at least one data table... :rolleyes:

Edit: Just noticed, that I can't add "OnRefresh" (or maybe "OnUpdate") events to controls when using SharePoint Access Services - so user ID logging seems impossible... :(
 
Last edited:
Hi,

If you're using unbound forms, you'll have to use named data macros to "push" new records into tables. If you're using bound forms, you can just assign the temporary variable to a control on the form that is bound to one of your fields. You won't have to create a named data macro then.

A few controls on web forms support AfterUpdate events like text boxes, combo boxes, list boxes, and check boxes. You can also test whether a form is dirty by checking the form's Dirty property using UI macros.

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
SDET II - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------
 
I am new to variables. I do not know if that is the solution. It may be Tempvars. Here is my dilemna. I am using Access 2010 web forms. Ther is very little help on the web at this time for Access 2010. You guys are the goto at this time.

I have a form with multiple records, frmStudentList. I created this with pre-existing data. Each record has a field named [StudentID] I have a button that will open another form, frmContacts, with that student’s contacts. I use “[StudentID]=[Forms]![frmStudentList]![StudentID]” in a macro to get the students records.

I am trying to figure a way to get the [StudentID] field into a blank record in order to add another contact. That form is called frmContactAdd. Can you solve this for me?
 
Last edited:
Hi,

I have a FAQ page on my site that has lots of information concerning Access 2010. It won't answer all questions of course, but it should prove useful in your study of Access 2010. You can find my page here:
http://www.AccessJunkie.com/faq2010.aspx

Back to your question.
Temporary variables should help solve your issue. In the macro logic for the button that opens frmContactAdd, set a temporary variable that equals the StudentID. Then in the frmContactAdd, assign the Default Value to a control on that form to the temporary variable you just created. Something like so:
=[TempVars]![tmpStudentID]

Most of the Access 2010 web templates do something similar. You might want to open the Charitable Contributions web template and study the form called DonationDetails. If you look at the Default Value property for the cboCampignID combo box control, you'll see that it uses a temporary variable to initially display a campaign previously viewed on the calling form.

Hope that helps,

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
SDET II - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------
 
It did not work. I am using "Is Null" as a criteria in the query. That is a required field. This means that no records will display. I believe that is why it won't take a variable, or any number. Is that true?
 
Last edited:
Could you provide a little more clarification on "it did not work"?

What exactly happened? Did you get an error message? If so, what was the message? If you used the temporary variable as the default value of the control, what do you see? Your ID number, #Ref!, #Error?, something else? Does it work in client, but not in the browser? Does it not work in client either?
Etc.

Is it possible for you post a small sample of the forms demonstrating what you're trying to do? Just the elements necessary (tables, queries forms) with dummy data would be great.

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
SDET II - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------
 
I am sorry about not getting back to you. Emergency response. Thank all of you for your help. One of the posts I read led me to use a form with a subform. I used the Master Links setting and used [StudentID] for both forms. That made it work.

Thank you.
 
Great, I'm glad you have it working now.
Thanks for posting back; good luck with your project.

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
SDET II - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------
 
Hi AccessJunkie

I am having a simillar problem, but without forms

If I open a new thread, can you assist?

I am asking because i need to know that you are still on this forum!
 
Just to record for posterity:

If you're doing this on a web database that's published, note that logging the user is likely to be redundant because all tables that's moved to SharePoint will have several metadata columns added so you could look at SharePointAuthor to see who created the record, SharePointEditor to see who last edited the record as well the corresponding timestamp.

In case you do need a column you can control (those columns are not modifiable at all), you alternatively can just use CurrentWebUser(x) with x being a number between 0 to 3 which will supply you with the currently logged in user's email, account name, display name and one more thing that escapes my memory -- there's a enumeration in VBA object named acWebUserDisplay or something like that. Anyway, CurrentWebUser() typically should be callable in the data macros or made a part of the query itself.


In case of client databases where we're not using any web tables, the function won't work and will return nulls until it's published. However, if you never publish, and plan to always use Access client, an alternative is to have Data macro do a LookUpRecord action upon a query that does this: SELECT WhoAmI(); where WhoAmI() is a VBA function that does the needed gathering to supply currently logged in user. Again, this is a client-only solution and will fail in a web database.

So there's two ways to handle the currently logged in users.

Hope that helps.
 
an alternative is to have Data macro do a LookUpRecord action upon a query that does this: SELECT WhoAmI();

Microsoft returns an error!

I am only using a client side database

Once I have the var data, how do I use it in the Data macro?

I have taken the liberty of attaching a copy of me database.
I have a table called tblTasks. Whenever a record is changed, the change is logged in tblAuditTrail

This works well!

I want to log the Id of the user making the change. To get the Id I have a function called WhoAmI - it is a simplified version of what I normally use, but is good enough for this learning process!

I am struggling to get the user Id `into the AuditTable!
 

Attachments

Here's the sample back.

I had to add a table because it does not like me calling a query that does not have a FROM clause but it works now. I also had to modify the WhoAmI function because you were setting a public variable so the WhoAmI was returning nothing.
 

Attachments

Banana said:
Here's the sample back.

I had to add a table because it does not like me calling a query that does not have a FROM clause but it works now. I also had to modify the WhoAmI function because you were setting a public variable so the WhoAmI was returning nothing.
Click to expand...

Many thanks. Just what I wanted.
I see that you added a query. What table did you add?
 
Actually, I apologize for not being clear - when I said "add a table", I meant to the query that I created. It does not like this form:

Code: 
SELECT WhoAmI();

So I had to change it to:

Code: 
SELECT TOP 1 WhoAmI()
FROM tblTasks;

which is basically identical but acceptable. Access occasionally does not like a query that does not have a FROM clause, and that was one of those cases.
 
Hello all,

I know this thread is pretty old but I am having difficulty working this exact same problem.

My tables are split into a back end and I can't reference the query in the lookup action. My first thought was to create an update query that could pull the tempvar then push it to a table but I can't get it to work.

So to sum it up, how can you do this if your tables are split from the front end where you can't reference the queries in the table macros?
 
Solved my own problem, merge the back end to the front end. Then split after the lookup record has been added.
 
Just kidding guys, this does not work if you split the database.

Help?
 
You must log in or register to reply here.

Similar threads

D
Solved Change password using tempvar not passing to following form
Replies
4
Views
276
Gasman
Gasman
D
Solved Tempvars username not displaying on subsequent forms
Replies
11
Views
361
NauticalGent
NauticalGent
J
accde doing crazy things
Replies
4
Views
977
John Sh
J
A
Access performance issues when more than one users log in simultaneously.
Replies
18
Views
846
The_Doc_Man
The_Doc_Man
M
Data Macro - Strange Performance Issue
2
Replies
32
Views
632
mellamokb
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom