Cascading Column Combo Boxes and Multiple Field Sources

[WillJ]

[FONT=&quot]Hi Guys,

I'm really new to this so all your help and comments are really appreciated, thanks in advanced!
I had spent 12 hours doing this:banghead: earlier today but to my satisfaction I've figured out one part of the puzzle; how to create 1 cascading combo box.
I have a Request form which has a variety of combos on it. The first combo is titled Request Type (cboRequestType) and the synced combo titled RequestTo (cboRequestTo).
I followed the instruction pretty much to the T in the following sequence;

Change Request Combo Event After Update to ...[/FONT]

• [FONT=&quot]Private Sub cboRequestType_AfterUpdate()
  Me.cbORequestTo.Requery
  If Me.cbORequestTo.ListCount > 0 Then
  Me.cbORequestTo = Me.cbORequestTo.ItemData(0)
  Me.cbORequestTo.SetFocus
  Me.cbORequestTo.Dropdown
  End If
  End Sub[/FONT]

[FONT=&quot]Changed Data Row Source[/FONT]

• [FONT=&quot]SELECT LocalAuthority.LocalAuthorityName, LocalAuthority.RequestType FROM LocalAuthority WHERE (((LocalAuthority.RequestType)=[Forms]![Requests]![cboRequestType])) ORDER BY LocalAuthority.LocalAuthorityName, LocalAuthority.RequestType; [/FONT]

[FONT=&quot]Mover over to changing Request To Details[/FONT]

• [FONT=&quot]SELECT LocalAuthority.LocalAuthorityName, LocalAuthority.RequestType FROM LocalAuthority WHERE (((LocalAuthority.RequestType)=[Forms]![Requests]![cboRequestType])) ORDER BY LocalAuthority.LocalAuthorityName, LocalAuthority.RequestType; [/FONT]

[FONT=&quot]And Wahoo, it works. 1 down 5 to go.[/FONT]

[FONT=&quot]The problem is;[/FONT]

1. [FONT=&quot]I'm unsure how to do this across the 4 other fields I'll need to. These other tables are Company, Contact, Universities and Events.[/FONT]
2. [FONT=&quot]Doing this in the contact field requires Combo Box Columns as the full names is split across; Salutation, First & Second.[/FONT]

[FONT=&quot]How can I write a/some query/coding that allows me to search the relevant fields to find the related data; and in the question of Contact, how do I display the multiple fields in this sequence.[/FONT]

[FONT=&quot]If it doesn't make to much sense just let me know and I'll try to explain better. [/FONT]

[FONT=&quot]Thanks
[/FONT]

 

[VilaRestal]

1. The same as you did before but for the AfterUpdate event of cbORequestTo setting the rowsource of cbCompany, he AfterUpdate event of cbCompany setting the rowsource of cboContact, etc, with ever more stringent where clauses combining the values of all higher comboboxes and perhaps including joins to the higher tables.

So the next one down, something like:

Private Sub cbORequestTo_AfterUpdate()
Me.cboRequestCompany.Requery
If Me.cboRequestCompany.ListCount > 0 Then
Me.cboRequestCompany= Me.cboRequestCompany.ItemData(0)
Me.cboRequestCompany.SetFocus
Me.cboRequestCompany.Dropdown
End If
End Sub

with cboRequestCompany's rowsource set to:

SELECT Name FROM Company WHERE LocalAuthority = [Forms]![Requests]![cboRequestTo] ORDER BY Name;

2. Is there any reason you can't combine the name components into one name: SELECT FirstName & ' ' & Surname AS Fullname... ?

Does that make sense?

 

[VilaRestal]

You still have to deal with the issue of the user changing a higher level combobox after already drilling down some of the way.

And I don't like referencing control values in expressions. It's very inefficient and makes for nasty looking formulas with Access insisting on putting brackets all over the place in where clauses and putting table names in front of fields even if there's only one table in the query.

So I would do something like this:

Private Sub cboRequestType_AfterUpdate()
    Me.cboRequestEvent = Null
    Me.cboRequestEvent.RowSource = ""
    Me.cboRequestUni = Null
    Me.cboRequestUni.RowSource = ""
    Me.cboRequestContact = Null
    Me.cboRequestContact.RowSource = ""
    Me.cboRequestCompany = Null
    Me.cboRequestCompany.RowSource = ""
    With Me.cboRequestTo
        .Value = Null
        If IsNull(Me.cboRequestType) Then
            .RowSource = ""
        Else
            .RowSource = "SELECT LocalAuthorityName FROM LocalAuthority WHERE RequestType = " _
                    & Me.cboRequestType & " ORDER BY LocalAuthorityName;"
            If .ListCount > 0 Then
                .Value = .ItemData(0)
                .SetFocus
                .Dropdown
            End If
        End If
    End With
End Sub

Private Sub cboRequestTo_AfterUpdate()
    Me.cboRequestEvent = Null
    Me.cboRequestEvent.RowSource = ""
    Me.cboRequestUni = Null
    Me.cboRequestUni.RowSource = ""
    Me.cboRequestContact = Null
    Me.cboRequestContact.RowSource = ""
    With Me.cboRequestCompany
        .Value = Null
        If IsNull(Me.cboRequestTo) Then
            .RowSource = ""
        Else
            .RowSource = "SELECT CompanyName FROM Company WHERE LocalAuthority = '" _
                    & Replace(Me.cboRequestTo, "'", "''") & "' ORDER BY CompanyName;"
            If .ListCount > 0 Then
                .Value = .ItemData(0)
                .SetFocus
                .Dropdown
            End If
        End If
    End With
End Sub

Private Sub cboRequestCompany_AfterUpdate()
    Me.cboRequestEvent = Null
    Me.cboRequestEvent.RowSource = ""
    Me.cboRequestUni = Null
    Me.cboRequestUni.RowSource = ""
    With Me.cboRequestContact
        .Value = Null
        If IsNull(Me.cboRequestCompany) Then
            .RowSource = ""
        Else
            .RowSource = "SELECT ContactID, FirstName & ' ' & Surname AS FullName FROM Contact WHERE Company = '" _
                    & Replace(Me.cboRequestCompany, "'", "''") & "' ORDER BY FirstName, Surname;"
            If .ListCount > 0 Then
                .Value = .ItemData(0)
                .SetFocus
                .Dropdown
            End If
        End If
    End With
End Sub

Private Sub cboRequestContact_AfterUpdate()
    Me.cboRequestEvent = Null
    Me.cboRequestEvent.RowSource = ""
    With Me.cboRequestUni
        .Value = Null
        If IsNull(Me.cboRequestContact) Then
            .RowSource = ""
        Else
            .RowSource = "SELECT Name FROM University WHERE Contact = " _
                    & Me.cboRequestContact & " ORDER BY Name;"
            If .ListCount > 0 Then
                .Value = .ItemData(0)
                .SetFocus
                .Dropdown
            End If
        End If
    End With
End Sub

'and so on for the other two

The exact code depends on the names of your tables and fields and the data types of the fields. But you hopefully get the idea of setting the rowsources in code and using that to ensure they show nothing if the combobox one higher hasn't had a value selected.

 

[WillJ]

Hi VilaRestal,

Firstly thanks so much for taking the time to help me out here, it's really appreciated.

I think you might have misinterpreted me, my fault as the title states cascading column combo boxes, you'll have to excuse me for that, I'm new to this and still finding might feet.

The suggestion you put forward would require that each table has a separate combo box, with each combo box dependent on the RequestType data selected. Is this correct, if I've lost it just let me know?

What I require is that there are two combo boxes only, Request Type and Request To. The RequestType combo box is bound to the Request To, as shown in the after update event.

I guess I could do it across with a combo for each table however it just seems cluttered.

Is there anyway to have multiple row sources dependent upon the RequestType combo, so if the user selects companys, the company names would appear, or would I have to do it the way you suggested.

Again thanks for your help so far.

 

[VilaRestal]

Is there anyway to have multiple row sources dependent upon the RequestType combo, so if the user selects companys, the company names would appear, or would I have to do it the way you suggested.

Yes indeed. The Row Source can be set to anything depending on anything.

You'll have to explain very clearly what the types of row sources would be for what selected in RequestType. You can change the number of columns of the combobox, the columnwidths.

If you SELECT eachField AS [Meaningful Names] in each row source you can show headers and the column headers will have the meaningful names.

But to do all this you would be setting the rowsource in code much like the code I posted. Something along the lines of:

Private Sub cboRequestType_AfterUpdate()
    With Me.cboRequestTo
        .Value = Null
        .RowSource = ""
        If Not IsNull(Me.cboRequestType) Then
            Select Case Me.cboRequestType
                Case "Local Authority"
                    .ColumnCount = 1
                    .ColumnWidths = ""
                    .ColumnHeads = True
                    .RowSource = "SELECT LocalAuthorityName As [Local Authority Name] FROM LocalAuthority ORDER BY LocalAuthorityName;"
                Case "Company"
                    .ColumnCount = 2
                    .ColumnWidths = "0cm;5cm"
                    .ColumnHeads = True
                    .RowSource = "SELECT ID, CompanyName As [Company Name] FROM Company ORDER BY CompanyName;"
            End Select
            If .ListCount > 0 Then
                .Value = .ItemData(0)
                .SetFocus
                .Dropdown
            End If
        End If
    End With
End Sub

The exact details depends on what you want and your tables' designs.

If selecting something in cboRequestTo does something then perhaps some anti-code (to undo that thing) is needed in this code because changing the selection in cboRequestFrom clears the value of cboRequestTo and maybe invalidates whatever it did.

 

[WillJ]

Hi,

Thanks for the reply.
I'll be trying to get me head round this tomorrow so will let you know how I get on.

Best,