Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Posts
    9

    Red face Unanswered: "Variable block or With variable block not set" problem

    hi, I'm new to visual basic programming, especially microsoft access. this part of code looks fine to me, but it show the message above when I run my event procedure that run this particular sub. I need some help here. And I'm using access 2000.

    Code:
    Public Sub CashFlowFormat(ByVal strExcelPath As String)
        Dim objXL As Excel.Application, objXLWB As Excel.Workbook, objXLWS As Excel.Worksheet
        Dim objXLWBTemp As Excel.Workbook
        Dim longMaxRow As Long, intMaxCol As Integer
        Dim dbCurrent As DAO.Database, rstTable As DAO.Recordset
        Dim rngCells As Range
        Dim qryCashFlowQuery As QueryDef
        Dim strDateStart As String, strDateEnd As String
        Dim strFileName As String
        Dim j As Long, k As Long, l As Long
            
        On Error GoTo Err_CashFlowFormat
        
        'Gets the input parameters - start date and end date
        strDateStart = DateValue(InputBox("Pls enter the start date:", "Start Date"))
        strDateEnd = DateValue(InputBox("Pls enter the end date:", "End Date"))
        
        'Runs the appropriate query and generates the excel file
        Set qryCashFlowQuery = dbCurrent.QueryDefs("qryCashflow")
        qryCashFlowQuery("Pls enter the start date:") = strDateStart
        qryCashFlowQuery("Pls enter the end date:") = strDateEnd
        
        Set rstTable = qryCashFlowQuery.OpenRecordset
        
        'Sets up the objects for the excel workbook and worksheets by creating a blank worksheet
        Set objXL = New Excel.Application
        objXL.Application.DisplayAlerts = False
        Set objXLWB = objXL.Workbooks.Add
        objXLWB.SaveAs FileName:=strExcelPath, FileFormat:=xlWorkbookNormal
        Set objXLWS = objXLWB.Worksheets(1)
        objXLWS.Name = "CashFlow Forecast '" + Format(Date, "yy")
        objXLWB.Worksheets.Add
        objXLWB.ActiveSheet.Name = "CashFlow Forecast '" + Format(Date, "yy")
        objXLWS.Activate
        objXLWB.ResetColors
        objXLWB.Worksheets("sheet2").Delete
        objXLWB.Worksheets("sheet3").Delete
        
        'Checks if the recordset is empty. An attempt to copy data from the recordset is carried out
        'only when the recordset is not empty
        If (rstTable.RecordCount > 0) Then
            Set rngCells = objXLWS.Cells(2, 1)
            rngCells.CopyFromRecordset rstTable
            
        End If
        
        'Finds the max row and column count of the first worksheet
        longMaxRow = objXL.Cells.find(What:="*", After:=objXL.Range("A1"), _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious).Row
        intMaxCol = objXL.Cells.find(What:="*", After:=objXL.Range("A1"), _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious).Column
        
        
                        
    Exit_CashFlowFormat:
        Exit Sub
        
    Err_CashFlowFormat:
        Debug.Print Err.Number
        Debug.Print Err.Description
        If (Not objXL Is Nothing) Then
            objXL.Quit
            
        End If
        
        Set qryCashFlowQuery = Nothing
        Set rngCells = Nothing
        Set dbCurrent = Nothing
        Set rstTable = Nothing
        Set objXL = Nothing
        Set objXLWS = Nothing
        Set objXLWB = Nothing
        MsgBox "An error has occured, pls try again!", vbInformation + vbOKOnly, "Error!"
        Resume Exit_CashFlowFormat
                        
    End Sub
    Last edited by olimin; 03-07-07 at 23:39.

  2. #2
    Join Date
    Feb 2004
    Location
    Canada
    Posts
    133
    I think you need a

    Set dbCurrent = ...

    in there somewhere.

  3. #3
    Join Date
    Mar 2007
    Posts
    9
    thanks a lot.. it solves the problem.

  4. #4
    Join Date
    Feb 2004
    Location
    Canada
    Posts
    133
    One other thing. Your "Set ... = Nothing" lines are all within your error handling code which means your variables only get cleared if there is an error condition. You might want to put those lines before your "Exit_CashFlowFormat:" line.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •