Symmetric Cascading Comboboxes

[schwabe]

Hi all,



presently, I'm setting up a form with three comboboxes:
- one cboOEMID for selection of OEM customer,
- one cboODMID for selection of ODM customer and
- one cboProjectID for Project.

All data is comming from a query qryProjectOpen with the fields:
- IDProject
- txtProjectName
- lnkOEM
- lnkODM
- txtODMName
- txtOEMName

And presently, the user can choose a project and the other two boxes are automatically updated. The code for this looks like this:

Private Sub cboProjectID_AfterUpdate()
    ' when Project ID is updated, adjust cboOEMID, cboODMID accordingly
 
    Me.cboOEMID.RowSource = " SELECT qryProjectOpen.lnkOEM, qryProjectOpen.txtOEMName" & _
                                          " FROM qryProjectOpen" & _
                                          " WHERE qryProjectOpen.IDProject =" & Me.cboProjectID & ""
    Me.cboOEMID = Me.cboOEMID.ItemData(0)
    Me.cboOEMID.Requery
 
    Me.cboODMID.RowSource = " SELECT qryProjectOpen.lnkODM, qryProjectOpen.txtODMName" & _
                                          " FROM qryProjectOpen" & _
                                          " WHERE qryProjectOpen.IDProject =" &  Me.cboProjectID & ""
 
    Me.cboODMID = Me.cboODMID.ItemData(0)
    Me.cboODMID.Requery
End Sub

So far, so good.

What I'd like to have at the end is following:
- The user should be able to start with any of the three comboboxes and the choice for the other two should reduce automatically according to these choices.
- In order to get rid of a choice, the user should select the "All" or "*" choice which has to be added to the list of choices I am giving right now.
- Alternatively, the user could use the backspace key and erase the choice entirely and an empty combobox should result in a removal of the choice.

How can I do this?

Thanks,
Benjamin

[June7]

Never set up cascading comboboxes like that. Can only suggest you give it a try. Put code in each combobox AfterUpdate event to set the others.

If you want an 'All' or '*' option in the list, will have to UNION, like:

" SELECT qryProjectOpen.lnkODM, qryProjectOpen.txtODMName" & _
" FROM qryProjectOpen" & _
" WHERE qryProjectOpen.IDProject =" & Me.cboProjectID & _
" UNION SELECT Null, '_All' FROM qryProjectOpen;"

[schwabe]

Hello,

thanks for your reply. The problem is, however, that I have the combo boxes linked to a subform...

Benjamin

[June7]

What do you mean by 'linked'? The subform is filtered by the comboboxes values? Not sure how that is an issue.

[schwabe]

Yes, I have a subform using LinkMasterFields cboProjectID;cboOEMID;cboODMID.

[schwabe]

Maybe, I should give you some more details about what's happening:

When I add the commando as you suggested to

 
Private Sub cboProjectID_AfterUpdate()
' when Project ID is update cboOEMID, cboODMID are adjusted accordingly
 
Me.cboOEMID.RowSource = " SELECT qryProjectOpen.lnkOEM, qryProjectOpen.txtOEMName" & _
" FROM qryProjectOpen" & _
" WHERE qryProjectOpen.IDProject =" & Me.cboProjectID & _
" UNION SELECT Null, '_All' FROM qryProjectOpen;"
 
Me.cboOEMID = Me.cboOEMID.ItemData(1)
Me.cboOEMID.Requery
Me.cboODMID.RowSource = " SELECT qryProjectOpen.lnkODM, qryProjectOpen.txtODMName" & _
" FROM qryProjectOpen" & _
" WHERE qryProjectOpen.IDProject =" & Me.cboProjectID & _
" UNION SELECT Null, '_All' FROM qryProjectOpen;"
 
Me.cboODMID = Me.cboODMID.ItemData(1)
Me.cboODMID.Requery
 
 
End Sub

I can still run the form, but when I choose a project with the cboProject, the other two fields are adjusted properly. However, the subform keeps being empty (as only a valid combination will show some results..)

[June7]

So the form RecordSource has criteria that refer to the comboboxes for input? The criteria needs wildcard. Under the field for txtODMName something like:
Forms!formname!cboProjectID & "*"

However, the "_All" value from comboboxes will not work because no records have a value "_All". An empty string would.

[schwabe]

Hello all,

above you have found the vba code I used for updating the OEM and ODM combobox after a project was chosen.

I have been able to set up the row sources now somewhat differently for the cbo boxes OEM and ODM.

I've used some queries to set up the data properly, most of it is explained here: https://www.accessforums.net/queries...ies-21514.html

I've added the lnkODM to the OEM query and vice versa and used the method for updating the rowsource like it is descibed here:
http://www.blueclaw-db.com/comboboxl...pdown_list.htm

This way, I've been able to synchronize the ODM and OEM Comboboxes.

[schwabe]

Hello all,

even some more progress, by setting up a query for the project, I am now able to update the list of project according to the chosen OEM and ODM.

What's now missing is: How can I remove a criterion, e.g.
lnkOEM=[Forms]![frmAccount].[cboOEMID]

So, what I'd like to have: If the user returns a empty value in the combo box, e.g. the ODM, then there are now restrictions with respect to the ODMID active. Does anyone have an idea?

@June7: Thanks for you assistance. However, I am not sure what you want to say with your last post. Could you be more elaborate about that? !

[June7]

Is the RecordSource of the form a query with parameters in the criteria row that refer to the comboboxes for input? If so, think need wildcards if you aren't already using. Check out this tutorial for example http://datapigtechnologies.com/flash...tomfilter.html

If the query does have input parameters then a selection of "_All" from the combobox will not work because no records have this value as data. However, an empty string with the wildcard criteria will work. So if user selects "_ALL", this will have to be dealt with in the query criteria, probably with an IIf expression.

[schwabe]

Hello June7,

thanks for your reply one more. Unfortunately, this does not work properly:

I've changed one of my queries:

Before:

 
SELECT tabODM.IDODM, tabODM.txtODMName, tabProject.logRunning, tabProject.lnkOEM
FROM tabODM INNER JOIN tabProject ON tabODM.IDODM = tabProject.lnkODM
GROUP BY tabODM.IDODM, tabODM.txtODMName, tabProject.logRunning, tabProject.lnkOEM, tabProject.lnkODM
HAVING (((tabProject.logRunning)=True) AND ((tabProject.lnkOEM)=[Forms]![frmAccount].[cboOEMID]))
ORDER BY tabODM.txtODMName;

now:

 
SELECT tabODM.IDODM, tabODM.txtODMName, tabProject.logRunning, tabProject.lnkOEM
FROM tabODM INNER JOIN tabProject ON tabODM.IDODM = tabProject.lnkODM
GROUP BY tabODM.IDODM, tabODM.txtODMName, tabProject.logRunning, tabProject.lnkOEM, tabProject.lnkODM
HAVING (((tabProject.logRunning)=True) AND ((tabProject.lnkOEM) Like [Forms]![frmAccount].[cboOEMID] & "*"))
ORDER BY tabODM.txtODMName;

Now I have a multiple entries with this customer again...

[schwabe]

However, there is also good news: It works for the project combobox. So, one step closer again :-).

[June7]

This is the RecordSource for form? Is it joining tables that have a 1-to-many relationship? If so then the 'multiple' entries is to be expected.

[schwabe]

Yes, this is the Row Source for one of the combo boxes and you are right, they are joining 1-to-many realtionships.
In order to have each ODM appear just once, I have used the Group By options which was working perfectly before...
As Projects are appearing only once, there is no problem here and it's what I want to have.

[June7]

My suggestion for LIKE and wildcard was for the form RecordSource, not the combobox RowSource.