Combo Box Selection Based on Another cbo 1

[toddOne]

I currently have 3 combo boxes linked in a hierarchical way, but I am experiencing a slight bug. First I have cboManager, then cboAgent, and finally cboLoanNo. First the user selects a Manager, then the cboAgent is populated with only the Agents that have worked with the selected Manager. Finally cboLoanNo has only the loans that both Manager and Agent have in common.

When I select a Manager all the text boxes in the form are populated with the first record for that Manager, which isn't a big deal. BUT, when I choose a Agent it repopulates the text boxes with the first record for that Agent regardless if it is a Loan record with the same Manger that was selected first. Then finally after the LoanNo is choosen all is fine again.

The row source queries are working fine b/c each combo box contains the correct set of data, but when agent is selected after manager I don't want to see a record pertaining to another manager.

Is there an easy way to clear all the text boxes while the search is taking place? Or is there a way to correct this bug with the linked combo boxes? Thanks!!!

I use the following code:

cboManager Row Source:
SELECT DISTINCT [qryAllInfo].[Manager] FROM qryAllInfo;

cboAgent Row Source:
SELECT DISTINCT [tblAgent].[AgentName] FROM qryAllInfo WHERE [tblManager].[Manager]=[Forms]![frmBrokeredTodd]![cboSrchManager].Value;

cboLoanNo Row Source:
SELECT DISTINCT [tblLoan].[LoanNo] FROM qryAllInfo WHERE [tblAgent].[AgentName]=[Forms]![frmBrokeredTodd]![cboSrchAgent].Value And [tblManager].[Manager]=[Forms]![frmBrokeredTodd]![cboSrchManager].Value;

Also I requery both cboAgnet and cboLoanNo in the After Update event for cboManager, and requery cboLoanNo in the After Update event for cboAgent.

 

[downwitchyobadself]

Limit the underlying query of the form to the same criteria that you're using in the combos; in other words, base the form query on Manager=cboManager and AgentName=cboAgent, rather than just AgentName=cboAgent. The underlying record source of the form can change just like the combos.

Alternatively, if you're doing a record search rather than an actual form filter, change the .FindFirst criteria, or whatever it is that you're using, to reflect the two.

Hope this helps