Access 2003 can't debug 1

[vba317]

I am running access 2003 with Windows XP as OS . I cant debug vba code when I try nothing happens. Is there something in the installation?

 

[dhookom]

Do you know how to debug code or are you asking us? Since we can't see your code can you tell us what you did that makes you think something should happen.

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

Sure I can provide the code, The problem is my counters are messed up so the data goes in the wrong rows on the excel spreadsheet and get two errors: the first one is I have a circular in my first formula so my endrow variable is wrong. The second error I get is a runtime error 91 Object variable or with block variable is not set. The problem is I have renamed the variables so they would make more sense in doing so I know that something is named wrong in a formula somewhere. So to fix the problem I wanted to step through the code to see where the errors of my ways are, but I can't. Here is the code:

Private Sub cmdExpExcel_Click()

Dim strSQL As String
Dim iRwCnt As Integer
Dim iRw1 As Integer
Dim iRw2 As Integer
Dim rst As Recordset
Dim Z As Integer
Dim strCurYr As String
Dim strGrpYr As String
Dim iBegRow As Integer
Dim iEndRow As Integer 'added 9/26/2012 3:35
Dim iColCnt As Integer 'added 9/26/2012 3:35
Dim Msg, Style, Title, Help, Ctxt, Response, Mystring As String


' OPEN EXCEL
Call XLCreate
If gbXLPresent = True Then
    '******************************************************************************************
    '*******************FIRST SHEET FOR TOTALS*************************************************
    '******************************************************************************************
    With goXL
        .Workbooks.Open FileName:="C:\TestDatabases\AnestheticSolutions\AnestheticSolutions.xlt"
        'Select Sheetname for information to go into.
        .Sheets("ASOData").Select
    End With
    ' Pull Totals by Month and Year
    strSQL = "SELECT pd.yr,asotot.rptpd,pd.mon_nm,Sum(asotot.CaseCnt)as 1,Sum(asotot.Units) as 2,Sum(asotot.ORFlag) as 3,Sum(asotot.ORUnits) as 4," & _
                    "Sum(asotot.Amt) as 5,Sum(asotot.TotPay) as 6,Sum(asotot.TotAdj) as 7,Sum(asotot.CurBal) as 8,Sum(asotot.[3MonChgs]) as 9 " & _
                "FROM dat_ASOData asotot " & _
                    "INNER JOIN dbo_dic_Period pd ON asotot.rptpd = pd.pd " & _
                "GROUP BY pd.yr,asotot.rptpd,pd.mon_nm " & _
                "ORDER BY pd.yr,asotot.rptpd;"
    Set rst = CurrentDb.OpenRecordset(strSQL, dbopensnapshot)
    If (rst.RecordCount > 0) Then
        With rst
            .MoveLast
            .MoveFirst
        End With
        iRwCnt = 5 ' Row starts at 5
        strCurYr = rst![yr]
        strGrpYr = rst![yr]
        'Sets 1st year
        With goXL
        .ActiveSheet.Cells(3, 2).Value = "'" & (strCurYr)
        'Call
        .ActiveSheet.Cells(3, 2).Font.Bold = True
        End With
        For Z = 1 To rst.RecordCount
         
            With goXL.ActiveSheet
          [Red] .Cells(iRwCnt, 2).Value = "'" & (rst![mon_nm]) & " " & (rst![yr]) [/REd]
                .Cells(iRwCnt, 3) = rst![1]
                .Cells(iRwCnt, 4) = rst![2]
                .Cells(iRwCnt, 5) = rst![3]
                .Cells(iRwCnt, 6) = rst![4]
                'Excel formula =IF(ISBLANK(E5),0,F5/E5)
                .Cells(iRwCnt, 7).Formula = "=if(ISBLANK(E" & (iRwCnt) & "),0,F" & (iRwCnt) & "/E" & (iRwCnt) & ")"
                .Cells(iRwCnt, 8) = rst![5]
                .Cells(iRwCnt, 9) = rst![6]
                .Cells(iRwCnt, 10) = rst![7]
                'Excel Formula =IF(ISBLANK(D5),0,I5/D5)
                .Cells(iRwCnt, 11).Formula = "=if(ISBLANK(D" & (iRwCnt) & "),0,I" & (iRwCnt) & "/D" & (iRwCnt) & ")"
                'Excel Formula =IF(ISBLANK(F5),0,I5/F5)
                .Cells(iRwCnt, 12).Formula = "=if(ISBLANK(F" & (iRwCnt) & "),0,I" & (iRwCnt) & "/F" & (iRwCnt) & ")"
                'Excel Formula =IF(ISBLANK(I6),0,I6/H6)
                .Cells(iRwCnt, 13).Formula = "=if(ISBLANK(I" & (iRwCnt) & "),0,I" & (iRwCnt) & "/H" & (iRwCnt) & ")"
                '=IF((I6+J6)=0,0,I6/(I6+J6))
                .Cells(iRwCnt, 14).Formula = "=IF((I" & (iRwCnt) & "+J" & (iRwCnt) & ")=0,0,I" & (iRwCnt) & "/(I" & (iRwCnt) & "+J" & (iRwCnt) & "))"
                .Cells(iRwCnt, 15) = rst![8]
                'Excel Formula =IF(ISBLANK(O6),=O6/(AA6/AB6)
                .Cells(iRwCnt, 16).Formula = "=if(ISBLANK(O" & (iRwCnt) & "),0,O" & (iRwCnt) & "/(AA" & (iRwCnt) & "/AB" & (iRwCnt) & "))"
                .Cells(iRwCnt, 27) = rst![9]
                .Cells(iRwCnt, 28) = Daysper3Mon(rst![rptpd])
                Msg = "iRwCnt after data is done"
                Style = vbYes
                Title = "iRwCntCounter 1"
                Help = iRwCnt
                Response = MsgBox(Msg, Style, Title, Help, Ctxt)
                End With
            rst.MoveNext
            iRwCnt = iRwCnt + 1
            If Not rst.EOF Then
                strCurYr = rst![yr]
                If (strCurYr <> strGrpYr) Then
                    iRwCnt = iRwCnt + 3
                    iRw1 = iRwCnt - 4
                    iRw2 = iRwCnt - 3
                    If iRwCnt < 14 Then iBegRow = iRwCnt - 3
                    If iRwCnt < 14 Then iEndRow = iRwCnt - 4
                    'Set up Cell for Bottom of Sheet
                    Call XLFormatBottomLine(iEndRow + 1, 2, 16)
                    goXL.ActiveSheet.Cells(iEndRow + 1, 2).Value = "Totals" ' Puts Totals in col 2
                    Call XLFormatBottomLine(iEndRow + 2, 2, 16)
                    goXL.ActiveSheet.Cells(iEndRow + 2, 2).Value = "Average" ' Puts average in col 2
                    Call XLFormatDoubleLine(iEndRow + 2, 2, 16)
                    'Sets Year at top of Sheet
                    strGrpYr = strCurYr
                    With goXL
                    'Set up Cell for Year on top of sheet
                    .ActiveSheet.Cells((iRwCnt - 2), 2).Value = "'" & (strCurYr)
                    .ActiveSheet.Cells((iRwCnt - 2), 2).Font.Bold = True
                    'Set up First Cell called Month
                    .ActiveSheet.Cells((iRwCnt - 1), 2).Value = "Month"
                    .ActiveSheet.Cells((iRwCnt - 1), 2).Font.Bold = True
                    .ActiveSheet.Cells((iRwCnt - 1), 2).RowHeight = 30
                    .ActiveSheet.Cells((iRwCnt - 1), 2).Font.Size = 12
                    End With
                    'Added block added 9/26/2012 3:35
                    Call ConvColLet(iColCnt)
                        With goXL
                            For iColCnt = 2 To 15
                                        Call ConvColLet(iColCnt)
                                        If iColCnt = 2 Then .ActiveSheet.Cells(iRw1, iColCnt).Value = "Totals" ' Puts Totals in col 2
                                        If iColCnt = 2 Then .ActiveSheet.Cells(iRw2, iColCnt).Value = "Averages" 'Puts averages in Col 2
                                        .ActiveSheet.Cells(iBegRow, iColCnt + 1).Formula = "=SUM(" & (ConvColLet(iColCnt + 1)) & (iBegRow) & ":" & (ConvColLet(iColCnt + 1)) & (iEndRow) & ")"
                                        .ActiveSheet.Cells(iBegRow, iColCnt + 1).Formula = "=AVERAGE(" & (ConvColLet(iColCnt + 1)) & (iBegRow) & ":" & (ConvColLet(iColCnt + 1)) & (iEndRow) & ")"
                                        'Call XLFormatRowHeight(iBegRow, iEndRow, 22)
                            Next iColCnt
                        End With
                End If
            End If
            iRw1 = iRwCnt + 1
            If iRwCnt < 12 Then iBegRow = iRwCnt 'added 9/26/2012 3:35
            If iRwCnt < 12 Then iEndRow = iRwCnt + 5
            'If iRw > 12 Then iEndRow = iRw  'added 9/26/2012 3:35
            'If iRw > 12 Then iBegRow = iRw - 11
        Next Z
      
    End If
    Set rst = Nothing
    With goXL.ActiveWorkbook
        .SaveAs FileName:="C:\TestDatabases\AnestheticSolutions\ASO_" & (MonShortName(CurMon())) & "Total Report" & ".xls"
        .Close
    End With
Else
    MsgBox "Can't create Excel Object", vbOKOnly, "Excel not found"
End If
' Close Excel Instance
Call XLKill
' Message it is closed
MsgBox "Reports Completed.", , "Done!"

End Sub

 

[SkipVought]

Why can't you STEP? Please explain. Did you try to put a break on your code and then DEBUG & STEP?

ALSO...

maybe explain what your code is trying to accomplish in plain words.

Then relate that to your control structure; ie in this loop, this occurs, in this if, this happens. These are the kind of COMMENTS that you ought to have at various places in your code anyhow!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

OK I will repost the code in a couple of minutes with explanations.

 

[dhookom]

You might want to check out faq705-7148


Duane
Hook'D on Access
MS Access MVP