Help with Combo Box Logic/VBA

[pcham]

I am currently working on an Access dbs for work. I have been thinking about how to do for awhile and still haven't figured this out (I am also somewhat of a newbie to Access).

The dbs is comprised of 18 tables including junction table. The main tables in questions are:

Request which has a M:M relationship with Client
Client which has a 1:M relationship to Program

What I am trying to do is in the Request form have a client subform where multiple clients can be associated to the Request

The subform is based on a query with Request, Client, Client Request junction
has the client, program fields

What i need to have done is for the user to be able to select the client from the client list box and then the program list box to be defaulted to a program associated with the client. If more than one program under the client exist then the user can click on the program list box to select the appropriate program associated with the client. The list box at this point should only display programs associated to the client. At the same time, the user should also be able to select from the program list box and then automatically populate the client info -- meaning the user have two ways to populate these two fields > via program or client

I believe VBA is the way to go since there could be two rowsources for Program Id listbox.... I tried various codes i can muster, but none worked.

Any help on this would be great.

Thanks!

[StephR]

I just tried the same thing with my database... I ended up making a find command button on the main form, which would then show all the records in the subform that I wanted.

Also, I had to go back and click "cascade updates" in the relationship (between the form and subform) to allow the user to enter information both ways.

Does that help any?

Stephanie

[Stewart Ross]

...Also, I had to go back and click "cascade updates" in the relationship (between the form and subform) to allow the user to enter information both ways...

Hi all. 'Cascade Updates' is a setting that ensures that any changes to the primary key value on the 'one' side of a one-to-many relationship are updated through all associated secondary keys in tables on the 'many' side of the relationship. If Cascade Updates is not set then once a related record is present no change to the primary key value is allowed (to avoid orphaning the related record).

This setting otherwise does not have anything to do with getting forms and subforms to work...

In the first post where the MM relationship is mentioned, has this been properly normalised to two 1-M relationships with a linking table in between? If not it should be, as try as you might you will not be able to use forms and subforms correctly on un-normalised data...

The HowTo section of the forum has a good article on database design and normalisation, at http://www.thescripts.com/forum/thread585228.html.

-Stewart