Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 26th, 2006, 01:22 PM
Friend of Wrox
 
Join Date: Sep 2005
Location: , , USA.
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Corey
Default Filter by Combo Boxes

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]


Reply With Quote
  #2 (permalink)  
Old May 27th, 2006, 12:08 PM
Friend of Wrox
 
Join Date: Apr 2006
Location: Ternat, , Belgium.
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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...



Reply With Quote
  #3 (permalink)  
Old May 28th, 2006, 08:04 PM
Authorized User
 
Join Date: Jul 2004
Location: , , .
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #4 (permalink)  
Old May 30th, 2006, 12:13 PM
Friend of Wrox
 
Join Date: Sep 2005
Location: , , USA.
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Corey
Default

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


Reply With Quote
  #5 (permalink)  
Old June 1st, 2006, 08:15 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
Reply With Quote
  #6 (permalink)  
Old June 1st, 2006, 07:33 PM
Authorized User
 
Join Date: Jul 2004
Location: , , .
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Subform Filter based on Multiselect Combo boxes natwong Access 2 November 16th, 2006 10:05 AM
Subform Filter using Multiple Combo boxes natwong BOOK: Expert One-on-One Access Application Development 0 November 13th, 2006 03:07 PM
Using check boxes to filter dstein4d Access VBA 13 January 25th, 2006 07:50 PM
Combo boxes socoolbrewster Access 1 March 4th, 2004 09:28 AM



All times are GMT -4. The time now is 09:16 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.