login / username

**Stewart Ross** · Mar 2 '08, 08:40 PM

Hi Dale. You can use a global variable to store the username, updated in the AfterUpdate event of the employee entry combo in your login code, and a public function to return the value of the username global variable.

In the code module of your login code add the following public (global) variable definition and function:
[code=vb]'global to store current user name
Public UserName As String

Public Function ReturnUsername( )
ReturnUserName = UserName
End Function
[/code]
Change the AfterUpdate event of the cboEmployee combo in the databasedev code to set the global, as indicated below:
[code=vb]Private Sub cboEmployee_Aft erUpdate()
UserName = cboEmployee '<<< This line sets the global variable to
' the value in the entry combo
'After selecting user name set focus to password field
Me.txtPassword. SetFocus
End Sub
[/code]
You can then add the username function call into your memo timestamp routine if you want, as shown below, although my preference would be to store it in a separate field created for that purpose:
[code=vb]Me.Comment = Me.Comment & " " & vbNewLine & UserName() & " " & Now & ": " & Me.NotesMemoFie ld & vbNewLine[/code]
-Stewart

**dale5804** · Mar 3 '08, 10:31 AM

cheers for this, will give it a go today. many thanks

**dale5804** · Mar 3 '08, 10:44 AM

hi, tried it, but the code below on the 'on open' is giving me a compile error, a invalid attribute in sub or function, ad is highlighting this bit...

Public UserName As String

any ideas
cheers

**Stewart Ross** · Mar 3 '08, 12:05 PM

Originally posted by dale5804

hi, tried it, but the code below on the 'on open' is giving me a compile error, a invalid attribute in sub or function, ad is highlighting this bit...

Public UserName As String

any ideas
cheers

Hi Dale. You need to add the definition at the top of a code module (the declaration section); I wonder if you have accidentally placed it within another procedure in the code module instead? If you are unsure about this, copy the code from the code module and paste it into your next post and I'll check it for you.

It is always worth compiling any code you add before finalising changes (Debug, Compile.. from the editor menu), as the compiler can let you know of these issues before you try out the revised forms...

-Stewart

**dale5804** · Mar 3 '08, 12:14 PM

Hi Stewart, thanks for the reply, ive done...

Code:

Private Sub cmdLogin_Click()
Public UserName As String
'global to store current user name
      Public Function ReturnUsername()
          ReturnUsername = UserName
      End Function

'Check to see if data is entered into the UserName combo box

    If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
            MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
            Me.cboEmployee.SetFocus
        Exit Function
    End If

'Check to see if data is entered into the password box

    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
            MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
            Me.txtPassword.SetFocus
        Exit Function
    End If

'Check value of password in Contacts  to see if this matches value chosen in combo box

    If Me.txtPassword.Value = DLookup("strEmpPassword", "Contacts", "[ID]=" & Me.cboEmployee.Value) Then

        lngMyEmpID = Me.cboEmployee.Value
        


'Close logon form and open Start
        
        DoCmd.Close acForm, "frmLogon", acSaveNo
        DoCmd.OpenForm "Start"

        Else
        MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
        Me.txtPassword.SetFocus
    End If
    
'If User Enters incorrect password 3 times database will shutdown
    
    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
        MsgBox "You do not have access to this database.  Please contact your system administrator.", vbCritical, "Restricted Access!"
        Application.Quit
    End If
    
    
    
End Function

End Sub

**Stewart Ross** · Mar 3 '08, 01:53 PM

Hi Dale. You have indeed placed the code in the wrong part of your code module; the definition cannot be placed inside the subroutine as you currently have it. I have taken these lines out of the Sub as shown below. As the Sub cmdLogin is but one of the procedures in the login code, I think you are not clear on what a code module is. A code module is a named code sheet within the Visual Basic environment. Each one has a declaration section followed by a code section which includes all function and subroutine definitions.

You MUST place the definition of the UserName variable in the top (declaration) section of a code module - it can't be placed within a function or sub procedure.

I also note that there is an End Function statement at the bottom for which I cannot see a corresponding opening Public Function or Private Function declaration. It may be that you have moved code around and accidentally cut off a function somewhere, but I'm sorry to say I can't debug the exemplar code. I would suggest you check your implementation of the original code from DatabaseDev to see that you have not accidentally overwritten some of the declarations.

-Stewart

Code:

'global to store current user name 
Public UserName As String '<<< this must be placed in the declaration section of the code module (the very top of the code sheet)
 
Public Function ReturnUsername() '<<< this function can be placed immediately after the declaration section
	ReturnUsername = UserName
End Function
 
Private Sub cmdLogin_Click()
 
'Check to see if data is entered into the UserName combo box
 
If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.cboEmployee.SetFocus
Exit Function
End If
 
'Check to see if data is entered into the password box
 
If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Function
End If
 
'Check value of password in Contacts to see if this matches value chosen in combo box
 
If Me.txtPassword.Value = DLookup("strEmpPassword", "Contacts", "[ID]=" & Me.cboEmployee.Value) Then
 
lngMyEmpID = Me.cboEmployee.Value
 
'Close logon form and open Start
 
DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "Start"
 
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
End If
 
'If User Enters incorrect password 3 times database will shutdown
 
intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database. Please contact your system administrator.", vbCritical, "Restricted Access!"
Application.Quit
End If
 
End Function '<<< This appears to be an orphaned End Function statement
 
End Sub

[/QUOTE]

**dale5804** · Mar 3 '08, 02:33 PM

hi.. thanks for that i understand better now.
now to move onto the code that does the time/users stamping
cheers

**dale5804** · Mar 3 '08, 02:44 PM

stuck again. when i click to add data (and stamp) having issue with this line:

Me.Comment = Me.Comment & " " & vbNewLine & UserName() & Now & ": " & Me.NotesMemoFie ld & vbNewLine

getting a compile error, which highlights the the Me.Comment, after the = sign.
It worked before, but once i add the UserName() it does as above.
any ideas. cheers

**Stewart Ross** · Mar 3 '08, 03:59 PM

Originally posted by dale5804

stuck again. when i click to add data (and stamp) having issue with this line:

Me.Comment = Me.Comment & " " & vbNewLine & UserName() & Now & ": " & Me.NotesMemoFie ld & vbNewLine

getting a compile error, which highlights the the Me.Comment, after the = sign.
It worked before, but once i add the UserName() it does as above.
any ideas. cheers

Hi Dale. I'm not seeing any obvious error in the line above. have you compiled the code in VB and checked that there are no debug errors introduced elsewhere by the changes (easy to do accidentally when moving code around)? Have you added the line to the cmbEmployee after update event which sets the value of the UserName public variable in the first place? Can you check using the VB code window what the value of the UserName() function is when the error occurs (if you hover your mouse over the function name the VB editor will tell you its value, or you can use the immediate window to 'print' the value by typing ? UserName() followed by Enter.

You could always remove the '& UserName()' part to check that you have not got a typo in your code line - which is actually my first thought about what may be wrong, given the changes you have had to make...

-Stewart

**dale5804** · Mar 3 '08, 04:40 PM

You could always remove the '& UserName()' part to check that you have not got a typo in your code line - which is actually my first thought about what may be wrong, given the changes you have had to make...

-Stewart

hi Stewart, ive, tried and removed the & UserName(), and when i do, it works again????

if it helps...
This is the whole login form VB code

Code:

Option Compare Database
Private intLogonAttempts As Integer
Public UserName As String
      Public Function ReturnUsername()
          ReturnUsername = UserName
      End Function

Private Sub Form_Open(Cancel As Integer)
'On open set focus to combo box
Me.cboEmployee.SetFocus
End Sub

Private Sub cboEmployee_AfterUpdate()
UserName = cboEmployee
          Me.txtPassword.SetFocus
End Sub

Private Sub cmdLogin_Click()

'Check to see if data is entered into the UserName combo box

    If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
            MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
            Me.cboEmployee.SetFocus
        Exit Sub
    End If

'Check to see if data is entered into the password box

    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
            MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
            Me.txtPassword.SetFocus
        Exit Sub
    End If

'Check value of password in Contacts  to see if this matches value chosen in combo box

    If Me.txtPassword.Value = DLookup("strEmpPassword", "Contacts", "[ID]=" & Me.cboEmployee.Value) Then

        lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open Start
        
        DoCmd.Close acForm, "frmLogon", acSaveNo
        DoCmd.OpenForm "Start"

        Else
        MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
        Me.txtPassword.SetFocus
    End If
    
'If User Enters incorrect password 3 times database will shutdown
    
    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
        MsgBox "You do not have access to this database.  Please contact your system administrator.", vbCritical, "Restricted Access!"
        Application.Quit
    End If
    
End Sub

and this is the whole vb code of the form which has the date/user stamp part with in it

Code:

Option Compare Database


Private Sub Command83_Click()
DoCmd.OpenReport "Issue Details", acViewPreview, , _
"[ID]=Issues"
End Sub

Private Sub Command85_Click()
strWhere = "[ID] = " & Me.[ID]
DoCmd.OpenReport "Issue Details", acNormal, , strWhere
End Sub



Private Sub DueDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
ocxCalendar.Visible = True
ocxCalendar.SetFocus
If Not IsNull(DueDate) Then
   ocxCalendar.Value = DueDate.Value
Else
   ocxCalendar.Value = Date
   End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Changes have been made to this Issue" _
        & vbCrLf & vbCrLf & "Do you want to save these changes?" _
        , vbYesNo, "Message From SCRM...") = vbYes Then
            DoCmd.Save
        Else
            DoCmd.RunCommand acCmdUndo
    End If
End Sub

Private Sub Image52_Click()
Dim strInput As String, strMsg As String

   strMsg = "Enter your password to proceed."
   strInput = InputBox(Prompt:=strMsg, _
       Title:="SCRM Security Check")

   If UCase(strInput) = "people5804" Then
        
        DoCmd.RunCommand acCmdDeleteRecord
   Else
        MsgBox "Sorry wrong password."
   End If
End Sub


Private Sub InputData_Click()
 If InputData.Caption = "Input New Notes" Then
   NotesMemoField.Visible = True
   NotesMemoField.SetFocus
   InputData.Caption = "Add Data"
Else
  If Len(NotesMemoField.Value) > 0 Then
    DoCmd.SetWarnings False
    NotesMemoField.SetFocus
    NotesMemoField.SelStart = 1
    NotesMemoField.SelLength = Len(NotesMemoField.Value)
    DoCmd.RunCommand acCmdSpelling
    NotesMemoField.SelLength = 0
    DoCmd.SetWarnings True
      Forms!Issues![TabControl].Pages("Comments").SetFocus
      Me.Comment = Me.Comment & " " & vbNewLine & UserName() & Now & ": " & Me.NotesMemoField & vbNewLine
  End If
   InputData.Caption = "Input New Notes"
   Me.NotesMemoField = ""
   NotesMemoField.Visible = False
End If
End Sub

Private Sub ocxCalendar_Click()
DueDate.Value = ocxCalendar.Value
DueDate.SetFocus
ocxCalendar.Visible = False
End Sub


Private Sub Text78_BeforeUpdate(Cancel As Integer)
Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me.Text78.Value
    stLinkCriteria = "[Sap Co No]=" & "'" & SID & "'"

    'Check issue table for duplicate Sapco
    If DCount("Text78", "Issues", stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning This SapCo Number " _
             & SID & " has already been entered into SCRM!" _
             & vbCr & vbCr & "You will now been taken to that Issue. You can Edit it from there.", _
               vbInformation, "Duplicate Information"
        'Go to record of original Sapco
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If

    Set rsc = Nothing

End Sub

cheers

**Stewart Ross** · Mar 3 '08, 05:17 PM

Hi Dale. I have inadvertently led you astray - sorry! The function is called ReturnUserName( ), not UserName(), which changes the call in your timestampline to
[code=vb]Me.Comment = Me.Comment & " " & vbNewLine & ReturnUserName( ) & " " & Now & ": " & Me.NotesMemoFie ld & vbNewLine[/code]Apologies for the overlook

-Stewart

**dale5804** · Mar 3 '08, 05:30 PM

Originally posted by Stewart Ross Inverness

Hi Dale. I have inadvertently led you astray - sorry! The function is called ReturnUserName( ), not UserName(), which changes the call in your timestampline to
[code=vb]Me.Comment = Me.Comment & " " & vbNewLine & ReturnUserName( ) & " " & Now & ": " & Me.NotesMemoFie ld & vbNewLine[/code]Apologies for the overlook

-Stewart

didnt work, have changed to ReturnUsername (capital R & U, small n) and same problem???

**Stewart Ross** · Mar 3 '08, 07:25 PM

Originally posted by dale5804

didnt work, have changed to ReturnUsername (capital R & U, small n) and same problem???

Hi Dale. Remove the definition of the ReturnUsername function from the form code module and place it in a public module instead (listed under Modules in the VBE editor window, or in the Module window of the Access database. Create a new module under any suitable name if there is not one already present). I did not know there were two different form code modules involved. The second one does not see the first module's functions, it would appear.

I have tried your memo update procedure and found it working when the definition of function ReturnUsername is in scope with the update routine. If you move the code to a public module this should resolve the problem altogether.

-Stewart

**dale5804** · Mar 3 '08, 08:35 PM

Originally posted by Stewart Ross Inverness

Hi Dale. Remove the definition of the ReturnUsername function from the form code module and place it in a public module instead (listed under Modules in the VBE editor window, or in the Module window of the Access database. Create a new module under any suitable name if there is not one already present). I did not know there were two different form code modules involved. The second one does not see the first module's functions, it would appear.

I have tried your memo update procedure and found it working when the definition of function ReturnUsername is in scope with the update routine. If you move the code to a public module this should resolve the problem altogether.

-Stewart

hi...sorry, as you may have guessed, still new at this vb stuff. not quite sure what your telling me to do.