Repopulating 2 combo boxes based on selections from each

[ChasBoots]

Ok, first, I apologize for the length of the subject. I didn't quite know how to describe my problem. Second, I learned a wealth of knowledge just from perusing and searching all these forums so my thanks go out to all those who have ever answered a post. Third, I couldn't really find a solution to my problem, hence my turn post.

I have a form with 2 combo boxes that ultimately provide data for a query that populates a subform. Both controls are unbounded. Combo1 gets its data from a very simple query based on a table:

SELECT LU_APPNAME.APPNAME
FROM LU_APPNAME
ORDER BY LU_APPNAME.APPNAME;

By default, Combo2 is based on the following code (please note that the fields used to populate each control are linked THROUGH multiple tables):

SELECT LU_SERVERNAME.SERVERNAME, LU_APPNAME.APPNAME FROM LU_SERVERNAME INNER JOIN (LU_SERVER INNER JOIN (LU_APPNAME INNER JOIN SDB_SESSION ON LU_APPNAME.PK_APPNAMEID=SDB_SESSION.FK_APPNAMEID) ON LU_SERVER.PK_SERVERID=SDB_SESSION.FK_SERVERID) ON LU_SERVERNAME.PK_SERVERNAMEID=LU_SERVER.FK_SERVERNAMEID GROUP BY LU_SERVERNAME.SERVERNAME, LU_APPNAME.APPNAME HAVING (((LU_APPNAME.APPNAME)=Forms![frm_User Session Metrics with Subform]!AppName)) ORDER BY LU_SERVERNAME.SERVERNAME, LU_APPNAME.APPNAME;

When a value in Combo1 is selected, the AfterUpdate event fires and requeries Combo2 to display a reduced list of choices based on Combo1:

  Me![SERVERNAME].Requery

All of this works as expected to produce desired results. What I want to do now is the reverse within the same form. In other words, I want to select a value from Combo2 which would alter the contents of Combo1 accordingly. I will illustrate further if it helps:

(Initial State)
Combo1 = "Access", "Word", "Excel", "Power Point", "Project",...,"AppN"
Combo2 = "Server1", "Server2", "Server3",...,"ServerN"

(Current Configuration)
Combo1 = "Access"
Combo2 = "Server2", "Server5",...,"ServerX"

(Desired Action)
Combo2 = "Server4"
Combo1 = "Word", "Project",...,"AppX"

Thanks in advance for your assistance...
Charlie

 

[TheAceMan1]

How are ya ChasBoots . . . . .

You need to:
[ol][li]Add a [blue]control that toggles[/blue] the [purple]RowSource[/purple] of the comboboxes ([blue]Normal/Reverse[/blue]). This could be a Toggle Button, Option Group . . . ect.[/li]
[li]Have [blue]4 Queries or SQLs[/blue]:
[ol a][purple][li] Query1 or SQL1 as Combo1 Normal[/li]
[li]Query2 or SQL2 as Combo2 Normal[/li]
[li]Query3 or SQL3 as Combo1 Reverse[/li]
[li]Query4 or SQL4 as Combo2 Reverse[/li][/purple][/ol]
Normal has criteria in Query2 or SQL2 that looks as Combo1 selection.
Reverse has criteria in Query3 or SQL3 that looks at Combo2 selection.[/li]
[li]In the [blue]AfterUpdate[/blue] events of the combos, [blue]check the state of the toggle control[/blue] to determine wether to requery the alternate combo and execute other code appropriately.[/li]
[li]in form design view [blue]set all defaults for normal[/blue].[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[ChasBoots]

Thanks AceMan. Unfortunately, I don't know if that idea will necessarily work for what I need do. The problem is more the toggle button could be more confusing to users. I'll at least take a look at it to see how it might work in case I cannot find an alternative.

 

[TheAceMan1]

Roger That ChasBoots . . . . .

It doesn't have to be a toggle button, but something has to trigger the [blue]functional switching![/blue]

Out of curiosity:

[blue]How would a user know they wanted to switch and what would you expect them to look for in order to do it?[/blue]

See Ya! . . . . . .