Form that Combines Two Tables with Photo

[QueenOfKings]

Hi, I'm trying to create a many to many table (which I know how to do). But this is a little complicated.

I have several photos that are part of my database, so I have them in their own table which contains:
ID
CallName
PhotoType
Source
Computer Link (to where photos are stored on my hard drive)

The photo type comes from another table, one of the types is People.

So I want to have a many to many table so I can connect people photos to places where they live. That's pretty easy, you just put foreign keys for the photo CallName and for the Place Name.

However, I want the person's photo to show up also (in a container I will put on the form). In order to do this, I need to have a field for computer link that is automatically populated based on which Photo Call Name I choose. Can you do that in a many to many form? How?

Thanks for any help!

 

[gerrythefish]

You need a third table with just the ID of the person and the ID of the photo as 2 fields.

You could make the 2 fields combo boxes - so for example you display the users name but you save the user ID into the field.

Then build a query off this table, add the photo table, join the tables on the photo ID field and make the join show all records from the third table and related records from the photo field (Im not sure this join type is necessary, but I find this usually make the table editable, where as a standard join can make the records in the query un-editable).

Now build your form from that query - you will have the person, the photo and the link available.