SQL Login form

[Pozzi]

I've created a login form for users to log into a database.

The User ID's & Passwords are stored within the DB. On starting my application I want to connect and open the database, then when the user enters the details carry out the nessary look-up.

Here's what I've done so far...

Option Explicit
    Dim adoCnn As New ADODB.Connection
 
Private Sub cmdCancel_Click()
 
    '* User cancelled, close application.
    Unload Me
    
End Sub
 
Private Sub cmdOK_Click()
 
    Dim adoRstOps As ADODB.Recordset
 
    Dim strUser As String
    Dim strPass As String
    Dim strSQL As String
    
    '* Check that user has entered numeric values.
    If Not IsNumeric(txtUser.Text) Then
        '* Not all numeric, prompt user.
        MsgBox "User ID must be numeric.", vbOKOnly + vbInformation, "Invalid User ID"
        txtUser.Text = ""
        txtUser.SetFocus
        Exit Sub
    End If
    
    If Len(txtPassword.Text) > 0 Then
        If Not IsNumeric(txtPassword.Text) Then
            '* Not all numeric, prompt user.
            MsgBox "Password must be numeric.", vbOKOnly + vbInformation, "Invalid Password"
            txtPassword.Text = ""
            txtPassword.SetFocus
            Exit Sub
        End If
    Else
        '* Password not entered, advise user.
        MsgBox "Please enter Password.", vbOKOnly + vbInformation, "Invalid Password"
        txtPassword.SetFocus
        Exit Sub
    End If
    
    strUser = txtUser.Text
    strPass = txtPassword.Text
    
    Set adoRstOps = New ADODB.Recordset
    adoRstOps.CursorLocation = adUseClient
    adoRstOps.CursorType = adOpenStatic
    
    strSQL = "SELECT CODE, PASSWORD FROM TB_OPERATORS"
    
    adoRstOps.Open strSQL, adoCnn, adOpenStatic, adLockOptimistic
    
    '* Check that records were returned i.e. User ID.
    If adoRstOps.RecordCount > 0 Then
        '* Record returned check password.
        If strPass = adoRstOps!PASSWORD Then
            MsgBox "You're in!"
        Else
            MsgBox "Password not valid.", vbOKOnly + vbInformation, "Invalid Password"
        End If
    Else
        MsgBox "User ID not valid.", vbOKOnly + vbInformation, "Invalid User ID"
        txtUser.SetFocus
    End If
    
End Sub
 
Private Sub Form_Load()
 
    '* Database connection properties,
    '* normally be either in an ini file or Registry.
    adoCnn.ConnectionTimeout = 25
    adoCnn.Provider = "sqloledb"
    adoCnn.Properties("Data Source").Value = "Datasource"
    adoCnn.Properties("Initial Catalog").Value = "Database"
    adoCnn.Properties("User ID").Value = "sa"
    adoCnn.Properties("Password").Value = "sapwd"
    
    '* Change mouse pointer whilst connecting.
    frmLogin.MousePointer = vbHourglass
    
    '* Open Database.
    adoCnn.Open
    
    '* Change mouse pointer back to default.
    frmLogin.MousePointer = vbDefault
    
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
 
    '* If connected close connection to Database.
    If adoCnn.State = adStateOpen Then
        adoCnn.Close
    End If
    
    End
    
End Sub
 
Private Sub txtUser_Change()
 
    '* Enable OK command button if entry greater than 0.
    If Len(txtUser.Text) > 0 Then
        cmdOK.Enabled = True
    Else
        '* Disable OK command button if entry is 0.
        cmdOK.Enabled = False
    End If
    
End Sub

The above code works fine.

I want the form to show and then establish the connection to the database, and in the event of the database not being available advise the user.

How can I do this?

I've tried putting the Form_Load() event code into the Form_GotFocus() event but the code doesn't run.

Any ideas?

Regards