Access: About Report selections and adding time

I decided to blog this post after a question from Stojan in the Access forum. Sometimes it's difficult to explain something in a thread in a forum/newsgroup. You always have the change of ending up in a very long thread explaining things you mean to tell. So after Stojan posted his question i told him i would blog about it. So here we go; opening reports based on parameters and 'summing' time in a report.

Choosing the right report and setting it's parameters
Very often the final output of the database ends up in a report. Wouldn't it be nice to present the user with some kind of form where he can choose the report he wants to see and set the parameters he wants to use. Over time i noticed i had to find a way to present the user with a choice and make it maintainable for the developer. So I came up with the idea of using a listbox in combination with comboboxes where you can set the parameters. It's nothing fancy and a lot of people will use a similar technique. Here's how it works.

Report table
First i created a reporttable in which i store all my reportnames. The first field is the actual reportname you give the reportobject in your database. The second field i added i use for a description for the report. This table is used as a source for the listbox i created on the form. The first column is set to zero width (0) and is the bound column. The second column (description) is what i will show to the user.

Now you have to add some other controls to the form which make it possible for the user to perform the various actions.
Start by placing the various comboboxes on the form. After that place a close button and a print preview button on the form. The basic idea will be that the user clicks a report on the left from the listbox. Based on the selected report the various comboboxes will be enabled or disabled. When the parameters are set the user can click print preview and the report will be shown.

I'm an old school kind a guy and learned not to use macro's other then for a startup action so i added some code behind the form in VBA. Remember i always try to keep everything as simple as possible so the code i write behind the form is very straight and very simple.

Which report is selected
The first piece of code i've written is to see which report is selected and which comboboxes should be enabled. You can see the actual code in the sample database i provided. But basically it comes down to this:

I declare a variable "strReport". Next i add the value of the listbox by setting it as follows:
strReport=me.lstReportChoice
Because the first column is set to zero width but it is the bound column the variable will be filled with the actual report name. Knowing the actual reportname you can now set the various comboboxes to enabled or not. That's the process you have to decide because som of them need to be enabled and others not.

Open the report based on the parameters
Ofcourse the reports that need to be opened should be created first. If you make it very dynamic you can create one report based on a query. In the query you can set the various parameters for the form. If no parameters are set the query will show all records otherwise it will use the set parameters.
So for instance say you have two parameters set e.g. Department and Employee.
In your query you can set the employee criteria to: forms!frmReport!cboEmployee. In this case it will only give output if an option is chosen and otherwise it will show nothing. So by adding just a little more information we can tackle this: like (forms!frmReport!cboEmployee) & "*" This way when no selection is made from the combobox all records will be shown. You can set a similar criteria for the department field in that same query.

Users like output
How strange it may seem the user always expects ouput no matter what. So if he forgets to set a parameter he still expects ouput. Based on that knowledge it's better to show all records instead of none at all. If a user gets no output at all the mind is going to work asking what he did wrong causing it to get no output. So i got used to showing everything instead of nothing. One exception though; if you have thousands of records to show it might be better to present the user with a messagebox stating that he forgot to make a selection instead of presenting him with thousands of records of information.

Summing time
The second part of the question Stojan had was about summing time. He had two fields (well actualy three) in which he entered time namely "Start Time", "End Time" and "Total Time". As you should know the field "Total" indicates that a calculation has to be made. Calculations should not be stored in your tables but be comrpomised fields in queries or reports. So answer the question for Stojan I added two fields one in his form and one is his report.

The only thing you have to adjust is setting the sourcecontrol to the following: =StartTime - 1 - EndTime
Furthermore i've set the fields to Date time (short time format). And that gave the desired output Stojan was looking for.

Ive added a sample database with this post so you can see the reportform an adding time option.

you can find it here:

http://cid-d6dbe8d993ad8c3e.skydrive.live.com/browse.aspx/Access%20Downloads