Question Populating a combo box

[dreno]

Hi all,

I just found this community.
Glad to be here, since I just started working with Microsoft Access.

I'm building a microsoft acess database of my movie collection and wanted some help from you guys.

I have two tables, where the first one contains actors and the second one contains movies.

Table 1 = tblactors
Table 2 = tblmovies

I have also created a Form where the information about the actor is displayed in a nice way.
(Getting information from the tblactor table such as the actors name, age and so on)

Every actor has a unique ID and the same ID is related to movies on the movie table tblmovies.

For example the actor Jack black has the ID 13.
Inside the movie table tblmovies there are alot of movies, and all the movies with the ID 13 belongs to the actor Jack Black.

So far so good....

What I want now is to create a Combo Box inside the Form that I currently have to display information about the actor.
I want the combo box to display the current actors movies.
(They are linked by the same ID, the actor and his movies)

For example when I view the actor Jack Black on the Form I want the combo box to be populated with the movies that share the same ID as actor.
(It should search the tblmovies table for movies belonging to the current actor that is being displayed and show me all the movies belonging to that actor in the combo box)

I have successfully created a combo box that displays the movies from the tblmovies table but the problem is that it displays all the movies that are on that table and not only the movies that belong to the current actor.

I supposes this is possible by creating a Query with criteria to only display the current actor but i have not managed to get this to work the way i want.

I tried some criteria but the closes I got was that when I selected the combo box a parameter value box appeared asking me to enter the ID and after entering the id it displayed the movies of that actor.

But I don't want to enter a value on the parameter value box everytime i switch to a different actor. I want access to get the ID of the current actor that is displayed on the form by itself and populate the combo box with the current actors movies.

Hopefully I will find a solution to this on this community, would be forever thankful.

 

[John Big Booty]

IN the criteria for the query that is populating your Combo box, put the following under ActorID;

Forms!YourFormName!ActorID

In the forms on Current event you will need to put the following;

Me.ComboName.Requery

You will also need to fire this code if you change the actor but not the current record, ie. when you add a new actor record

Given that actors will appear in a number of movies a list box might be a better option than a combo box.

 

[dreno]

Thanks a million John Big Booty, it works .

I dont know if I understood you on this one though:

In the forms on Current event you will need to put the following;

Me.ComboName.Requery

You will also need to fire this code if you change the actor but not the current record, ie. when you add a new actor record

Given that actors will appear in a number of movies a list box might be a better option than a combo box.

I went inside visual basic and created this on my form:

Private Sub Form_Current()
Me.moviecombo.Requery
End Sub

Is that correct?

 

[John Big Booty]

Yes that should do the trick

 

[dreno]

Wonderful, thanks once again .

I want to take it to the next level now.

I want to control the movies through the form, by adding or deleting new movies for the current actor.

The form that I have is based on the tblactors table, where the information about the actor is stored.
What I want now is to create a function so that i can add new movies for the current actor.

Currently i have to open the tblmovies table and enter the information through the table, by adding ActorID, MovieName, MovieDescription.

For examlpe when I want to enter a new movie for the actor Jack Black i have to enter ActorID 13, movie name and movie description on the tblmovies table.

I want to handle this proceess through my form.

Is this possible?

 

[John Big Booty]

Have a look at the attached, this is how I'd do, there are undoubtedly other options.

 

[dreno]

Wonderful, exactly what I was looking for. Thanks alot.

The only thing I would like to have now is to show a description when selecting a movie from the list.

On the TBL_Films table i would like to create a new field where i will store a small description about every movie. and when selecting a movie from the list, it should display the information on a textbox for that movie. this information should change when clicking on other movies.

 

[John Big Booty]

I've made a couple of changes to the DB, you can now view the film details you are interested in by clicking on the film in the List box.

 

[dreno]

Man you are my hero, this is just perfect. Thank you so much.

 

[John Big Booty]

Glad to be of service. Play with the DB, pull it apart, see how it works, and bend it to your own needs.

 

[John Big Booty]

Tweaked the DB slightly so that the first item is the List is now automatically selected.