login / username

[dale5804]

hi.... i am used a tutorial found at...http://www.databasedev.co.uk/login.html... to create a login form. works fine, what i am wanting to do is on one of my forms, where data is entered i use the following code..

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 & Now & ": " & Me.NotesMemoField & vbNewLine
  End If
   InputData.Caption = "Input New Notes"
   Me.NotesMemoField = ""
   NotesMemoField.Visible = False
End If
End Sub
 

to move the data entered to another memo box on the form where the data is appended to. as you can see it date/time stamps the data entered as it moves it across.
is there anyway of altering either/both code so after the date/time stamp it also stamps the name of the person that logged on the database from the login form from the tutorial???
cheers

[Stewart Ross]

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:

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

Change the AfterUpdate event of the cboEmployee combo in the databasedev code to set the global, as indicated below:

Private Sub cboEmployee_AfterUpdate()
    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
 

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:

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

-Stewart

[dale5804]

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

[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

[Stewart Ross]

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]

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

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]

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

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

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

[dale5804]

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

Me.Comment = Me.Comment & " " & vbNewLine & UserName() & Now & ": " & Me.NotesMemoField & 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]

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

Me.Comment = Me.Comment & " " & vbNewLine & UserName() & Now & ": " & Me.NotesMemoField & 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]

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

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

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]

Hi Dale. I have inadvertently led you astray - sorry! The function is called ReturnUserName(), not UserName(), which changes the call in your timestampline to

Me.Comment = Me.Comment & " " & vbNewLine & ReturnUserName() & " " & Now & ": " & Me.NotesMemoField & vbNewLine

Apologies for the overlook

-Stewart

[dale5804]

Hi Dale. I have inadvertently led you astray - sorry! The function is called ReturnUserName(), not UserName(), which changes the call in your timestampline to

Me.Comment = Me.Comment & " " & vbNewLine & ReturnUserName() & " " & Now & ": " & Me.NotesMemoField & vbNewLine

Apologies for the overlook

-Stewart

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

[Stewart Ross]

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]

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.

[Stewart Ross]

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

Hi Dale. Simplest way if you are not sure is to create a new, blank, module (select Modules in the database window, then New). The VB editor will open in a new, blank, module. From the VB editor window go to the form code module in which the ReturnUsername function is currently placed, select the function, then cut that definition from the form's module. Re-select the new, blank, code module and paste the function in there instead. It is so short you could retype it if you have to.

Save all changed modules. Compile the code to ensure there are no accidental errors.

This should resolve the problem you are experiencing, which is because one form's code module cannot see the definition of ReturnUsername in the other.

-Stewart

[dale5804]

do i need to call the module anything for it to work?

[Stewart Ross]

do i need to call the module anything for it to work?

Any module name will do, Dale. Even t he default of Module1, although I would change it to something such as General Routines.
-Stewart

[dale5804]

Any module name will do, Dale. Even t he default of Module1, although I would change it to something such as General Routines.
-Stewart

thought so, so i did what u said, and still having the same problem. any other ideas. cheers

[Stewart Ross]

thought so, so i did what u said, and still having the same problem. any other ideas. cheers

No other ideas, sorry. It works in my test system as long as the function is in scope. You must be overlooking something, but I'm afraid at this distance I simply can't think of any relevant explanation. You have declared it as a Public function? Have you tested it in the VB immediate window as I suggested much earlier? You could set break points and step through the code one line at a time, but I think you would not be in a position to benefit from this unless you know what you are looking for. I would recommend you read a good introductory text on VBA programming.

I use system functions to retrieve the username of the currently-logged-in user from the windows API in my own systems without any hitches at all. Although I am not querying a value supplied by the user, the return values are done in a similar way to what you have been advised.

Sorry it has not worked out for you after all the effort you have put in.

-Stewart

[dale5804]

hi Stewart, thanks for all you help. i will keep trying, but you've been a star. wouldn't have got this far without your advice. cheers

[Stewart Ross]

One final thing: move the global variable Username to your new code module as well. Same issue - the variable needs to be in scope.

-Stewart

[Stewart Ross]

Hi again Dale. One other overlook on my part is that the ReturnUsername function should have been typed as a String. This is what happens when I take a working function of my own and change it to post it.

The global and the function, both now in your public module, should be:

Public UserName as String 
 
Public Function ReturnUserName () as String
    ReturnUserName = UserName
End Function
 

and final thing to try, because there are no parameters for ReturnUserName remove the brackets in timestamp function call:

...vbNewLine & ReturnUserName & ...

So near yet so far...

-Stewart

[dale5804]

Hi again Dale. One other overlook on my part is that the ReturnUsername function should have been typed as a String. This is what happens when I take a working function of my own and change it to post it.

The global and the function, both now in your public module, should be:

Public UserName as String 
 
Public Function ReturnUserName () as String
    ReturnUserName = UserName
End Function
 

and final thing to try, because there are no parameters for ReturnUserName remove the brackets in timestamp function call:

...vbNewLine & ReturnUserName & ...

So near yet so far...

-Stewart

cheers, getting there, the code doesnt throw an error up, but when the data is transferred, it prints the user as '1' then the time/date stamp?? I've also tried and removed the brackets as you suggested, and i stll get 1 as the username???

[Stewart Ross]

[font=Arial]

cheers, getting there, the code doesnt throw an error up, but when the data is transferred, it prints the user as '1' then the time/date stamp?? I've also tried and removed the brackets as you suggested, and i stll get 1 as the username???

Hi Dale. I did not check what was in the combo box in the DatabaseDev code; it is clearly the employee number which is in the default column. This one is easily fixed.[/font]

[font=Arial]In the cboEmployee after update, change the code as follows:

Private Sub cboEmployee_AfterUpdate()
    UserName = cboEmployee.column(1) '<<< 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

This will refer to the name in the second column of the combo box (referred to as column(1) because the numbering is from 0).
-Stewart
[/font]

[dale5804]

[font=Arial]Hi Dale. I did not check what was in the combo box in the DatabaseDev code; it is clearly the employee number which is in the default column. This one is easily fixed.[/font]

[font=Arial]In the cboEmployee after update, change the code as follows:

Private Sub cboEmployee_AfterUpdate()
    UserName = cboEmployee.column(1) '<<< 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

This will refer to the name in the second column of the combo box (referred to as column(1) because the numbering is from 0).
-Stewart
[/font]

thankyou so much Stewart. it works :-)