Coding a form

[cathywoodford]

Hi. I have a simple database that has 3 tables - customer, rental and
movies. The fields are:
Customer Table Rental Table Movie
Table
CustomerID CustomerID MovieID
FirstName Rented (y/n checkbox) MovieTitle
LastName RentalID
MovieID
The relationships are obvious - cust id (customer table) to cust id
(rental table) and movie id (movie table) to movie id (rental table).
I have a search form with a combo box that displays the list of movies
from the movie table. When the user selects a movie, a movie form
opens displaying movie information, the rented checkbox (on a rental
subform) and customer information (on a customer subform).
I have a RENT command button on this form so that if the movie is not
rented, the user can click RENT and a form opens up. I'm having
trouble setting up this form. I know the form needs to be be in Add
mode and I would like the movie information to be carried over and have
the user just fill in the customer information and tick off the rented
check box. I don't know what to base my form on (ie: what table). I
have it carrying over the information and just being able to check the
box and add customer information but then it adds a second movie title
as well. When I put the movie information in a subform, it doesn't get
carried over.

Any ideas?

Cathy

 

[Jeff Boyce]

Cathy

If one customer could rent one/more movies, consider using a main
form/subform approach. The main form would be your customer information,
while the subform would be the movies rented by that customer.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[cathywoodford]

Thanks. That's what I have done but what would I do about the add form
and copying the information from the search a movie form?

Cathy

 

[Jeff Boyce]

Cathy

Why use separate forms? If your subform has a combo box control that lists
all available movies, you simply pick the rented movie.

Not sure I understand the "search" form...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ron2006]

some thoughts

Main form has customer info etc.
subform on that form is movies rented (or to be rented.)
one piece of data is date, etc.
combo box for name of move etc.
if you want to use a search on that maybe have the search
opened up on a double click of that field. Now how to get the
information back.

do a search in this form on calendars and popup calendars. What
you want to look at is how the calendar popup returns the information
BACK to the calling form and how the calling form actuall calls the
calendar popup. That is exactaly what you to do with you search popup.
All it really has to do is return the id of the record because that is
waht you want to save. The criteria for this popup (and the combo drop
down) must include criteria from the table behind the rental subform
above so that it knows what videos can actually be rented out. If you
have 5 copies of a certain video you only want to see the 3 that have
not been already rented out AND haven't come back yet.

Hope this gives you some ideas....

Ron

 

[cathywoodford]

Hi Jeff. Sorry I have you so confused

What I have is a splash screen that has a button to a pop up search
form. On the search form there is a combo box with all the movies
listed. The user will select a movie from the list and a form will
open with the movie information, if it is rented (just a check box y/n)
and the customer who has it rented. This main form is movie and the
subforms are rented and customer.

What I now have is a RENT button that is going to be enabled if the
rented check box is not checked and when the user clicks the button a
Add Rental form opens for the user to enter the customer information.
I would like this form to carry over the movie information from the
other form so the user doesn't have to select it or type it again.

Clear or not? Hope it is so that someone can give me a hand. I've
been racking my brain!! I'm not a professional at Access/programming
but love it!

Thanks again,
Cathy

If you want I could send you the database.

 

[cathywoodford]

Now my problem is that I have created a form based on rental
information with movie information and customer information as well.
When the user clicks on add this form opens and copies the movie id and
movie title from the previous form and you fill in the rest (rented
check box, customer information). This works good but it adds another
instance of the movie as well. How do I get around this?

Cathy

 

[Jeff Boyce]

Not knowing how you have your forms bound to your tables, or what SQL
statement you might have embedded in the forms/command buttons makes it
tough to diagnose and "fix".

Have you looked into any of the templates that come with Access? They might
offer you examples of how this is handled. I believe I saw one about a
CD/DVD collection...

Regards

Jeff Boyce
Microsoft Office/Access MVP