Why isn't my combo box updating the form fields?

[oZone]

I'm trying to make a form for my DB that allows me to select movie names from a combo box, and after choosing the movie, the movie details will be populated into their respective fields in the form.

I created a combo box to select the movies, it lists all the movies in the DB, but when I pick one it doesnt update the rest of the fields in the form, only the name of the movie. If you navigate through the different movies, you'll see the form fields being populated as they should be but for some reason the combo box isnt...

Help?

I've attached my DB below, any help would be greatly appreciated, thanks.

 

[John Big Booty]

The problem is that your Form is Bound to your tblMovies. What you need is a form bound to a query that uses your Combo as it's criteria.

 

[John Big Booty]

Also it makes no sense to have single column tables for Genre, Media, Rating. Each of those tables should have an AutoNumber this is the field that should then be stored in your movies table ie. as GenreID, etc. The way you have it set up at the moment you may as well do away with those three tables, you'd loose no functionality in your DB

 

[oZone]

I see, thanks for the advice.

Several questions follow:
1) Originally I had autonumber fields in the single column tables, but removed them because I didn't understand what purpose they served. I don't understand (due to my noobness...) why one would use an autonumber when they could use another unique column as a PK instead. Can you explain why it would be better to use an autonumber with these tables?

As far as removing them altogether, I created the single column tables for a use with a separate form that allows the user to alter or edit the existing genre, rating, and media type. I also used these tables with combo boxes in an "Add Movies" form so the user could select the genre, rating, and media type when entering new movies into the databse.

There are other forms that use these tables, I just removed those forms from the file I uploaded because I wanted it to be easier to see which form I was referring to in my post.

2) Given these facts, what would be the best way to handle the situation of a user needing to select the genre, rating, and media types from combo boxes when entering new movies into the database?

The problem is that your Form is Bound to your tblMovies. What you need is a form bound to a query that uses your Combo as it's criteria.

3) I don't know how to make a query use a combo box as its criteria, how do you do this? I tried using the query wizard, and also creating one manually and didn't see any menus that allow me to select a combo box as a source type... Sorry, I just dont understand how to do what you suggested... sometimes it sux being a noob

Do you mean to create a query that uses the same values from the same table that my combo box uses and bind the form to that new query?

 

[John Big Booty]

The autonumber will mean that as your DB grows it will take up less storage space if you are storing a digit rather that a text string as your PK.

Using an autonumber should have no impact on your user, if you have the Combobox set up correctly. Which if you follow the ComboBox wizard should not be hard, just make sure that you select both the ID and the subject in the columns you want in your Combo. So long as the AutoNumber is flagged as a PK the wizard will identify this and give you the option to hide it, (you can always hide a column in a combo by going to it's propeties and setting the column width to 0{zero}) then make sure that that is the field you choose to store in your bound field.

Have a look at the form I have created, and the query.

 

[oZone]

I understand now about the autonumber and why you would use it, thank you for helping me understand. I see the efficiency of using a digit rather than a text string.

I really appreciate all the help. My instructor is good, but honestly I'm learning more here than I am in class

I've got to go watch a movie with the wife. I looked at the form you made briefly, it's so cool how it works! I dont understand how it works yet, but I'll spend some more time looking at it later.

Thanks again

 

[oZone]

I've modified the layout of the form you helped me with (Form1), but i'm having a problem with it, could someone help?

I added an "Add New Movie" command button to the form, but it's not working rite.

When I click it, I get all blank fields and I can then add new movie info in the fields, but when I navigate to the "Stars of this movie" section and try to pick stars from the combo box, I get a PK error on every star, I have no idea why.

Help?

I've attached my updated DB below. Disregard the other forms in it (unless you feel like helping me fix them too), they're forms I've tried to add but some dont work rite.

My end goal with this project is to be able to edit existing movie details, and add new movies all within the same form.

 

[boblarson]

You need a Subform to add the stars so you can add many to the movie.

 

[oZone]

I've already tried that, but because the form uses a query as its source (or I think thats why...) the subform doesnt list all the stars, it only lists one per movie, even if the movie has 50 stars... It also asks for a value when you start up the form too. Theres something happening that I dont fully understand here...

More help? LOL

I've attached the version of Form1 that uses a subform. I've used two in this case, one using Query1 as its source, the other using tblStars as its source. I still get the same result with both subforms.

 

[John Big Booty]

My initial Form was set up as a continuous form with teh actors in the body (continuous part) and the name of the film etc in either the form header or footer.

 

[oZone]

Yes, and your form worked great, but I need to allow the user to either add additional stars to a movie, or add new movies and add stars to the new movies, and it wont do that rite now for some reason.

How would I get the form to do this?

I tried replacing the Names text box with a combo box but it didnt work. I could select stars from the list, but I got an error when trying to add stars to the movie.

I also tried using a subform to list the stars, but that didnt work.

I posted both versions of the DB.

Please help.

 

[boblarson]

Here it is fixed.

1. You didn't have the right recordsources in both the main and subforms.
2. You didn't have the right combo rowsource in the star selection

See attached.

 

[boblarson]

Oh, and you didn't have the right Master/Child links set (because you didn't have the correct recordsource for the subform).

 

[oZone]

I still have allot to learn

Thanks for the help.

Next question, how do I get the subform to not allow duplicate star entries? I just tested it but it let me add the same star several times.

I looked in the subform properties but dont see anything that lest you prevent duplicates. I know how to set a field to indexed-no duplicates but this is a query. I dont know how to do that with a query.

 

[John Big Booty]

If you use a pop up form to add actors you could use a combo that is populated by a query that excludes actors that have already been selected.

 

[oZone]

Okay thanks.

I know how to create a query, and how to specify basic criteria, but I'm not quite sure how to exclude already selected actors in a query. I'll give it a try though.

Edit:
I just tried to do that but I have no idea how. I hate being a noob sometimes...

Could someone please tell me how?

 

[John Big Booty]

You will need your query to reference the current MovieID, you should now know how to achieve this. You will then need to exclude all ActorID's that are already linked to that MovieID.

 

[oZone]

I'm not sure if I know how to reference the current MovieID or not... Heres what I did though; I created a new query with Query1 as it's source and added the MovieID field to it. Did I do that part rite?

As far as excluding all ActorID's that are already linked to that MovieID, I dont know how to specify that as criteria in a query...

I think I'm going to start a new thread asking for help with this specific issue because this thread has moved on from what it initially was, to something else that I need help with. I think I might get more responses if I post regarding this specific issue.

 

[John Big Booty]

You reference the MovieID of the current record by using Forms!FRM_YourFormName!MovieID. From memory of your DB you probably only need Your Actors to Movies Table and your Actors table, in your query. in the criteria under Movies in the Actors to Movies table put <> Forms!FRM_YourFormName!MovieID that should show all the actors that have not already been associated with that movie.

 

[oZone]

I think I did something wrong, it isn't working. It still lets me add duplicate stars to a movie, but also it now lists duplicate stars in the drop down list too.

It seems to be listing stars by MovieID because it shows a star name 4 times, and that star is assigned to 4 movies also...

Heres what I did:
I created a query that had tblStars(StarID), tblStars(Name), and tblStarsToMovies(MovieID) in it.

I put this code in the criteria for MovieID:

<> Forms!Form1!MovieID

I wasnt sure if I should use the subform name in it, or the main form, so I chose the main form name Form1.

Then I added this new query (qryForm1Subform_NoDuplicates) as the row source for the stars combo box in the subform.

I'll attach the updated DB.