rentals database

[Ryko]

Hello!

Since my video rental database is in Slovene I'm affraid I can't post an example so I'll try to translate the tables involved and post some questions. Hopefully I'll make sense, keep in mind I'm a beginner.

tblMember:
MemberID (PK)
Fname
Lname
etc.

tblRental:
RentalID (PK)
MemberID
DateRented

tblMovie:
MovieID (PK)
Title
etc.

tblCopy:
CopyID (PK) (autonumber type used instead of a composite key of MovieID and Copy)
MovieID
Copy
Rented (flag)

To explain the above table, let's say my rental shop holds three copies of Citizen Kane (MovieID=100), which takes three records in the tblCopy table:
...
CopyID MovieID Copy
...
34 100 1
35 100 2
36 100 3
...

tblCopyRental:
CopyID (PK)
RentalID (PK)
DateReturned (not in tblRental as movies from a particular rental could be returned at different dates)

I'd like to be able to assign to a rental only copies of movies currently available. So I created a query (in design view, mind you) that lists currently available movies:

SELECT tblCopy.CopyID, tblMovie.MovieID, tblMovie.Title, tblCopy.Copy
FROM tblMovie INNER JOIN tblCopy ON tblMovie.MovieID=tblCopy.MovieID
WHERE (((tblCopy.Rented)=False))
GROUP BY tblCopy.CopyID, tblMovie.MovieID, tblMovie.Title, tblCopy.Copy
ORDER BY tblMovie.Title

I actually created the Rented flag for this purpose. At the mom I have to manually check copies that are rented.

What I want to know, is something like Rented flag in tblCopy neccessary? Couldn't this be achieved somehow by only excluding copies from tblCopyRental where DateReturned doesn't yet exist from all the copies rental shop holds (listed in tblCopy)? I mean the way I see it all data is there to figure out which movies are available.

If Rented flag is a good idea, is it possible to have it automatically change status when available copies are assigned to a rental, and then back when DateReturned is entered?

Thanks for your help.

 

[llkhoutx]

I would think that you'd want to flag specific copies, in whatever table they're specifically identified. If your tracking number of copies of a specific title, that of course won't work. In the latter case you only track total number of a totle and number rented.

Older copies might be sold because their play qulaity has deteriated. Just my thoughts.

 

[Ryko]

I have decided not to go with the Rented flag after reading some other threads and suggestions.

I created a query qryMaxOfDateRented that looks like this:

SELECT tblCopyRental.CopyID, Max(tblRental.DateRented) AS MaxOfDateRented
FROM tblRental INNER JOIN tblCopyRental ON tblRental.RentalID=tblCopyRental.RentalID
GROUP BY tblCopyRental.CopyID;

This query gives me all copies that have been rented before with latest rental dates.

Then I created this query

SELECT tblCopyRental.CopyID
FROM tblRental INNER JOIN (qryMaxOfDateRented INNER JOIN tblCopyRental ON qryMaxOfDateRented.CopyID = tblCopyRental.CopyID) ON tblRental.RentalID = tblCopyRental.RentalID
WHERE (((tblRental.DateRented)=([qryMaxOfDatRented].[MaxOfDateRented])) AND ((tblCopyRental.DateReturned) Is Not Null))
GROUP BY tblCopyRental.CopyID;

and it will give me only those copies that were rented before and also returned (DateReturned Is Not Null), checking only latest dates rented assures they weren't rented again - meaning they are available.

However this list does not include those copies that have never been rented before and are, of course, also available. What I want is a full list of available copies but I'm lost in how to accomplish this. So - I need to pick all those CopyID's from tblCopy that haven't even once been recorded in tblCopyRental and then add them to the results of the above query. Any ideas?

 

[gromit]

Hi -

Pardon me for pushing my nose in. You can use the IN statement to identify all records that are in (or not in) a particular group.

E.g.

SELECT * FROM tblCopy WHERE tblCopy.CopyID NOT IN 
(Select CopyID FROM qryMaxOfDateRented)

I haven't tested this, so you might have to tweak the syntax.

- g

 

[Ryko]

gromit - it works perfectly. Thanks!

I combined results using union query and now I have a list of all available movies in a query.

I also started a simple Rental form, which uses a RentalCopy subform to add copies (of movies) to a particular rental (check attached jpg).

Now if you would please open this database found in another thread

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=3307&d=1063634029

and choose the "Uses second form" option, then click on "Find Employee Name" command button.

Here's my idea. Suppose I wanted to use such search to pick available movies and assign them one by one to a rental. Instead of using a command button I'd have the search form pop up by clicking on a empty row in a CopyID column (see where arrow points in the picture attached). Instead of EmployeeID and EmployeeName I would have CopyID and TitleCopy (combined Title and Copy, like "Citizen Kane - 2nd copy").

What I would like to know is, when I find a copy I'm looking for (using the search form), how do I make it's CopyID be put in that exact same row I clicked on for search form to pop up in the first place? I guess I'm looking to store a CopyID of a copy of a movie, chosen in that search form, through Rental form (and RentalCopy subform) into tblCopyRental.

 

[gromit]

Here is a general approach, though there are likely some details to work out:

Set your subform in continous view. Then assign an event (probably DoubleClick) for the appropriate field. When you get a double click, you want to open the search form and let the user select a copy. If the user finds a copy, then you would set the value of the subform according to CopyID found. If not, then you would simply cancel the search form. Either way, you close the search form and return to the original.

E.g. for the subform:

Private Sub CopyID_DblClick(Cancel As Integer)
    DoCmd.OpenForm "frmSearch", acNormal
    
End Sub

Then on the search form, the code for the button

Private Sub SelectButton_Click()

'  Assuming that the rental info needs to go to the 
'  subform "frmsubRentals" on the main form "frmCustomer"
Forms!frmCustomer!frmsubRentals!MovieID = Me.txtMovieID

DoCmd.Close acForm, "frmSearch"
    
End Sub

hth,

g

 

[Ryko]

gromit

I've been trying to get it work but as a novice it simply seems too much for me.

I'm using the frmSearch pretty much the way it is in the example found in the link I posted in my previous message (just a few names changed)

The whole code of that search looks like this:
-----------------------------------------
Private Sub lstTitleCopies_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCopyRental"

stLinkCriteria = "[CopyID]=" & Me![lstTitleCopies]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Me![lstTitleCopies] = ""
DoCmd.Close acForm, "frmSearch"

End Sub
-------------------------------------
Private Sub txtInput_Change()
Dim vSearchString As String

vSearchString = txtInput.Text

txtSearchString.Value = vSearchString
Me![lstTitleCopies].Requery
End Sub


I've tried to put the code somewhere in Sub lstTitleCopies_Click() or even created a button on the frmSearch with your SelectButton code but I'm just getting answers like "Microsoft Office Access can't find the field 'frmCopyRental" (thats my subform) referred to in your expression". Or when I leave out my main frmRental and tried something like

Forms!frmCopyRental!CopyID = Me.?!?

and got "You tried to assign Null value to a variable that is not a Variant data type".

?!? - I've used a few things here, like Me.lstCopyTitles or just Me.CopyID but don't really know what this is referring to. I guess to the copy selected in search form?

Hmm, I think I'm just getting way ahead of myself, dealing with code in VBA which I have VERY little knowledge of. It's hard to succeed by trying and see if it works when you don't exactly know what you're doing.

 

[gromit]

Welcome to the cold cruel world of VBA programming! Until you get to a certain point, the error messages just seem mysterious and minor changes cause all kinds of problems. But don't feel bad, everyone has to start somewhere.

The message "Microsoft Office Access can't find the field 'frmCopyRental" means that Access is trying to find a field called frmCopyRental which is the name of a form. So you are trying to pass the form name where Access is looking for a field name.

I'm assuming that you will have a main form (Customer or Rental) with a subform (Copies) that shows multiple items (in continuous form view).

So double clicking the appropriate field on the subform needs to launch the search form. [Have you gotten that to work?].

Then the search form needs to find the correct copy. I'm not quite sure how you want to go about doing that, but somehow you need to access the records to let the user search for a Copy and identify both the name (for the user) and the CopyID (for the visual basic).

The value of the CopyID on the search form needs to get pushed back onto the appropriate field of the subform (see my earlier post). Using the search form example that you mentioned earlier, the CopyID will be analgous to the NameID field on the search form.

You want to make sure that you push the CopyID on to the subform as it exists on the Main form, not the standalone version of the subform. Hence the "Forms!frmCustomer!frmsubRentals!MovieID" form of my earlier example. (Although the names have to be adjusted for your forms). Also, the main form needs to be open when you run the search.

The null value error is also something that will need to be trapped out. If you search and don't return any records, then the CopyID will be null. You don't want to set the value of the field to null, just ignore it. But we can leave that for a little later.

So, see how far you can get with the following.
1. Create an event to launch the search form from your rental subform
2. Have the search form locate a movie and the correct CopyID
3. Put two buttons on the search form: one to select the chosen movie, another to cancel
4. Set the VBA code for the cancel button to close the form and do nothing
5. Set the VBA code for the select button to MsgBox the CopyID (just as check) and then set the appropriate value on the rental subform. Then code should then close the search form.

Let me know if you have more questions.

- g

 

[Ryko]

Okay this is mystery to me. I was so off track I was making a whole new database importing tables, queries and forms directly involved with the Rental form, renaming everything neccessary to English and trying to make it work so you could take a look at it.

While searching using frmSearch in the new database all of a sudden clicking on a selected movie didn't throw either a blank frmCopyRental or some VBA error message but rather nothing happened, selected movie (copy, to be precise) was just kept highlighted. So I added a command button using your SelectButton code Forms!mainform!subform!etc. and opened Rental form for the 1000th time and voila - copy was added to the CopyRental subform.

Working code looks like this:

Private Sub Command8_Click()
On Error GoTo Err_Command8_Click

Forms!frmRental!frmCopyRental!CopyID = Me.lstTitles
DoCmd.Close acForm, "frmSearch"
Exit_Command8_Click:
Exit Sub

Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click

Anyway, I kept subform in datasheet view as I find it much more practical just moving between rows to add more copies to a Rental than having to use navigation buttons. Any particular reason why you suggested setting it in continous view?

Now I have a couple new problems:
1. making it work in my original database, hopefully I'll deal with it tomorrow
2. when I select a copy and add it to a row in a subform, I would expect a new empty row to appear but it doesn't. Any ideas?

Adding "DoCmd.GoToRecord , , acNewRec" between the "Forms!..." and "Do.Cmd.Close..." lines doesn't work.

 

[gromit]

Sounds like progress.

1. I suggested using the subform in continous forms because I don't think you can catch the click event in the datasheet (maybe I'm wrong, so test it first). If you design your subform as a long skinny set of columns, you can obtain something that looks much like the datasheet. You can eliminate the navigation button and just use the drag bar at the side, like a datasheet.

2. Is Me.lstTitles a string variable or a foreign key to the copy table? You probably want to bring in the foreign key (numeric) value. (You may be fine, I just can't tell from the code).

3. Whether or not you create a record will depend on which record you are on in the Rentals table. If you are on the new record, it should add the information and then create a new record automatically. If you are just on an existing record, it will only update the field.

See the attached db for some illustration of what we have discussed. If you open the frmCustomers you will see the main form (frmCustomers) and the subform (frmsubRentals). If you double click on the RentalID field, it will open a search form (well, not really). You can, however, pick a movie and hit select to update the rental subform. Try clicking on different RentalID (like an existing vs. a new field) and see the effect. I have also added VBA code in the search form to handle nulls.

hth,

- g

 

[Ryko]

gromit

take a look at part of my database - try frmRental and see for yourself it won't go to a new record when you double click CopyID and pick/add a copy. It goes if you manually enter a copyID tho. I did change the subform to continuous view but double click worked just as well in datasheet view.

Thank you for being so helpful.

 

[gromit]

Man, I'm stumped. The subform definitely acts a little strangely, but I can't see what is different from the example I posted. Perhaps I can take another look tomorrow.

 

[WayneRyan]

Ryko,

I think I'd just put an unbound listbox on the main form and use
its event, but in this context you should switch the location of
the logic to declare a new record:

Private Sub tblCopyRental_CopyID_DblClick(Cancel As Integer)

    Dim stDocName As String
    Dim stLinkCriteria As String

    DoCmd.GoToRecord , , acNewRec ' <-- Added this line
    stDocName = "frmSearch"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub



Private Sub Command8_Click()
On Error GoTo Err_Command8_Click

Forms!frmRental!frmCopyRental!CopyID = Me.lstTitles
' DoCmd.GoToRecord , , acNewRec   '<-- Removed this line
DoCmd.Close acForm, "frmSearch"
Exit_Command8_Click:
    Exit Sub

Err_Command8_Click:
    MsgBox Err.Description
    Resume Exit_Command8_Click
    
End Sub

Wayne

 

[Ryko]

Wayne

As for the DoCmd.GoToRecord , , acNewRec line you suggested removing, it actually did nothing in the first place. I added it in my "trial and error" approach to get it work but forgot to remove it afterwards.

As a beginner I don't get your idea. Why a listbox on the main form, and why would that make it work?

Anyway, the whole thing I implemented pretty much works but it won't go to a new record. If I move to the next rental record and then back, an empty line to add a new movie appears. Same happens if I create/push the "refresh form data" button on the main form. If I enter a copyID it does too. But it doesn't when I select a copy through a search form? Beats me.

 

[gromit]

If you add a refresh after setting the value in the subform, it will show a new record. E.g.

Forms!frmRental!frmCopyRental!CopyID = Me.lstTitles
Forms!frmRental!frmCopyRental.Form.Refresh

However, it goes to the beginning of the subform, which is not particularly convenient.

I can't explain it either - guess I'm running out of ideas. I'll post as a new thread in the Forms forum and see if any wiser heads can suggest an answer-
http://www.access-programmers.co.uk/forums/showthread.php?t=99697

- g

 

[gromit]

Sergeant posted an easy fix:

Change the code for the Pick button to
Forms!frmRental!frmCopyRental.Form.Controls("tblCopyRental_CopyID") = Me.lstTitles

Seems to take care of the problem!

 

[Ryko]

gromit

What can I say? I admire you! I'm reluctant to post any further questions if/when they pop up as I don't want to put you into more trouble.
You're the MAN! Many thanks!

 

[gromit]

No problem. Hey, I learned something too. Why don't we close off this thread, and you can start a new post if something else creates questions.

;-)

 

[Ryko]

I don't really know how to address this new question, so pardon me for adding to this thread.

As you can see in my rentals database posted earlier, DateReturned is part of tblCopyRental (not tblRental) as copies of the same rental can be returned at different dates.

Since that would happen only on a rare occassion, I'm looking for a command button on the subform frmCopyRental, so when a DateReturned is assigned to one of the copies of a particular rental, this button would assign the same date to all other copies of that rental.

I'm not sure what's the right approach. I was thinking of an update query, but I'm only capable of updating dates of all rentals together (qryCopyRental is the same as tblCopyRental, works as a duplicate):

UPDATE qryCopyRental INNER JOIN tblCopyRental ON qryCopyRental.RentalID = tblCopyRental.RentalID
SET tblCopyRental.DateReturned = qryCopyRental!DateReturned
WHERE (((tblCopyRental.DateReturned) Is Null) AND ((qryCopyRental.DateReturned) Is Not Null));

I, however, only wish to be able to update return dates of a single rental as selected in frmRental. I'm pretty sure I can't help myself with the update query above, but it was another learning experience.

 

[Ryko]

I've already solved this by adding a command button that looks like a check box to assign a DateReturned to each copy when it is returned. Works perfectly!