Make DB remember who is logged in 2

[rodrunner79]

Hi guys,

I have a startup form that prompts user to login, the values from this form are from a table (table of users).

Private Sub cmdLogIn_Click()
If LOGIN1 = 1 Then
    Do While flag = False
       If txtUserName.Value = User.Value And txtUserPass.Value = Password.Value Then
            flag = True
            MsgBox "Welcome " & User.Value & " to Inventory Tracer 2.01"
            DoCmd.Close
            DoCmd.OpenForm "Main Switchboard"
        Else
            If User.Value <> "" Then
                DoCmd.GoToRecord , , acNext
            Else
                MsgBox "Please try again or contact your System Administrator"
                DoCmd.GoToRecord , , acFirst
                flag = True
            End If
        End If
    Loop
Else
Do While flag = False
   If txtUserName.Value = User.Value And txtUserPass.Value = Password.Value And txtLevel.Value >= 99 Then
        flag = True
        MsgBox "Welcome " & User.Value & " to Inventory Tracer 2.01"
        DoCmd.Close
        DoCmd.OpenForm "Administration"
    Else
        If User.Value <> "" Then
            DoCmd.GoToRecord , , acNext
        Else
            MsgBox "You don't have access System Administration", vbOKOnly, "Sorry"
            DoCmd.GoToRecord , , acFirst
            DoCmd.Close
            flag = True
        End If
    End If
Loop
End If
End Sub

When the user log's in successfully, I want the database to remember who that user is? So I can pull his/her name from any form I want. Just like how the CurrentUser() function works when using Access Security. The reason for this is I'm trying to create a audit table, and I want to be able to pull this name when I need to audit something. How do I begin?

 

[fneily]

One way: you could define a global variable and then set it equal to txtUserName.value. Then you can call this value from any form.
To store the userid in, let's say, an audit table, just write some DAO code. Example (the following is for non-numeric field. For numeric, drop the CHR(34)'s)

Private Sub estv_AfterUpdate()
Dim DB As Database
Dim RS As Recordset
Dim strWhere As String

Set DB = CurrentDb()
Set RS = DB.OpenRecordset("MMCMainTable", dbOpenDynaset)
strWhere = "[MMCNumber] = " & Chr(34) & Me![MMCNumber] & Chr(34)
RS.FindFirst strWhere
If RS.NoMatch Then
MsgBox "No match found"
Else
RS.Edit
RS![EstimatedValue] = Me![estv]
RS.Update
End If
RS.Close
DB.Close
Set RS = Nothing
Set DB = Nothing
End Sub

Don't forget to make a reference to the DAO library in your VBA(ALT +F11) (Tools-References)
You obviously can save other fields to record something like DATE/TIME. So you would add a line like RS![RecordTime]= DATE()

 

[rodrunner79]

One way: you could define a global variable and then set it equal to txtUserName.value.

Hey thanks for the response, however I do have a question regarding the statement above. When you said set it to equal txtUserName.value, is that from a control (txtUserName) from a Form or is that value coming from a table? Thanks!

 

[rodrunner79]

You know nevermind, that was a stupid question, please disregard the last post. Again, thanks for your help. I will work on the mod as you suggested when I get more info.

 

[rodrunner79]

Hi again, I have another question. Okay I created a module (modDBUser) and had this code in it

Function DBUser() As String
    Set DBUser = txtUserName.Value
End Function

Then I created a startup form (frmWelcomeScreen) with this code in it.

Private Sub Form_Close()
    Call DBUser
End Sub

When I close the form it gives me this error saying object required, then it highlights the code in module (Set DBUser) and gives this error saying "Compile Error: Object Required"

Please show me how to setup the mod properly and call it properly. Thanks!

 

[fneily]

To set a global variable, do ALT+F11 to go to VBA. On the menu bar, click Insert - Module. On the left, double click the new module. On the right, you'll see:

Option Compare Database

Directly under this statement, create your variable. Examples followdepends if you want to hold a string, numeric, or a control)

Global mmcsw As Integer
Public ctlIn As Control
Global holdmmc As String

Close VBA. When you close your database, it will prompt you for a module name. Name it anything you want, no spaces.

Then in your code you can set your variable, let's say it's called holduser, to txtUserName. ie.
holduser - txtUserName.value

You can then use holduser throughout your forms.

 

[rodrunner79]

Then in your code you can set your variable, let's say it's called holduser, to txtUserName. ie.
holduser - txtUserName.value

I'm sorry, where did you say I should set my variable, is it in the form itself or in the mod? Also,is it

holduser - txtUserName.value or
holduser = txtUserName.value

Thanks!
Rod

 

[rodrunner79]

Nevermind, please disregard the last post, I figured it out. Thank you very much, I really appreciated the help.

 

[fneily]

Oops. I meant =. Typo error. Sorry.

 

[rodrunner79]

Good morning guys,

fneily, i took your advise and created a global variable and on start up, assign a value to that variable. I also created a function and have set it to equal that variable. Here's the module.

Option Compare Database

Global intHoldUserID As Integer
Public ctlIn As Control
Global strHoldUser As String
Global strHoldPass As String
Global strHoldGroup As String
Global strCheckUser As String

Public Function CheckUser() As Variant
    If IsNull(CurrentDBUser()) Or CurrentDBUser() = "" Then
    Application.Quit
    End If
    
End Function

Public Function CurrentDBUser() As String
On Error GoTo Err_Handler
    'Purpose:   Assign login user as current DB user
    CurrentDBUser = strHoldUser

Exit_Handler:
    Exit Function

Err_Handler:
    Resume Exit_Handler
End Function
[CODE]

Now, when an error occurs in the program (in runtime), and I debug it and then save.  When I go back into runtime the value of strHoldUser (CurrentDBUser) is realeased.  How can I make it so that it doesn't release that value whether there's errors or not, I want to hold/retain that value of the variable (Global strHoldUser) as long as the DB is opened and release it only when Access or DB is closed.

Note: An error that occur in order for the variable strHoldUser to release its value is from a procedure or function without an error handle.  For the ones with error handling, it doesn't affect the variable.

Example of an error could be "Invalid use of null" or "Type mismatch", if errors like this occurs and the procedure that caused it has no error handler, it released the value in my variable (strHoldUser).

 

[fneily]

First, shouldn't it be strHoldUser = CurrentDBUser?

 

[rodrunner79]

Wait, why should it be the other way around... The reason why I set it up as CurrentDBUser = strHoldUser is because... When a user open the DB, a login form appears, a user must enter a username and passsword and if successful, it takes that username and assign it to the global variable (strHoldUser). So now the function CurrentDBUser is used to make it equal to strHoldUser... Now I can set =CurrentDBUser() to any textbox to display the current person signed in.

 

[fneily]

Oh, I thought that's where you're setting strHoldUser. My mistake. The purpose of a global variable is to hold a value throughout the DB usage. You don't need the CurrentDBUser function. Just set whatever you want to strHoldUser. eg. txtBox3.value = strHoldUser
Also, just to be sure, your global statements and functions are in a Module, not with the form. The globals shouldn't lose their value because of an error in a form.

 

[PHV]

The globals shouldn't lose their value because of an error
unless you reinitialize the project ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rodrunner79]

fneily said:

The globals shouldn't lose their value because of an error in a form.

PHV said:

The globals shouldn't lose their value because of an error

If what you guys are saying is true then, take a look at the image below and tell me why it is doing that

(If you don't picture, just wait a few seconds, it's coming from a secure site, you must accept certificate to see picture).

 

[fneily]

PHV - right. Sometimes when you're testing, you just start over or from a very early check point. Then the variable must be set again. Good point.

 

[rodrunner79]

Can you guys see the picture?

 

[PHV]

If you click the End button the you reinitialize your VBA project and thus loose all your global variables value as I already explained here.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MistyWolf]

I hope you have found the answer you needed. However, here is a way that I have accomplished this that seems fairly simple to me.

I set up a setUserName and a getUserName procedure with a variable in a module I called GenModule as listed below.

Private mstrUserName As String

Public Sub SetUserName(userName As String)
mstrUserName = userName
End Sub

Public Function GetUserName()
GetUserName = mstrUserName
End Function

This code sets up a private variable to hold the data from the user name control on your form.

The SetUserName procedure accepts the value from the control and stores it to the private variable

The GetUserName procedure retrieves the stored value from the private variable and returns it to any form that calls it.

The syntax for calling either of these two functions are listed below.

In one form use:
GenModule.SetUserName Me.txtUserName (control)

In a separate form use:
Me.txtUserName2 = GenModule.GetUserName

Test this in a test database. It should work as I basically pulled the code directly from my program. which store logon information and then using it in other areas of my program to track time clock and employee processes for a manufacturing company.

If this sounds to complicated or does not work, feel free to disregard.

Happy programming.

MistyWolf

 

[rodrunner79]

Thanks everyone, I understand now.