Create Dynamic Reports using VBA
This has been a great post and all code works as described. However, I am still struggling with a problem, for which I haven't been able to find an answer anywhere online.
I need to create a report in Access VBA, where I manipulate the data, and not tie it to a query.
The best Access offers you is calculated control, which (again) is calculated from query data. What I need is totally different.
EXAMPLE:
I need to pull a list of transactions with a specific query.
From another table I need to pull transaction details.
The two tables share a common key.
Depending on whether transaction details are debit or credit I have to calculate running balance and obviously deside whether I will display the value - on the left or right-hand side....
Now... as you see there are many decisions to be made about the POSITION and VALUES of different info, so I cannot just tell Access -- here is the query, go play... I need to populate the report with my controls, where I say (left or right) with the value I calculate...
I have not been able to achieve this. Always reports are generated on the basis of a query and that is it.
IDEAS THAT MIGHT WORK?
• Maybe one way is to create a dummy recordset, populate it and use it as a source for the report (however I do not know if that is at all possible).
• Another possibility is to export everything to Excel, but then I am giving up the built-in Reports utility Access has, plus users must have Excel installed.
I am running out of ideas... Any help will be appreciated.