Tuesday, 31 July 2012 07:39 Last Updated on Sunday, 12 August 2012 10:53 Written by Steve Powell

Introduction

Sometimes getting multiple controls to talk nicely to each other in a Microsoft Access form can be difficult. In this example the goal is to create a form with a list box with multiple columns which the user will be able to select a company with and a combo box that will filter it’s results based on the selection in the listbox. The two controls will be used as a filter for a report that will be opened when the user clicks on a button on the form.

 

The Database

I created two tables: Companies and Work. Both are filled with junk data that I made up. The Company table has a few columns with information on the company and an ID. The Work table records work that employees do for each company (date it was done, who did it, hours worked). The idea is that the database is a log of the hours of work done which would be used by the office admin/accountant/owner to generate invoices.

The Query

On the form will be a combo box with a list of weeks i.e. weeks 1-52/53 of the year. In order to do that I created a query to calculate the week number. The key part of the query is where it calculates ServiceWeek. It takes the service date as a double (number of days from some start date) divides it by seven (i.e. days in a week) then it subtracts the 1st of the year divided by seven too. In other words, it calculates the week of the service call from the beginning of time (at least Microsoft Access’s definition) and subtracts the week of the first of the year from the beginning of time, so what your left with is the week relative to the first week of the year.

SELECT Work.ID, Work.CompanyID, Work.ServiceDate, Work.Employee, Work.Hours, Round(CDbl([ServiceDate])/7-CDbl(DateSerial(Year([ServiceDate]),1,1))/7,0)+1 AS ServiceWeek, [Companies].[Name] AS CompanyName
FROM [Work] INNER JOIN Companies ON [Companies].ID = [Work].[CompanyID];

Exhibit 1: The SQL for the WorkWeekQuery.|

The Module

The form and report will use a module to pass some parameters along. The name of the company and the work week selected will be assigned to global variables in the module then the report will assign the title based on these variables.

Public CompanyWorkWeekReportCompanyName As String
Public CompanyWorkWeekReportWeek As Integer

Exhibit 2: The module will just hold a couple of variables that we'll use to pass values between the form and report.

The Form

Our form will be super simple containing a couple of labels, a list, a combo box, and a button. Nothing fancy, this article isn’t about form design. The goal is to have the user click on a company in the listbox and the options in the combo box will change so that it lists only weeks where work was done for that company. Then when the user clicks the button, a report will open and we’ll supply both the company ID and the week to the query.

Exhibit 3: The simple search form.

To create the form just drop a listbox, combo box and a button onto the form. Change the names to whatever you want, I used cboServiceWeek and lstCompany. Now we want to set the row source for the controls. For the company list box, find the row source property and click on the “…” button. Add ID, Name, Address, and Industry to the query and uncheck show for ID, and sort on Name.

Exhibit 4: The query for the row source of the company list box.

We want the user to select a company from the company list box and then populate the combo box with the weeks that work has been done for that company. To do that we use VBA, so look for the On Click event in the properties of the company list box and click on the “…” button to add an event handler. The handler is pretty simple, we just assign the RowSource property of the combo box to a SQL query that selects all the weeks where there is a match on the company ID

Private Sub lstCompany_Click()
    Me.cboServiceWeek.RowSource = "SELECT DISTINCT ServiceWeek FROM WorkWeekQuery WHERE CompanyID = " & lstCompany.Column(0) & " ORDER BY ServiceWeek"
End Sub

Exhibit 5: The company list click handler.

Now when the user clicks the “Open Report” button we want Access to open the report, supplying it with the company ID and the service week. To do that add a handler for the click event to the button. The code for the handler checks if the week is actually selected, then assigns the company name and week to a global variable (we’ll use that later) and calls DoCmd.OpenReport to open the report and supplies it with a WhereCondition that does the filtering for us based on the selections made.

Private Sub CmdOpenReport_Click()
    If IsNull(cboServiceWeek.Column(0)) Then
        MsgBox "Please select a service week."
    Else
        WorkSearchModule.CompanyWorkWeekReportCompanyName = lstCompany.Column(1)
        WorkSearchModule.CompanyWorkWeekReportWeek = cboServiceWeek.Column(0)
        DoCmd.OpenReport "CompanyWorkWeekReport", acViewReport, WhereCondition:="[CompanyID] = " & lstCompany.Column(0) & _
        " AND [ServiceWeek] = " & cboServiceWeek.Column(0)
    End If
End Sub

Exhibit 6: The open button's click handler.

The Report

The button on the form still doesn’t do anything, we left out the report! Click on the Create tab in the Access ribbon and then the Report Wizard. Choose WorkWeekQuery and add the columns ServiceDate, Employee, and Hours. After clicking finish, change the name of the title label to something more descriptive, I used lblTitle. In the property sheet for the report click on the “…” button for the On Load. The code for the On Load handler sets up the title of the report using the global variables in the module.

Private Sub Report_Load()
    Me.lblTitle.Caption = WorkSearchModule.CompanyWorkWeekReportCompanyName & " Week " & WorkSearchModule.CompanyWorkWeekReportWeek & " Report"
End Sub

Exhibit 7: When the report loads this code fills in the title based on the values of the variables in the module.

The only other thing I changed was to add a total to the report footer that summed the Hours column. To do that I just dropped a text box in there and set the control source to =SUM([Hours]).

Conclusion

Hopefully this article gives you some ideas for designing your own forms and passing arguments between forms and reports/queries. Microsoft Access provides a good assortment of controls but some things just are not all that obvious. Although it may not seem all that elegant passing the selection between the form and report through a module is fairly simple and doesn’t really have any potential problems since Access isn’t multithreaded (i.e. no code will run between the button being pushed and the report loading).

Download

Download the sample here.