We help IT Professionals succeed at work.
Get Started

Excel error code 91

PeterBaileyUk
on
454 Views
Last Modified: 2012-08-11
My vba (courtesy of harfang, and much appreciated) has started tripping over after populating the months of July only (july is last data set).

It says error 91 I have f8 through and everything looks ok.


Sub GetTotalNewCodesCategoryOthersCW2012()
    Set qdf = db.QueryDefs("qptotNewCodes 3 parameters")
    
 
    Set RstCurrentYearMonth = db.OpenRecordset("SELECT Max(CWSMMTBuilds.BuildYearMonth) AS MaxOfBuildYearMonth FROM CWSMMTBuilds;", dbReadOnly)
    
    'get current data month
    With RstCurrentYearMonth
    .MoveLast
    CurrentDataMonth = .Fields("MaxofBuildYearMonth").Value
    
    End With
      
    datMonth = #1/1/2012#
   
    
    Do While datMonth <= CurrentDataMonth
     

        qdf.Parameters("BYM") = DateSerial(Format(datMonth, "yyyy"), Format(datMonth, "mm"), 1)
        qdf.Parameters("[Yes=CW Codes/No=SMMT Codes]").Value = True
        qdf.Parameters("[Enter Cars, Bikes, Lcv, Others]").Value = "Others"
        

          With wks.Cells(12, Format(datMonth, "mm") + 1)

            .Select
            .Value = "...."
            DoEvents
            .Value = qdf.OpenRecordset!Nb
            DoEvents
        End With
        
        datMonth = DateAdd("m", 1, datMonth)
       
    Loop

Set RstCurrentYearMonth = Nothing
db.Close
Set db = Nothing

End Sub

Open in new window


variables defines at top:
Option Explicit
Const DATA_SOURCE = "N:\Data\smmt\CWSMMT2012.mdb"
   Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim qdfMatched As DAO.QueryDef
    Dim intM As Integer
    Dim wks As Worksheet
    Dim RstCurrentYearMonth As DAO.Recordset
    Dim CurrentDataMonth As Date
    Dim datMonth As Date

Open in new window


I call the subs from here:

Sub GetTotalNewCodesSMMT2012()
Set wks = Sheets("2012 Data feed")
    Set db = DAO.OpenDatabase(DATA_SOURCE)
 Call GetTotalNewCodeCounts2012
 Call GetTotalNewCodesCategorySMMT2012
 Call GetTotalNewCodesCategoryCW2012
Call GetTotalNewCodesCategoryCarsSMMT2012
Call GetTotalNewCodesCategoryCarsCW2012
Call GetTotalNewCodesCategoryBikesSMMT2012
Call GetTotalNewCodesCategoryBikesCW2012
Call GetTotalNewCodesCategoryLCVSMMT2012
Call GetTotalNewCodesCategoryLCVCW2012
Call GetTotalNewCodesCategoryOthersSMMT2012
Call GetTotalNewCodesCategoryOthersCW2012




End Sub

Open in new window

Comment
Watch Question
Keep everyone healthy; Wear a mask - Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 1 Answer and 9 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE