Having trouble filtering data into Bridge Entities

[fedesso]

Hey everyone,

I've been working on a general Movie Database for awhile now that labels the Highest-Grossing movies worldwide. Easy enough. I'm still fairly new to Access and the logic behind queries and data implementation, and while I've normalized my tables and entered data into my main tables VIA forms I haven't been able to understand why I can't enter data into my bridge entities. (I receive an error when doing so, probably because of the enforcement of referential integrity settings)

Here's a picture of my relationships: https://i.imgur.com/fa8OdVJ.png

I've tried creating a form for the purpose of choosing a movie from the MOVIE table and an actor from the ACTOR_INFO table to fill the MOV_IDS and ACT_IDS in the MOVIE_LEAD_ACTOR table, but haven't been getting any results. I'm sure that there's something I'm just not understanding, and I'd like to remedy that if anyone has any ideas or time.

Thanks!

[June7]

Suggest that since actors can be directors and directors can be actors, really should be one table for 'People'. Then junction table could be MoviesPeople with field to identify the individual's function. Sometimes an individual will have multiple functions and therefore have more than one record for each movie.

[fedesso]

Working with the five tables I have, though, is there any way to make that logic work?

[June7]

Why would you want to enter same name into two tables?

What you show should work. What is the exact error message?

Actors and Movies records have to exist before creating junction record. Same for Directors and Movies. I think you said you already did that.

If you want to create Actors, Movies, Directors records 'on-the-fly' while entering junction record, that will require VBA.

If you want to provide db, follow instructions at bottom of my post.

Search forum on topic 'movies', I am sure you will find other discussions

[fedesso]

I'd prefer if you could see the database for yourself. I know it's a cop out, but I've talked to a few other Access users today and they haven't had a clue. The error lies in the Movie Actor Form, where upon creating a new entry it specifies that the Actor_Info table requires a related field. I appreciate your time.

[June7]

All I had to do was set the ControlSource property of the actor combobox. No problem entering record.

Suggest you change TabOrder setting for each control.

[fedesso]

If only I had known it was just a few clicks away... but is it possible to have those newly generated rows with ACT_IDs adhere to the same numbering constraints I set for the ACT_ID in the Actor_Info table?

[June7]

You simply set formatting on the PK field which does not alter the actual value. Why bother with formatting? The autonumber PK/FK has no meaning to users. They really have no reason to even know it exists. Same for directors. An autonumber PK/FK is intended to serve as records link, not to generate an identifier that has meaning to users, especially if you don't want gaps in sequence.

When your numbers exceed the formatting constraints, will have to modify table design.

I never bother with these sort of settings in table. Do formatting on forms and reports.

[fedesso]

I agree, but for the sake of conventionality I have to have unique numbers. There won't be much data incorporated in total though, so we'll see. I've got everything fixed up now I think, I really appreciate the assistance!