[tbzserge]

Hi there. I'm trying to do validation when trying to connect to excel in vb6.

Suppose when the login textbox left blank and we press enter, it prompts out a message. But for my case, it also prompts out this error code.

Run-time error 91:
Object variable or with block variable not set.

Below is my code for that part that i did.

Private Sub txtLogin_KeyDown(KeyCode As Integer, Shift As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

If KeyCode = 13 Then
        If Trim(txtLogin.Text = "") Then
                MsgBox "Please Login using login ID!", vbOKOnly, "Field Required!"
                txtLogin.SetFocus
           Else
           
           Dim oWorkbook As Workbook
           Set oWorkbook = Excel.Workbooks.Open(FileName:="" & Trim(GetINISetting("Excel_Path", "DirectoryPath", App.Path & "\SETTINGS.INI")) & "LoginTable1.xlsx", Password:="" & Trim(GetINISetting("Pwd", "Pass", App.Path & "\SETTINGS.INI")) & "")
           Set cn = New ADODB.Connection
           Set rs = New ADODB.Recordset
          
           cn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Trim(GetINISetting("Excel_Path", "DirectoryPath", App.Path & "\SETTINGS.INI")) & "LoginTable1.xlsx;Extended Properties=""Excel 12.0;HDR=YES;""")
           
           If rs.State = adStateOpen Then rs.Close
    rs.Open ("select * from [sheet1$] where Login_ID ='" & txtLogin.Text & "'"), cn, adOpenStatic, adLockReadOnly
If rs.RecordCount > 0 Then
    txtLogin.Text = rs!Login_ID
    Login.Visible = False
    FormMain1.Show
Else
    MsgBox "Incorrect Login ID.", vbOKOnly, "Message"
    txtLogin.Text = ""
    txtLogin.SetFocus
End If
        End If
        oWorkbook.Close
        Set oWorkbook = Nothing
        End If
        
End Sub

The mistake it shows is at
oWorkbook.Close that line.

Any help would be much appreciated. Thanks.

This post has been edited by tbzserge: 28 November 2012 - 01:00 AM

[maj3091]

That's because you declare the workbook inside the IF statement, so it only gets declared when you press enter.

So, if you don't press enter and try to close it, you haven't created it in the first place, hence the error.

[tbzserge]

Thanks for the reply.

But then even i have declared

Dim oWorkbook As Workbook

outside the if statement, i'm still having this error.

Is it i have missed out anything else?

Thanks.

[maj3091]

Sorry, if I've confused you....just woke up here and I misread the code as the tabs are all over the place!

It's not so much the declaration, but the creation of it (SET).

Check the paths through the code and you will find that there is a route that tries to close it, without it ever being opened.

[tbzserge]

Thanks for the prompt reply.

I have tried to check all the possible routes in the codes but still couldn't find out which is the culprit that tries to close the excel, without it ever being opened.

Possible if you can guide me a bit.

Thanks.

[maj3091]

If you could repost your code with it indented correctly, then that would make it easier to spot.

[BobRodes]

Put your line 11 on line 4, it's cleaner.

If you clean up your indents, as maj suggests, you will see that if you hit the enter key with an empty login textbox, you'll get the error. Then you might also see that every time you press a key in your textbox for the first time your textbox is empty. A textbook example on why it makes sense to keep your code neat and tidy.

So, you need to rethink your way of doing things. You shouldn't have this in the keydown event in the first place. Rather, this should be on a command button that says something like "Submit". Then you can use the keypress event (not keydown) to enable the command button as soon as there is any text in the box.

Private Sub txtLogin_KeyDown(KeyCode As Integer, Shift As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim oWorkbook As Workbook

If KeyCode = 13 Then
    If Trim(txtLogin.Text = "") Then
        MsgBox "Please Login using login ID!", vbOKOnly, "Field Required!"
        txtLogin.SetFocus
    Else       
        Set oWorkbook = Excel.Workbooks.Open(FileName:="" & Trim(GetINISetting("Excel_Path", "DirectoryPath", App.Path & "\SETTINGS.INI")) & "LoginTable1.xlsx", Password:="" & Trim(GetINISetting("Pwd", "Pass", App.Path & "\SETTINGS.INI")) & "")
        Set cn = New ADODB.Connection
        Set rs = New ADODB.Recordset
       
        cn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Trim(GetINISetting("Excel_Path", "DirectoryPath", App.Path & "\SETTINGS.INI")) & "LoginTable1.xlsx;Extended Properties=""Excel 12.0;HDR=YES;""")
        
        If rs.State = adStateOpen Then rs.Close
        rs.Open ("select * from [sheet1$] where Login_ID ='" & txtLogin.Text & "'"), cn, adOpenStatic, adLockReadOnly
        If rs.RecordCount > 0 Then
            txtLogin.Text = rs!Login_ID
            Login.Visible = False
            FormMain1.Show
        Else
            MsgBox "Incorrect Login ID.", vbOKOnly, "Message"
            txtLogin.Text = ""
            txtLogin.SetFocus
        End If
    End If
    oWorkbook.Close
    Set oWorkbook = Nothing
End If        
End Sub

This post has been edited by BobRodes: 01 December 2012 - 12:49 PM