Public functions (1 Viewer)

mba_110

Registered User.
Local time
Today, 12:18
Joined
Jan 20, 2015
Messages
280
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
 

June7

AWF VIP
Local time
Today, 11:18
Joined
Mar 9, 2014
Messages
5,423
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.
 

mba_110

Registered User.
Local time
Today, 12:18
Joined
Jan 20, 2015
Messages
280
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.
 

Attachments

  • Test1.zip
    78.6 KB · Views: 88

June7

AWF VIP
Local time
Today, 11:18
Joined
Mar 9, 2014
Messages
5,423
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() [COLOR=red]& "'[/COLOR])"
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?
 
Last edited:

Users who are viewing this thread

Top Bottom