Combo Boxes in Main Form: Duplicate Value Warning

[tatihulot]

Here's where I'm at now. I added a separate Actresses field to help keep the list of actors a bit smaller. Good idea? Not a good idea? What do you think? Thank you!

Master Final.zip

[davegri]

Master Final-davegri-v03.zip

Added/Fixed a few things:
Added record counter to form so that navigation controls at bottom could be eliminated.
Added search combobox for movie title search
Added Not-in-list code for Actress
Added Not-in-list event for Actor
Added Not-in-list code for Genre
Corrected code for Location Not-in-list

[Micron]

Didn't look in favour of addressing what appears to be a question of gender. What about those who consider themselves nonbinary? Are you going to arbitrarily decide which category they fall in? You can take the approach that actors are male, actresses are female but now there is at least a third option.

In this new world it seems that everyone who performs for film/video or stage is an actor, and they may identify with being male, female or neither. Maybe the first and last names are important as fields (not Actor or Actress) and then the gender is another field: M or F or N as time and society evolves. Then again, is gender even important? Perhaps only if you want to query all female actors/actresses that won an Oscar (or whatever) but at some point that will leave out those who won the prize and are neither, unless they are subject to the category you decide they belong to.

Perhaps your concern about the length of an actors list can be handled in some other way. One might be gender (M or F or N), or by FAYT combo boxes (look that up) or something else.

Just food for thoughts.

[Bob Fitz]

In your version, you aren't using the tblSubgenre table at all, are you? Should I just get rid of it completely?

I see no need of it if you use a subform

Also, when I add items to the Genre list in your example, and then sort the list, it doesn't appear sorted in the Combo box. How do you fix that

Change the RowSource property of the combo to an SQL query statement. Something like:-

SELECT tblGenre.GenrePK, tblGenre.GenreFROM tblGenre
ORDER BY tblGenre.Genre;

And...I see you have a subform named frmJunction, and the genres are in the GenreFK field. I need to understand that. I'm going to try to duplicate your version of the database (minus the FIND box in the header, at least for now). But in the meantime, is there anything I should focus on, specifically, in order to do that successfully?

Write a little bit of code for the NotInList event of the combo so that new genres can be added when using the combo. Post back if you need help with that.

[Bob Fitz]

Write a little bit of code for the NotInList event of the combo so that new genres can be added when using the combo.

On reflection, you would also need an index set on tblJunction to prevent a genre being selected for more than once for each film.

I have added both of these features to the attached db

[tatihulot]

Thank you very much for assisting me. Unfortunately, neither sample databases' not in field code works. I get this warning in both databases:


I should probably also mention that when I load those databases in Access, I see the following message:



And I see this above the form:


Is that why the code isn't working?

[davegri]

Unblock the downloaded .accdb file and then investigate Office "Trusted Locations" in google.

[tatihulot]

Okay, done. Now...is it absolutely necessary to have those confirmation dialog boxes? Or, at the very least, can those dialog boxes be designed to have a "Do not show this box again" option, if I so choose?

[davegri]

Okay, done. Now...is it absolutely necessary to have those confirmation dialog boxes? Or, at the very least, can those dialog boxes be designed to have a "Do not show this box again" option, if I so choose?

I had two suggestions. That's one completed. Did you research "Trusted Locations"? Dozens of YouTube videos out there.

Bye the way, when you reply to a post when you are interacting with several helpers, please indicate to whom you are replying.

[Bob Fitz]

Okay, done. Now...is it absolutely necessary to have those confirmation dialog boxes? Or, at the very least, can those dialog boxes be designed to have a "Do not show this box again" option, if I so choose?

Absolutely necessary? No, but I would advise that you have them. You might change the default button to Yes rather than No, so the user only has to click the Enter button to accept the entry of the new genre but I would even advise against that. Consider this. Once you have the list, new entries will be rare. If you do away with the confirmation dialog, users will need to have away of editing the list to correct the typing/spelling mistakes made, and there will be lots of those. I have developed many applications. I wouldn't even think of removing them. They help to prevent cr*p data.

[tatihulot]

Absolutely necessary? No, but I would advise that you have them.

Okay, I'll keep them. I'll be the only person using this database, but it would be in my best interest to adhere to established protocols.

At this point in time, I consider the database completed (with a couple of tweaks, such as indexing, pending). Any further work I do on it will be for educational purposes. I do have a few things in mind, but right now I'm interested in learning SQL.

Thank you all for your assistance, advice, and patience.

[Bob Fitz]

Okay, I'll keep them. I'll be the only person using this database, but it would be in my best interest to adhere to established protocols.

At this point in time, I consider the database completed (with a couple of tweaks, such as indexing, pending). Any further work I do on it will be for educational purposes. I do have a few things in mind, but right now I'm interested in learning SQL.

Thank you all for your assistance, advice, and patience.

You're welcome. Good luck with your project.

[ArviLaanemets]

Another possible design is to scrap both ´GenreFK and SubGenreFK from tblMovies, and add a table
tblMovieGenres: MovieGenrePK, MovieFK, GenreFK (and add an unique index based on both MovieFK and GenreFK, so any Genre can be linked to specific movie only once).

This design allows the movie to belong to any number of different genres. In case you need some ranking (like primary and secondary, or whatever), for the different genres of movie, then add the Rank field to tblMovieGenres too.