Auto Populating a combo box based on the value in a different combo box

[mp3909]

Hi,



As the title says, I am trying to auto populate a combo box called cmbCaseID. Here is its properties:

As you can see the Row Source is set to:

SELECT tbl_List_of_Cases.[CaseID] 
FROM tbl_List_of_Cases 
WHERE tbl_List_of_Cases.[Client Legal Name]=[Forms]![Form1].[cmbClientName];

It should auto populate after user has selected a value in the combo box called cmbClientName. Here is its properties:



As you can see its Row Source is set to the following:

SELECT [tbl_List_of_Cases].[CaseID], [tbl_List_of_Cases].[Client Legal Name] 
FROM tbl_List_of_Cases 
ORDER BY [Client Legal Name];

I have also added an Event Procedure for "After Update" for the combo box cmbClientName which is defined as follows:


But when I test out my form to see if this works, it does not auto populate the first combo box cmbCaseID (i.e. CaseID) even though I have made a selection in the second combo box cmbClientName (i.e. Client Legal Name = "Capital PLC")


Looking at the table tbl_List_of_Cases you can see I have this client with a caseid = 287 so 287 should be populated automatically in that combo box.




Why is not working, any help would be much appreciated. Thank you

[Welshgasman]

TBH no idea?
However as you only appear to have one entry for any clientname for the caseID, why is it even a combo?
You already have the CaseID in the clientname combo?

Plus to me the datae entry seems weird?, I would have the client name first, then populate any other controls, if that is the defining control for gathering other data.

Failing that, upload a sample db to show the issue, as what you said you have, should work.

[mp3909]

Why is it even a combo? - The user who fills in the form will not necessarily know the case id of the client, so they will fill in the form by selecting the name of client. Once they have selected name of client, it should show the case id.
I have moved the client name at the top then case id after to follow correct flow.
I am unable to attached the db as its exceeds the limit size allowed on here but even with the below records for tbl_client_list you think you can achieve what I am trying to do?





Thank you

[Micron]

What you're doing makes no sense to me. If you pick a list value from a combo and populate a combo with related info, you can then change that 2nd value by picking from its list. That doesn't seem right. If there is only one value because of the 1st selection, then a combo makes no sense (again).

My suggestion would be to make the 2nd control a textbox and edit the query of the 1st one to include the caseID field. Make sure you add a column to the column count property in the sheet. If you want to see the name in combo1 after a selection, make the 1st column width 0. Then in the event, make the textbox value that of column(0) from the chosen combo value.

EDIT - if it works but the information passed to the textbox is the opposite of what you have in the combo then the combo field order is backwards, or you used the wrong column in the code. I would order the combo columns as id field, then name field.

BTW, you probably didn't compact zip your file copy first. Almost always required in order to get the size down to where you can post it.

[Welshgasman]

Micron,
AFAIAA the clientname, which is now the first control, already includes the caseID?
However if the client can have more than one CaseID, then I can see the need for cascading combos?

[Micron]

All true I guess.
Requerying a combo rebuilds the list - it doesn't set its value, yes? The code only requeries the combo/list, it doesn't set its value. Nor does setting the 2nd combo rowsource based on the value of the set the 2nd value either, so don't expect a value to appear there. You have to make the 2nd value property what you want in code, not just requery the list. The other option would be to manually choose a value from the 2nd list. It seems OP expects 2nd combo to populate by way of setting rowsource properties and requerying the list, and that's not going to happen.

Me.combo2 = Me.combo1.column(0) would seem more likely as a solution. Or whatever would be the correct column reference.

[mp3909]

Hi, I managed to follow your instructions although it took me a while but managed to accomplish what I was trying to achieve.
I changed one of the combo boxes to a text box and then made the value in the text box equal to the value in the 1st column of the combo box.

Thank you

[Micron]

That's great! Mark this one as solved then (thread tools at top right-ish)?