Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


MS Access Forms - Passing Parameters to Queries Dynamically

How to Use MS Access Forms to Pass Values to Queries

This article describes how to dynamically change a SQL Passthrough query and a local Access query. A SQL Server View from the AdventureWorks database is used, but a local Access table may be used, too.

Creating the Data Source for the Form

Create a SQL view in the Adventure Works database. The code below is a view that pulls data about the employees in the Adventure Works database.

USE [AdventureWorks]
GO

CREATE VIEW [dbo].[vw_employees]
AS
SELECT HumanResources.Employee.EmployeeID,
Person.Contact.FirstName + CASE WHEN Len(IsNull(Person.Contact.MiddleName,''))>0 THEN ' ' + Person.Contact.MiddleName + ' ' ELSE '' END +
Person.Contact.LastName as EmployeeName, Person.Address.AddressLine1,
Person.Address.AddressLine2, Person.Address.City, Person.StateProvince.StateProvinceCode, Person.Address.PostalCode, HumanResources.Department.Name as DeptName
FROM Person.Address
INNER JOIN HumanResources.EmployeeAddress ON Person.Address.AddressID = HumanResources.EmployeeAddress.AddressID
INNER JOIN HumanResources.Employee ON HumanResources.EmployeeAddress.EmployeeID = HumanResources.Employee.EmployeeID
INNER JOIN HumanResources.EmployeeDepartmentHistory ON HumanResources.Employee.EmployeeID = HumanResources.EmployeeDepartmentHistory.EmployeeID
INNER JOIN HumanResources.Department ON HumanResources.EmployeeDepartmentHistory.DepartmentID = HumanResources.Department.DepartmentID
INNER JOIN Person.StateProvince ON Person.Address.StateProvinceID = Person.StateProvince.StateProvinceID
INNER JOIN Person.Contact ON HumanResources.Employee.ContactID = Person.Contact.ContactID

A Select statement on the new view displays the following results.

SQL View Results

Create a SQL passthrough query in Access that will connect to the SQL Server and the Adventure Works database. Save and name the Pass Through query SQL_EmployeeList.

SQL View option

Click on the Pass-Through button in the Ribbon.

SQL View

Click on the Properties button and click on the ellipsis button next to the ODBC Connect Str property.

Property Sheet

Select the Adventure Works data source. If not already created, click New and follow the Wizard to set up a new data source.

DSN selction

Create the Unbound MS Access Form

Create the unbound form in Access using a couple of labels, a textbox and a command button.

Form Design View

Add code to the button by double clicking it.

This code will be creating an Excel file of the values from the query using a FileSystemObject and DoCmd to export the data. To set the query dynamically, a QueryDef object is used.

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click
 Dim strFile As String
 Dim sFile As String
 Dim strExt As String
 Dim appExcel As Object, appexcel2 As Object
 Dim wb As Object
 Dim qdf As QueryDef

 Set appExcel = GetObject("", "Excel.Application")
 If appExcel Is Nothing Then
  Set appExcel = CreateObject("Excel.Application")
 End If

  sFile = "C:\temp\vw_Employees"
  strExt = ".xls"

  strFile = sFile & strExt
 Dim fs As Object
 Dim isthere As Boolean
  Set fs = CreateObject("Scripting.FileSystemObject")
  isthere = fs.FileExists(strFile)
 If isthere = True Then
  fs.DeleteFile strFile, True
 End If

  Set qdf = CurrentDb.QueryDefs("SQL_EmployeeList")
 qdf.SQL = "SELECT * FROM vw_Employees WHERE [EmployeeID] in(" & Forms!frmquerytest!Text1 & ")"

  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
  "SQL_EmployeeList", strFile, True

  appExcel.workbooks.Open (strFile)
  appExcel.Visible = True
  Set appExcel = Nothing
  Set fs = Nothing
  DoCmd.Close

 Exit_cmdOK_Click:
  Exit Sub
 Err_cmdOK_Click:
  MsgBox Err.Description
  Resume Exit_cmdOK_Click
End Sub

Test the form by going into FormView and entering an employee ID.

Form View

Excel opens with the data for the employeeID entered.

Excel View

Note that we used the SQL keyword "IN" instead of and = sign in building the query. We can also enter multiple Employee ID numbers using a comma to separate them.

Form View with Multiple IDs

Excel opens again with the data for the employeeIDs entered in the TextBox.

Excel View Results

Using a Linked SQL View in MS Access

This may also be done using a local version of the view by linking to it in the Access database. To do so, select the External Data tab and ODBC Database.

Select the Link to the Data Source option.

Link to Data source

Select the AdventureWorks DataSource.

DSN View

Select the view from the list of tables.

Table List Links

Create a local query and save it as qryEmployeeList.

local Employee MS Access Query

Make a couple of changes to the code behind the form by replacing the name of the SQL Passthrough query with the name of the local query.

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click
 Dim strFile As String
 Dim sFile As String
 Dim strExt As String
 Dim appExcel As Object, appexcel2 As Object
 Dim wb As Object
 Dim qdf As QueryDef

 Set appExcel = GetObject("", "Excel.Application")
 If appExcel Is Nothing Then
  Set appExcel = CreateObject("Excel.Application")
 End If

  sFile = "C:\temp\vw_Employees"
  strExt = ".xls"

  strFile = sFile & strExt
 Dim fs As Object
 Dim isthere As Boolean
  Set fs = CreateObject("Scripting.FileSystemObject")
  isthere = fs.FileExists(strFile)
 If isthere = True Then
  fs.DeleteFile strFile, True
 End If

  Set qdf = CurrentDb.QueryDefs("qryEmployeeList")
 qdf.SQL = "SELECT * FROM vw_Employees WHERE [EmployeeID] in(" & Forms!frmquerytest!Text1 & ")"

  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
  "qryEmployeeList", strFile, True


  appExcel.workbooks.Open (strFile)
  appExcel.Visible = True
  Set appExcel = Nothing
  Set fs = Nothing
  DoCmd.Close

 Exit_cmdOK_Click:
  Exit Sub
 Err_cmdOK_Click:
  MsgBox Err.Description
  Resume Exit_cmdOK_Click
End Sub

Test the form again and notice that the results are the same.

Excel View Multiple EmpIDs

Using QueryDef, it is easy to change query criteria in MS Access.