Access - How to log the Windows Username

hi it's Philip from codecop Netcom it's been quite a while since my last video but unfortunately I don't find the time at the moment to record much so this is only a very quick tip for access beginners it's actually pretty basic but it's asked over and over again on the Internet so I thought I make a quick video about it it's about how to record the user that enters a record in access and how to record the user that makes a change to the record so very basic but I think there are a lot of people out there who want to know that okay let's get started right away so here we are I prepared a absolutely simple demo database for this it has only one table and there's an ID column a dummy data column that is what you would normally have in your table really interesting for us today are these form fields insert user is just a short text with nothing special about it for the username obviously the insert date is of day day time and then there's the last update user short text again for the username who modified the record and the last update date to record the day time of the last modification pretty simple now here's a simple data entry form he is just two fields for the data that is the the real data in your application we are going to ignore this today and this is what we are looking at for fields to record the user data and the date of records changes and and record inserts so you see the four fields I showed you in the table they absolutely basic fields I bound them directly to the table but the whole form is bound directly to the table and those text boxes here bound directly to the table fields you see here the control sources insert user I named this field txt for text box insert user I always recommend use different names for your controls as the database the data table fields behind them that way it's much easier to see which one is which and you might be able to avoid a couple of errors by separating those so and obviously you should always use some meaningful name for your controls the default that access provides is like text 1 and text 375 that's total nonsense whenever you use a control in code whenever you work with it just take the two seconds for entering a meaningful name for it I think that is really really important so the other fields all the same I think I don't need to say much about them back to the insert user it's pretty easy to record the insert user or rather it would be pretty easy I switch through the data tab in here and one way to get the current windows user name is the environ function and I would just call it this way and that should retrieve the user name system variable from Windows the environment function just retrieves environmental variables from the operating system and there is a variable named user name that is a bit of security weak because access cannot know what that variable is so it will retrieve an unknown value from the operating system and that might be anything you can't know that in advance and for that reason this very simple approach I use here does not work because the environment is blocked as an expression directly in access however I still can use this function I need just a shallow wrapper in VBA VBA around this function so that means I write my own little basic function that just calls the environ system function to get the username to do that I switch to database tools Visual Basic now I'm in the visual basic editor here I hit this button to insert a module and now I just write a function the public function that is important name it get a user name and it is a string datatype and now i just assigned the return value our expression from from a minute ago it's just in wiring get user name now I save this and yes I want to enter a name does not really matter too much what name you enter I go for multi-user just in case I want to add some other user related functions in there later so I hit OK and save it now that is the module not user that contains the function there's just one thing you should not do you should not use the name of a function as module name if both are named the same you will get an error because access does not know which is which when you call the so now I can just easily put this in the immediate pain down here and this is actually my windows username so it basically works what we do now we use this function as the default value for our control and now for the insert date that is even easier I can enter the null function depends if you only want the date then you can enter the date function in here but I would prefer to record date and time and that can be achieved by the null function I just enter that in here save the form and switch to normal form view and this is an old record it does not work here because that was inserted before I go to a new record and you see instantly this is my user name it's in here this is the current date it's in there now I enter some dummy data and store the record I close the form and just to make sure the data ended up in here let's see the username and the current date are stored in the table so the first step is completed already we record the insert date and insert use of new records now for the update user that is a tiny bit more complicated because the default value that we used here for new records that is obviously not going to work for the last update user and the last update date because that is going to change all the time so I cannot use a default value that would only be inserted the very first time you enter a new record but not for subsequent edits of the same record but it's easy to achieve that as well we need just a tiny bit of VBA code I just marked the form up here and go to the event tab on the property sheet and I select the selector before update event and I hit that button and say I want to use the code builder now I've got a event procedure right here behind the form in the VBA editor and it's pretty easy what I do here it's inside the form so I can use the me keyword to reference the current object I'm in and that is the form and I can reference my controls and here's the last update user and I want to modify its value and I can just call my my function get user name and I set that to the control value and the same for the last update date I use the null function as I did before with the default values and the before update event this event procedure that is always called before a record in that form is updated so let's try this out [Music] now this is the new record that was already in the database before I did anything now I enter something here and now I click on the record selector here to end the Edit process and in that instance the before update event was fired and here is the data that was created or inserted by the tiny VBA procedure I just added to the form so very very basic and the same happens with this record if I enter some more data in here so I'm changing the record and hit the record selector to save it now you see the username of the last update user and the last update date is stored but that is only as the name says the last update user it's not a real history it's only the last edit now look it's this is the time 42 seconds at the end now I changed the record again and you see this has changed and it's the the date of the last edit but the previous edit date and the previous edit user they are overwritten so you do not know who edited the record before if you want to achieve that you would create a history table where each edit needs to be locked but that is a more complex topic and I'm not going to cover it today so here we are so that's it I'm ready for today if you liked the video please down there is the thumbs up click it please if you want to see more of my videos subscribe to the channel and well I honestly hope it will not be that long time until I be able to record the next video until then bye bye

Channel: codekabinett.com/en

Views: 17,619

Rating: 4.9681697 out of 5

Keywords: Microsoft Access, Windows Username, Environ, Before Update, Default Value, VBA, Function, VBA Function, Now(), edit date, edit user, Microsoft Access programming, Microsoft Access development

Id: pJL1CCfvf7s

Length: 12min 38sec (758 seconds)

Published: Wed Sep 06 2017