Hyteware Logo

How to design an Ms Access Query and Report that prompts a User for Criteria from another Table

In this example, we use our Employee table that has foreign keys such as 1,2,3 that point to the Position table which has records as shown below.
We don't want to store the word 'Administrator' in the Employee records so we just store the number 1 and this number is called a foreign key.


Position Table
Position IdPosition
1Administrator
2Custodian
3Driver

Now how can we design a Employee Query/Report that prompts the User to enter which positions they want to report? In other words we want to display something like this to the user so they can easily select the position to report (also see image below that shows us prompting the user by showing all our positions).

1. Administrator
2. Custodian
3. Driver

We will create a Module Function that will display the above positions and the user can make a note of the associated number for the position that he wants to report and then enter that number when prompted by the functions inputbox statement (see the function below).

Below we will show how to create this module function that displays all of the positions and lets the user enter the number of the posittion they want to report.

Paste this VBA module code below into a module and save it as any name you like. This function will be called by the query that we will show you how to design.

Notice that the function name 'Present_Positions' is used in the query definition (see image below) and tells the query to call this function. The function will serial through all the 'position' table records, and create an msgbox prompt that displays them all and allows the user to select which position id to use for their query.

Public Function Present_Positions() As String 'use DAO to read the current database Present_Positions = "" Dim wrkJet As Workspace Dim dbsNorthwind As Database Dim wrkODBC As Workspace Dim conPubs As Connection Dim conLoop As Connection Dim prpLoop As Property On Error GoTo ErrorHandlerRead Set wrkJet = CreateWorkspace("NewJetWorkspace", _ "admin", "", dbUseJet) Set dbsNorthwind = CurrentDb Dim rstTemp As Recordset Dim sSel As String Dim sKey As String Dim sField As String sSel = "Select * from Position order by [position id]" Set rstTemp = dbsNorthwind.OpenRecordset(sSel, dbOpenDynaset, dbReadOnly) rstTemp.MoveLast rstTemp.MoveFirst Dim sPositions As String sPositions = "These are the positions " & vbCrLf Dim pArray(40) As String Dim idx As Integer Do Until rstTemp.EOF idx = rstTemp("Position Id") pArray(idx) = rstTemp("Position") sPositions = sPositions & idx & ". " & rstTemp("Position") & vbCrLf rstTemp.MoveNext Loop MsgBox sPositions Present_Positions = InputBox("Enter Position Id", "Select Employees with Selected Position", "") If Len(Trim(Present_Positions)) = 0 Then Present_Positions = "" Exit Function End If Present_Positions = pArray(Present_Positions) dbsNorthwind.Close wrkJet.Close On Error Resume Next Exit Function ErrorHandlerRead: MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description & vbCrLf & sSel On Error Resume Next End Function



Query Design where we simply reference our function 'Present_Position' in the Position.Position criteria which means that when the query executes, it will call our function and that function will return the literal position that the user selected, for example 'Driver'

This image shows us the report we are designing which will report our Employees and their position. Since Reports drive off of Queries as does this one, the user will be prompted to enter which position they want to report when this runs because it uses the Query we designed.

Showing the User all our position so that the next imput box prompt, found in our function, can get the number of the Position the user wants to report