Combo Boxes in Main Form: Duplicate Value Warning

[tatihulot]

Hello. I've working on a movie database, and am having difficulty with combo boxes.

I have two combo boxes in my main form, for movie genres.

The two combo boxes will be Genre and Subgenre. The reason I'm doing this is because the vast majority of movies will have only one genre. Only a few will have a second genre. I don't think it's necessary to have a subform with that. Am I correct?

So, what I did was this: I created a table named tblGenre, and typed in the genres I want in that list. I then copied it and pasted it back into the database, renaming it tblSubgenre. I went into Design Mode and renamed everything that was originally listed as Genre.

I then changed the Lookup to Combo Box for both tables, and changed the "Required" setting for tblSubgenre to NO, because it won't be required for every entry. Am I right about that?



Then, I made the form, with no subform, using Form Wizard. The two combo boxes were in the form, so I began entering data to see what would happen.

I made it to two records. When I entered the third, I got the following dialog box:



Please forgive me, I'm really confused. What should I do now?

Also, can I use the tblGenre for both the Genre combo box and the Subgenre combo box, rather than have two tables, one for each? Or do I need a table for each combo box, even though they are both nearly identical?

Thank you!

[Welshgasman]

General rule is one form, one source.
IF (and a big IF) you can only ever have one second genre, then I would say you could just have Genre1 and Genre2 in your table.
Combos for me, are generally queries, with bound field the first field in the query and second the descriptive column.

You could have one table for genre, but would possibly need another field to indicate whether genre1 or genre2, else you could pick Western and Western?
If you wanted cascading combos, then you would need a record for each combination possible and an extra field to show the parent.

[tatihulot]

If you wanted cascading combos, then you would need a record for each combination possible and an extra field to show the parent.

Thank you for responding. You've planted a seed in my head. I was just about to start researching cascading combo boxes. I have an idea about them, but I don't know if it can be done. Please let me do a bit of research into them and experiment with them, and if I have any questions after that, then I hope it's all right if I ask them here.

I do have an idea about designing the Genre field. I'll tell you about it now, to see what you think. Maybe it's a good idea, maybe not. Since a lot of my movies are comedies, I think what I'll do is have a Comedy check box. That might help narrow things down when I start doing queries and reports. I'll have one combo box, in case the comedy has a subgenre. (For example, Spaceballs is a comedy, so I would check off the Comedy check box for it. Then, in the Subgenre combo box, I'd select Science Fiction.)

It's going to be so rare for a film that is not a comedy to have multiple genres, I really don't think it's worth having a subform for them. Do you agree? Thanks again.

[isladogs]

For an example, see this article: Cascading Combo Boxes (isladogs.co.uk)

[Welshgasman]

Thank you for responding. You've planted a seed in my head. I was just about to start researching cascading combo boxes. I have an idea about them, but I don't know if it can be done. Please let me do a bit of research into them and experiment with them, and if I have any questions after that, then I hope it's all right if I ask them here.

I do have an idea about designing the Genre field. I'll tell you about it now, to see what you think. Maybe it's a good idea, maybe not. Since a lot of my movies are comedies, I think what I'll do is have a Comedy check box. That might help narrow things down when I start doing queries and reports. I'll have one combo box, in case the comedy has a subgenre. (For example, Spaceballs is a comedy, so I would check off the Comedy check box for it. Then, in the Subgenre combo box, I'd select Science Fiction.)

It's going to be so rare for a film that is not a comedy to have multiple genres, I really don't think it's worth having a subform for them. Do you agree? Thanks again.

Not a subform, but a multi select listbox?

[Bob Fitz]

• tatihulot

However you go about the task in hand, the use of Lookup fields in tables, such as the field named "Genre" in the table named "tblMovies", is not considered to be best practice and should be avoided.

[tatihulot]

I am most definitely NOT going to use a lookup field, no. I have learned the disadvantages of them. I quickly discovered the list of genres will change frequently. Having a lookup field for them is highly impractical.

The problem is this: I am not really all that well-versed in database design. I've come a long way in the past month, but I'm still a novice. I'm currently having so much difficulty creating what should be a simple, rudimentary combo box, it's making my head spin. So, I'm researching and studying, and experimenting. The experiments often come close, but not quite what I want.

Right now, I'm not really sure what I want to do.

The key issues I have are these:

1) Having a fixed, but updatable, list of genres. That leaves out a lookup field.
2) Being able to select from that list, without having to type anything in, which prevents typographical errors. (Not a real big deal, but I'd prefer it not to happen.)
3) Being able to create a query and/or report that displays all of the data I request.

I'm really in a fog here, not being able to create a combo box because I'm just not experienced enough to know what's wrong.

[Micron]

I am most definitely NOT going to use a lookup field,

But you are, in at least 3 tables. So why present a file for an issue that isn't designed the way it will be but isn't now?
EDIT -IMO all you need is one genre table and two combos (Not lookup fields) in the form. The 2nd combo contains all genres except that of the first one.

[Bob Fitz]

I am most definitely NOT going to use a lookup field, no. I have learned the disadvantages of them. I quickly discovered the list of genres will change frequently. Having a lookup field for them is highly impractical.

The problem is this: I am not really all that well-versed in database design. I've come a long way in the past month, but I'm still a novice. I'm currently having so much difficulty creating what should be a simple, rudimentary combo box, it's making my head spin. So, I'm researching and studying, and experimenting. The experiments often come close, but not quite what I want.

Right now, I'm not really sure what I want to do.

The key issues I have are these:

1) Having a fixed, but updatable, list of genres. That leaves out a lookup field.
2) Being able to select from that list, without having to type anything in, which prevents typographical errors. (Not a real big deal, but I'd prefer it not to happen.)
3) Being able to create a query and/or report that displays all of the data I request.

I'm really in a fog here, not being able to create a combo box because I'm just not experienced enough to know what's wrong.

Why not use a subform for the Genres. See attached

[tatihulot]

But you are, in at least 3 tables. So why present a file for an issue that isn't designed the way it will be but isn't now?

I'm sorry...you've lost me here. Maybe my definition and concept of lookups aren't correct. What I meant was I wasn't going to use the Lookup Wizard to create a fixed list of values that will never change. The Genre list will most likely change, so I wouldn't use the Lookup Wizard for that.

EDIT -IMO all you need is one genre table and two combos (Not lookup fields) in the form. The 2nd combo contains all genres except that of the first one.

I was going in that direction, sort of. I was going to have a Yes/No checkbox for "Comedy." Then, I wanted a combo box for every genre EXCEPT comedy. That's sort of the same thing, in a sense, isn't it?

[tatihulot]

Why not use a subform for the Genres. See attached

That is exactly, precisely what I want to do, but I just haven't figured out how to do that! (Yet.)

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

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?

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?

[davegri]

Actors-tat-davegri-v02.zip

Movies form has not-in-list code for adding new entries for 5 comboboxes.
Here's how to use the same table for 2 comboboxes:
tblGenre and tblGenre_1 are both the same table. It's just how Access shows it when you add a table more than once to the diagram.

[tatihulot]

I think I got it. I think I got the combo box for genres done. I just haven't figured out how to get the genre list sorted within the combo box itself. The Genre field's list is sorted, but the box's is not.

Now, to plug in the Location Type/Size subform and the Actor subform. Hopefully, that will go smoothly. We'll see.

Thank you, everyone, for your responses, and your patience.

Genre Subform Test.zip

[CJ_London]

the problem with using the same genre table for two classifications is any searching for films with a specific genre requires two searches

Film...Genre1...Genre2
abc....horror.....comedy
def.....comedy...horror

so you want a list of comedy films? you need to search Genre1 and Genre2

[Micron]

I should have mentioned that I was going along with the notion expressed regarding both fields in one table since it was stated (I think) that there would never be another sub category. Not that I'm really advocating that but only 1 search would be required because it would all be in 1 record.