Updating a subform from a combo box

[duluter]

Hi, all.

This seems easy, but I've looked up and down and can't locate posts that clearly answer this question (though perhaps my search terms weren't good).

My Situation
I have two tables, Client and Project. There is a one-to-many relationship: each client can have many projects associated with it.
I want to make a form on which the user selects the Client name from a combo box and the associated projects are displayed in a subform.
I started by creating an unbound blank form and adding a combo box to it, cboClient, that gets populated from the ClientID field in the Client table. So far, so good.
I create a separate form that is bound to the Project table.
I then added the bound form as a subform to the main form.
I understand that I would requery the subform in the AfterUpdate event of cboClient. What I don't understand is how to link the subform to the combo box. I tried setting the child/master field links in the subform, but Access tells me that "I can't build a link between two unbound forms." This is weird because I'm pretty sure that the subform is bound to the Project table.

How, specifically, do I get the subform to only show projects associated with the client selected in cboClient?

Thanks,
Duluter

 

[maxmangion]

Hi

Base your subform on a query and then in the client field of your query put the following in the criteria row:

[Forms]![FormName]![cboClient]

Replace FormName with the actual name of your Form.

 

[duluter]

Max:

That worked, thank you.

I wonder, in order to do this, does the subform have to be based on a query? It can't just be based directly off of the table?

 

[maxmangion]

Hi duluter,

I'm glad that it worked.

In my opinion, ideally any form ought to be based on a query rather than on a table directly. There are several reasons for this, one of them being your scenario where you can use criteria in order to limit the number of records to be returned.

If you try searching this forum, this subject has been discussed several times so i am sure that you will find several more reasons why you should base your forms on queries and not directly to tables.

 

[duluter]

Good to know. Thanks for the quick and helpful reply.

 

[maxmangion]

you're welcome

 

[hasanrazaj1]

update 2nd combo box and subform with 1st combo box

HI all.

i am facing a difficult situation. i have 3 tables thats are:

1.AccountType (AcTypeID, AcTypeName,)
2.Account(AcID, AcTypeID, AcName, AcDetail, AcDate)
3.Payments(AcID, PayID, PayType, PayMode, Amount, PayDate)

i want to have a bound form which include:

1st.(combo box)AcTypeID
2nd.(combo box)AcID
3rd.(text box)AcName
4th.(text box)AcName
5th.(subform)Payments

then my requirements are that;

when i select a AcType from 1st combo box then 2nd combo box populated with related accounts list, and
when i chose an account from 2nd combo box then the 3rd, and 4th text box filled up with related record and as well as 5th Subform of payments also present related payments transctions.

i will thankful to you for help.

 

[Trodelphin]

have a similar problem the query works but the results dont refresh later
ie queries all the items in (storeA) on first form for the combo box but when i move to next form/s with (StoreB) the combo box results still equals results of (StoreA)
if open fresh and go to a form with (StoreB) it will show its results on all Forms from then on

 

[gemma-the-husky]

have a similar problem the query works but the results dont refresh later
ie queries all the items in (storeA) on first form for the combo box but when i move to next form/s with (StoreB) the combo box results still equals results of (StoreA)
if open fresh and go to a form with (StoreB) it will show its results on all Forms from then on

i would start a new thread, rather than tack on to a 2yo one.