CODEWITHSUNDEEP
ms-accessautomationms-access-2007vba
Mr. Finn
Mr. Finn

Reputation: 99

VBA Access Runtime Error 440 Automation error

I am uncertain what is causing this error and would like some help understanding what mistake I made which caused it as well as help or suggestions on how to correct the issue Below is a section of code that I am getting the error on. The debug flags up on line 7 "Feb = (Me.BillRate * DayNum) * Me.Util_"

    Set dayRs = db.OpenRecordset("SELECT WrkDays FROM WrkDays ORDER BY WrkMonth;")
    dayRs.MoveFirst
    Set DayNum = dayRs.Fields("WrkDays")
    While Not dayRs.EOF
        Jan = (Me.BillRate * DayNum) * Me.Util_
        dayRs.MoveNext
        Feb = (Me.BillRate * DayNum) * Me.Util_
        dayRs.MoveNext
        Mar = (Me.BillRate * DayNum) * Me.Util_
        dayRs.MoveNext
        Apr = (Me.BillRate * DayNum) * Me.Util_
        dayRs.MoveNext
        May = (Me.BillRate * DayNum) * Me.Util_
        dayRs.MoveNext
        Jun = (Me.BillRate * DayNum) * Me.Util_
        dayRs.MoveNext
        Jul = (Me.BillRate * DayNum) * Me.Util_
        dayRs.MoveNext
        Aug = (Me.BillRate * DayNum) * Me.Util_
        dayRs.MoveNext
        Sep = (Me.BillRate * DayNum) * Me.Util_
        dayRs.MoveNext
        Oct = (Me.BillRate * DayNum) * Me.Util_
        dayRs.MoveNext
        Nov = (Me.BillRate * DayNum) * Me.Util_
        dayRs.MoveNext
        Dec = (Me.BillRate * DayNum) * Me.Util_
    Wend

I am guessing based on how I built this code that I will very likely get a similar error on the lines of code which follow after the "Feb" line. So I want to understand this error more clearly so I can correct future occurances.

UPDATE After working with Hans he pointed me to using the recordset.getrows method which accomplished the same process that I was trying to do with less headache. So thank you very much Hans

Upvotes: 1

Views: 8540

Answers (3)

donPablo
donPablo

Reputation: 1959

This will work with exactly 12 records, or more importantly with less or more. After a time you may find that you have more records. 

   Set dayRs = db.OpenRecordset("SELECT  WrkMonth, SUM( WrkDays )FROM WrkDays GROUP BY  WrkMonth  ORDER BY WrkMonth;")
   dayRs.MoveFirst
   Do While not dayRs.EOF
    DayNum = dayRs.Fields("WrkDays")
    SELECT CASE WrkMonth;
     CASE 1
      Jan = (Me.BillRate * DayNum) * Me.Util_daysRs
     CASE 2
      Feb = (Me.BillRate * DayNum) * Me.Util_daysRs
     CASE 3
      Mar = (Me.BillRate * DayNum) * Me.Util_daysRs
     CASE 4
      Apr = (Me.BillRate * DayNum) * Me.Util_daysRs
     CASE 5
      May = (Me.BillRate * DayNum) * Me.Util_daysRs
     CASE 6
      Jun = (Me.BillRate * DayNum) * Me.Util_daysRs
     CASE 7
      Jul = (Me.BillRate * DayNum) * Me.Util_daysRs
     CASE 8
     Aug = (Me.BillRate * DayNum) * Me.Util_daysRs
     CASE 9
      Sep = (Me.BillRate * DayNum) * Me.Util_daysRs
     CASE 10
      Oct = (Me.BillRate * DayNum) * Me.Util_daysRs
     CASE 11
      Nov = (Me.BillRate * DayNum) * Me.Util_daysRs
     CASE 12
      Dec = (Me.BillRate * DayNum) * Me.Util_daysRs
    SELECT END
   daysRs.movenext
   Loop

Upvotes: 1

Smandoli
Smandoli

Reputation: 7019

If you are merely looking for days (or work-days) per month, you may do better using VBA date functions. For example, info here.

Your requirements may be more complex -- maybe the days for a given month is specified by the user. In that case, you need to fix the loop structure. While...Wend is advancing the cursor through the recordset, and so is MoveNext. 

While Not dayRs.EOF
    ...
    dayRs.MoveNext
    ...
    dayRs.MoveNext
    ...
    dayRs.MoveNext
    ...
Wend

Try commenting out the While and Wend lines. Does your code run the same?

Upvotes: 1

HansUp
HansUp

Reputation: 97101

Unfortunately, that particular error message is rather thin on details.

My first suggestion is to disable this line ...

'Set DayNum = dayRs.Fields("WrkDays")

Then wherever you use DayNum in the rest of the code, reference the field value directly.

'Jan = (Me.BillRate * DayNum) * Me.Util_  ' use the following instead
Jan = (Me.BillRate * dayRs!WrkDays.Value) * Me.Util_ ' .Value should not be needed here; use it anyway

However, I'm not confident that suggestion is the fix. If it's not, set a break point on the Feb = line and investigate the status of the recordset's current row and the values of all the entities ...

While Not dayRs.EOF
    Jan = (Me.BillRate * dayRs!WrkDays.Value) * Me.Util_
    dayRs.MoveNext
    Feb = (Me.BillRate * dayRs!WrkDays.Value) * Me.Util_ ' <-- set break point on this line

In the Immediate window ...

' are we perhaps at EOF already?
? dayRs.EOF
' confirm you still get the same error with this ...
? (Me.BillRate * dayRs!WrkDays.Value) * Me.Util_
' examine the components
? (Me.BillRate * dayRs!WrkDays.Value)
? Me.BillRate
? dayRs!WrkDays.Value
? Me.Util_

Hopefully that effort will reveal something which can lead to the fix.

Upvotes: 1

Related Questions