Recordset open/close problem.

[IndigoDragon]

Hi there,

I set up a recordset in the following way:

Sub GetMailData(theMailID As Integer, skipMode As String)
        
    ' Declare variables.
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer, r As Long, strSourceFile As String, strSQL As String, newString As String
        
    ' Get source file.
    strSourceFile = AnswerMailsForm.MailFileList.Value
    If strSourceFile = "" Then MsgBox "No file selected!" & Chr(13) & "Select a file from the list or" & Chr(13) & _
    "generate a new file.", vbOKOnly + vbExclamation, "Select file."
    
    strSourceFile = "myFile.xls"

    ' Set up database connection.
    Set cn = New ADODB.Connection
    On Error Resume Next
    cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;ReadOnly=True;" & "DBQ=" & strSourceFile & ";"

    On Error GoTo 0
    
    If cn Is Nothing Then
        MsgBox "Mailfile can not be found!", vbExclamation, ThisWorkbook.Name
        Exit Sub
    End If
    
    ' Construst sql string.
    strSQL = "SELECT * FROM [Mails] WHERE [MailID] LIKE '" ' & theMailID & "'"
    
    ' Open the recordset.
    Set rs = New ADODB.Recordset
    On Error Resume Next
    newString = strSQL & theMailID & "'"
    
       
    rs.Open newString, cn, adOpenStatic, adLockOptimistic, adCmdText
    
    On Error GoTo 0
    
    If rs Is Nothing Then
        MsgBox "Can not open file!", vbExclamation, ThisWorkbook.Name
        cn.Close
        Set cn = Nothing
        Exit Sub
    End If
    
    ' If end of file is reached.
    If rs.EOF = True Then Exit Sub
    
    ' *****************************
    ' etc., etc.
    
End Sub

The code returns the error: "Action not allowed when object is closed." On the line

 If rs.EOF = True Then Exit Sub

I don't get a message that the requested file can not be found or loaded...?

Am I overlooking something?

ThanX for Your Replies!

 

[IndigoDragon]

Hi, I solved the problem.
Thanx, 'anyone' for looking at my post.

cYa.