Refresh combobox data upon recordset change

[TS in FL]

Form A contains a "quick search" combobox filled with a list of all
customers. There is a button on this form that opens Form B, which has a
combobox where you can select a salesman to filter for only that salesman's
customers, which then appear in Form A. How do I then update the "quick
search" combobox on Form A to contain only that Salesman's customers?

I will also need to add a button to remove the filter and again show all of
the customers in the Form A "quick search" box.

I'm sure this is easy to do, but I've been staring at this thing all day and
can't seem to get it figured out. I learned the above techniques from the
Access 2003 Bible. Thanks in advance for the help!

 

[Graham Mandeno]

Hi TS

Basically you need to change the RowSource of the combo box at the same time
as you change the Filter or RecordSource of FormA.

If the "all customers" RowSource is:

Select CustID, CustName from tblCustomers order by CustName;

and you apply the filter "CustSalesman=35" to FormA, then at the same time
you should apply that filter to the combo box:

Select CustID, CustName from tblCustomers
where CustSalesman=35 order by CustName;

You can change the RowSource of a combo or listbox on the fly with a simple
piece of code:

strRowSource = "Select .... "
Me.cboSelectCustomer.RowSource = strRowSource

or, from a different form (FormB):

Forms![FormB]!cboSelectCustomer.RowSource = strRowSource

I'm not sure why you need a separate form (FormB) for your "Select salesman"
combo box. Why not just put it on FormA?

 

[TS in FL]

Thank you my Kiwi friend. I'll give it a shot and let you know whether
or not I hurt myself in the process. You are certainly correct that it would
be logical to just put the combobox on Form A, and that's what I am going to
attempt to do. Thanks for your time!

Hi TS

Basically you need to change the RowSource of the combo box at the same time
as you change the Filter or RecordSource of FormA.

If the "all customers" RowSource is:

Select CustID, CustName from tblCustomers order by CustName;

and you apply the filter "CustSalesman=35" to FormA, then at the same time
you should apply that filter to the combo box:

Select CustID, CustName from tblCustomers
where CustSalesman=35 order by CustName;

You can change the RowSource of a combo or listbox on the fly with a simple
piece of code:

strRowSource = "Select .... "
Me.cboSelectCustomer.RowSource = strRowSource

or, from a different form (FormB):

Forms![FormB]!cboSelectCustomer.RowSource = strRowSource

I'm not sure why you need a separate form (FormB) for your "Select salesman"
combo box. Why not just put it on FormA?

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Form A contains a "quick search" combobox filled with a list of all
customers. There is a button on this form that opens Form B, which has a
combobox where you can select a salesman to filter for only that
salesman's
customers, which then appear in Form A. How do I then update the "quick
search" combobox on Form A to contain only that Salesman's customers?

I will also need to add a button to remove the filter and again show all
of
the customers in the Form A "quick search" box.

I'm sure this is easy to do, but I've been staring at this thing all day
and
can't seem to get it figured out. I learned the above techniques from
the
Access 2003 Bible. Thanks in advance for the help!