1

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

4
  • I would plead with you to re-name Me.Util_. Use of underscore is certain to create problems, including perhaps the one you are dealing with.
    – Smandoli
    Commented Dec 26, 2013 at 17:39
  • I would think the line Set DayNum = dayRs.Fields("WrkDays") needs to go WITHIN the While loop.
    – Smandoli
    Commented Dec 26, 2013 at 17:41
  • Unfortunately the Me.Util_ is based on a field I have no control on renaming. Can you explain why the Set DayNum = dayRs.Fields("WrkDays") line should be inside the Loop?
    – Mr. Finn
    Commented Dec 26, 2013 at 17:47
  • Because dayRs.Fields("WrkDays") presumably changes with each new record, so you have to get it fresh.
    – Smandoli
    Commented Dec 26, 2013 at 17:58

3 Answers 3

Reset to default
1

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.

12
  • dayRs is a single field record set. Will that work with a recordset?
    – Mr. Finn
    Commented Dec 26, 2013 at 18:49
  • I can't think of any reason why it would not work in general, but your code puzzles me. The best I can suggest is try it.
    – HansUp
    Commented Dec 26, 2013 at 18:53
  • Yeah the best I can call it is an attempt at a possible solution. I am trying the stuff you posted right now to see what I can get working
    – Mr. Finn
    Commented Dec 26, 2013 at 18:56
  • Ok I had an idea but I am not certain how to do it. is there a way to populate an array with the values from a table? because that would much more easily solve my problem.
    – Mr. Finn
    Commented Dec 26, 2013 at 18:59
  • Please try the change I suggested first. It should be a quick test. Perhaps we can discuss other approaches later if needed.
    – HansUp
    Commented Dec 26, 2013 at 19:03
1

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?

4
  • I am not certain I understand what you mean. my understanding is that the dayRs.MoveNext command tells the program to move to the next record in the dayRs Recordset. This is a section I had a lot of problems with to start with so I would be very interested in possible alternate solutions. I need to pull a series of values from the table where that recordset is pointed at (which is baisically working days of the various months) I need to then do the assigned math that you see on the lines between the dayRs.MoveNext to get those assigned. If there is a better way to go about it im all for it.
    – Mr. Finn
    Commented Dec 26, 2013 at 18:05
  • Does WrkDays have 12 records? If so, it seems you want to determine billing per month. I'm thinking there are indeed easier ways to handle this. I wonder what data is in Util_?
    – Smandoli
    Commented Dec 26, 2013 at 18:24
  • Its a calculation of revenue forcast. Bill rate is what their billable time is worth. The "" is the place holder VBA uses for the "%" symbol the is in the actual column name. Util is the % of their hours which can be considered "Billable" hours. so its Bill rate * (Working days of the month*40) * the % of Utilization which gives the forcast of the expected revenue production.
    – Mr. Finn
    Commented Dec 26, 2013 at 18:30
  • Does WrkDays have 12 records?
    – Smandoli
    Commented Dec 26, 2013 at 18:32
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.