Results 1 to 9 of 9
  1. #1
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581

    Log in form

    I created a log in form for my database. It's sitting on a network. When the network is up and running, there is not problem. When the network is down, Access send the user to the debugger. I was wondering how I can get Access to put out a message box instead of going to the debugger. Here's my code:

    If IsNull(txtUsername) Then
    MsgBox "Invalid username"


    Exit Sub
    End If
    If IsNull(txtPassword) Then
    MsgBox "Invalid Password"
    Exit Sub
    End If
    Dim X As Long
    X = Nz(DLookup("EmployeesID", "qryEmployeesCurrent", "Username='" & txtUsername & "' AND Password='" & txtPassword & "'"))

    If X > 0 Then
    ' We have a valid user
    DoCmd.OpenForm "frmMainMenu"
    Forms!frmMainMenu!txtUserID = X
    Forms!frmMainMenu!txtUsername = txtUsername
    DoCmd.Close acForm, "frmLogin"
    Else
    MsgBox "invalid Login"
    End If

  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 haven't specified what error occurs in that situation

    When the network is down, am I correct in assuming the database doesn't have access to the tables in qryEmployesCurrent so it can't check the user name & password.
    Therefore presumably all database tables aren't available and the database needs to close.

    So you need to check (before users enter their details) whether the shared network drive is available, and if not, show a message then exit the database

    I do something similar for a split database with a SQL server backend.
    When the login form loads, it runs the code below.

    Code:
    Public Function CheckSQLServerConnection() As Boolean 'CR v5238
    
    'returns true if SQL Server is running and the listed database is available
    'Otherwise false
    
    On Error GoTo Err_Handler
    
    Dim cnn As ADODB.Connection
    
    CheckSQLServerConnection = False
    
        Set cnn = New ADODB.Connection
        'cnn.Open "Provider=SQLOLEDB;Data Source=SERVER;Initial Catalog=DB;User ID=sa;Password=PASSWORD"
      '  cnn.Open "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=SDABE;User ID=SDAuser;Password=SDApassword"
        cnn.Open "Provider=SQLOLEDB;Data Source=" & GetSQLServerName() & ";Initial Catalog=SDABE;User ID=SDAuser;Password=SDApassword"
        
        If cnn.State = adStateOpen Then
            CheckSQLServerConnection = True
            cnn.Close
        End If
    
        'Debug.Print CheckSQLServerConnection
        
    Exit_Handler:
        Exit Function
        
    Err_Handler:
        'CheckSQLServerConnection = False
        'err = -2147467259 'can't open SQL database - server or database name incorrect or SQLServer not running
        'err = -2147217843 'incorrect UserID / password
        If Err = -2147467259 Or Err = -2147217843 Then
            DoCmd.Close acForm, "frmLogoutTimer" 'prevents err = 3146 in frmLogoutTimer
        Else
            MsgBox "Error " & Err.Number & " in CheckSQLServerConnection procedure : " & vbNewLine & _
                "  " & Err.Description & "   ", vbCritical, "SQL Server error"
        End If
        
        Resume Exit_Handler
        
    End Function
    If the output is false, the database is then closed 'gracefully'.
    You should be able to do something similar with a SQL or Access backend database
    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
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I'm not using a SQL server. I was wondering if there was a simpler code. Something that would just go to a message box and then stop without having to exit the program or go to the debugger. I have a small part of the database attached. I removed the Employees table so it will give the error I've been having.
    Attached Files Attached Files

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    OK several things before I give the solution:

    1. As you have a login form you should have provided the user name / password combination required
    2. You INCLUDED tblEmployees! I renamed it to trigger the error
    3. You should add Option Explicit at the top of EVERY module to ensure compile errors don't slip through.
    To set this by default, tick Require Variable Declaration in the VBE Editor Options dialog box
    The database you supplied won't compile because of errors - you need to identify & fix these one by one
    4. Why is your sample database so large?
    I decompiled & compacted it - no change. You have no attachment fields (good!). Lots of forms but even so ....
    5. Strongly recommend you name controls something meaningful - Command0 isn't helpful
    6. Also recommend you use error handling in your procedures

    Anyway, the solution for your issue is error handling.
    I added it & identified the error as 3078 so I then applied a rule for that error
    In this case, to show a message & quit the app ... modify as you wish

    Code:
    Private Sub Command0_Click()
    
    On Error GoTo Err_Handler
    
    
        If IsNull(txtUsername) Then
            MsgBox "Invalid username"
            Exit Sub
        End If
        If IsNull(txtPassword) Then
            MsgBox "Invalid Password"
            Exit Sub
        End If
        Dim X As Long
        X = Nz(DLookup("EmployeesID", "qryEmployeesCurrent", "Username='" & txtUsername & "' AND Password='" & txtPassword & "'"))
        
        If X > 0 Then
            ' We have a valid user
            DoCmd.OpenForm "frmMainMenu"
            Forms!frmMainMenu!txtUserID = X
            Forms!frmMainMenu!txtUsername = txtUsername
            DoCmd.Close acForm, "frmLogin"
        Else
            MsgBox "invalid Login"
        End If
        
    Exit_Handler:
        Exit Sub
        
    Err_Handler:
        If Err = 3078 Then
            MsgBox "The network connection is currently not available." & vbCrLf & _
                "Please try again later ..." & vbCrLf & vbCrLf & _
                "This application will now close", vbExclamation, "Network error"
            Application.Quit
        Else
            MsgBox "Error " & Err.Number & " in frmLogin Command0_Click procedure: " & Err.Description, vbCritical, "Program Error"
            GoTo Exit_Handler
        End If
        
    End Sub
    Modified version attached with this code
    Attached Files Attached Files
    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

  5. #5
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    You're awesome. You know your stuff. Thanks. That worked perfectly. I'm working hard at learning how to do this. I appreciate the help. Where do I find the Require Variable Declaration in the VBE Editor Options dialog box? Also, I'm not sure about what you were saying about compile. How do I go about fixing these errors?

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by UT227 View Post
    Where do I find the Require Variable Declaration in the VBE Editor Options dialog box? Also, I'm not sure about what you were saying about compile. How do I go about fixing these errors?
    Open the VB Editor. Click Tools ..Options then select the Editor tab & tick the box highlighted below

    Click image for larger version. 

Name:	OptionExplicit.PNG 
Views:	27 
Size:	13.6 KB 
ID:	32182

    That will add the line Option Explicit to all NEW modules
    Existing modules will need the line added manually as shown above
    This is one of the most important ways of preventing databases having built in errors
    It means that any undeclared variables will prevent the database being compiled until the errors are fixed

    To do this, click Debug ... Compile ...
    The first error it found is on the form frmIssueUniform - cboEquipID doesn't exist

    Click image for larger version. 

Name:	CompileError.PNG 
Views:	27 
Size:	17.0 KB 
ID:	32181

    Fix the error by removing or updating the code or renaming the control as appropriate.
    Repeat until no more errors are found. At that point the compile menu item will be disabled.
    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

  7. #7
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    Sounds like I've got a lot to do. Thanks for the help.

  8. #8
    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.
    Hopefully not too much work but its a one off task - don't put it off!
    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

  9. #9
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I have one more question. Instead of quitting the application, how do I just go around the error? I have a code written in a form in the On Current. It locks the controls if I have a certain field checked. When trying to delete the record, it gives me an error 94 and goes to the debugger. If you quit the debugger or click END, it will delete the record. How would I just go around this and have it delete the record?
    Click image for larger version. 

Name:	error.jpg 
Views:	23 
Size:	102.1 KB 
ID:	32209

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

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