Results 1 to 7 of 7
  1. #1
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192

    Problem in Login Form

    Dear members.
    I am using a MS Access 2013 DB & the Login Form contains the following VBA code:

    Option Compare Database
    Option Explicit

    Private Sub btnLogin_Click()
    Dim rs As Recordset

    Set rs = CurrentDb.OpenRecordset("tblUser", dbOpenSnapshot, dbReadOnly)

    rs.FindFirst "txtUserName ='" & Me.UserName & "'"

    If IsNull([UserName]) Then
    MsgBox "You must enter an User Name"


    Me.UserName.SetFocus
    Exit Sub
    End If

    If IsNull([Passwrd]) Then
    MsgBox "You must enter Pass Word"
    Me.Passwrd.SetFocus
    Exit Sub
    End If

    If rs.NoMatch = True Then
    Me.lblWrongUser.Visible = True
    Me.UserName.SetFocus
    Exit Sub
    End If
    Me.lblWrongUser.Visible = False

    If rs!txtUserPassword <> Me.Passwrd Then
    Me.lblWrongPass.Visible = True
    Me.Passwrd.SetFocus
    Exit Sub
    End If
    Me.lblWrongPass.Visible = False

    TempVars("UserType") = rs!UserType_ID.Value

    If rs!UserType_ID = 2 Then
    Dim prop As Property
    On Error GoTo SetProperty
    Const DB_Boolean As Long = 1
    Set prop = CurrentDb.CreateProperty("AllowBypasskey", DB_Boolean, False)

    CurrentDb.Properties.Append prop

    SetProperty:
    If MsgBox("Would you like to turn on the bypass key?", vbYesNo, "Allow Bypass") = vbYes Then
    CurrentDb.Properties("AllowBypassKey") = True
    Else
    CurrentDb.Properties("AllowBypassKey") = False
    End If
    End If

    DoCmd.Close acForm, "frmLogon", acSaveNo

    Dim intStore As Integer
    intStore = Nz(DCount("[Factory_ID]", "[InsurancePositionQry]"), 0)

    If intStore = 0 Then
    DoCmd.OpenForm "Switchboard"
    ElseIf intStore = 1 Then
    If MsgBox("For one Factory, insured value is less than stock value." & _
    vbCrLf & vbCrLf & "Would you like to see the details and analyze the reason now?", _
    vbYesNo + vbCritical, "CAUTION....FACTORY SHORT OF INSURANCE...") = vbYes Then
    DoCmd.OpenForm "InsurancePositionForm", acNormal
    Else
    DoCmd.OpenForm "Switchboard"
    End If
    Else
    If MsgBox("For " & intStore & " Factories, insured value is less than stock value." & _
    vbCrLf & vbCrLf & "Would you like to see the details and analyze the reason now?", _
    vbYesNo + vbCritical, "CAUTION....FACTORIES SHORT OF INSURANCE...") = vbYes Then
    DoCmd.OpenForm "InsurancePositionForm", acNormal
    Else
    DoCmd.OpenForm "Switchboard"
    End If
    End If
    End Sub

    Private Sub Form_Open(Cancel As Integer)
    Me.UserName.SetFocus
    End Sub

    The above code I got from Steve Bishop's Access 2013 YouTube lessons and working fine for the past about 2 years. Last Week I was creating an new data entry form & after that on Login the following message popped up:

    Run-time error '3367' :
    Cannot append. An object with the name asready exists in the
    collection.

    On debugging the error shows in the following line:

    CurrentDb.Properties.Append prop

    Even after removing the newly created form, the same message pops up. However I removed the particular code line & it works fine. I request the experts to please analyze the reason & also advise if it is advisable to permanently remove that line.

    Regards

    Alex

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You can't create a property that already exists.
    So in that sense you are safe to remove it in this case
    However, as a general rule, it would be better to check whether the property exists and add it if it doesn't

    I do hope the bypass key isn't enabled for standard users....
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192
    Thanks ridders52 for your response.

    So I can remove the code line CurrentDb.Properties.Append prop which may not give problems in future.
    However, can you just guide me how to check whether the property exists?

    Regards
    Alex

  4. #4
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192
    ridders 52

    The bye pass key is not enabled for standard users.

    Alex

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954

    Post

    I'll give you two answers to this

    1. Manage the error using error handling:

    Code:
    Private Sub btnLogin_Click()
    
    On Error GoTo Err_Handler
    
    All your procedure code goes here
    
    Exit_Handler:
        Exit Sub
    
    Err_Handler:
       'ERR 3367 property already exists
       If Err = 3367 Then Resume Next 
    
        MsgBox "Error " & Err.Number & " in btnLogin_Click procedure : " & Err.Description
        Resume Exit_Handler

    2. Check property exists:

    Add this function to a standard module
    Code:
    Public Function HasProperty(obj As Object, strPropName As String) As Boolean    'Purpose:   Return true if the object has the property.
        Dim varDummy As Variant
        
        On Error Resume Next
        varDummy = obj.Properties(strPropName)
        HasProperty = (Err.Number = 0)
    End Function
    Now modify your code as shown below

    Code:
    Private Sub btnLogin_Click()
    
    On Error GoTo Err_Handler
    
    First part of your procedure code goes here
    
    'test if property exists - if not create it
       If HasProperty(CurrentDb, "AllowByPassKey") = False Then
            Set prop = CurrentDb.CreateProperty("AllowBypasskey", DB_BOOLEAN, False)
            CurrentDb.Properties.Append prop
        End If
    
    
    Rest of your procedure code here
    
    Exit_Handler:
        Exit Sub
    
    Err_Handler:
    
     '   If Err = 3367 Then Resume Next 'OMIT as no longer needed
        MsgBox "Error " & Err.Number & " in btnLogin_Click procedure : " & Err.Description
        Resume Exit_Handler
    I would strongly recommend all your procedures include error handling similar to that above
    Last edited by isladogs; 04-23-2018 at 06:16 AM. Reason: added comment line
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192
    Thanks a lot ridders52, your solution works fine.
    Alex

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're welcome.
    Forgot to say that the HasProperty function is one of Allen Browne's
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-17-2015, 10:59 AM
  2. Login Form Problem with VBA
    By data808 in forum Access
    Replies: 31
    Last Post: 03-21-2014, 09:36 AM
  3. Replies: 3
    Last Post: 03-17-2014, 10:23 AM
  4. Problem with User Login and Password
    By sk88 in forum Access
    Replies: 3
    Last Post: 12-16-2011, 08:11 AM
  5. Login form with nt login
    By hitesh_asrani_j in forum Forms
    Replies: 6
    Last Post: 09-22-2011, 11:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums