Home

Macros and Programming

 

CardboxForumsMacros and Programming > "Macro for adding field totals."

Macro for adding field totals.

Need to total numbers contained in a series of fields, both for one record only and for a selection of records.

Current user: [none]
Register / Log In · Help

Posted By Post

Helen

23-Mar-2009 14:00

We have written macros that do this, and also ones that can revert the field values back to zero. However, they can take a long time to do their thing, and I get the feeling that there is a quicker way to achieve our aim.
We have a client based database (2000 records), and want to record the number of times each client attends an activity. We then want to extrapolate attendance figures for each activity per client and per selection. The date of attendance is also important; we currently record attendances within each quarter.
I would be very happy to show the macros we currently have and provide more information. I would be eternally grateful for any ideas as to how we could do this differently and/or better.

Many thanks.

bert

23-Mar-2009 14:53

Hi,
I would say, copy and paste one of your macros as an exaple in a new message.
Regards
bert

Helen

24-Mar-2009 13:51

Thanks Bert,

This is the Macro we use to total a matrix of fields both individually and collectively by year and quarterly. The Matrix counts activities attended.

For pos=1 to RecordCount
GoToRecord pos
EditRecord
GoToField "TOTCF"
Command cmdSelectAll
Command cmdDelete
TypeText Fields("CF2")+0+Fields("CF3")+0+Fields("CF4")+0+Fields("CF5")+0+Fields("CF6")+0+Fields("CF7")
GoToField "TOTCFA"
Command cmdSelectAll
Command cmdDelete
TypeText Fields("CF1A")+0+Fields("CF2A")+0+Fields("CF3A")+0+Fields("CF4A")+0+Fields("CF5A")+0+Fields("CF6A")+0+Fields("CF7A")
GoToField "TOTCFB"
Command cmdSelectAll
Command cmdDelete
TypeText Fields("CF1B")+0+Fields("CF2B")+0+Fields("CF3B")+0+Fields("CF4B")+0+Fields("CF5B")+0+Fields("CF6B")+0+Fields("CF7B")
GoToField "TOTCFC"
Command cmdSelectAll
Command cmdDelete
TypeText Fields("CF1C")+0+Fields("CF2C")+0+Fields("CF3C")+0+Fields("CF4C")+0+Fields("CF5C")+0+Fields("CF6C")+0+Fields("CF7C")
GoToField "TOTCFD"
Command cmdSelectAll
Command cmdDelete
TypeText Fields("TOTCF")+0+Fields("TOTCFA")+0+Fields("TOTCFB")+0+Fields("TOTCFC")
SaveRecord
Next

Again, Many thanks for any help or advice.

Helen

Charles Welling

24-Mar-2009 14:30

Helen, what you do here is making the cursor move through the record(s), which takes quite a lot of time. This really slows things down.

Instead of:

GoToField "TOTCF"
Command cmdSelectAll
Command cmdDelete
TypeText Fields("CF2")+0+Fields("CF3")+0+Fields("CF4")+0+Fields("CF5")+0+Fields("CF6")+0+Fields("CF7")

You could use:

Fields("TOTCF") = Fields("CF2")+0+Fields("CF3")+0+Fields("CF4")+0+Fields("CF5")+0+Fields("CF6")+0+Fields("CF7")

Change the macro in a similar way for the other fields, and you'll see a huge increase in speed.

And you may consider why you add the "0". I don't think it makes any difference.
So try this:

Fields("TOTCF") = Fields("CF2")+Fields("CF3")+Fields("CF4")+Fields("CF5")+Fields("CF6")+Fields("CF7")

bert

24-Mar-2009 16:31

You can get much more speed *not* using EditRecord but something like this:

GoToRecord 1
For pos=1 to RecordCount
    Set arec = Records(pos)
    arec.edit
    arec.Fields "TOTCF" = arec.Fields("CF2")+ arec.Fields("CF3")+ arec.Fields("CF4")+ arec.Fields("CF5")+ arec.Fields("CF6")+ arec.Fields("CF7")

'and so on - have attention: always use arec. before fieldname !!
'at the end of your calculations:

  arec.save
  NextRecord
Next

Try and see it will work at least 2-3 times faster!
Regards
Bert

Charles Welling

25-Mar-2009 07:12

And we both made a terrible mistake, Bert. Cardbox sees the content of fields as strings and adding strings makes the "+" work exactly as if it were an "&". Very confusing, but that's how it is.
So, "1" + "2" will make "12" instead of 3!
Unless Cardbox somehow "senses" that we're dealing with numbers, and that is probably why the 0 was inserted in Helen's macro. You could use NumberfromCardbox(Fields("CF2")) etc., but putting the zero back will do as well. A single zero suffices.

Fields("TOTCF") = 0 + Fields("CF2")+Fields("CF3")+Fields("CF4")+Fields("CF5")+Fields("CF6")+Fields("CF7")

Helen

26-Mar-2009 13:09

Thanks guys for all your brilliant help. I have made your suggested amendments and the following Macro works well:

For pos=1 to RecordCount
GoToRecord pos
EditRecord
Fields ("TOTC") = 0+Fields("C1")+Fields("C2")+Fields("C3")+Fields("C4")+Fields("C5")+Fields("C6")+Fields("C7")+Fields("C8")+Fields("C9")+Fields("C10")
Fields ("TOTCA") = 0+Fields("C1A")+Fields("C2A")+Fields("C3A")+Fields("C4A")+Fields("C5A")+Fields("C6A")+Fields("C7A")+Fields("C8A")+Fields("C9A")+Fields("C10A")
Fields ("TOTCB") = 0+Fields("C1B")+Fields("C2B")+Fields("C3B")+Fields("C4B")+Fields("C5B")+Fields("C6B")+Fields("C7B")+Fields("C8B")+Fields("C9B")+Fields("C10B")
Fields ("TOTCC") = 0+Fields("C1C")+Fields("C2C")+Fields("C3C")+Fields("C4C")+Fields("C5C")+Fields("C6C")+Fields("C7C")+Fields("C8C")+Fields("C9C")+Fields("C10C")
Fields ("TOTCD") = 0+Fields("TOTC")+Fields("TOTCA")+Fields("TOTCB")+Fields("TOTCC")
SaveRecord
Next

However, I am keen to get the process as quick as possible, but couldn't get the arec. suggestion to work. Here's what I have, and I admit, I'm in a little over my head now.

GoToRecord 1
For pos=1 to RecordCount
Set arec = Records(pos)
arec.edit
arec.Fields "TOTC" = 0+arec.Fields("C1")+arec.Fields("C2")+arec.Fields("C3")+arec.Fields("C4")+arec.Fields("C5")+arec.Fields("C6")+arec.Fields("C7")+arec.Fields("C8")+arec.Fields("C9")+arec.Fields("C10")
arec.Fields "TOTCA" = 0+arec.Fields("C1A")+arec.Fields("C2A")+arec.Fields("C3A")+arec.Fields("C4A")+arec.Fields("C5A")+arec.Fields("C6A")+arec.Fields("C7A")+arec.Fields("C8A")+arec.Fields("C9A")+arec.Fields("C10A")
arec.Fields "TOTCB" = 0+arec.Fields("C1B")+arec.Fields("C2B")+arec.Fields("C3B")+arec.Fields("C4B")+arec.Fields("C5B")+arec.Fields("C6B")+arec.Fields("C7B")+arec.Fields("C8B")+arec.Fields("C9B")+arec.Fields("C10B")
arec.Fields "TOTCC" = 0+arec.Fields("C1C")+arec.Fields("C2C")+arec.Fields("C3C")+arec.Fields("C4C")+arec.Fields("C5C")+arec.Fields("C6C")+arec.Fields("C7C")+arec.Fields("C8C")+arec.Fields("C9C")+arec.Fields("C10C")
arec.Fields "TOTCD" = 0+arec.Fields("TOTC")+arec.Fields("TOTCA")+arec.Fields("TOTCB")+arec.Fields("TOTCC")
arec.save
NextRecord
Next

This Macro results in the error popup: Object doesn't support this property or method: 'arec.Fields' (position in the macro: line 5, character 1)

Thank you for any further assistance you can give me with this

Helen

Charles Welling

26-Mar-2009 13:36

Helen, there should be parentheses on either side of "TOTC" in line 5:

arec.Fields("TOTC")

Helen

26-Mar-2009 14:09

Thanks Charles, that's fantastic -much quicker!

Quick Reply

Please log in or register before trying to post a reply.

 
© 2010 Cardbox Software Limited   Home