Using VBA to populate text fields with report statistics

[dabowles]

I have an Access report that I developed that essentially is just a report that was developed from the Access Report Wizard. At the end of the report, I modified the form to include two end pages that both of them break down the record totals that were listed in the previous pages. I had my report basically finished when I received some additonal requirements from my boss. After adding in an additonal column of text boxes and populating them with '=Sum(IIf())...' statements, I realized that the last four that I added caused an error "Query too complex". The last four were no different than the eight before that, other than they referenced months August through December. I finally realized I had too many calculations for Access to handle on one report. The only alternative I can think up now is to write VBA code to populate the text fields instead of Access Built-In functions. I have the form designed exactly how I want it and it does fine with everything but the last two pages. I want to be able to go into the footer header, choose the On Print method and start developing the assignment statements.

My question is two-fold. First, is there an easier way of doing this other than having, in my case, 120 lines that say:

Me!Text1 = ...
Me!Text2 = ...
...
Me!TextN = ...

Keeping in mind that in each one of these text fields I am wanting to use a conditional statement to only sum RecordSet entries that meet a certain criteria.

Secondly, I can't quite visualize how I would even get started with this. Specifically, I know how to manipulate recordsets in VB6, but I've never done it before programmitically via VBA and on an Access Report. I'm not looking for step by step hand-holding, if someone could point me to a snippit of code or an entire report's VBA Module, that includes situations like I referenced above, taht would be a great help.

Thanks,

David B.

 

[BoxHead]

David,

When I've gotten that "too complex" message in the past, it has usually been because of some syntax or data type issue.

I don't think you need code, I think you need a totals query as the recordset for a subreport taht you insert into your existing report.

Build a new query on your report table, grouping it by month and summing your values. See if you can't get the data you need for the report calculated in the query rather than doing it one-at-a-time on the report.

If you have trouble, post back with a little more detail on your table and field names and what you are looking for.

HTH


John

Use what you have,
Learn what you can,
Create what you need.

 

[powella]

David,

Have you considered opening a record set in your code? If you report is not to slow you could requery the data with a group by clasue and get your totals.

    Dim conn as String
    Dim rsQuery As dao.Recordset
    conn1 = "Slect * from talbe1"
    Set rsQuery = CodeDb.OpenRecordset(conn, dbOpenSnapshot)

    Me!text1 = rsQuery!MonthlyTotal