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.
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
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 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