Solved

Access VBA: Object Variable Not Set error occurs only after running code once before

Posted on 2014-10-02
18
575 Views
Last Modified: 2014-10-06
I have a piece of code that runs beautifully the first time I run it. The second time I click the button that runs it, I get Run-time error 91. "Object variable or With block variable not set"

If I debug, it highlights
Selection.Range(C, C.Offset(0, 1)).Merge

Open in new window


Once I reset everything and click the button that runs the code again, it works fine again.  I DON'T UNDERSTAND!

Here is the whole code. The line is 166:
Private Sub cmdExport_Click()
'On Error GoTo Error_Handler
Dim Db As Database, Rs As Recordset, sSQL As String, iW As Integer, iRow As Integer
'dim statements for new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRange As Excel.Range
'Add the field names in row 1
Dim i As Integer
Dim iNumCols As Integer
Dim TheTO As String
Dim TheSTOname As String
Dim TheStaffName As String
Dim TheActDesc As String
Dim C As Object
Dim Qd As QueryDef

'Initialize variables (this resets them so they do not hold old values)
sSQL = ""
iW = 0
iRow = 0
'oApp =
'oBook = Nothing
'oSheet = Nothing
'oRange = Nothing
i = 0
iNumCols = 0
TheTO = ""
TheSTOname = ""
TheStaffName = ""
TheActDesc = ""
'C = ""
'Qd = Null

SQL = "qry_report1"
Set Db = CurrentDb
Set Qd = Db.QueryDefs("qry_report1")

Set Rs = Qd.OpenRecordset(dbOpenDynaset, dbReadOnly)
Rs.MoveLast
Rs.MoveFirst

If Rs.RecordCount = 0 Then
    GoTo Bail
End If

Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
oApp.Visible = True
oApp.UserControl = True


'ok We want each task order to be on a row by itself in column A
'Headers
oSheet.Cells(1, 1).Value = "Task Order/Sub Tasks"

'oSheet.Cells(1, 2).Value = "Sub Tasks"

oSheet.Cells(1, 2).Value = "Staff"

oSheet.Cells(1, 3).Value = "Details"

TheTO = Rs!to
oSheet.Cells(2, 1).Value = TheTO
    Set C = oSheet.Cells(2, 1)
    With C
        
        .Font.Name = "Arial"
        .Font.Bold = True
        .Font.Size = 12
        .Interior.ColorIndex = 15
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeTop).Weight = xlThin
        .Borders(xlEdgeTop).ColorIndex = xlAutomatic
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).Weight = xlThin
        .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlEdgeRight).Weight = xlThin
        .Borders(xlEdgeRight).ColorIndex = xlAutomatic
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeLeft).Weight = xlThin
        .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
        .Borders(xlInsideVertical).LineStyle = xlContinuous
        .Borders(xlInsideVertical).Weight = xlThin
        .Borders(xlInsideVertical).ColorIndex = xlAutomatic
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
        .Borders(xlInsideHorizontal).Weight = xlThin
        .Borders(xlInsideHorizontal).ColorIndex = xlAutomatic
     End With

TheSTOname = Rs!STO
oSheet.Cells(3, 1).Value = TheSTOname
TheStaffName = Rs!TeamName
oSheet.Cells(3, 2).Value = TheStaffName
TheActDesc = Rs!ActDesc
oSheet.Cells(4, 3).Value = TheActDesc

'this is the last row I just wrote to   
iRow = 4

Rs.MoveNext

'what are these for? when are these used?
iNumCols = 3


Do Until Rs.EOF

    If Rs!to = TheTO Then
        If Rs!STO = TheSTOname Then
            If TheStaffName = Rs!TeamName Then
                If TheActDesc = Rs!ActDesc Then
                    Rs.MoveNext
                Else
                    iRow = iRow + 1
                    oSheet.Cells(iRow, 3).Value = Rs!ActDesc
                    Rs.MoveNext
                End If
            Else
                'iRow = iRow + 1
                oSheet.Cells(iRow, 2).Value = Rs!TeamName
                TheStaffName = Rs!TeamName
                Rs.MoveNext
            End If
        Else
            iRow = iRow + 1
            oSheet.Cells(iRow, 1).Value = Rs!STO
           TheSTOname = Rs!STO
            TheStaffName = ""
            
            Rs.MoveNext
        End If
    Else
        iRow = iRow + 1
        oSheet.Cells(iRow, 1).Value = Rs!to
       'FormatTO (oSheet.Cells(iRow,1),"Arial")
        Set C = oSheet.Cells(iRow, 1)

        
        With C
            .Font.Name = "Arial"
            .Font.Bold = True
            .Font.Size = 12
            .Interior.ColorIndex = 15
            .Borders(xlEdgeTop).LineStyle = xlContinuous
            .Borders(xlEdgeTop).Weight = xlThin
            .Borders(xlEdgeTop).ColorIndex = xlAutomatic
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
            .Borders(xlEdgeBottom).Weight = xlThin
            .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
            .Borders(xlEdgeRight).LineStyle = xlContinuous
            .Borders(xlEdgeRight).Weight = xlThin
            .Borders(xlEdgeRight).ColorIndex = xlAutomatic
            .Borders(xlEdgeLeft).LineStyle = xlContinuous
            .Borders(xlEdgeLeft).Weight = xlThin
            .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
            .Borders(xlInsideVertical).LineStyle = xlContinuous
            .Borders(xlInsideVertical).Weight = xlThin
            .Borders(xlInsideVertical).ColorIndex = xlAutomatic
            .Borders(xlInsideHorizontal).LineStyle = xlContinuous
            .Borders(xlInsideHorizontal).Weight = xlThin
            .Borders(xlInsideHorizontal).ColorIndex = xlAutomatic

Selection.Range(C, C.Offset(0, 1)).Merge

            .WrapText = True
            .RowHeight = 54.75
            
         End With

        TheTO = Rs!to
        Rs.MoveNext
        
    End If


skip:
Loop

iNumCols = IIf(w > 0, w, iNumCols)
'Format the header row as bold
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
'Size the columns
.Columns("A:A").ColumnWidth = 40
.Columns("B:B").ColumnWidth = 22
.Columns("C:C").ColumnWidth = 73.44
End With

Bail:
    Rs.Close
    Set Rs = Nothing
    Set oApp = Nothing
    Db.Close
    Set Db = Nothing
    Qd.Close
   Set Qd = Nothing
   
    Exit Sub
    
Error_Handler:
    MsgBox Error$
    Resume Bail
  
End Sub

Open in new window


Help!
0
Comment
Question by:Megin
  • 8
  • 5
  • 3
  • +1
18 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 40358216
I just took a quick glance but I notice you're destroying object variables at the end of the routine but neither C nor oSheet get destroyed.  Perhaps this is causing a conflict the second time through?   Try destroying those along with the other object variables and see if the problem goes away.

OM Gang
0
 
LVL 57
ID: 40358222
Not sure about the error....I would step through with F8 (put a STOP at the top of the procedure) and see if you are executing the procedure the same way each time.

Also, couple other comments:

1. On this, this is wasteful:

Set Rs = Qd.OpenRecordset(dbOpenDynaset, dbReadOnly)
Rs.MoveLast
Rs.MoveFirst

If Rs.RecordCount = 0 Then
    GoTo Bail
End If

  No need to movelast/first unless you want an accurate count.  If all you need to know is if there are records, you can either:

a. check for BOF and EOF
b. That .RecordCount >0

2. On Clean-up, make sure you close anything you open, and set all object variables = nothing.   I don't see "C" in there.  You should be taking care of all your object variables as well.

3. Add an option explicit to the top of the module.  Do a compile and make sure you have no errors.

Jim.
0
 
LVL 57
ID: 40358236
And on the error:

Selection.Range(C, C.Offset(0, 1)).Merge

  have you done a select prior to this (don't see one)?  What is it your trying to do there?  

Jim.
0
 

Author Comment

by:Megin
ID: 40358307
Jim: I have not done a select prior to this. When I try to, I get a different error the second time I run it. When I have:
Range(C, C.Offrset(0,1)).select
Selection.merge

Open in new window


I get Run-time error 1004 "Method 'Range' of object'_Global' failed"

But still only the second time I try to run it. The first time it works great.

OmGang: I just ran it with osheet and C both set to nothing at the end and the code still has the same problem. Is that what you meant by destroying them? I am not fluent in vba yet.
0
 
LVL 28

Expert Comment

by:omgang
ID: 40358320
<<Is that what you meant by destroying them?>>
Yes.

OM Gang
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40358761
You have to be very strict closing the Excel objects in reverse order:

Bail:
    Rs.Close
    Set Rs = Nothing
    Qd.Close
    Set Qd = Nothing  
    Db.Close
    Set Db = Nothing
    Set oRange = Nothing
    Set oSheet = Nothing
    Set oBook = Nothing
    Set oApp = Nothing

/gustav
0
 
LVL 57
ID: 40359228
<<Range(C, C.Offrset(0,1)).select>>

 You need to name an object there that your selecting the range through (a sheet for example).  This might be of help:

How to select cells/ranges by using Visual Basic procedures in Excel
http://support2.microsoft.com/kb/291308

  There are a number of examples there that show the various ways you can select cells and ranges.  

 Since your early bound, intellisense should be working and you should be getting clean compiles.    When you type that statement, does it resolve and do you have any compile errors?

 The other approach is to put a STOP above the offending statement and execute.   Now when you hit that stop, you can call up the debug window (CTRL/G), and execute commands to figure out what's allowed or not (that's usually how I feel my way though an object model).   You can also use things like the locals window to see exactly what has and has not been instantiated at that point.

 And again, it would be helpful if you explained what your attempting to do at that point.

<<But still only the second time I try to run it. The first time it works great.>>

  Which suggests either:

a. You don't fall through that ELSE section on the first time around, but do on the second.

b. That your destroying something before you hit the else section.

  Again, stepping through with F8 will help you figure it out.   While doing that, you may want to limit the records in the record set by adding some criteria to qry_report1 so that it is not a long process.   Get it working with a few records, step through and verify your logic, then open it back up and see if it works.

 You have a number of things going on in that routine, and without understanding exactly what your trying to do and have the data, it's difficult to offer specifics, which is why I'm giving you general techniques that you can use to debug.

 That also helps you not only with this, but will in the future as well.

Jim.
0
 

Author Comment

by:Megin
ID: 40359795
Jim: I tried all of that and I am still lost.

If the error happened the second time the loop ran, I could see why some of that would be an issue. But it happens after I run the code completely the first time. I click on my "Run Report" button in my form once and everything goes great. The report is created and everything happened as it should. I try to run the report again and the error happens.

The trouble piece (Range (C, C.Offset(0,1)).select    Selection.merge) is an addition to the formatting. It is just supposed to take the cell that is being formatted in that part, and merge it with the cell next to it.

Gustav: I made those changes and am still getting the same error.
0
 
LVL 28

Expert Comment

by:omgang
ID: 40359853
Possible to upload a sample db with all necessary components and some sample data?
OM Gang
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40359869
You also need this:

Dim C As Excel.Range

Bail:
     Rs.Close
     Set Rs = Nothing
     Qd.Close
     Set Qd = Nothing  
     Db.Close
     Set Db = Nothing
     Set C = Nothing
     Set oRange = Nothing
     Set oSheet = Nothing
     Set oBook = Nothing
     Set oApp = Nothing

And as Jim mentioned, get rid of the selection stuff. Replace by using a Range.

/gustav

Set C = oSheet.Cells(iRow, 1)
0
 

Author Comment

by:Megin
ID: 40359961
Okay. Attached is a copy of the database. The form to go to is frm_Reports and click the button "Full Report."

Thank you for looking at this!
ForEE.accdb
0
 
LVL 28

Expert Comment

by:omgang
ID: 40360246
Changes.

1) How you instantiate the Excel App
'Dim oApp As New Excel.Application
Dim oApp As Excel.Application

2)This doesn't work so we need to change it
'Range(C, C.Offrset(0, 1)).Select
Range(C.Cells, C.Cells.Offset(0, 1)).Select

3)Additional declarations and instantiate Excel
'Dim C As Object
Dim C As Excel.Range
Dim Qd As QueryDef

'added by OMGang  -- I added Option Explicit at the top of the module (as suggested by  Jim in the second post) so we can be sure all variables are declared
Dim SQL As String
Dim w As Integer

Set oApp = Excel.Application

4)better job of cleaning up as recommended by gustav
Bail:
    Rs.Close
     Set Rs = Nothing
     Qd.Close
     Set Qd = Nothing
     Db.Close
     Set Db = Nothing
     
     Set C = Nothing
     Set oRange = Nothing
     Set oSheet = Nothing
     
     Set oBook = Nothing
     Set oApp = Nothing
   
   
    Exit Sub


OM Gang
0
 
LVL 28

Expert Comment

by:omgang
ID: 40360247
Works as you want it to now.
OM Gang
0
 

Author Comment

by:Megin
ID: 40360608
Nope. Still doesn't work for me.
Maybe there is a problem with my version of Access. I am getting run time error 1004: Method 'range' of object'_Global' failed when I run the code the second time.

Also, when I changed "New Excel.application" to just "Excel.Application", I ended up with run time error 91 - "Object variable or With block variable not set"

I am going to take this home and see if it works on a different computer.
0
 
LVL 28

Expert Comment

by:omgang
ID: 40360639
Re. the just Excel.Application, notice that I instantiate Excel later after I added some additional declarations.


 'added by OMGang  -- I added Option Explicit at the top of the module (as suggested by  Jim in the second post) so we can be sure all variables are declared
 Dim SQL As String
 Dim w As Integer

 Set oApp = Excel.Application

OM Gang
0
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 40360642
Here's the entire module with my modifications.  Works just fine for me in Access 2010.
OM Gang


Option Explicit

Private Sub cmdExport_Click()
On Error GoTo Error_Handler

Dim Db As Database, Rs As Recordset, sSQL As String, iW As Integer, iRow As Integer
'dim statements for new workbook in Excel
'Dim oApp As New Excel.Application
Dim oApp As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRange As Excel.Range
'Add the field names in row 1
Dim i As Integer
Dim iNumCols As Integer
Dim TheTO As String
Dim TheSTOname As String
Dim TheStaffName As String
Dim TheActDesc As String
'Dim C As Object
Dim C As Excel.Range
Dim Qd As QueryDef

'added by OMGang
Dim SQL As String
Dim w As Integer

Set oApp = Excel.Application

'Initialize variables (this resets them so they do not hold old values)
sSQL = ""
iW = 0
iRow = 0
'oApp =
'oBook = Nothing
'oSheet = Nothing
'oRange = Nothing
i = 0
iNumCols = 0
TheTO = ""
TheSTOname = ""
TheStaffName = ""
TheActDesc = ""
'C = ""
'Qd = Null

SQL = "qry_report1"
Set Db = CurrentDb
Set Qd = Db.QueryDefs("qry_report1")

Set Rs = Qd.OpenRecordset(dbOpenDynaset, dbReadOnly)
Rs.MoveLast
Rs.MoveFirst

If Rs.RecordCount = 0 Then
    GoTo Bail
End If

Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
oApp.Visible = True
oApp.UserControl = True


'ok We want each task order to be on a row by itself in column A
'Headers
oSheet.Cells(1, 1).Value = "Task Order/Sub Tasks"

'oSheet.Cells(1, 2).Value = "Sub Tasks"

oSheet.Cells(1, 2).Value = "Staff"

oSheet.Cells(1, 3).Value = "Details"

TheTO = Rs!to
oSheet.Cells(2, 1).Value = TheTO
    Set C = oSheet.Cells(2, 1)
    With C
       
        .Font.Name = "Arial"
        .Font.Bold = True
        .Font.Size = 12
        .Interior.ColorIndex = 15
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeTop).Weight = xlThin
        .Borders(xlEdgeTop).ColorIndex = xlAutomatic
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).Weight = xlThin
        .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlEdgeRight).Weight = xlThin
        .Borders(xlEdgeRight).ColorIndex = xlAutomatic
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeLeft).Weight = xlThin
        .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
        .Borders(xlInsideVertical).LineStyle = xlContinuous
        .Borders(xlInsideVertical).Weight = xlThin
        .Borders(xlInsideVertical).ColorIndex = xlAutomatic
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
        .Borders(xlInsideHorizontal).Weight = xlThin
        .Borders(xlInsideHorizontal).ColorIndex = xlAutomatic
     End With

TheSTOname = Rs!STO
oSheet.Cells(3, 1).Value = TheSTOname
TheStaffName = Rs!TeamName
oSheet.Cells(3, 2).Value = TheStaffName
TheActDesc = Rs!ActDesc
oSheet.Cells(4, 3).Value = TheActDesc

'this is the last row I just wrote to   *****WHY IS THIS HARD CODED TO 4?
iRow = 4

Rs.MoveNext

'what are these for? when are these used?
iNumCols = 3


Do Until Rs.EOF

    If Rs!to = TheTO Then
        If Rs!STO = TheSTOname Then
            If TheStaffName = Rs!TeamName Then
                If TheActDesc = Rs!ActDesc Then
                    Rs.MoveNext
                Else
                    iRow = iRow + 1
                    oSheet.Cells(iRow, 3).Value = Rs!ActDesc
                    Rs.MoveNext
                End If
            Else
                'iRow = iRow + 1
                oSheet.Cells(iRow, 2).Value = Rs!TeamName
                TheStaffName = Rs!TeamName
                Rs.MoveNext
            End If
        Else
            iRow = iRow + 1
            oSheet.Cells(iRow, 1).Value = Rs!STO
           TheSTOname = Rs!STO
            TheStaffName = ""
           
            Rs.MoveNext
        End If
    Else
        iRow = iRow + 1
        oSheet.Cells(iRow, 1).Value = Rs!to
       'FormatTO (oSheet.Cells(iRow,1),"Arial")
        Set C = oSheet.Cells(iRow, 1)

       
        With C
            .Font.Name = "Arial"
            .Font.Bold = True
            .Font.Size = 12
            .Interior.ColorIndex = 15
            .Borders(xlEdgeTop).LineStyle = xlContinuous
            .Borders(xlEdgeTop).Weight = xlThin
            .Borders(xlEdgeTop).ColorIndex = xlAutomatic
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
            .Borders(xlEdgeBottom).Weight = xlThin
            .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
            .Borders(xlEdgeRight).LineStyle = xlContinuous
            .Borders(xlEdgeRight).Weight = xlThin
            .Borders(xlEdgeRight).ColorIndex = xlAutomatic
            .Borders(xlEdgeLeft).LineStyle = xlContinuous
            .Borders(xlEdgeLeft).Weight = xlThin
            .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
            .Borders(xlInsideVertical).LineStyle = xlContinuous
            .Borders(xlInsideVertical).Weight = xlThin
            .Borders(xlInsideVertical).ColorIndex = xlAutomatic
            .Borders(xlInsideHorizontal).LineStyle = xlContinuous
            .Borders(xlInsideHorizontal).Weight = xlThin
            .Borders(xlInsideHorizontal).ColorIndex = xlAutomatic
            .WrapText = True
            .RowHeight = 54.75
           
         End With

           
Range(C.Cells, C.Cells.Offset(0, 1)).Select
'Range(oSheet.Cells(iRow, 1), oSheet.Cells(iRow, 1).Offset(0, 1)).Select
'C.Offset(0, 1).Select
'Range(C, C.Offrset(0, 1)).Select
Selection.Merge




        TheTO = Rs!to
        Rs.MoveNext
       
    End If


skip:
Loop

iNumCols = IIf(w > 0, w, iNumCols)
'Format the header row as bold
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
'Size the columns
.Columns("A:A").ColumnWidth = 40
.Columns("B:B").ColumnWidth = 22
.Columns("C:C").ColumnWidth = 73.44
End With

Bail:
    Rs.Close
     Set Rs = Nothing
     Qd.Close
     Set Qd = Nothing
     Db.Close
     Set Db = Nothing
     
     Set C = Nothing
     Set oRange = Nothing
     Set oSheet = Nothing
     
     Set oBook = Nothing
     Set oApp = Nothing
   
   
    Exit Sub
   
Error_Handler:
    MsgBox Error$
    Resume Bail
 
End Sub
0
 

Author Closing Comment

by:Megin
ID: 40364551
Sorry I was so dense about this! I just went through and compared your code to mine, line by line, and finally found what you were talking about.

Thank you for the help and the hand holding!!
0
 
LVL 28

Expert Comment

by:omgang
ID: 40364577
Not a problem.  Glad it's working for you.
OM Gang
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now