Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-15-2019, 04:05 AM   #16
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,107
Thanks: 0
Thanked 260 Times in 260 Posts
June7 will become famous soon enough
Re: Public functions

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.

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 01-15-2019 at 04:13 AM.
June7 is offline   Reply With Quote
Old 01-15-2019, 04:19 AM   #17
mba_110
Newly Registered User
 
Join Date: Jan 2015
Posts: 217
Thanks: 0
Thanked 0 Times in 0 Posts
mba_110 is on a distinguished road
Re: Public functions

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.
mba_110 is offline   Reply With Quote
Old 01-15-2019, 04:25 AM   #18
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,107
Thanks: 0
Thanked 260 Times in 260 Posts
June7 will become famous soon enough
Re: Public functions

So where is the code that sets the variables? As demonstrated back in post 4.

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
June7 is offline   Reply With Quote
Old 01-15-2019, 04:37 AM   #19
mba_110
Newly Registered User
 
Join Date: Jan 2015
Posts: 217
Thanks: 0
Thanked 0 Times in 0 Posts
mba_110 is on a distinguished road
Re: Public functions

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 by mba_110; 01-15-2019 at 05:28 AM.
mba_110 is offline   Reply With Quote
Old 01-15-2019, 05:38 AM   #20
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,107
Thanks: 0
Thanked 260 Times in 260 Posts
June7 will become famous soon enough
Re: Public functions

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.
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 01-15-2019 at 06:35 AM.
June7 is offline   Reply With Quote
Old 01-15-2019, 10:51 PM   #21
mba_110
Newly Registered User
 
Join Date: Jan 2015
Posts: 217
Thanks: 0
Thanked 0 Times in 0 Posts
mba_110 is on a distinguished road
Re: Public functions

Code looks to me perfect but why i am getting error for null.

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

lngEmp = DLookup("EmpID", "tblUserSecurity", "LoginID='" & myLogin & "'")
strComputer = Environ("ComputerName")
myLogin = TempVars("LoginID").Value

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


CurrentDb.Execute "UPDATE tblLoginSessions SET Loggedon = Now() WHERE EmpID=" & lngEmp & " AND Loggedoff is Null"
Error: Invalid use of null
mba_110 is offline   Reply With Quote
Old 01-15-2019, 11:47 PM   #22
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,107
Thanks: 0
Thanked 260 Times in 260 Posts
June7 will become famous soon enough
Re: Public functions

For what field or variable on what line?

Don't need an UPDATE on tblLoginSessions to set Loggedon date/time - you have the field in table set with DefaultValue.

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
June7 is offline   Reply With Quote
Old 01-16-2019, 12:20 AM   #23
mba_110
Newly Registered User
 
Join Date: Jan 2015
Posts: 217
Thanks: 0
Thanked 0 Times in 0 Posts
mba_110 is on a distinguished road
Re: Public functions

I am tired of this, what is null in code i dont know after checking all this still the same, please can you fix the 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


'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
     

'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"

'Call LoginSessions functions to record the Logged on user activity

TempVars("LoginID").Value = Me.cboUsername.Value

Dim lngEmp As Integer
Dim strComputer As String
Dim myLogin As String

lngEmp = DLookup("EmpID", "tblUserSecurity", "LoginID='" & myLogin & "'")
strComputer = Environ("ComputerName")
myLogin = TempVars("LoginID").Value

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



  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 & "'"), "")

'Update the tblLoginSessions to users



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




'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
I want in same line of login it should be recorded as logout hence i mentioned this in forms upload event.

Code:
Private Sub Form_Unload(Cancel As Integer)
Dim lngEmp As Integer
'Record Logout activity of user

CurrentDb.Execute "UPDATE tblLoginSessions SET Logout = Now() WHERE EmpID=" & lngEmp & " "
End Sub
i have change some fields on tblLoginSessions, now i dont have Date/Time field and i replace it with [Login] and [Logout] fields which is date/time fields in [Login] i have set default value is =Now() but in [Logout] the procedure should update the data and time.

Please fix it, i have attached the db also thanks.
Attached Files
File Type: zip Test1.zip (78.6 KB, 6 views)
mba_110 is offline   Reply With Quote
Old 01-16-2019, 12:53 AM   #24
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,107
Thanks: 0
Thanked 260 Times in 260 Posts
June7 will become famous soon enough
Re: Public functions

Code uses myLogin variable before it is set. Change order of lines so myLogin is set first:

Code:
myLogin = TempVars("LoginID").Value
lngEmp = DLookup("EmpID", "tblUserSecurity", "LoginID='" & myLogin & "'")
strComputer = Environ("ComputerName")
The INSERT has syntax error at the end, missing & and there is extra comma, correction shown in red:
Code:
CurrentDb.Execute "INSERT INTO tblLoginSessions(EmpID, ComputerName, ComputerIP) VALUES(" & lngEmp & ",'" & strComputer & "','" & GetMyPublicIP() & "')"
The logout update will fail because lngEmp variable is not set in the Unload procedure. The lngEmp variable declared and set in BtnLoginOK_Click is not available to other procedures. This is why global variables and TempVars are used. A variable can be declared in the header of the form's code module and that variable will be available to any procedure within that module. Or set a textbox on form with the value needed by multiple procedures. As long as form remains open, can reference textbox. Also need the Loggedout Is Null criteria.

Should change TabOrder of Username and Password textboxes.

Are you familiar with debugging techniques? Do you know how to set a breakpoint and step through code?

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 01-16-2019 at 01:22 AM.
June7 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Public Functions ddrew Forms 22 11-28-2011 02:03 AM
Public Functions ddrew Forms 1 12-22-2010 09:32 AM
Is all functions 'public' by default? prabha_friend Modules & VBA 6 01-22-2010 07:59 AM
Declaring and using Public Functions JoshuaAnthony Modules & VBA 2 01-29-2004 01:45 PM
Public Functions raskew Modules & VBA 4 01-24-2003 04:52 PM




All times are GMT -8. The time now is 08:22 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World