Cascading Combo Boxes and Form or Alternative Method

[TopherGeorge]

Hi,

I have a desperate problem of which I can not get to grips with using cascading Combo Boxes. I am working on a database for project managers to allow them to get a holistic overview of their projects. I am normally an Aircraft Engineer in the Royal Navy not a database expert!!!
I have imported the main project data file, Project_Data.xmlx. as a table which is where all the info is stored for the 200 projects.

The primary key is the ProjectID and there are a further 30 fields. I need to create a form with cascading combo boxes to identify the following fields:

ChangeExecutive
ProgramManager
ProjectManager
ProjectProgram
ProjectID

Further fields include; FinancialForcast, ProjectStatus, ProjectRisks, ProjectDescription, GeographicalRegion, PrjStartDate, PrjEndDate, the list goes on. I am not breaking up this main table when first imported as it needs to be left together.


From these fields I need to refine the original Project_Data Tble (to include the remaining 30 fields) and bring up a new table based on the filtered fields above, and from there create differnt pivot charts based on ProjectStatus and Financial Data.

What is the best way to go about this? This should be included on the welcome screen. Up until now I have been having trouble.

 

[TopherGeorge]

P.S if anyone can offer me some help I will fedex them wine!!!! ha

 

[VilaRestal]

Let's see if I've got this right:

You want a form with five comboboxes along the top.

Four of them are disabled at first. Only the first is enabled and that lists all Change Executives.
The user must select one and that enables the second combobox that lists all Program Managers for that Change Executive.
When they select a Program Manager, that enables the third combobox that lists all Project Managers for that Change Executive/Program Manager combination.
When they select a Project Manager, that enables the fourth combobox that lists all Project Programs for that Change Executive/Program Manager/Project Manager combination.
When they select a Project Program that enables the fifth combobox that lists all Project IDs for that Change Executive/Program Manager/Project Manager/Project Program combination.
And when they select a Project ID the form displays the record.

If so then this is how I'd do it:

Start with the form based on the table with allow additions turned off.
Put the unbound comboboxes in the header, name them appropriately, and set the rowsource for the first one to "SELECT DISTINCT ChangeExecutive FROM Project_Data ORDER BY ChangeExecutive" and set it to limit to list (unless Change Executives are in a lookup table, which they should really be, in which case set that as the rowsource).
In the form's module add code in its Load event to filter out all records at the start:
Me.Fitler = "1 = 0"
Me.FilterOn = True

Then you'd add code in each combobox's AfterUpdate event that sets the rowsource of the next combobox and enables it (and resets the forms filter to 1= 0 if need be and clears and redisables all comboboxes beyond the next one). Except the final combobox, which will set the filter of the form to the selected Project ID.

 

[TopherGeorge]

Oh good, so I prob should move some of that to look up tables. once all that's selected I'm guessing I can easily produce a new table and work with the results? How can I then link through to this next screen. Sorry I'm used to programming in Matlab and Simulink.

From that initial look up screen I need to go into a page that gives two main areas of options (again all based on the results just derived)

Section A: Canned Reports;

project status report, project health report, project overview, project financials, project descriptions​

section B: Free form reports

This will be more pivot based and give the user the ability to derive their own reports​

I am also looking at writing code to download and create a local copy of the source Project_Data file which is hosted on an intranet site. Is there a good way of doing this?

 

[TopherGeorge]

Or do u think there is a batter way of doing this database?

 

[VilaRestal]

I don't completely understand what you're envisaging so I couldn't say there's a better way. And besides you seem to have a clear and detailed idea so - go with it.

It's all quite complex though: There's a lot of levels to it.

As the other sections are based on the same user-selected record then they may as well exist on the same form. Within tab pages perhaps. And as there's a lot of fields in the forms table you're probably going to need a tab control to fit them all on.

The reports and pivot tables would warrant a new thread on this forum each when you get to that stage.

The cascading comboboxes is enough of a task to be getting on with for now

 

[TopherGeorge]

Your a star. Just given me food for thought. I have my story boards all planned out but when it got down to it I realized how complex this all way. I think that last comment is most helpful. I have only just got into databases. But they seem interesting. If u want I can email u my story board of what I'm trying to create. Typical military fashion though I have only been given 3-4 weeks to do it all and continue learning. Full steam ahead.

 

[VilaRestal]

I've got my own databases to be working on so I can't give too much help - sorry

Just do what you can and ask for help if you get stuck.

I expect it's important you understand how it all works when it's finished and that's more likely if you solve things the hard way

Just keep it as simple as possible.

Good luck and have fun

 

[TopherGeorge]

Let's see if I've got this right:


Start with the form based on the table with allow additions turned off.
Put the unbound comboboxes in the header, name them appropriately, and set the rowsource for the first one to "SELECT DISTINCT ChangeExecutive FROM Project_Data ORDER BY ChangeExecutive" and set it to limit to list (unless Change Executives are in a lookup table, which they should really be, in which case set that as the rowsource).
In the form's module add code in its Load event to filter out all records at the start:
Me.Fitler = "1 = 0"
Me.FilterOn = True

Then you'd add code in each combobox's AfterUpdate event that sets the rowsource of the next combobox and enables it (and resets the forms filter to 1= 0 if need be and clears and redisables all comboboxes beyond the next one). Except the final combobox, which will set the filter of the form to the selected Project ID.

This is where I am having trouble. I dont really understand how to do this "Set the rowcourse of the next combo box and enables it"

 

[VilaRestal]

OK, let say the comboboxes are called:
cboFilter_ChangeExec
cboFilter_ProgramMan
cboFilter_ProjectMan
cboFilter_ProjectProg
cboFilter_Project

Then the cbo's after update events would look something like this:

Private Sub cboFilter_ChangeExec_AfterUpdate()
    'Apply 'show-no-records' filter to form:
    Me.Filter = "1 = 0"
    'Clear and disable all lesser filters:
    cboFilter_ProjectMan.Value = Null
    cboFilter_ProjectMan.Enabled = False
    cboFilter_ProjectProg.Value = Null
    cboFilter_ProjectProg.Enabled = False
    cboFilter_Project.Value = Null
    cboFilter_Project.Enabled = False
    With cboFilter_ProgramMan
        .Value = Null
        .Enabled = False
        If Nz(cboFilter_ChangeExec.Value,"") <> "" Then
            .RowSource = "SELECT DISTINCT ProgramManager FROM Project_Data WHERE ChangeExecutive = " _
                    & Chr(34) & cboFilter_ChangeExec.Value & Chr(34) & " ORDER BY ProgramManager"
            .Enabled = True
        End If
    End With
End Sub


Private Sub cboFilter_ProgramMan_AfterUpdate()
    'Apply 'show-no-records' filter to form:
    Me.Filter = "1 = 0"
    'Clear and disable all lesser filters:
    cboFilter_ProjectProg.Value = ""
    cboFilter_ProjectProg.Enabled = False
    cboFilter_Project.Value = ""
    cboFilter_Project.Enabled = False
    With cboFilter_ProjectMan
        .Value = Null
        .Enabled = False
        If Nz(cboFilter_ProgramMan.Value,"") <> "" Then
            .RowSource = "SELECT DISTINCT ProjectManager FROM Project_Data WHERE ChangeExecutive = " _
                    & Chr(34) & cboFilter_ChangeExec.Value & Chr(34) & " AND ProgramManager = " _
                    & Chr(34) & cboFilter_ProgramMan.Value & Chr(34) & " ORDER BY ProjectManager"
            .Enabled = True
        End If
    End With
End Sub


Private Sub cboFilter_ProjectMan_AfterUpdate()
    'Apply 'show-no-records' filter to form:
    Me.Filter = "1 = 0"
    'Clear and disable all lesser filters:
    cboFilter_Project.Value = Null
    cboFilter_Project.Enabled = False
    With cboFilter_ProjectProg
        .Value = Null
        .Enabled = False
        If Nz(cboFilter_ProjectMan.Value,"") <> "" Then
            .RowSource = "SELECT DISTINCT ProjectProgram FROM Project_Data WHERE ChangeExecutive = " _
                    & Chr(34) & cboFilter_ChangeExec.Value & Chr(34) & " AND ProgramManager = " _
                    & Chr(34) & cboFilter_ProgramMan.Value & Chr(34) & " AND ProjectManager = " _
                    & Chr(34) & cboFilter_ProjectMan.Value & Chr(34) & " ORDER BY ProjectProgram"
            .Enabled = True
        End If
    End With
End Sub



Private Sub cboFilter_ProjectProg_AfterUpdate()
    'Apply 'show-no-records' filter to form:
    Me.Filter = "1 = 0"
    'Clear and disable all lesser filters:
    With cboFilter_Project
        .Value = Null
        .Enabled = False
        If Nz(cboFilter_ProjectProg.Value,"") <> "" Then
            .RowSource = "SELECT DISTINCT ProjectID FROM Project_Data WHERE ChangeExecutive = " _
                    & Chr(34) & cboFilter_ChangeExec.Value & Chr(34) & " AND ProgramManager = " _
                    & Chr(34) & cboFilter_ProgramMan.Value & Chr(34) & " AND ProjectManager = " _
                    & Chr(34) & cboFilter_ProjectMan.Value & Chr(34) & " AND ProjectProgram = " _
                    & Chr(34) & cboFilter_ProjectProg.Value & Chr(34) & " ORDER BY ProjectID"
            .Enabled = True
        End If
    End With
End Sub



Private Sub cboFilter_Project_AfterUpdate()
    If Nz(cboFilter_Project.Value,0) <> 0 Then 
        Me.Filter = "ProjectID = " & cboFilter_Project.Value
    Else
        Me.Filter = "1 = 0"
    End If
End Sub

I've assumed the fields are all strings except ProjectID
And I haven't tested that, but off the top of my head, I think it should work.

 

[TopherGeorge]

That is amazing, I will give it a try now and report back to you. Again thank you so much for your help it is very much appriciated.