Cascading Combo Box with Value List Row Source Type (1 Viewer)

shafara7

Registered User.
Local time
Today, 12:29
Joined
May 8, 2017
Messages
118
I have two combo box on the main form and both with a Value List for the Row Source Type.
The first combo box name is Project and the second one is Inspector.
Right now, when I select a project from cboProject, the subform will be filtered only for the selected Project. Same thing goes when I select the Inspector names.

Now, I want the Inspector combo box be controlled by the Project combo Box.
When I select a project, I want the Inspector combo box to only show the related Inspectors and filter the subform.

I have searched for reference but I couldn't find any cascading combo box reference for Value List Type. I can't add query for Value List.
Any idea how?
And how can I make it a two ways cascading combo box?
For example, when I select an Inspector, the Project combo box will also be filtered.
 

isladogs

MVP / VIP
Local time
Today, 11:29
Joined
Jan 14, 2017
Messages
18,186
As you want each combo to interact with each other, you must be storing both the project and inspector data. So it makes much more sense to use a table/query row source for each
 

shafara7

Registered User.
Local time
Today, 12:29
Joined
May 8, 2017
Messages
118
So there is no other way to make cascading combo box without using table/query type?
I chose to use the Value List because there always seems to be an error with my form when I use Table/Query row source type for my combo box.
 

isladogs

MVP / VIP
Local time
Today, 11:29
Joined
Jan 14, 2017
Messages
18,186
It may be possible but it will be easier to use table/query row source
 

Orthodox Dave

Home Developer
Local time
Today, 11:29
Joined
Apr 13, 2017
Messages
218
If you must use Value Lists as the row sources, this makes it difficult but not impossible to do what you are asking.

I am assuming your row sources have 2 rows for each combo box, each containing Project and Inspector (otherwise how could you filter them?) And you want to limit the list so that, for example, if Project 1 is selected in the Project combo, you only want the inspectors for Project 1 to be listed in the Inspector combo.

So in the AfterUpdate event for Project, you need VBA code to modify the RowSource for Inspector.

Now you need code to remove the rowsource items where the Project is not the current Project. As a Value List, the Rowsource is a semicolon-separated list alternating between column0 and column1. I am assuming here that there are just 2 columns Inspector and Project (in that order).

Inspector1;Project1;Inspector2;Project1;Inspector3;Project2 ...etc

So if you take out Project1 in the above list, you also need to take out Inspectors 1 and 2, or the list will cease to alternate and you'll have inspectors appearing in the Project column and vice versa.

So use the Instr function to find the location of the semicolons. S1 will be the semicolon after the Inspector name; S2 after the Project name; S0 will be starting point of the next search

Code:
Private Sub Project_AfterUpdate()
Dim RowSourceInsp As String, S0 As Integer, S1 As Integer, S2 As Integer
RowSourceInsp = Me.Inspector.RowSource
S0 = 1: S1 = 1: S2 = 1

Do Until IsNull(S1) Or S1 = 0

S1 = InStr(S0, RowSourceInsp, ";") 'first semicolon position
S2 = InStr(S1 + 1, RowSourceInsp, ";") 'second semicolon position

'For the last item in the list, there will be no semicolon so
If IsNull(S2) Or S2 = 0 Then S2 = Len(RowSourceInsp) + 1

'using "like" because of the possibility of spaces after each colon
If Mid(RowSourceInsp,S1+1,S2-1) not like ("*" & Me.Project) then
  If S0 > 1 Then
  RowSourceInsp = Left(RowSourceInsp, S0 - 1) & Mid(RowSourceInsp, S2 + 1)
  S0 = S1 + 1 'because we took a chunk out of the middle
  Else
  RowSourceInsp = Mid(RowSourceInsp, S2 + 1)
  S0 = 1 'because we took a chunk out of the left
  End If
Else
S0 = S2 + 1 'because we took nothing out so start at the next colon +1
End If

Loop

Me.Inspector.RowSource = RowSourceInsp

End Sub

I haven't tested the above so there will probably be bugs, but this should give you the gist. Hope that helps!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:29
Joined
Aug 30, 2003
Messages
36,118
Post 5 was moderated, now approved. Posting to trigger email notifications.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:29
Joined
Sep 21, 2011
Messages
14,051
So there is no other way to make cascading combo box without using table/query type?
I chose to use the Value List because there always seems to be an error with my form when I use Table/Query row source type for my combo box.

I think you'd be better off finding out why you are having problems with a table query?, and ask for assistance on that. That method also allows for adding new criteria easily?
 

missinglinq

AWF VIP
Local time
Today, 07:29
Joined
Jun 20, 2003
Messages
6,423
I think you'd be better off finding out why you are having problems with a table query?

I agree, otherwise anytime you add an inspector, anytime you add a project, anytime you change an inspector on a project, you've got to go back into Design View for the Form, to do so, as opposed to opening a simple Table based Form to do so, then redistribute copies of your Front End, to every user, assuming that this is a multi-user environment and the database is split, as it needs to be.

Linq ;0)>
 

shafara7

Registered User.
Local time
Today, 12:29
Joined
May 8, 2017
Messages
118
If you must use Value Lists as the row sources, this makes it difficult but not impossible to do what you are asking.

I am assuming your row sources have 2 rows for each combo box, each containing Project and Inspector (otherwise how could you filter them?) And you want to limit the list so that, for example, if Project 1 is selected in the Project combo, you only want the inspectors for Project 1 to be listed in the Inspector combo.

So in the AfterUpdate event for Project, you need VBA code to modify the RowSource for Inspector.

Now you need code to remove the rowsource items where the Project is not the current Project. As a Value List, the Rowsource is a semicolon-separated list alternating between column0 and column1. I am assuming here that there are just 2 columns Inspector and Project (in that order).

Inspector1;Project1;Inspector2;Project1;Inspector3;Project2 ...etc

So if you take out Project1 in the above list, you also need to take out Inspectors 1 and 2, or the list will cease to alternate and you'll have inspectors appearing in the Project column and vice versa.

So use the Instr function to find the location of the semicolons. S1 will be the semicolon after the Inspector name; S2 after the Project name; S0 will be starting point of the next search

Code:
Private Sub Project_AfterUpdate()
Dim RowSourceInsp As String, S0 As Integer, S1 As Integer, S2 As Integer
RowSourceInsp = Me.Inspector.RowSource
S0 = 1: S1 = 1: S2 = 1

Do Until IsNull(S1) Or S1 = 0

S1 = InStr(S0, RowSourceInsp, ";") 'first semicolon position
S2 = InStr(S1 + 1, RowSourceInsp, ";") 'second semicolon position

'For the last item in the list, there will be no semicolon so
If IsNull(S2) Or S2 = 0 Then S2 = Len(RowSourceInsp) + 1

'using "like" because of the possibility of spaces after each colon
If Mid(RowSourceInsp,S1+1,S2-1) not like ("*" & Me.Project) then
  If S0 > 1 Then
  RowSourceInsp = Left(RowSourceInsp, S0 - 1) & Mid(RowSourceInsp, S2 + 1)
  S0 = S1 + 1 'because we took a chunk out of the middle
  Else
  RowSourceInsp = Mid(RowSourceInsp, S2 + 1)
  S0 = 1 'because we took a chunk out of the left
  End If
Else
S0 = S2 + 1 'because we took nothing out so start at the next colon +1
End If

Loop

Me.Inspector.RowSource = RowSourceInsp

End Sub

I haven't tested the above so there will probably be bugs, but this should give you the gist. Hope that helps!

Thank you for your great reply Orthodox Dave.
It looks very complicated. But both my combo box is not related directly to each other.
They are related by the subform.

For example, the fields in the cboProject are[ID, Name of Project] and in Inspectors are [ID, Name of Inspectors].

There is a subform that listed all the Projects and it's specifications like Project serial number, locations etc.
When I click one of the project, there will be more information showing up under the form. It acts like an Info box. And that's where the information about the inspectors are shown.

I have included a screenshot for your reference.
 

Attachments

  • Teilimport.jpg
    Teilimport.jpg
    95.3 KB · Views: 70
Last edited:

shafara7

Registered User.
Local time
Today, 12:29
Joined
May 8, 2017
Messages
118
I think you'd be better off finding out why you are having problems with a table query?, and ask for assistance on that. That method also allows for adding new criteria easily?


I agree, otherwise anytime you add an inspector, anytime you add a project, anytime you change an inspector on a project, you've got to go back into Design View for the Form, to do so, as opposed to opening a simple Table based Form to do so, then redistribute copies of your Front End, to every user, assuming that this is a multi-user environment and the database is split, as it needs to be.

Linq ;0)>

Thank you.
The error is not so problematic.
When I select a value in the combobox, nothing happend. I have to select the value in the combobox 2 times for the filter to act upon my subform.
It is an undesired error and I want it to be flawless, hence, the value list.
 

Orthodox Dave

Home Developer
Local time
Today, 11:29
Joined
Apr 13, 2017
Messages
218
Hi shafara7

The code I posted was in answer to your question
"I want the Inspector combo box be controlled by the Project combo Box"
This can only be done if each combo box contains a column referring to the other.

I have to agree with Gasman and missinglinq that it would be best to use tables to populate the combo boxes. And your problem has become clear in that you have to select the item twice before the subform responds. This is easy to fix. You just need to requery the subform in the AfterUpdate event of each combobox. Code should be as simple as:

Me.[suform name].Requery
 

Orthodox Dave

Home Developer
Local time
Today, 11:29
Joined
Apr 13, 2017
Messages
218
Sorry shafara7 that code doesn't work (Me.[subform name].Requery) - I just tried it on one of mine.

What worked was Me.Refresh
 

shafara7

Registered User.
Local time
Today, 12:29
Joined
May 8, 2017
Messages
118
Thank you Orthodox Dave!
I tried the Me.Refresh work perfect when I changed my combo box to table/query.
But I might want to change it back to Value list, just to see if your code works.
Thanks again. :)
 

shafara7

Registered User.
Local time
Today, 12:29
Joined
May 8, 2017
Messages
118
Hey Orthodox Dave, I have tried using the codes that you gave me earlier but it gets very complicated because I have 3 columns for cboProject and 2 column for cboInspector.
And I have over 60 Projects and Inspectors respectively.

Anyway I currently have the following codes..

Code:
Private Sub Form_Load()
    Dim rsType As DAO.Recordset
    Set rsType = CurrentDb.OpenRecordset("SELECT ID, txtType, txtSeries FROM tblProject ORDER BY txtType")
    cboProject.RowSource = ""
    cboProject.AddItem ("0;All;All Projects")
    Do While Not rsType.EOF
        cboProject.AddItem (rsType.Fields(0) & ";" & rsType.Fields(1) & ";" & rsType.Fields(2))
        rsType.MoveNext
    Loop
    rsType.Close
    Set rsType = Nothing
    cboProject_AfterUpdate
    updatePart "0", 0
    enableBillsInformation (False)
End Sub

Combo Box Project
Code:
Private Sub cboProject_AfterUpdate()
    If (cboProject.value = 0) Then
        subRootData.Form.FilterOn = False
    Else
        subRootData.Form.FilterOn = True
        subRootData.Form.Filter = "indType = " & cboProject.value
cboProject.Column(1) & "*'"
    End If
End Sub

Combo Box Inspector
Code:
Private Sub cboInspector_AfterUpdate()
    If (cboInspector.value = 0) Then
        subRootData.Form.FilterOn = False
    Else
        subRootData.Form.FilterOn = True
        subRootData.Form.Filter = "indInspector = " & cboInspector.value
    End If
End Sub

Any idea where I should put the Me.cboProjects.Requery or Forms![frmImportParts]![cboProjects].Requery
or any other relevant codes
So that the combo box would be cascaded?
 

Orthodox Dave

Home Developer
Local time
Today, 11:29
Joined
Apr 13, 2017
Messages
218
Hi Shafara7,

In all the examples I've seen, the FilterOn property comes after the Filter, e.g.

Me.Filter = "Country = 'USA'"
Me.FilterOn = True
not the other way round. It may not make a difference.

In your AfterUpdate code for each of the two comboboxes, you should include filters for Both comboboxes in order to filter by both. So this code should be included in both combo boxes:

Code:
Private Sub cboInspector_AfterUpdate()

    If (cboInspector.value = 0) Then

	if (cboProject.value = 0) Then
        subRootData.Form.FilterOn = False
        Else 'Project has a value (but not Inspector)
        subRootData.Form.Filter = "indType = " & cboProject.value
        subRootData.Form.FilterOn = True
        End If

    Else 'Inspector has a value

	if (cboProject.value = 0) Then
        subRootData.Form.Filter = "indInspector = " & cboInspector.value
        subRootData.Form.FilterOn = True
        Else 'BOTH Project AND Inspector have values
        subRootData.Form.Filter = "indType = " & cboProject.value & " And indInspector = " & cboInspector.value
        subRootData.Form.FilterOn = True

    End If

End Sub

That will ensure the subform filter is based on both combo boxes.
 

Orthodox Dave

Home Developer
Local time
Today, 11:29
Joined
Apr 13, 2017
Messages
218
Sorry Shafara7, I missed off one "End If" at the end of the "if (cboProject.value = 0) Then" paragraph. There's always somethilng!
 

Users who are viewing this thread

Top Bottom