Combo box

[Cathy W]

Hi. I have a combo box that displays titles of movies. What I would like to
do is for the user to select a movie title from the combo box and have all
the movie information related to what the user choose to display in a form
ie: Movie Id, rented or not, if rented display the customer name and id.

Cathy

 

[Arvin Meyer [MVP]]

There is a wizard built into the combo box designer that will do this for
you. Essentially all it does is to open a form based upon the ID value of
the bound column of the combo:

DoCmd.OpenForm "YourFormName", , , , "ID=" & Me.ComboName.Value
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Cathy W]

Hi Arvin. When I create the combo box I pick the option "Find a record in my
table....". When I pick a movie title, I want it to display a subform to
show if the movie is rented. I assume this will come from the rented table.
How do I get this to work? Essentially what the user is doing is searching
for a movie to see if it is rented.

Thanks,
Cathy

 

[Klatuu]

Oh Horrors! Please don't say you have one table for movies that are not
rented and one for those that are. BAd, Bad, Bad. This creates more
problems than it cures. You should have only one table of moves with a
Yes/No field to indicate whether it is rented. For a more sophisticated
approach, it would be a status field to show it Available, Rented, Reserved,
Damaged, Sold, etc.

For a true movie rental system, you really need two tables. One that has
all the info on the movie itself and a related table that has the status info

 

[Cathy W]

Hi Klatuu. The situation I have is: 3 tables - Customer, Rental and Movies.
Yes, in my movie table I have a yes/no field for rental status. I really am
not quite sure where to start but I know that I would like the user to be
able to pick a movie from a combo box and then in a subform (or another
form), the movie information would display. Having that said, if in the
movie table the rented check box is yes, then the customer information would
also display, showing who has the movie rented.

Make sense....any ideas where to start.

Thanks,
Cathy

 

[Arvin Meyer [MVP]]

So your main form has the movies, and the subform's recordsource is a query,
based on your rented table. If you link on the MovieID in a query based on
the rented table and sort on the rented date (descending) you should see the
last record and be able to determine if the movie is rented or not (the
returned date is empty or not).
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Klatuu]

If you want to be able to present info on movies whether they are rented or
not, the row source for your combo needs to include all movies in the movies
table. In the After Update event of the combo box, you will need to filter
the data for 2 sub forms, one for the customer and one for the movie and
display the appropriate data. What I don't know is the relationships of your
tables, so it is hard to be specific. It may require a query based on the
relationship of the Customer Table to the Rental Table to include the
customer info.