Calling Public Variable collected from one form into another form

Question

My access programming has gone rusty and I have a question. My database requires that the user log in before they can enter and search the database. With the help from others here - my log in form as two unbound combo boxes to collect the user name from a drop down of our staff list and then to enter the password. When my user enters submit - the username and the current date and time are stored in a log list.

My users will be updating parts of a record based on what is received and reviewed.  I have it set up that if a change is made to a record - it stores the change in an archive table.  We aren't looking for mistakes but to track the progress of an item received.  The final piece I need is to attach the user that is logged in to the change that was made.  I have read some different blogs about audit forms but can't seem to figure out how to make that work.

I am thinking that this might be achieved by somehow storing the user name collected on the log in form as a global variable that can then be called into the form where the records are being updated.  Does that sound like a reasonable option?  If it does, I would need a big assist on the how part of it.  Or is there a way to pull out the user name from the log list and use that somehow.

So this is where I am currently stuck - any suggestions is appreciated!

Answer 1

One option, since Access 2007, would be at successful login to set a TempVar and then you can call it whenever you need to identify the current user.

 

Another option is to simply use a function such as http://www.theaccessweb.com/api/api0008.htm which retieves the windows login name.  What I often do is have a user table with

UserId
FirstName
LastName
NTUserName   (which correspond to the functions results - the username the user uses to log into their PC)

Then I can either use the NTUsername at anytime to cross reference against the user table and retrieve the UserId to use in various tracking tables...

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

I think you are on the right track. You should be able to create a module level Public variable in your Form and set the value of that to the value entered in your logon Form.  If the user name is only referenced within the Form code you can set it to Private instead.

Option Compare Database

Public Username As String

Private Sub Form_Load()

    Username = Forms!frmLogin!txtUsername.Value
    
End Sub

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 3

You might like to take a look at ChangedRecordDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

This little demo file illustrates methods for time stamping or logging changes to a record, or inserting a row into an audit table.  The user name recorded is the current user's Windows log-in name, which is obtained by calling a Windows API function, but it would not be difficult to record a user's log-in name to the database where this functionality is included.  I have done this on occasions by simply hiding the log-in form, by setting its Visible property to False, rather than closing it.  The relevant control in the form can then be referenced at any time to get the name.

One important thing to note about my demo, and this is the principal reason for it, is that it does not merely log updates to a record, but actual changes to the data.  This is done by means of the RecordHasChanged function in the basChangedRecord module.  A record can be updated with no actual changes to the values of its data, in which case the update is not logged.

---

Ken Sheridan, Stafford, England

Answer 4

Hello,

Has your original issue been resolved? If it has, I would suggest you mark the helpful reply as answer or provide your solution and mark as answer to close this thread. If not, please feel free to let us know your current issue.

Best Regards,

Terry

---

MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].