Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    South Carolina, USA
    Thanked 0 Times in 0 Posts

    VBA code to produce report in Access (Access 97/SR2)

    I have an access database that I am trying to create a report from a query. The query contains information based on various departments. The report will need to list the topics on the left and have the department names aligned from left to right. I want to be able to read the data, calculate a percentage and display the answer under the correct department. It is similar to a crosstab query or a pivot table. I have the work on the code and I have it printing department #1 information everywhere. I can't seem to get it to read the next department.

    Here is a sample:

    Standard.......Department01....Deparment 02..etc

    Data correct......100%............75%..........
    Forms correct.....100%............100%........

    The standards are hard coded in the report using a label. On the on format event, I go to the routine and and calculate the totals.
    I know there is a design flaw, it would have been easier to have all the standards as records but this was inherited and I was told to produce this report. I know that this is feasible in VBA. I have a degree in programming and in my early years could get COBOL and BASIC to do anything. My knowledge of VBA is very limited.

    Can anyone help? Is this as clear as mud?


  2. #2

    Re: VBA code to produce report in Access (Access 97/SR2)

    The Detail section of a report posts each row of data in sequence. The problem is that you're trying to force the report to work another way.
    Make a table for 'Standards' with a field for the Standard text and another field for the print order. Delete all the labels for Standard and replace with one text box. Set its control source to the Standard field.
    Make sure you have an indexing field (that matches values with the print order field in the Standards table) in the query that supplies the percentages, and join the table and query in another query to establish the relationship. Use this final query as your report recordsource.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts