Using VBA Events in Access Code

[cathylp]

Hi all. (Sorry this is so long)I am reading a billing data base and creating a listing of the billing records by type of service being billed for. These records are generated by many customers and many customers have more than one billing record for service received. Access easily created the report that I needed. But now my user wantes to know how many customers received each service, counting each customer only once for the service they received but to continue to list them for all of the services that they received.

Is there an Event that I can use to add code after a record has been read to check and see if it is the same customer number as the last record read and if it is not the same then add it to a customer counter. I would then print that counter and it's label before moving onto the next type of billing service.

For example:
Billing Service - Group Therapy
Customer # Date of Service Amount Billed
0001 01/02/07 25.00
0001 01/09/07 25.00
0002 01/05/07 25.00
0005 01/02/07 25.00
Totals for Group Therapy 100.00 Cust= 3

The first two lines are generated by the group section header. The next four lines are generated by the detail section and the last line is generated by the group section footer.

Is it possible in Access to read a record/row, test to see if it equals the last customer id read and if it does not to add it to a counter which can then be printed in the footer for that group?

Thanks so much for you help. I can do this in VB6 but the user wants it in Access if possible.

 

[SkipVought]

Hi,

I would think that you would NOT want to use a REPORT as source for your taks: rather the tables themselves. Perform a query to get your counts. I don't believe you need VBA to do this.

Skip,

_[red][/red]

 

[cathylp]

Hi Skip,

Thank you for replying so quickly to my post. Sorry that I was not clear in my question.

I am running this report against a query that contains all of my billing records, since I need to display them all. But I only want to count records with unique customer numbers within each service that they received. So a customer can be counted more than once in a report but only once for each service received. So as I am moving my way through the sorted records in the query, I want to count a customer if he has received the service for the first time but not to count him again for that same service if he received it more than once.

For example for the service of group therapy, John has group therapy five times this month and Joe and Charlie have group therapy each once this month. I will print each of the seven billing records but my customer count will only be three, counting John once and Joe and Charlie once.

If I was using VB6, as I read each record in the table, I would check the customer number for the current record against the customer number from the previous record and if they were different I would add 1 to an accumlator field to be displayed in the group footer for the service rendered (ie: group therapy, individual therapy, etc). If the customer numbers were the same, I would know that he/she had already been added to the accumulater field and would not add him/her again.

Sorry for the confusion. I really appreciate your replying to my post.