Skip to content

Sharing data between Access form and report – programatically

September 30, 2016

Sounds good right?  I have this old legacy MS Access DB that I have been supporting for a few years.  I make updates to the code from time to time, clean up the DB, and explain how things work to my customers who use it.

Recently, I added some new functionality.  Typical stuff, a form with a listbox and sort buttons.  I threw in a report with a preview option.  Where things got a little interesting is when you realize that the SQL pulling the data is the same for both the form listbox and the report.  The only difference in the SQL is the district chosen and the sort option.  So, If I could figure out programatically how to share the selected district and the sort button clicked, then I could reuse the SQL and NOT HAVE TO CREATE SEPARATE QUERY’S AND REPORTS for 5 different times. If I was not willing to solve this programatically, then I would wind up with something like this.  Its the same report! over and over.

  • report 1 – with its own saved query1
  • report 2 – with its own saved query2
  • report 3 – with its own saved query3
  • report 4- with its own saved query 4
  • report 5 – with its own saved query 5

Trust me, it happens all the time.  Too many reports and queries that are almost identical, the only difference is an option used to filter the SQL.

Got it?

First, a look at the form.  Nice right?  Notice the Sort by buttons, that are not available at the moment – but when they are, they simply tell the data in the form how to be ordered.  If the user chooses to order the recs by teacher name in the form, then that needs to be *passed along to the report.  If the user chooses to sort the data in the form by Send date, then that needs to be passed along to the report.  If the user has choosen district a, then that needs to be passed along to the report.

Got it?


When the user selects a district, the sort buttons activate.  When the user clicks on a sort button, the selection is  *remembered, but use of a public variable declared in the form. The state of the application is maintained.

When the Preview Report button is clicked – the values are passed to the report, using the this command

 DoCmd.OpenReport “rpt_district_building_kits_dates_specific_district”, acViewPreview, , , , sorttype ”sorttype passed as arg to report

Using this technique, we avoid duplicating the report and the query.  This technique allows us to *pass data between different objects in access.  From the form object to the report object.

Here is a picture of the form and resulting report, a single report, responsively to the district and sort option.


So this is what I did programatically to share both the sort and select options with one report.  Its true, you have to understand events and how they relate to things you do in the form. Look under the hood!  Notice the SQL statement in the *global area of the form – all the functions in the form can see the code.  Notice too the use of the public variable type.

In the General declaration of the form

Option Compare Database
Option Explicit
Public strsql As String
Public sorttype As String
Public districtID As Integer
Const strSQL1 = “SELECT tblDistricts.DISTRICT, tblKits.Kit_Number, tblKits.Kit_Title, tblTeachers.Teacher_FN + ‘ ‘ + tblTeachers.Teacher_LN AS Teacher_name,

… rest of long SQL join statement ”

Const strSQL2 = ” WHERE tblBookings.SchoolYearID = 19″

Const buildingSort = ” ORDER BY tblBuildings.Building_Name;”
Const weekStartSort = ” ORDER BY tblSchoolWeeks.Week_Start”
Const weekEndSort = ” ORDER BY tblSchoolWeeks_1.Week_End”
Const teacherSort = ” ORDER BY tblTeachers.Teacher_LN”
Const districtName = ” ORDER BY tblDistricts.DISTRICT”
Const kitSort = ” ORDER BY tblKits.Kit_Title”

On the district select event

Private Sub select_district_Click()

If Me!select_district.Value = 82 Then ”82 = all – show all the districts
strsql = strSQL1 & strSQL2 & districtName
strsql = strSQL1 & strSQL2 & ” AND tblDistricts.DISTRICTID = ” &           Me!select_district.Value
End If

Me!teachersList.RowSource = strsql
Call check_sortButtons

End Sub

On one of the sort buttons – setting sortype in other click events for buttons…

Private Sub sort_returnDate_Click()
sorttype = “return” ”update global variable used in report
Call sortlist
End Sub

on the sortlist function

Private Sub sortlist()
Dim lsort As String

”depending on which sort button clicked, update the lsort value using the constrant

If (sorttype = “return”) Then
lsort = weekEndSort
ElseIf sorttype = (“send”) Then
lsort = weekStartSort
ElseIf sorttype = (“building”) Then
lsort = buildingSort
ElseIf sorttype = (“teacher”) Then
lsort = teacherSort
ElseIf sorttype = (“kit”) Then
lsort = kitSort
lsort = weekStartSort ” default
End If

”MsgBox (lsort)

strsql = strSQL1 & strSQL2 & ” AND tblDistricts.DISTRICTID = ” & Me!select_district.Value & lsort

Me!teachersList.RowSource = strsql
End Sub

On the preview report click

Private Sub btnPreviewReport_Click()

”MsgBox (sorttype)

” need to close report explicitly before call open – or else!
DoCmd.Close acReport, “rpt_district_building_kits_dates_specific_district”
DoCmd.OpenReport “rpt_district_building_kits_dates_specific_district”, acViewPreview, , , , sorttype ”sorttype passed as arg to report

End Sub

On form activate

Private Sub Form_Activate()
sorttype = “start”

Call check_sortButtons
Call FillList
End Sub

Filllist function – used to populate the listbox on form

Private Sub FillList()

If Me!select_district.Value = 82 Then ”value for option all
strsql = strSQL1 & strSQL2 ” when viewing all districts sort by district name
strsql = strSQL1 & strSQL2 & Me!select_district.Value & weekStartSort
End If

”MsgBox (strsql)

Me!teachersList.RowSource = strsql

End Sub

and finally, the check sort buttons – to determine if they should be enabled

Private Sub check_sortButtons()
” if the selected district is all – then the sort options are disabled, since they dont make sense in context
” of all the districts and all the kits included in a report.
” james 9/2016

If Me!select_district.Value = 82 Then ”82 = all – show all the districts
Me!sort_sendDate.Enabled = False
Me!sort_returnDate.Enabled = False
Me!sort_building.Enabled = False
Me!sort_teacher.Enabled = False
Me!sort_kit.Enabled = False
Me!sort_sendDate.Enabled = True
Me!sort_returnDate.Enabled = True
Me!sort_building.Enabled = True
Me!sort_teacher.Enabled = True
Me!sort_kit.Enabled = True
End If
End Sub

This is the code in the report – actually, it is all in the report open event

Private Sub Report_Open(Cancel As Integer)

Dim local_strsql As String
Dim local_district As String

local_district = [Forms]![frm_districts_building_kits_dates]![select_district]
”MsgBox (local_district)

”arg sent by command below in form opening this report
” DoCmd.OpenReport “rpt_district_building_kits_dates_specific_district”, acViewPreview, , , , sorttype ”sorttype passed as arg to report
Dim argsort As String
argsort = Me.OpenArgs

”MsgBox (argsort)

”evaluate the sort arg to build the SQL and assign to sort statement
Dim sort As String

If argsort = “send” Then
sort = “ORDER BY tblSchoolWeeks.Week_Start”
ElseIf argsort = “return” Then
sort = “ORDER BY tblSchoolWeeks_1.Week_End”
ElseIf argsort = “teacher” Then
sort = “ORDER BY tblTeachers.Teacher_LN”
ElseIf argsort = “kit” Then
sort = “ORDER BY tblKits.Kit_Title”
sort = “ORDER BY tblBuildings.Building_Name”
End If

Dim districtSTR As String
districtSTR = ” AND tblDistricts.DISTRICTID = ” & local_district & “”

Dim SchoolYearID As Integer
SchoolYearID = 19
Dim schoolyearSTR As String
schoolyearSTR = “WHERE tblBookings.SchoolYearID = ” & SchoolYearID

If local_district = 82 Then
local_strsql = “SELECT tblDistricts.DISTRICT, tblKits.Kit_Number, tblKits.Kit_Title,    tblTeachers.Teacher_FN+’ ‘+tblTeachers.Teacher_LN AS Teachername,    tblBuildings.Building_Name, tblSchoolWeeks.Week_Start,    tblSchoolWeeks_1.Week_End” & _
“…long join statement” & _
” WHERE tblBookings.SchoolYearID = ” & SchoolYearID & ” ORDER BY    tblDistricts.DISTRICT, tblKits.Kit_Title”

local_strsql = “SELECT tblDistricts.DISTRICT, tblKits.Kit_Number, tblKits.Kit_Title, tblTeachers.Teacher_FN+’ ‘+tblTeachers.Teacher_LN AS Teachername, tblBuildings.Building_Name, tblSchoolWeeks.Week_Start, tblSchoolWeeks_1.Week_End” & _
” …long join statement ” & _
” ” & _
” WHERE tblBookings.SchoolYearID = ” & SchoolYearID & districtSTR & ” ” & sort
End If

Rem MsgBox (local_strsql)
Rem MsgBox (local_district)
Me.RecordSource = local_strsql

End Sub

The SQL in the report is repeated because I struggled with building the statement, so I repeated it.  If I spent more time on this, I could probably figure out how to build the statements and include in a single SQL statement – but, I got tired of messing with it.

Still, the technique is good because I avoided replicating the report and create a query for each version of the report.  Plus its good brain development to figure out how to do things like this.


From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: