VBA - Do While Loops and Edit data question

Question

I am using Access 2007

Every month I have to produce a recharge list for everyone in the health scheme.

The amounts for each individual can change throughout the year.

We start the year with an Annual Bill, we can negotiate and later get a discount to be applied against each individual.

People start during the year and people leave during the year.

The recharge is based on the total amount less recharged to date divided by the remaining months.

Currently I have 12 update queries (one for each month) which I run. These update the recharge for the current month and the following months. This enables me to produce the recharge list and a projection for the rest of the year which is used for budget purposess.

This is no problem for me as I 'own' the database.

However, I have now been asked to add a 'magic' button from which the user can select the recharge month and do all the subsequent calculations and update the table.

I have tried to set up a VBA query to do this which will be activated by a control button on the menu.

I have first tried to update the totals ensure they are correct and my problem is:

1. I want to look at every record
2. stop when the last record has been read
3. recalculate the totals in put into the appropriate fields and update.

The code I am using does not work and although taken directly from the help file it is not recognised.

Just in case this may be the problem I am working on a copy of the database.

Please any help would be gratefully appreciated as once I get pass this hurdle I can add the rest of the code.

I attach a copy of my code

Option Compare Database

Option Explicit

Public Sub Recharges()

Dim DB As Database

Dim RS As Recordset

'Declare Table Variables

Dim EAnnualSub As Double

Dim ENewBillCash As Double

Dim ELeftBillCash As Double

Dim EDiscount As Double

Dim ENetAnnual As Double

Dim YearEMar As Double

Dim YearEApr As Double

Dim YearEMay As Double

Dim YearEJun As Double

Dim YearEJul As Double

Dim YearEAug As Double

Dim YearESep As Double

Dim YearEOct As Double

Dim YearENov As Double

Dim YearEDec As Double

Dim YearEJan As Double

Dim YearEFeb As Double

Dim JournalMth As String

Dim EPayRef As Double

Dim TR1 As Double

Dim TR2 As Double

Dim TR3 As Double

Dim TR4 As Double

Dim TR5 As Double

Dim TR6 As Double

Dim Pay As Double

'Select Month to Update from

JournalMth = InputBox("Enter the Journal Month as 3 letter eg. 'Mar'", "Select Journal Month")

'Open the Database

Set DB = OpenDatabase("PPP_Health_2007.accdb")

'Open the Table

Set RS = DB.OpenRecordset("2011_2012", dbOpenDynaset)

'Define Fields

Pay = RS.Fields("EPayRef")

TR1 = RS.Fields("EAnnualSub")

TR2 = RS.Fields("EDiscount")

TR3 = RS.Fields("ENetAnnual")

TR4 = RS.Fields("ENewBillCash")

TR5 = RS.Fields("ELeftBillCash")

TR6 = RS.Fields("EP11DNet")

Do While Not EOF(1)

     If IsNull(TR1) = True Then TR1 = 0

     If IsNull(TR2) = True Then TR2 = 0

     If IsNull(TR3) = True Then TR3 = 0

     If IsNull(TR4) = True Then TR4 = 0

     If IsNull(TR5) = True Then TR5 = 0

     If IsNull(TR6) = True Then TR6 = 0

               With RS

                  .Edit

                         !TR6 = Val(TR1 - TR2)

                         !TR3 = Val((TR1 + TR4) - (TR2 + TR5))

                    .Update

               End With

RS.MoveNext

Loop

End Sub

Thank you for your help.

Sean

Answer 1

The following is a GUESS at what you are attempting to accomplish.

Option Compare Database

Option Explicit

Public Sub Recharges()

Dim DB As Database

Dim RS As Recordset

'Declare Table Variables

Dim EAnnualSub As Double

Dim ENewBillCash As Double

Dim ELeftBillCash As Double

Dim EDiscount As Double

Dim ENetAnnual As Double

Dim YearEMar As Double

Dim YearEApr As Double

Dim YearEMay As Double

Dim YearEJun As Double

Dim YearEJul As Double

Dim YearEAug As Double

Dim YearESep As Double

Dim YearEOct As Double

Dim YearENov As Double

Dim YearEDec As Double

Dim YearEJan As Double

Dim YearEFeb As Double

Dim JournalMth As String

Dim EPayRef As Double

Dim TR1 As Double

Dim TR2 As Double

Dim TR3 As Double

Dim TR4 As Double

Dim TR5 As Double

Dim TR6 As Double

Dim Pay As Double

'Select Month to Update from

'This value is not used in your code to so I am not sure why you are attempting to get it

JournalMth = InputBox("Enter the Journal Month as 3 letter eg. 'Mar'", "Select Journal Month")

'Open the Database

Set DB = OpenDatabase("PPP_Health_2007.accdb")

'Open the Table

Set RS = DB.OpenRecordset("2011_2012", dbOpenDynaset)

WITH RS

Do While Not .EOF

'Define Fields  --- problem here if any of the fields are null.

'You have defined the fields as double and double will NOT accept a null value

   Pay = .Fields("EPayRef")

   TR1 = Nz(.Fields("EAnnualSub"),0)

   TR2 = NZ(.Fields("EDiscount"),0)

   TR3 = Nz(.Fields("ENetAnnual"),0)

   TR4 = NZ(.Fields("ENewBillCash"),0)

   TR5 = Nz(.Fields("ELeftBillCash"),0)

   TR6 = Nz(.Fields("EP11DNet"),0)         

   .Edit

'A little confusion may exist on my part with TR6 being a variable and a field name

      !TR6 = Val(TR1 - TR2)

      !TR3 = Val((TR1 + TR4) - (TR2 + TR5))

   .Update

   .MoveNext

Loop

End With 'RS

End Sub

Answer 2

Sir,

Thank you for your reply and my apologies for not replying sooner but grand-daugher's 21st got in the way.

It worked - thank you

I noticed that I had not defined the database or recordset as  DAO once I did this your code worked.

To answer your question the input box is for later code when I will be calculating projections from the input month onward.

As they will update the table the principle regarding the edit will be the same so I can move forward with confidence.

Again thank you for your time and patience.

Sean

Answer 3

Thank you for your help.

I amended the code as you suggested.

Went to debug - compile

When it got to the ".Edit" line it stopped with the message

Compile error:

Method or data member not found

Is there another Edit method ?

Thank you,

Sean

Answer 4

It looks at though you're not reassigning values for TR1 through TR6: you're looping through the recordset using the values you got from the first record only!

Replace

'Define Fields

Pay = RS.Fields("EPayRef")

TR1 = RS.Fields("EAnnualSub")

TR2 = RS.Fields("EDiscount")

TR3 = RS.Fields("ENetAnnual")

TR4 = RS.Fields("ENewBillCash")

TR5 = RS.Fields("ELeftBillCash")

TR6 = RS.Fields("EP11DNet")

Do While Not EOF(1)

     If IsNull(TR1) = True Then TR1 = 0

     If IsNull(TR2) = True Then TR2 = 0

     If IsNull(TR3) = True Then TR3 = 0

     If IsNull(TR4) = True Then TR4 = 0

     If IsNull(TR5) = True Then TR5 = 0

     If IsNull(TR6) = True Then TR6 = 0

               With RS

                  .Edit

                         !TR6 = Val(TR1 - TR2)

                         !TR3 = Val((TR1 + TR4) - (TR2 + TR5))

                    .Update

               End With

RS.MoveNext

Loop

with

Do While Not EOF(1)

    Pay = RS.Fields("EPayRef")

    TR1 = Nz(RS.Fields("EAnnualSub"), 0)

    TR2 = Nz(RS.Fields("EDiscount"), 0)

    TR3 = Nz(RS.Fields("ENetAnnual"), 0)

    TR4 = Nz(RS.Fields("ENewBillCash"), 0)

    TR5 = Nz(RS.Fields("ELeftBillCash"), 0)

    TR6 = Nz(RS.Fields("EP11DNet"), 0)

               With RS

                  .Edit

                         !TR6 = Val(TR1 - TR2)

                         !TR3 = Val((TR1 + TR4) - (TR2 + TR5))

                    .Update

               End With

RS.MoveNext

Loop

Answer 5

I think one problem is that the statements after "Define fields" should be within the Do While ... Loop.

In the EOF "1" must be "RS".