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.

Step 1 – Create a room booking database

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

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

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.

Step 4 – 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 = "where [Empl ID] =" & "'" & emplID_global & "'" & " AND [Booking Date]=#" & bookingDate_global & "#"
    searchSQL_global = "Select * From [Schedule_Table] " & 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

Method 2: Return SQL result in another Form

Create a Form called Result Form as below, and then

– 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

Method 3: Return SQL result in Report

Create a Form called Result Report as below, and then

– 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

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 (searchSQL_global) as Record Source to return Access Form input.

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 = "where [Empl ID] =" & "'" & emplID_global & "'" & " AND [Booking Date]=#" & bookingDate_global & "#"
    searchSQL_global = "Select * From [Schedule_Table] " & searchCriteria
    
    DoCmd.OpenForm "Result_Form", acViewPreview, searchSQL_global
    '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

 

Return Access Form input to Access Report or Form or Query