Angela Hart

Jul 15, 20232 min

Microsoft Access VBA - Create a User Login and Logout function with user functionality

Hi, I'm thinking you're here because you have seen my video regarding this, in case not I'll briefly outline what I wanted and how I think I have resolved it!
 

You can find the YouTube here

I have created the video and this blog not for seasoned developers but for people like me that dabble, and just get by writing code. I hope it is useful to you, at least it may give you some ideas. Note that I have created this over the past 8 months, when I find a moment at work, often I completely forget where I got to, so sometimes it may not seem to flow!

What I needed
 In Microsoft Access I wanted a name and date/time stamp to be stored against a record on a button press. For the system to know that name I decided that it would need to know who was using it. I wanted a modicum amount of validation to that login. I couldn't find anything suitable on Access help websites, or on YouTube.

This blog details what I have created to do this. But it is important to note that I have not been able to test this on a multi-user basis as I have no network.

Foundations

Tables

  • TabLogins - Status of logged in users and previous details of logins/logouts

  • TabUserType - Types of user to determine permissions

  • TabUsers - Recorded user names, passwords, user types and full name

Forms

  • FrmLogin - Form used to login

Unbound field for user name is named txtusername

Unbound field for PIN or password is named txtpassword

Log in Button is named ButtonLogin

Cancel Button is name ButtonCancel

  • FrmLogout - Form used to logout (note this is always open while logged in)

Unbound field is named TxtFullName

End Session button is named ButtonEnd

VBA code

  • Button named ButtonLogin on FrmLogin

Download file below

  • Button named ButtonCancel on FrmLogin

This has a Macro on event of button click to Close Window no further code

  • Button named ButtonEnd on FrmLogout

Download file below

  • On Load of form FrmLogout VBA as below

    Private Sub Form_Load()

    'FINDING THE USERS FULL NAME FROM THE TabLogins TABLE/POPULATING TxtFullName

    Me!TxtFullName = DLookup("IsLoggedIn", "tabLogins", "UserLogged = '" & LoggedInAs & "'")

    'SETTING THE PUBLIC VARIABLE FULL NAME

    FullNameLoggedInAs = Me!TxtFullName

    End Sub

  • Public variables declared as below(make sure the dropdowns are set to General and Declarations

Public variable LoggedInAs stores the User Name (initials)

Public variable FullNameLoggedInAs stores the Full Name (this is what is displayed in the FrmLogout


 I hope this is useful to you, if you have any questions or suggestions please comment!
 

    300
    3