Results 1 to 2 of 2
2001-07-02, 14:31 #1
- 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:
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?
2001-08-01, 21:17 #2dave_hillGuest
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.