Coding a search form (sorry if this is duplicate, computer locked

[Guest]

Hi. I have a database with 3 tables - movies, rentals and customers. They
are all related. I have a movie form with MovieID, MovieTitle (from the
Movie Table) and Rented (from the Rented table). I have a subform on that
form that holds customer information from the Customer table. I have set up
a search form that has a combo box with movie titles bound to it. What I
want to happen is when a user selects a movie title from the combo box, if
the movie is rented then open the Movie form to show the information on the
rental. If it is not rented then display a message box to say "Movie not
rented". Here is the code I have on the combo box of the search form.

Private Sub cboSearch_AfterUpdate()

Dim stLinkCriteria As String
stLinkCriteria = " [MovieTitle] = '" & Me!cboSearch & "'"
If Form_frmMovie.Rented.Value = False Then
MsgBox "The movie you selected is not rented", vbOKOnly, "Movie Not
Rented"
ElseIf Form_frmMovie.Rented.Value = True Then
DoCmd.OpenForm "frmMovie", acNormal, , stLinkCriteria, acFormReadOnly
End If
End Sub

Hope someone can help!

 

[Marshall Barton]

Hi. I have a database with 3 tables - movies, rentals and customers. They
are all related. I have a movie form with MovieID, MovieTitle (from the
Movie Table) and Rented (from the Rented table). I have a subform on that
form that holds customer information from the Customer table. I have set up
a search form that has a combo box with movie titles bound to it. What I
want to happen is when a user selects a movie title from the combo box, if
the movie is rented then open the Movie form to show the information on the
rental. If it is not rented then display a message box to say "Movie not
rented". Here is the code I have on the combo box of the search form.

Private Sub cboSearch_AfterUpdate()

Dim stLinkCriteria As String
stLinkCriteria = " [MovieTitle] = '" & Me!cboSearch & "'"
If Form_frmMovie.Rented.Value = False Then
MsgBox "The movie you selected is not rented", vbOKOnly, "Movie Not
Rented"
ElseIf Form_frmMovie.Rented.Value = True Then
DoCmd.OpenForm "frmMovie", acNormal, , stLinkCriteria, acFormReadOnly
End If
End Sub

How are your tables related?

What is the combo box's RowSource and BoundColumn?

Normally the combo box would be set up to display the title
but have the MovieID as its value. The table relationships
would also be by movieID so the criteria would be:

stLinkCriteria = " MovieID = " & Me!cboSearch

 

[Guest]

Thanks for the reply. The tables are connected as follows:

CustID in Customer tbl to CustID in Rental tbl
MovieID in Movie tbl to MovieId in Rental tbl

The rowsource for the combo box is SELECT tblMovie.MovieID,
tblMovie.MovieTitle FROM tblMovie ORDER BY tblMovie.MovieTitle; and the
bound column is 1.

I have changed my stLinkCriteria to your suggestion (I understand what you
mean) and it works the same as before - the form opens when a movie is rented
and when it is not rented. If I could have it open when it is not rented
with just the movie information, that would be fine as well (instead of the
message box). Right now if the movie is not rented, the form opens blank.

Hi. I have a database with 3 tables - movies, rentals and customers. They
are all related. I have a movie form with MovieID, MovieTitle (from the
Movie Table) and Rented (from the Rented table). I have a subform on that
form that holds customer information from the Customer table. I have set up
a search form that has a combo box with movie titles bound to it. What I
want to happen is when a user selects a movie title from the combo box, if
the movie is rented then open the Movie form to show the information on the
rental. If it is not rented then display a message box to say "Movie not
rented". Here is the code I have on the combo box of the search form.

Private Sub cboSearch_AfterUpdate()

Dim stLinkCriteria As String
stLinkCriteria = " [MovieTitle] = '" & Me!cboSearch & "'"
If Form_frmMovie.Rented.Value = False Then
MsgBox "The movie you selected is not rented", vbOKOnly, "Movie Not
Rented"
ElseIf Form_frmMovie.Rented.Value = True Then
DoCmd.OpenForm "frmMovie", acNormal, , stLinkCriteria, acFormReadOnly
End If
End Sub

How are your tables related?

What is the combo box's RowSource and BoundColumn?

Normally the combo box would be set up to display the title
but have the MovieID as its value. The table relationships
would also be by movieID so the criteria would be:

stLinkCriteria = " MovieID = " & Me!cboSearch

 

[jahoobob via AccessMonster.com]

Seems to me that your main form should be Rentals, not Movies, in which you
search for a movie. If the movie is not rented then the form is filled in
with the movie info and you proceed to enter the customer info. If it is
rented a form pops up with the info on the rental of that movie and on
closing of the pop up no movie info is entered in the rental form
Another alternative to the pop up would be to base your movie look up on a
query on all the movies and the rental info on those that are currently out..

Hi. I have a database with 3 tables - movies, rentals and customers. They
are all related. I have a movie form with MovieID, MovieTitle (from the
Movie Table) and Rented (from the Rented table). I have a subform on that
form that holds customer information from the Customer table. I have set up
a search form that has a combo box with movie titles bound to it. What I
want to happen is when a user selects a movie title from the combo box, if
the movie is rented then open the Movie form to show the information on the
rental. If it is not rented then display a message box to say "Movie not
rented". Here is the code I have on the combo box of the search form.

Private Sub cboSearch_AfterUpdate()

Dim stLinkCriteria As String
stLinkCriteria = " [MovieTitle] = '" & Me!cboSearch & "'"
If Form_frmMovie.Rented.Value = False Then
MsgBox "The movie you selected is not rented", vbOKOnly, "Movie Not
Rented"
ElseIf Form_frmMovie.Rented.Value = True Then
DoCmd.OpenForm "frmMovie", acNormal, , stLinkCriteria, acFormReadOnly
End If
End Sub

Hope someone can help!

 

[Guest]

Thanks for the reply! Not quite sure what you mean though. When I search on
a movie now, I get a movie form with movie id and movie title and rented.
Rented is ticked if the movie is rented and the customer subform is
populated. If the movie is not rented I just want a pop up to say "This
movie is not rented". Or the movie form could open but the only information
that would be filled in would be movie id and movie title.

Anyway that it would work would be fine for me so if you could explain your
reply a little more in depth that would be great.

Seems to me that your main form should be Rentals, not Movies, in which you
search for a movie. If the movie is not rented then the form is filled in
with the movie info and you proceed to enter the customer info. If it is
rented a form pops up with the info on the rental of that movie and on
closing of the pop up no movie info is entered in the rental form
Another alternative to the pop up would be to base your movie look up on a
query on all the movies and the rental info on those that are currently out..

Hi. I have a database with 3 tables - movies, rentals and customers. They
are all related. I have a movie form with MovieID, MovieTitle (from the
Movie Table) and Rented (from the Rented table). I have a subform on that
form that holds customer information from the Customer table. I have set up
a search form that has a combo box with movie titles bound to it. What I
want to happen is when a user selects a movie title from the combo box, if
the movie is rented then open the Movie form to show the information on the
rental. If it is not rented then display a message box to say "Movie not
rented". Here is the code I have on the combo box of the search form.

Private Sub cboSearch_AfterUpdate()

Dim stLinkCriteria As String
stLinkCriteria = " [MovieTitle] = '" & Me!cboSearch & "'"
If Form_frmMovie.Rented.Value = False Then
MsgBox "The movie you selected is not rented", vbOKOnly, "Movie Not
Rented"
ElseIf Form_frmMovie.Rented.Value = True Then
DoCmd.OpenForm "frmMovie", acNormal, , stLinkCriteria, acFormReadOnly
End If
End Sub

Hope someone can help!

 

[Marshall Barton]

Ok, the form is opening correctly when the movie is not
rented.

This leads me to conclude that the statement:
If Form_frmMovie.Rented.Value = False Then
is not doing anything useful. This makes sense if the form
is not already open because the Rented control will not have
a value. When you come right down to it, that kind of
reference is really invalid in this kind of usage, so I
don't see how the form is opening at all.

Try getting rid of all the If stuff and just open the form
to if things make more sense.

To check if the movie is rented or not, you need to retrieve
the rented value at the current time. I don't know what you
are putting in the Rental table so I can't suggest a way to
look it up.

A different approach to this whole issue is to set up the
combo box so it filters out the movies that are not rented.
How you would do this also depends on what's in the Rental
table.
--
Marsh
MVP [MS Access]

Thanks for the reply. The tables are connected as follows:

CustID in Customer tbl to CustID in Rental tbl
MovieID in Movie tbl to MovieId in Rental tbl

The rowsource for the combo box is SELECT tblMovie.MovieID,
tblMovie.MovieTitle FROM tblMovie ORDER BY tblMovie.MovieTitle; and the
bound column is 1.

I have changed my stLinkCriteria to your suggestion (I understand what you
mean) and it works the same as before - the form opens when a movie is rented
and when it is not rented. If I could have it open when it is not rented
with just the movie information, that would be fine as well (instead of the
message box). Right now if the movie is not rented, the form opens blank.

Hi. I have a database with 3 tables - movies, rentals and customers. They
are all related. I have a movie form with MovieID, MovieTitle (from the
Movie Table) and Rented (from the Rented table). I have a subform on that
form that holds customer information from the Customer table. I have set up
a search form that has a combo box with movie titles bound to it. What I
want to happen is when a user selects a movie title from the combo box, if
the movie is rented then open the Movie form to show the information on the
rental. If it is not rented then display a message box to say "Movie not
rented". Here is the code I have on the combo box of the search form.

Private Sub cboSearch_AfterUpdate()

Dim stLinkCriteria As String
stLinkCriteria = " [MovieTitle] = '" & Me!cboSearch & "'"
If Form_frmMovie.Rented.Value = False Then
MsgBox "The movie you selected is not rented", vbOKOnly, "Movie Not
Rented"
ElseIf Form_frmMovie.Rented.Value = True Then
DoCmd.OpenForm "frmMovie", acNormal, , stLinkCriteria, acFormReadOnly
End If
End Sub

How are your tables related?

What is the combo box's RowSource and BoundColumn?

Normally the combo box would be set up to display the title
but have the MovieID as its value. The table relationships
would also be by movieID so the criteria would be:

stLinkCriteria = " MovieID = " & Me!cboSearch

 

[jahoobob via AccessMonster.com]

Lot of assumptions on my part.

I assume you don't rent movies like Blockbuster where the customer comes in,
finds an empty dvd box and takes it to checkout. If there are no empty dvd
boxes then there are no movies to rent.
From what you describe, the customer comes to the counter or phones seeking a
movie and you check to see if the title is available. If it is then you want
to be able to enter the rental info and if it isn't you want to be able to
tell the customer that it is out.

If you have only one copy of each movie it is pretty simple. If you have
more than one copy then if at least one copy hasn't been rented, ok. I will
assume that if you have more than one dvd of a movie each copy is in your
movie table so you would have something like "Chronicles of Narnya 1",
Chronicles of Narnya 2", etc.

You could open a search form that would search movie titles in a query with
the Movie and the rental info. The search would allow you to put in "Rambo"
and get "Rambo: First Blood", "Rambo: First Blood Part II", and "Rambo III".
Use tis inthe criteria of teh movie title in the query:
Like "*" & [Forms]![name of the search form]![name of the text box] & "*"

You would get another form with your search results from that query that
would show the movie and rental info including whether or not the movie is
available. Put a button on this second form to go back to the search form
and a button to go to the rental form. When the record is selected with the
available title on the second form and the Rent button is selected you can
pass the movie info to the rental form via code. Remember to open the rental
form before closing the search results form. You can then enter the customer
info via a combo box.

Thanks for the reply! Not quite sure what you mean though. When I search on
a movie now, I get a movie form with movie id and movie title and rented.
Rented is ticked if the movie is rented and the customer subform is
populated. If the movie is not rented I just want a pop up to say "This
movie is not rented". Or the movie form could open but the only information
that would be filled in would be movie id and movie title.

Anyway that it would work would be fine for me so if you could explain your
reply a little more in depth that would be great.

Seems to me that your main form should be Rentals, not Movies, in which you
search for a movie. If the movie is not rented then the form is filled in

[quoted text clipped - 27 lines]

 

[Guest]

Hi. Thanks again for the reply and your assumptions are correct. What I now
have is a search screen with a combo box of movie titles, the user selects
one and a form opens showing movie information, rental information and
customer information (if the movie is rented). I have added a "Rent" button
on this form and would like to have it disabled if the movie is already
rented and enabled if not. The code I was trying to use for this was:

If chkRented.Value = -1 Then
Form_frmMovie.cmdRent.Enabled = False
Else
Form_frmMovie.cmdRent.Enabled = True
End If

I'm not too sure where to put this code or even if this is what I need.
frmRental_subform is a subform of frmMovie that only contains the Rented Y/N
checkbox. When the user selects this the cmdRent then a new rental form will
open.

Cathy

Lot of assumptions on my part.

I assume you don't rent movies like Blockbuster where the customer comes in,
finds an empty dvd box and takes it to checkout. If there are no empty dvd
boxes then there are no movies to rent.
From what you describe, the customer comes to the counter or phones seeking a
movie and you check to see if the title is available. If it is then you want
to be able to enter the rental info and if it isn't you want to be able to
tell the customer that it is out.

If you have only one copy of each movie it is pretty simple. If you have
more than one copy then if at least one copy hasn't been rented, ok. I will
assume that if you have more than one dvd of a movie each copy is in your
movie table so you would have something like "Chronicles of Narnya 1",
Chronicles of Narnya 2", etc.

You could open a search form that would search movie titles in a query with
the Movie and the rental info. The search would allow you to put in "Rambo"
and get "Rambo: First Blood", "Rambo: First Blood Part II", and "Rambo III".
Use tis inthe criteria of teh movie title in the query:
Like "*" & [Forms]![name of the search form]![name of the text box] & "*"

You would get another form with your search results from that query that
would show the movie and rental info including whether or not the movie is
available. Put a button on this second form to go back to the search form
and a button to go to the rental form. When the record is selected with the
available title on the second form and the Rent button is selected you can
pass the movie info to the rental form via code. Remember to open the rental
form before closing the search results form. You can then enter the customer
info via a combo box.

Thanks for the reply! Not quite sure what you mean though. When I search on
a movie now, I get a movie form with movie id and movie title and rented.
Rented is ticked if the movie is rented and the customer subform is
populated. If the movie is not rented I just want a pop up to say "This
movie is not rented". Or the movie form could open but the only information
that would be filled in would be movie id and movie title.

Anyway that it would work would be fine for me so if you could explain your
reply a little more in depth that would be great.

Seems to me that your main form should be Rentals, not Movies, in which you
search for a movie. If the movie is not rented then the form is filled in

[quoted text clipped - 27 lines]

Hope someone can help!