Filtering a Combo Box

[Brennan]

Hello:

I have a Main form named Statments that has a subform
named Statement Details. On the statement detail subform
there is a combo box named Accounts that allows the user
to drop down a list of accounts for that particular
client. Both the main form and subform have a field
called StmntNum which joins the 2 forms. There is also a
field called ClientID on the main form.

What I'd like to do is restrict the Accounts drop down
list to only those accounts for the client for whom the
statement is being prepared. The recordsource for the
combobox is a query which has fields named Account ID,
Account Name and Client ID. I have put the following
criteria on the query:

[Forms]![frmStatments]![cboStmntClientID]

[cboStmntClientID] is the combo box on the main form that
allows the user to select from a list of clients.

My problem is that when I select any client except the
first one, My accounts combo box doesn't restrict the
values to that particular client. It returns all of
them. If I leave the form open and manually open the
query, the recordset is restricted. How can I get my
combo box to reflect this?

Any help will be appreciated.

Brennan

 

[Marshall Barton]

Hello:

I have a Main form named Statments that has a subform
named Statement Details. On the statement detail subform
there is a combo box named Accounts that allows the user
to drop down a list of accounts for that particular
client. Both the main form and subform have a field
called StmntNum which joins the 2 forms. There is also a
field called ClientID on the main form.

What I'd like to do is restrict the Accounts drop down
list to only those accounts for the client for whom the
statement is being prepared. The recordsource for the
combobox is a query which has fields named Account ID,
Account Name and Client ID. I have put the following
criteria on the query:

[Forms]![frmStatments]![cboStmntClientID]

[cboStmntClientID] is the combo box on the main form that
allows the user to select from a list of clients.

My problem is that when I select any client except the
first one, My accounts combo box doesn't restrict the
values to that particular client. It returns all of
them. If I leave the form open and manually open the
query, the recordset is restricted. How can I get my
combo box to reflect this?

I think you need to requery the combo box whenever the
client ID changes. Tipically, you can use code in both the
clientID tesxt box's AfterUpdate event and in the form's
Current event:

Me.[Statement Details].Form.Accounts.Requery

Change the [Statement Details] above if the subform
**control** on the main form is not named [Statement
Details]