Best way to tie in 3 linked tables on an input form

[KateyNixon]

Hi,

I've 15 years experience of programming but COBOL and not recent. I've started a job as an advocacy coordinator and decided to create a database in access thinking it would be easy - no way!

I have a many to many join which I have normalised as follows

Advocate table : Advocate_Id, FirstNames, Surname, Address, and other fields
Client table : Client_Id, First_name Surname and other fields

now an advocate can have many clients and over time a client might have more than one advocate so I needed to join the in a many to many relationship, so I created a thrid table

Advocate_Client table : Advocate_Client_ID, Advocate_Id, Client_Id

Where I ran into trouble was on the input form for the Advocate_client table. I want to chose a client from a drop down box that displays Client _Id and client name, and store the client id - easy enough - but then you are left with little feedback as to which client you have chosed as the client_id is an numeric field. Therefore once you have chosen the client all the feedback in terms of which client it is disappears and you dont know if you have selected the right one

Normally I'd have a label field underneath which automatically displayed the client name for the client_id that had been selected but I cant find a way of doing this in access.

I've tried using a subform but cant seem link it properly to the client id that has been selected.

Once I've succeeded I want to do the same thing for advocate id, displaying the name of the one that has been selected

Are there any experienced access programmers out there who know what I'm talking about and can help

Remember this is an input form rather than a report. I understand if I was doing a report I'd join the three tables in a query and base the report on the query.

Thanks in advance for any help - ask me if its not clear

Katey

 

[PHV]

Provided the RelationShips are correctly created, simply follow the combobox wizard.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Pampers]

Problem lies with the setup of you combo. You want to store the ClienID but display the ClientName on the form and not the CLientID.

Follow the combo wizard (which will hide the ClientID) as PHV suggest, or look at your combo properties. They have to look something like this:

row source: Select ClientID, CLientName from tblClient
column count: 2
column withs: 0; 5 (centimeters) - this will hide the ID field and display the Name)
bound column: 1 (field is bound to the ID)
list with: 5 (cm)

Pampers
Keeping it simple can be complicated