Hello everyone,

I’m trying to build a form that uses three dropdown combo boxes.

The first one would show you all of the Managers in the company.

The second drop down would show all of the supervisors that report directly to the Managers you picked in the first combo box.
And if you don’t choose any of the managers, it would show you all of the supervisors in the company.

The third dropdown combo box will show all of the employees that report directly to the supervisor that was picked from the second dropdown combo box, and if no supervisor was picked and no Manager was picked, you would see all the employees. But if you picked a manager and no supervises in the employee dropdown combo box you would see all the employees that report directly to all the supervises that report directly to that managers


I was also hoping I could view/print a report that shows this information.

I started working on it in VBA, but continue to get a lot of errors.
Below is the code I have put together so far.

If anyone can help at all, I would be very appreciate of

Thank you again for all your help

[code]
Private Sub cmdVeiwReport_Click()
If IsNull(Me.cboManagerDropdown) Then
DoCmd.OpenReport Me.cboReportDropDown, acViewPreview

Else

If IsNull(Me.cboManagerDropDown] Then
DoCmd.OpenReport Me.cboReportDropDown, acViewPreview, , "[Manager]='" & Me.cboManagerDropDown.Column(1) & "'"

Else

DoCmd.OpenReport Me.cboReportDropDown, acViewPreview, , "[ supervisor]='" & Me.cboMagaerDropDown(1) & "' And " & "[Manager5] = '" & Me.cbosupervisor Downdown & "'"

End If
End Sub
[code]

How did you create the tables where the info is located?

Did you use a first table for the managers, a second one for the supervispors and a thirth one for the employees?
Or did you create 1 table with field 1 = manager, field 2 = supervisor and field 3 = employee?
This table would look like
field 1 field 2 field 3
John Jeff Bill
John Marc George
John Marc Anthony
David James Arnold
David James Sylvester

In this case, John would be the manager, Jeff and marc would be the supervisors from Jeff and Marc would have George and Anthony as employees, while Jeff has only Bill has his employee...

Maybe you could try something like this??

I would create a new module Named VarDec and declare two variables
Public Mgr as string, Spr as string

-------------------------------------
Then on your
Private Sub cmdVeiwReport_Click()

If IsNull(Me.cboManagerDropdown) Then
Mgr = ""
Else
Mgr = Me.cboManagerDropdown.value
End if

If IsNull(Me.cboSupervisorDropDown] Then
Spr = ""
Else
Spr = Me.cboSupervisorDropDown.Value
End if

DoCmd.OpenReport Me.cboReportDropDown, acViewPreview
End Sub

-------------------------------------
On the open event of the report
Private Sub Report_Open(Cancel As Integer)
Dim strSelect As String
Dim strFrom As String
Dim strWhere As String
Dim sql As String

strSelect = "SELECT Manager, Supervisor, Employee
strFrom = " FROM Table1"

If Mgr <> "" and Spr <> "" then
StrWhere = " WHERE Manager = '" & Mgr & "' and Supervisor = '" & Spr & "'
sql = strSelect & strFrom & strWhere
Else
End if

If Mgr <> "" and Spr = "" then
StrWhere = " WHERE Manager = '" & Mgr & "'
sql = strSelect & strFrom & strWhere
Else
End if

If Mgr = "" and Spr <> "" then
StrWhere = " WHERE Supervisor = '" & Spr & "'
sql = strSelect & strFrom & strWhere
Else
End if

If Mgr = "" and Spr = "" then
sql = strSelect & strFrom
Else
End if

Me.RecordSource = sql
End Sub

Hi bab02,

The code you provided below looks great and what I'm looking for. When I was entering the code I had a couple of places that turned red. and an error message:

Red:
If Class <> "" and Sub <> "" then StrWhere = " WHERE Class = '" & Class & "' and SubClass = '" & Sub &
"'

If Class <> "" and Sub = "" then

If Class = "" and Sub <> "" then
StrWhere = " WHERE SubClass = '" & Sub & "'

If Class = "" and Sub = "" then

StrWhere = " WHERE Manager = '" & Mgr & "' and Supervisor = '" & Spr &
"'

error: Expected:expression

Bab02, instead of this lengthy construct:

If IsNull(Me.cboManagerDropdown) Then
    Mgr = ""
Else
    Mgr = Me.cboManagerDropdown.value
End if

Use simply this one-liner:

Mgr = Nz(Me.cboManagerDropdown, "")

But in this case, you don't need that. Also, the Value property is not needed.

Corey, you need something like this:

Dim strCriteria as String

If IsNull(Me.cboManagerDropdown) Then
    strCriteria = ""
Else
    If IsNull(Me.cboSupervisorDropdown) Then
        strCriteria = "[Manager] = '" & Me.cboManagerDropDown.Column(1) & "'"
    Else
        strCriteria = "[Supervisor] = '" & Me.cboManagerDropDown(1) & _
        "' And [Manager] = '" & Me.cboSupervisorDropDown & "'"
    End If
End If

DoCmd.OpenReport Me.cboReportDropDown, acViewPreview, , strCriteria


Corey, I noticed a lot of inconsistent spelling of your fields, etc. and attributed that to just fast typing here and that they're OK in your code. I made sure they were consistent in my code.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Corey - I forgot to put the " on the end of the where clause. Try this and see if it works:

On the open event of the report
Private Sub Report_Open(Cancel As Integer)
Dim strSelect As String
Dim strFrom As String
Dim strWhere As String
Dim sql As String

strSelect = "SELECT Manager, Supervisor, Employee
strFrom = " FROM Table1"

If Mgr <> "" and Spr <> "" then
StrWhere = " WHERE Manager = '" & Mgr & "' and Supervisor = '" & Spr & "'"
sql = strSelect & strFrom & strWhere
Else
End if

If Mgr <> "" and Spr = "" then
StrWhere = " WHERE Manager = '" & Mgr & "'"
sql = strSelect & strFrom & strWhere
Else
End if

If Mgr = "" and Spr <> "" then
StrWhere = " WHERE Supervisor = '" & Spr & "'"
sql = strSelect & strFrom & strWhere
Else
End if

If Mgr = "" and Spr = "" then
sql = strSelect & strFrom
Else
End if

Me.RecordSource = sql
End Sub