How to refresh the second combo box?

[rod4]

Hello guys,

i have made a data entry form with two combo boxes.

The first one called [company ID] from the table [company] with the

row source:

SELECT [company].[company ID], [company].[company name] FROM company;
 

so i don't need to remember the company ID but can see the company name connect to the company ID instead.

the next combo box is called [subs ID] from the table [subsidiary]
( incl. subs ID, company ID, ...),
which populates the combo box with the correct list of subsidiaries of the company chosen in the first combo box.

row source:

SELECT subsidiary.[subs ID], company.[company ID], subsidiary.[subs name] LEFT JOIN subsidiary ON company.[company ID]=subsidiary.[subs ID] WHERE ((([company].[company ID])=[Forms]![xxx]![company ID]))
 

this works like a charm, but if i accidentally took the wrong company and wanna change that, the subsidiary list in the second combo box remains the same. it doesn't change to the new situation meaning to the new list of subsidiaries of the new company.

how can i refresh or requery the list of the second combo box???

[jimatqsi]

In the After Update event of [Forms]![xxx]![company ID]) you will want to put this line of code to requery the 2nd combo box:

Me.[Subs ID].requery

Do yourself a favor and avoid putting spaces in object names in the future. You won't have to bracket [] everything.

Jim

[rod4]

Thanks that was the perfect reply! i was so close but then so far! ;)
the spaces problem is the next item on my lists of problems to solve!
thanks a lot!!!

[NeoPa]

Please understand this is not advice to ignore what Jim's said, but simply an alternative that you should understand.

Access will also make controls with spaces in their names available by replacing those spaces with underscore (_) characters. Thus, Me.Subs_ID is another way to refer to it.

Far better not to have them though, as you've already agreed.