Public functions (1 Viewer)

mba_110

Registered User.
Local time
Today, 06:59
Joined
Jan 20, 2015
Messages
280
Hi i am trying to create public functions for my login database.

I want following functions to call at when anyone login to database

1. Computer name i have tblLoginSessions where i have field name [ComputerName] i want when user login then it should capture that user's computer name and store it in tblLoginSesssions.[ComputerName]


2. ComputerIP this is IP address this is one more layer of reconciliation because most of the time we work on network and not individual PC also user can be a IT guy so he can manipulate the things after login.

for this i have field in tblLoginSessions called [ComputerIP] the same way of doing computer name it should also capture single row and full IP address and store it in table.


3. I have field call Active which is check box field that indicate user have logged in and active this will facilitate to find out how many users are online and this also same we need to call function on login form to mark it as active also when he close database or close the form it should have sort of arrangement to uncheck that box as he is no more active.


4. I have [EmpID] field in tblLoginSessions that also should be filled with Emp ID who is logging in the database.

I check examples online but most of it are mixed with username and login name etc no function is separately coded.

Please i need this all three in separate functions and call procedures, if you have any idea of preparing this or have sample that will help me a lot.

I will be happy if its not required the public variables and can done by just coding the parameters in frmLogin btnOK.
 

June7

AWF VIP
Local time
Today, 05:59
Joined
Mar 9, 2014
Messages
5,425
Environ("COMPUTERNAME") will grab computer name.

Environ("USERNAME") wil grab the network user name. So if you have a Users table with EmpID and Username, can do a DLookup to then grab the EmpID.

http://codevba.com/office/environ.htm#.XDxiwWaWyCg

Bing: Access VBA IP address
Consider:
Code:
Public Function getMyIP()
Dim myWMI As Object, myobj As Object, itm
Set myWMI = GetObject("winmgmts:\\.\root\cimv2")
Set myobj = myWMI.ExecQuery("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled = True")
For Each itm In myobj
  getMyIP = itm.IPAddress(0)
  Exit Function
Next
End Function
 

mba_110

Registered User.
Local time
Today, 06:59
Joined
Jan 20, 2015
Messages
280
So, the following code are correct to insert in my login button.


for EmpID (What is correct procedure)

Code:
me.EmpIDtxt.value = [tblLoginSession]![EmpID].value

for ComputerName (What is correct procedure)

Code:
if me.txtLogin = [tblUserSecurity]![LoginID] then
tblLoginSession!ComputerName = environ("ComputerName).value

Also for IP address kindly show me how and where to call this function i need to store that IP address in [tblLoginSessions]![ComputerIP]
 

June7

AWF VIP
Local time
Today, 05:59
Joined
Mar 9, 2014
Messages
5,425
I don't know your database structure nor your login process. If user has to provide LoginID and password then you can do a DLookup with LoginID instead of Environ("USERNAME").

Abbreviated example of what I would do, assuming all fields are text type:
Code:
lngEmp = DLookup("EmpID", "tblUserSecurity", "LoginID='" & Me.txtLogin & "'")

strComputer = Environ("ComputerName")

CurrentDb.Execute "INSERT INTO tblLoginSession(EmpID, ComputerName, IPAdd) VALUES('" & lngEmp & "','" & strComputer & "','" & getMyIP() & "')"
 
Last edited:

mba_110

Registered User.
Local time
Today, 06:59
Joined
Jan 20, 2015
Messages
280
This is my full login code and i want to add those new codes (Active, IP address,EmpID,ComputerName) here in this code.

Code:
Option Compare Database
Option Explicit

Private Sub BtnLoginOK_Click()
On Error GoTo Err_Handler

Dim vpwd As String
Static Attempts As Integer  'for info Static / Dim do the same thing
Dim SLevels As String

[COLOR="Red"]'Code to record (tblLoginSessions, from tblUserSecurity [Active] (un check when loggoff), EmpID, ComputerName, ComputerIP)[/COLOR]



'Check if username is entered.

If Nz(Me.cboUsername, "") = "" Then
        MsgBox "Username is required", vbOKOnly, "Invalid Entry!"
        Me.cboUsername.SetFocus
        Exit Sub
 End If

'Check if Password is entered.
 
 If Nz(Me.txtPassword, "") = "" Then
        MsgBox "Password is required", vbOKOnly, "Invalid Entry!"
        Me.txtPassword.SetFocus
        Exit Sub
    End If
  
'Check case sensitivity of entered username and password is correct 'z123VF&@'.
      
    If strLoginID <> "" Then
        
'CR - replaced LoginID with strLoginID & moved Debug.Print to separate line
        
      vpwd = Nz(DLookup("strPassword", "tblUserSecurity", "LoginID='" & strLoginID & "'"), "")
  
'Debug.Print vpwd
    
    If StrComp(Me.txtPassword.Value, vpwd, vbBinaryCompare) <> 0 Then
              MsgBox "Invalid Password.", vbOKOnly, "Invalid Entry"
              Exit Sub
              End If
    End If
     
TempVars("LoginID").Value = Me.cboUsername.Value



'Check if user's 3  attempts are wrong then close the database with message.

If Me.txtPassword.Value = DLookup("[strPassword]", "tblUserSecurity", "[LoginID]='" & Me.cboUsername.Value & "'") Then

'MsgBox "Welcome to Employee Management System"
     
  Else
     Attempts = Attempts + 1
     Select Case Attempts
     
     Case 1
         MsgBox "Username or password is incorrect!" & vbCrLf & _
           "Please try again", vbCritical, "Warning"
         Me.cboUsername.SetFocus
         Exit Sub
     
     Case 2
         MsgBox "You have entered an incorrect username or password twice" & vbCrLf & _
          "You have one more chance to do this correctly", vbCritical, "Warning"
         Me.cboUsername.SetFocus
         Exit Sub

     Case 3
           MsgBox "You do not have access to EMS Database, Please contact system Administrator", vbOKOnly, "Invalid Entry!"
           Application.Quit

     End Select
 End If

 SLevels = Nz(DLookup("SecurityLevel", "tblUserSecurity", "[LoginID] = '" & Me.cboUsername & "'"), "")

Select Case SLevels

Case "Admin"
    'Full control all buttons & options ON (on frmDataEntry_Navigation i have enabled button based on "Admin").
       DoCmd.OpenForm "frmAdminNavigation"
       Forms![frmAdminNavigation]![txtAccessLevel] = Me.txtSecurityLevel
       Forms![frmAdminNavigation]![txtLoginID] = Me.cboUsername
    '   Exit Sub 'CR - removed
 
Case "Employee"
      'Go to Data entry menu with User Access Level (by default button is disabled on frmDataEntry_Navigation).
       DoCmd.OpenForm "frmEmployeeNavigation"
        Forms![frmEmployeeNavigation]![txtEmpNavLoginID] = Me.cboUsername
       
   '   Exit Sub 'CR - removed

Case "User"
       'Go to Individual record access form.
        DoCmd.OpenForm "frmUserNavigation"
        Forms![frmUserNavigation]![txtEmpID] = Me.EmpIDtxt
        Forms![frmUserNavigation]![txtLoginEmp] = Me.cboUsername
    '   Exit Sub 'CR - removed
End Select

ModLoginSessions.Logging "Logon"

'close this form!
'DoCmd.Close acForm, Me.Name

Me.Visible = False


Exit_Handler:
      Exit Sub

Err_Handler:
       MsgBox "Error " & Err.Number & " " & Err.Description & " iun cmdLogin_Click procedure"
       Resume Exit_Handler

End Sub
Private Sub cboUsername_AfterUpdate()

    'CR - added to check user selected from the list

    If Nz(DLookup("EmpID", "tblUserSecurity", "LoginID='" & Me.cboUsername & "'"), 0) <> 0 Then
        'valid user
        strLoginID = Nz(Me.cboUsername, "")
    Else
        MsgBox "This is not a valid user name." & vbCrLf & vbCrLf & _
            "Please select a user name using the dropdown list", vbCritical, "Incorrect user name"
            Me.cboUsername = ""
    End If
 '   Debug.Print strLoginID
End Sub


Private Sub Form_Unload(Cancel As Integer)
ModLoginSessions.Logging "LogOut"
End Sub

EmpID is number field i have done lot of work on this login code, so please suggest how and where i have to add the below code.


Code:
strEmp = DLookup("EmpID", "tblUserSecurity", "LoginID='" & Me.txtLogin & "'")

strComp = Environ("ComputerName")

CurrentDb.Execute "INSERT INTO tblLoginSession(EmpID, ComputerName, IPAdd) VALUES('" & strEmp & "','" & strComp & "','" & getMyIP() & "')"


I need highlighted in red to be added in my above login code.
 
Last edited:

June7

AWF VIP
Local time
Today, 05:59
Joined
Mar 9, 2014
Messages
5,425
Do you need to retain history of logins? In my db I only care about who is actively using db. This means I have a Users table with one record for each user. When user opens db I set a field with the computer name. When they close db I set the field to Null. If you need to retain history, this gets more complicated.

So where should you put the new code? Most logical place would seem to be where you have the commented Welcome MsgBox. If you want to set Active field to true, then just expand the INSERT sql to include that.
 
Last edited:

mba_110

Registered User.
Local time
Today, 06:59
Joined
Jan 20, 2015
Messages
280
It will be sessions for each event of user who enter and exit the database.

the text i have highlighted in red are required the code, yes we can place it below welcome message but need to fix your code fields and delimiters for text and numeric fields.

I have tblUserSecurity that holds all single entries for users now i have moved Active field to tblUserSecurity to see who is online tblloginSessions will be as retainer for all sessions.
 

Minty

AWF VIP
Local time
Today, 13:59
Joined
Jul 26, 2013
Messages
10,355
Just to add to the mix, and referencing your other thread this code is really handy and sets a global property for User name, and I've added one for EmpID in a similar vain.

Code:
Option Compare Database
Option Explicit
Private pUser As String


Public Property Get glUserName() As String
    'Uses a self-healing property to return the network username
    'Thanks to Brent Spaulding (datAdrenaline), MVP for the function
    'to retrieve the username without using API
    'Thanks to Jack Leach (jleach), MVP for the idea on self-healing propeties
    'Source: http://accessmvp.com/thedbguy
    'Usage: Me.ControlName = modUser.Username

    If pUser > "" Then
        'Just return the existing username
    Else
        'Get username
        pUser = CreateObject("wscript.network").username
    End If
           
    glUserName = DLookup("First_Last_Name", "Employees", "[UserNameField] = '" & pUser & "'")

End Property

Obviously you need to amend the Dlookup to your table and field names.
 

June7

AWF VIP
Local time
Today, 05:59
Joined
Mar 9, 2014
Messages
5,425
So do the fixes, test code, and when you have specific issue, post question.

Shouldn't you also record date/time into tblLoginSessions?

Sounds like you will need UPDATE sql action to set the Active field True.

Then in some form Unload event, another UPDATE sql action to set Active field False. I use a form that never closes (Main Menu) until the db is closed.

I also save UserID in a hidden textbox on Main Menu so it is always available for reference. I don't fully trust global variables and I've never utilized TempVars.

Just saw Minty's post. Something new to me.
 

isladogs

MVP / VIP
Local time
Today, 13:59
Joined
Jan 14, 2017
Messages
18,186
PMFBI but a couple of additional points:
1. The output from the Environ function can be 'spoofed' i.e. falsified
If that might be an issue you are better off using another method.
See these links for 2 better alternatives: Get User Name , Get Computer Name
I recommend using the WScript approach

2. There are two IP values - Local & Public
The code June provided gives the local IP.
The following code gives your public IP as your computer is seen to the world

Code:
Function GetMyPublicIP() As String

    Dim HttpRequest As Object
    
    On Error Resume Next
    'Create the XMLHttpRequest object.
    Set HttpRequest = CreateObject("MSXML2.XMLHTTP")

    'Check if the object was created.
    If Err.Number <> 0 Then
        'Return error message.
        GetMyPublicIP = "Could not create the XMLHttpRequest object!"
        'Release the object and exit.
        Set HttpRequest = Nothing
        Exit Function
    End If
    On Error GoTo 0
    
    'Create the request - no special parameters required.
    HttpRequest.Open "GET", "http://myip.dnsomatic.com", False
    
    'Send the request to the site.
    HttpRequest.Send
        
    'Return the result of the request (the IP string).
    GetMyPublicIP = HttpRequest.ResponseText

End Function

HTH
 

mba_110

Registered User.
Local time
Today, 06:59
Joined
Jan 20, 2015
Messages
280
I am sorry but, need your kind help for my code goals i am not professional like you guys, with my limited knowledge i came this far in my database and would appreciate your help to complete the following code.


error on foscomputerName function:

This code in this project must be updated for use on 64-bit systems
Please review and update declared statements and then mark them with
the Ptrsafe attribute.


Code:
strEmp = DLookup("EmpID", "tblUserSecurity", "LoginID=" & Me.txtLogin & "")
strActive = DLookup("Active", "tblUserSecurity", "LoginID=" & Me.txtLogin & "")

StrComp = Modfunctions.fOSComputerName

CurrentDb.Execute "INSERT INTO tblLoginSession(EmpID, ComputerName, ComputerIP) VALUES('" & strEmp & "','" & strComp & "," & getmypublicIP() & ") _
tblUserSecurity (Active.value = true)"

Things this code should do.
1. [EmpID] in tblLoginSessions should be updated with user's EmpID stored in tblUserSecurity

2. [ComputerName] in tblLoginSessions should be updated with user's Login Computer name.

3. [ComputerIP] in tblLoginSessions should be updated with user's IP address.

4. [Active] in tblUserSecurity Should be true tick when user login and un check false when he close the database by anyway.


Fields that fall within this exercise

[EmpID] is number field (tblEmployees.EmpID is mother of all EmpID fields)
[ComputerName] is text field
[ComputerIP] is text field
[Active] is Yes/no field


Sorry to bother you but this could fix my essential part of database.
 
Last edited:

June7

AWF VIP
Local time
Today, 05:59
Joined
Mar 9, 2014
Messages
5,425
I don't see need for DLookup on Active field.

Since EmpID is a number type, change the variable name to lngEmp. I am assuming you are explicitly declaring all variables.
Code:
CurrentDb.Execute "INSERT INTO tblLoginSession(EmpID, ComputerName, ComputerIP) VALUES(" & lngEmp & ",'" & strComp & "','" & getmypublicIP() & "')"
 
CurrentDb.Execute "UPDATE tblUserSecurity Set Active = True WHERE EmpID = " & lngEmp
 
Last edited:

mba_110

Registered User.
Local time
Today, 06:59
Joined
Jan 20, 2015
Messages
280
it is giving error message for red highlighted area
"Expected: ("

Secondly compile error syntax error, code's first line in red.


I have add the variable to EmpID filed.

Code:
Dim lngEmp As Integer

DB.Execute "INSERT INTO tblLoginSessions(EmpID, ComputerName, ComputerIP) VALUES(" & lngEmp & ",'" & strComp [COLOR="Red"]&[/COLOR] "','" & getmypublicIP() & "')"
 
CurrentDb.Execute "UPDATE tblUserSecurity Set Active = True WHERE EmpID = " & lngEmp
 

June7

AWF VIP
Local time
Today, 05:59
Joined
Mar 9, 2014
Messages
5,425
Where is CurrentDb for that line? There is just DB.

I should have said I intended lngEmp to be declared as Long but if the field really is set as Integer field size, Integer should work as well. Usual convention is for prefix to indicate the type: intEmp, lngEmp, strEmp - use whichever is appropriate.

Perhaps you should post the complete revised procedure. Or provide db for analysis because we appear to be going in circles.
 

mba_110

Registered User.
Local time
Today, 06:59
Joined
Jan 20, 2015
Messages
280
I have uploaded the my DB Login form and functions.

Secondly i need your help in sorting out the modules and functions separately and call it wherever is required, also if you have some good functions that can really help in auditing and cross checking please add it to modules.

Many thanks for your help.
 

Attachments

  • Test1.zip
    77.8 KB · Views: 94

June7

AWF VIP
Local time
Today, 05:59
Joined
Mar 9, 2014
Messages
5,425
VBA has an issue with variable strComp so change it to strComputer. Turns out strComp is an intrinsic function I had forgotten.

I really don't see need to create functions just to run those 2 SQL action statements. Nor for the one action to reverse the "Active" status field and I see you may need another to enter a Logout record? Instead of separate records for login and logout, could be 1 record.

No, I don't have code for 'auditing' and 'cross-checking', although I am not totally clear what that means in your db.

Advise not to use punctuation such / character (underscore is only exception) in naming convention. So Date/Time would be better as DateTime or Date_Time.
 
Last edited:

mba_110

Registered User.
Local time
Today, 06:59
Joined
Jan 20, 2015
Messages
280
This is not performing as what i am required.

ComputerIP is ok but EmpID, ComputerName and Active field are not working.


Code:
Dim lngEmp As Long
Dim strComputer As String

CurrentDb.Execute "INSERT INTO tblLoginSessions(EmpID, ComputerName, ComputerIP) VALUES(" & lngEmp & ",'" & strComputer & "','" & GetMyPublicIP() & "')"
 
CurrentDb.Execute "UPDATE tblUserSecurity Set Active = True WHERE EmpID = " & lngEmp

Please try the code for cross check.
 

June7

AWF VIP
Local time
Today, 05:59
Joined
Mar 9, 2014
Messages
5,425
So where is the code that sets the variables? As demonstrated back in post 4.
 

mba_110

Registered User.
Local time
Today, 06:59
Joined
Jan 20, 2015
Messages
280
aah sorry code is working thanks.

but it create another issue in mytblLoginSession their is [Event] and [LoginID] both became empty?

for event i have function called logging which i call but its giving me syntax error.

Code:
ModLoginSessions.Logging "Logon"

but why [LoginID] is also empty?

Code:
Dim lngEmp As Integer
Dim strComputer As String
Dim StrEvent As String

lngEmp = DLookup("EmpID", "tblUserSecurity", "LoginID='" & GetLoginID() & "'")
StrEvent = DLookup("Event", "tblUserSecurity", "LoginID='" & GetLoginID() & "'")
strComputer = Environ("ComputerName")

CurrentDb.Execute "INSERT INTO tblLoginSessions(EmpID, ComputerName, ComputerIP, LoginID, Event) VALUES(" & lngEmp & ",'" & strComputer & "','" & GetMyPublicIP(), "'" & GetLoginID & "','" & StrEvent.Value = "logon" & "' '" & Activity & "')"
 
CurrentDb.Execute "UPDATE tblUserSecurity Set Active = True WHERE EmpID = " & lngEmp

End Sub

Please i need logging function to add up so in single row i will have all details mentioned now its coming from two different rows for same login.
 
Last edited:

June7

AWF VIP
Local time
Today, 05:59
Joined
Mar 9, 2014
Messages
5,425
The syntax error is the red characters: GetMyPublicIP() & "','"

Remove: StrEvent.Value = "logon" & "' '" &

LoginID is blank because the strLoginID global variable is not getting set therefore calling GetLoginID function fails. Code is setting a TempVars variable. So you need to decide which you want. I avoid global variables because they lose value when code has run-time error. Can be frustrating when debugging code. TempVars do not lose value.

In posted db, tblUserSecurity does not have Event field. Why would it? Why a DLookup?

If you want 1 record for each log session then need 2 date/time fields - Login and Logout - and Event field would not be needed. Use an UPDATE action to populate the Logout date/time.
CurrentDb.Execute "UPDATE tblLoginSessions SET Logout = Now() WHERE EmpID=" & lngEmp & " AND Logout Is Null"

Are all users also employees? If so, why have 2 tables?

Why duplicate LoginID into tblLoginSessions? It is stored in tblUserSecurity and doesn't need to be in both tables.

Why have the Logging sub? Just run the SQL actions directly in the Login form code. These actions are not needed anywhere else.
 
Last edited:

Users who are viewing this thread

Top Bottom