Return Access Form input to Access Report or Form or Query

This tutorial explains how to return Access Form input to Access Query, Access Report, Access Form

MS Access Search Form

In order to explains how to return Access Form input to Access Query, Access Report, Access Form, , I will illustrate with a room booking system example.

To goal of the below example is to allow employee search which room he has booked in the system.

search_form_09

Step 1 – Create a room booking database

In this database, I created four fields, each employee can book multiple room.

search_form_00

Step 2 – Create a room booking search Form

Create a Form as below, using Employee ID and Booking Date as search criteria.

– Rename Empl ID text box as EmplID_tb

– Rename Booking Date text box as bookingDate_tb

– Change Record Selectors to No

search_form_03

Step 3 – Store user Input as Global variable

User input of Employee ID and Booking Date need to be stored in VBA Global variables, and then pass these variables to a search result.

Press ALT+F11, create a new Module called “globalVar” to declare these two variables. I separate these variables in a new Module just for easy reference. searchSQL_global is for used in SQL statement that I will explain later on.

Public emplID_global
Public bookingDate_global
Public searchSQL_global

Right click on Search button > Build Event > Code Builder, a new Access Class Objects called “Form_Search_Form” is created. Copy and paste the below code

Private Sub search_btn_Click()
   emplID_global = EmplID_tb.Value
   bookingDate_global = bookingDate_tb.Value
End Sub

Once you click on the Search button, the text boxes in the Form are stored as global variable.

Return result from database using SQL

You can return SQL result in Report, Form, or Query.

Method 1: Return SQL result in Query

The below code creates a Query called “tempQry” using CreateQueryDef Method and then open it using DoCmd.OpenQuery Method.

Just in case “tempQry” already exists in database, DoCmd.DeleteObject Method deletes tempQry if it exists.

Private Sub search_btn_Click()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim qdf As DAO.QueryDef
    Dim searchSQL_global As String
    
    emplID_global = EmplID_tb
    bookingDate_global = DateSerial(Year(bookingDate_tb), Day(bookingDate_tb), Month(bookingDate_tb))
    searchCriteria = "[Empl ID] =" & "'" & emplID_global & "'" & " AND [Booking Date]=#" & bookingDate_global & "#"
    searchSQL_global = "Select * From [Schedule_Table] WHERE " & searchCriteria
    
    On Error Resume Next
    DoCmd.DeleteObject acQuery, "tempQry"
    On Error GoTo 0
    
    Set qdf = db.CreateQueryDef("tempQry", searchSQL_global)
    DoCmd.OpenQuery ("tempQry")
    'DoCmd.OpenForm "Result_Form"
    'DoCmd.OpenReport "Result_Report", acViewPreview
End Sub

Return Access Form input in Query

search_form_05

Method 2: Return SQL result in another Form

Create a Form called Result Form as below, and then

search_form_04

– Change Record Source to tempQry

– Change Allow Additions to No (not allow user to add record, because it would show an additional blank row)

– Change Record Selectors to No (Optional, I just don’t like the selector)

– Insert code in Method 1 and change the last line to DoCmd.OpenForm “Result_Form

Return Access Form input in Form

search_form_06

Method 3: Return SQL result in Report

Create a Form called Result Report as below, and then

search_form_07

– Change Record Source to tempQry

– Insert code in Method 1 and change the last line to DoCmd.OpenReport “Result_Report”, acViewPreview

Return Access Form input in Report

search_form_08

Alternative method to return Access Form input

Instead of creating a Query to use as Record Source for Form / Report, you can also use the SQL String (searchSQL_global) as Record Source to return Access Form input. However, the below method requires that the Report or Form to open first.

Private Sub search_btn_Click()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim searchSQL_global As String
    
    emplID_global = EmplID_tb
    bookingDate_global = DateSerial(Year(bookingDate_tb), Day(bookingDate_tb), Month(bookingDate_tb))
    searchCriteria = "[Empl ID] =" & "'" & emplID_global & "'" & " AND [Booking Date]=#" & bookingDate_global & "#"
    searchSQL_global = "Select * From [Schedule_Table] WHERE " & searchCriteria
    
    Forms!Result_Form.RecordSource = searchSQL_global
    'Reports!Result_Report.RecordSource = searchSQL_global
End Sub

Alternatively, set the WHERE CLA– USE (without WHERE) when the Form or Report is opened. This method requires you to set the Form or Report Control Source first.

Private Sub search_btn_Click()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim searchSQL_global As String
    
    emplID_global = EmplID_tb
    bookingDate_global = DateSerial(Year(bookingDate_tb), Day(bookingDate_tb), Month(bookingDate_tb))
    searchCriteria = "[Empl ID] =" & "'" & emplID_global & "'" & " AND [Booking Date]=#" & bookingDate_global & "#"
    
    DoCmd.OpenForm "Result_Form", acViewPreview, , searchCriteria
    'DoCmd.OpenReport "Result_Report", acViewPreview, , searchCriteria
End Sub

Or you can use the criteria in Query or SQL String

Private Sub search_btn_Click()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim searchSQL_global As String
    emplID_global = EmplID_tb
    bookingDate_global = DateSerial(Year(bookingDate_tb), Day(bookingDate_tb), Month(bookingDate_tb))
    searchCriteria = "[Empl ID] =" & "'" & emplID_global & "'" & " AND [Booking Date]=#" & bookingDate_global & "#"    
    searchSQL_global = "Select * From [Schedule_Table] WHERE " & searchCriteria

    DoCmd.OpenForm "Result_Form", acViewPreview, searchSQL_global   'or "tempQry"
   'DoCmd.OpenReport "Result_Report", acViewPreview, searchSQL_global
End Sub

 

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff820845.aspx?f=255&MSPPError=-2147217396

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *