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

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