Combo Box on Parameter Form

  • Thread starter Thread starter BeaBea
  • Start date Start date
B

BeaBea

I have a parameter form I use to run reports from. On the form I have two
combo boxes one for supervisor and one for employee. I also have a field for
start date, a field for end date and a control to run the report.

In the row source for the Supervisor I have: SELECT"All" As Supervisor
FROM tblMain UNION SELECT Supervisor FROM tblMain ORDER BY Supervisor. The
column count and bound column are 1.

In the row source for the employee I have: SELECT employee FROM
tblemployee. The column count and bound column are set to 1.

When I run the report and I select the supervisor name and select the
employee name, the report gives me all of the employees. How can I set this
up to select just one supervisor and one employee?
 
One approach might be to make those comboboxes "cascading" (search on
"Cascading Comboboxes" on-line or at mvps.org/access).

This way, after selecting a supervisor, the "employee" combobox would only
display that supervisor's employees.

Then, after selecting an employee, your query that 'feeds' the report would
use the supervisor-combobox and the employee-combobox as criteria.

Of course, the details will depend on exactly how your data (tables) are
structured.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
You can turn your combos into what is known as Cascading Combos. It is a
technique that does exactly what you are wanting to do. The concept is you
filter the query in the Employee combo to include only those employees that
are assigned to the supervisor. It, of course, requires that you have the
employee's supervisor identified in the employee table.

It takes a bit of extra effort when you are using the All option. In this
case, I do not assign a row source to the second combo, but assign it at run
time so it will pull either all the records or those for a specific
supervisor.

Note that I am using autonumber key values and that the primary key of the
employee's supervisor is the value carried in the employee table as the
foreign key. There are several good reasons to relate your data this way.

Here is some example code.

Private Sub cboSupers_AfterUpdate()
If Me.cboSupers = 0 Then
Me.cboEmps.RowSource = "SELECT tblEmp.EmpId, tblEmp.EmpName " & _
"FROM tblEmp;"
Else
Me.cboEmps.RowSource = "SELECT tblEmp.EmpId, tblEmp.EmpName " & _
"FROM tblEmp WHERE SuperID = Forms!frmCascade!cboSupers;"
End If
Me.cboEmps.Requery
Me.cboEmps = Me.cboEmps.ItemData(0)
End Sub

'You need to use the form Load event to populate the combo boxes when the
form opens:

Private Sub Form_Load()
Me.cboSupers = 0
Call cboSupers_AfterUpdate
End Sub
 
The two combo boxes on my form are FindSup and FindEmp. The table with the
employee name is tblEmployee. The field for supervisor on the tblEmployee is
cmbSupervisor. I do not have anything in the row source of the second combo
box. I used the code you provided but I keep getting a syntax error. I
typed exactly as below. I have triple checked the code and cannot see what
is wrong with it.

Private Sub FindSup_AfterUpdate()
If Me.FindSup = 0 Then
Me.FindEmp.RowSource = "SELECT tblEmp.EmpName,"
tblEmp.EmpName " & EmpName"
"FROM tblEmp;'
Else
Me.FindEmp.RowSource = "SELECT tblEmp.EmpName,"
tblEmp.EmpName " &_"
FROM tblEmp WHERE FindSup = cmbSupervisor
Forms!FrmCascade!FindSup;'
End If
Me.FindEmp.Requery
Me.FindEmp = Me.FindEmp.ItemData(0)

End Sub
 
I tried changing the code as follows and I keep getting error messages:
Compile Error: Expected End of Statement and Compile Error: Syntax Error.

I also tried the website mvps.org/access as suggested in an earlier post but
cannot seem to get this to work.

The combo boxes on my form are: FindSup and FindEmp.

Private Sub FindSup_AfterUpdate()
If Me.FindSup = 0 Then
Me.FindEmp.RowSource = "SELECT tblEmp.EmpName,"
tblEmp.EmpName " & _
"FROM tblEmp;"
Else
Me.FindDBS.RowSource = "SELECT tblEmp.EmpName,"
tblEmp.EmpName " &_"
FROM tblEmp
WHERE FindSup = Forms!ParamForm2!FindSup;"
End If
Me.FindEmp.Requery
Me.FindEmp = Me.FindEmp.ItemData(0)

Does anyone have any other suggestions as to how to get the cascading combo
box to work?

End Sub
 
Back
Top