How to change a value on another form/table using a button on a form

[groovygirl3003]

The database is designed to record all Dvd's in my collection and monitor them when friends take them, kind of like a rental system.

The tables/forms in question are DVD's (a table storing all information about the Dvd's) and rentals (the basic rental details, who, when etc) and rental details (the list of films)

The DVD table/form has an availability check box (yes/no) to state whether the dvd is avialable or not.

The Rentals form has Rental details as a subform and I would like to have a button on the main form that when clicked, changes the availability check box on the DVD table or form for the DVD's listed in the rental details subform.

I want to say it can be done using an If statement but I don't know how to go about it. I have tried adding this object onto the subform with the control source set to the DVD table. I then wrote VBA on the lostfocus event of another field to try and update this field but I got an error saying I can't write to this field.

Any ideas? I would be more than grateful if anyone can help. It's driving me crazy

Thanks

Groovygirl

[patjones]

I think this is possible but it would be great if you could post all the code you have so far (that is related to just this process) and we can go from there.

Pat

[groovygirl3003]

I've actually taken the code out now as I got really frustrated with it but I replaced it with a macro.

I added a quantity field to the subform (which will always be 1 for a dvd) and on the LostFocus event added a Setvalue macro as follows:

Item: [DVDs]![Available]
Expression: False

This doesn't work and now I have no idea. All I want it to do is uncheck the box when the button is clicked.

I have just had an operation on my ear and finding it very hard to concentrate on anything :-(

[NeoPa]

If you are considering updating a field in your DVD table to reflect when one is currently on loan (as opposed to unavailable for any other reasons) then I would reconsider. This information should properly be extracted from the [Rental Details] table at time of enquiry. This is in accordance with the rules of Normalisation (See Normalisation and Table structures). If it's a project, I doubt very much you'll gain points for ignoring the fundamental principles of database design.

Welcome to Bytes! and I wish you well with your ear. May it heal quickly and fully (and as much as possible painlessly).

[groovygirl3003]

I don't think I understand what you mean when you say extract from the rental details table. Should the yes/no field be in this one instead?

I am a distinction level student and I have developed many databases in the past that all work properly and follow the fundamental principles of database design.

Thanks for the warm welcome and well wishes. I hope it heals quickly too as it is very painful and uncomfortable.

[NeoPa]

OK. Let's see if we can break it down. Are you requiring a field (CheckBox whatever) to indicate that a DVD is presently unavailable to rent, so that you can reflect the status, when this status can be determined by looking at another table which contains start- and end- dates of rentals for all the DVDs?

[groovygirl3003]

Currently the DVD table holds the availability check box.

On the rental details subform, I have a combo box that shows only the DVD's that are available using a query. This means the user cannot select a dvd that is already out.

I want a button on the rental/rental details form saying "Proceed" and for that button to change the availability checkbox so that dvd won't show in the combo box.

Once the end date of the rental has been put it in, the dvd will become available again.

[NeoPa]

Essentially then, that's a Yes.

It is not good database design to proceed in that direction (Ask your tutor. I expect they'll tell you something very similar).

One of the fundamental guiding principles of database design and work is that data be stored :

1. In the most appropriate place.
2. Only that place.

In this case that would be the data indicating whether or not a DVD is available. The appropriate place is in the fact that the return date is not yet set for it. Storing that same information in the DVD table would be non-normalised. Hence I advise you, to get more points in your assessment, to avoid that approach.

I hope that clarifies things for you, and best of luck with your work.

BTW. That works that way because the data is available to you via SQL, or queries. It is not about not having that info at all, Just about where best it is stored.

[groovygirl3003]

I get what you are saying now but how would if I removed that check box and worked with the end date, how would I get it to work?

I would have to set the combo box to somehow cross reference the DVD's table with the rentals table to see if the dvd has been returned before listing it in the combo box.

I can't see how I would do this.

This is my last database assignment for this course and I seem to be making it more complicated and getting myself lost in the process.

I really appreciate your help, just wish it was simple :-(

[patjones]

Sorry that I'm jumping in a little bit late, but I have been following the thread.

I would have to set the combo box to somehow cross reference the DVD's table with the rentals table to see if the dvd has been returned before listing it in the combo box.

The DVD availability should be tied to whether it has been returned. Such a cross-reference/query is exactly how the combo box should be populated.

How can the DVD be available for the next rental unless it has been physically returned? If I take a DVD for a specified period of 5/1 to 5/3, but bring it back late (say 5/5)...a scheduling problem ensues.

Pat

[groovygirl3003]

@zepphead80
It can't be taken unless it's back, I get that bit. Queries are really not my strong point.

I think I have looked at just about every site on the internet trying to find the answer but just can't do it.

Queries are not my strong point. Any pointers as to how I would set one up?

[groovygirl3003]

I just tried a query selecting the following fields:

DVD_ID (dvd table), DVD_Title (dvd table) and Date_Returned(rentals table)

I set the criteria for Date_returned to "Is not Null"

This then only displays the DVD's that have previously been borrowed and returned.

It is one step closer however; I would like it to show all the dvds including the ones that have not been borrowed before.

[patjones]

How have you been doing queries up to this point? You must have some basis in experience with queries...otherwise your databases wouldn't function.

Pat

[patjones]

So the criteria really has two possibilities to it:

1. The DVD has never been borrowed, in which case it does not appears in the rentals table at all.
   
2. The DVD is in the rentals table, but all of it's entries in the rentals table have the returned date filled in.

If a DVD satisfies these two conditions, then you would want it in the drop down box...

Pat

[groovygirl3003]

Sorry got a bit confused, I thought it was going to have to be a cross tab query for some reason and that's where I have no experience. I have used select queries in all of my databases to search criteria, create product catalogues and much more. Sorry for the confusion. I am not quite with it at the mo, thanks to the pain killers.

[NeoPa]

groovygirl3003: It is one step closer however; I would like it to show all the dvds including the ones that have not been borrowed before.

I would suggest you need to return a record in the query for each DVD, but include a field (column) that indicates whether the DVD is available or not.

ComboBoxes can contain and show multiple columns. Only one column can be the default returned value, but more are available to be accessed.

From Pat's last post (#13) we would need to have an understanding of your own understanding of queries to know where to start helping you with them.

It may be a good idea here to post the SQL of the query you have so far that you felt was one step closer.

[groovygirl3003]

When you say add a field to the query, is this a field not linked to a table? I have done this before when adding calculated fields etc.

I currently use combo boxes elsewhere in the database that display multiple columns so I know how to do that.

Queries I have done in the past include:

Search queries (using parameters so the user can enter a value)
Queries used to just display the data (no criteria selected: e.g. phonebook, catalogues)
Creating Invoices (including additional fields for calculations etc)
Update queries (to update prices within a database etc)

These are the only ones I can think of off the top of my head. I hope this shows my understanding a little bit more.

I always make queries in design view rather than actually writing the SQL language but this is what I have:

SELECT DVDs.DVD_ID, DVDs.DVD_Title, Rentals.Date_Returned
FROM Rentals INNER JOIN (DVDs INNER JOIN [Rental Details] ON DVDs.DVD_ID = [Rental Details].DVD_ID) ON Rentals.Rental_ID = [Rental Details].Rental_ID
WHERE (((Rentals.Date_Returned) Is Not Null));

Hope it makes sense. I really do appreciate you all trying to help :-)

[patjones]

Well your query as it is written won't return DVD's that have not been rented before, because the INNER JOIN will pull ID's that appear in both tables only. By changing to a RIGHT JOIN and putting another condition in the WHERE clause, this query could work, but I like NeoPa's suggestion quite a bit.

Pat

[groovygirl3003]

You have lost me a bit there with SQL speak. I don't know what the INNER JOIN/ RIGHT JOIN is about.

I always use the design view.

NeoPas suggestion sounds good but again, I don't fully understand it.

Do they mean add a field to say whether the DVD is available and have both of these display in the combo box?

If this is the case how would I get the available column to say whether it is available or not depending on the end date?

By displaying more than one field in the combo box, do they mean the dvd and availability? If so would this not display both available and unavailable dvd's?

I am really trying here, thanks so much for your patience :-)

[NeoPa]

Can there be multiple Rentals per DVD? Or is the Rental reused for each use?

This is important as multiple possible Rentals will necessitate filtering out earlier records.

[NeoPa]

BTW SQL JOINs may help with the understanding. It's a really important and fundamental part of SQL - which in itself is a pretty important part of any database work.

That said, Access keeps this a little hidden in the background generally, so it's understandable you may not have come across it. In Access we're talking about a Type 1, as compared with a Type 2 table join.

[patjones]

@groovygirl3003
In query design view, on the field line, you can do something like this:

Status: IIf([tblLeave]![fldReturned],"CLOSED","OPEN")

This is an example that I just tested out in my leave of absence tracking database at work. What you see above adds a column called "Status" to my query...the value of which is determined by fldReturned ("CLOSED" if the employee has returned to work, "OPEN" if the employee is still on leave).

Granted, my example is simple because it is based only on what one field is doing, but you can use a little logic to form more complicated tests.

Pat

[groovygirl3003]

Sorry I haven't responded for a while, I have been unwell. NeoPa, as this is relating to a personal DVD collection, there is only one copy of each DVD.

Zepphead, is the value in [fldreturned] on your database already open or closed?

I've tried a number of possibilities I have found on the internet and none of them work.

I think I may just give up, identify it in my project work as a problem I couldn't solve and hope I still get tops marks for the rest of the system.

[NeoPa]

groovygirl3003: NeoPa, as this is relating to a personal DVD collection, there is only one copy of each DVD.

The question wasn't so much about the DVD data as such, but more about the rental data. I'm not sure from your answer if you understood that.

Consider DVD A is lent out on Monday; returned on Tuesday; relent on Wednesday. In this scenario do you keep track of the Monday lend, or just the latest one (Wednesday)?

If a full history is kept then more work would need to be done first to restrict the data returned just to the latest lend. If not, then the query is simpler. Does that all make sense?

[groovygirl3003]

My apologies, I had misunderstood the question. Information is recorded for each rental.

Would it be easier if I emailed a copy of the database for you to have a look at?

I now have to queries set up. One shows all the DVD's in the rental table that have a return date and the other is an Unmatched query (I think that's what it's called) that shows all records that are in the DVDs table but not in the rentals table.

Now I am busy researching Union queries in order to combine these queries to use in order to populate the combo box.

[NeoPa]

You can attach the database if you like. It might make understanding all the inter-relationships much easier as we'd have the database to refer to. I'll include instructions below.

What I would say relating to what you've already done, is that the information that Pat left in post #18 is a far better approach than the one you're following. A single query should do it for you.

When attaching your work please follow these steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If you've done anything in steps 1 to 3 then make sure that the problem you're experiencing is still evident in the updated version.
5. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
6. Compact the database.
7. Compress the database into a ZIP file.
8. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[groovygirl3003]

How do I compact the database and turn it into a zip file?

[groovygirl3003]

Please find attached a copy of the database I am currently working on.

Please note that this database is still mid production therefore not all forms are formatted and the buttons have not been finished.

[groovygirl3003]

As for the suggestion in post 18, I got lost. I had a quick look at the SQL thread but didn't manage to take much in.

I am working on three assignments at once and haven't got much time to learn SQL.

[patjones]

@groovygirl3003
I've looked at your file. It seems like the combo box works fine...you have it based on the Available? column in the DVDs table. I'm not sure I see what the issue is...

Pat

[groovygirl3003]

@zepphead80
That value is set when the dvd is originally recorded however; I somehow need to update this value when a dvd is on loan to show it is unavailable (therefore won't show in the combo box) perhaps using a button on the form. I was then advised to reconsider (post #4) and have since been looking at queries.

[patjones]

OK, well...then we're sort of coming around full circle. Looking at your relationships diagram validates what we have been advising you to do up to this point.

You have a Date_Returned field in the Rentals table. If there are DVD's in the Rentals table which have a null Date_Returned, it means that DVD is still out and should not appear in the drop-down list. Otherwise, there are two circumstances where it should appear, which I listed in post #14.

I tried this:

SELECT DVD_Title 
FROM DVDs
WHERE DVD_ID NOT IN (SELECT [Rental Details].DVD_ID
                     FROM [Rental Details] INNER JOIN Rentals ON [Rental Details].Rental_ID = Rentals.Rental_ID
                     WHERE Rentals.Date_Returned IS NULL);
 

And this gave me results that I think are correct in terms of what you have in the tables. However I don't think the data you have in the tables is quite right. For example, Rental_ID's 6, 7 and 8 appear in Rentals, but not Rental Details...which can't be the case. So in order for you to fully test my query you would need to make your data a little more self-consistent.

What this query does is finds out what DVD's are in the Rental table and have not been returned, and then give us the DVD's that are not in that list (are available).

As a side note on your table design...the way I look at this, your Rental Details table is not really necessary. You can very easily insert DVD_ID into the Rental table and directly connect the DVDs table to Rental via a one-to-many relationship, thus eliminating the Rental Details table. This would also greatly simplify the query that I wrote out above.

Pat

[groovygirl3003]

Sorry about the incomplete data. I have been entering data as I go along to test things out and I was obviously using the rentals form and subform and have entered a new rental but no data in the subform.

I will try your query tonight, once I return from work and my course.

In terms of the rental details being in the rentals table, would this not go agianst the rules of normalisation? If someone borrows more than one dvd, this would cause repeating data in the table.

[NeoPa]

groovygirl3003: In terms of the rental details being in the rentals table, would this not go agianst the rules of normalisation? If someone borrows more than one dvd, this would cause repeating data in the table.

That depends on how you are using the term Rental. My guess would have been that a rental involved a one-to-one relationship between a friend and a DVD. From your comments though, I'm thinking now that your understanding is of an agreed transaction between you and a friend where one or many DVDs could go on loan. If this latter is the case then Pat's SQL handles that for you. If not, then some simplification of the structure could prove beneficial.

[NeoPa]

Another way of producing the same results as Pat's, but without the need for a subquery in the WHERE clause, and along the lines I was suggesting earlier :

SELECT DVDs.DVD_ID
     , [DVD_Title]
 
FROM   [DVDs] LEFT JOIN (
    SELECT tRD.DVD_ID
    FROM   [Rental Details] AS tRD INNER JOIN
           [Rentals] AS tR
      ON   tRD.Rental_ID=tR.Rental_ID
    WHERE  tR.Date_Returned IS NULL
    ) AS subQ
  ON   DVDs.DVD_ID=subQ.DVD_ID
 
WHERE  subQ.DVD_ID IS NULL

[patjones]

That's fine; let me know how it works out.

I should add that my query is not intended to actually update the Available? column. It is meant to just populate your combo box with the names of the DVDs which are actually available to be rented. Perhaps NeoPa has thoughts on what to do with the Available? column itself.

As for your last point, if a friend takes more than one DVD, I don't see a problem with duplication because the combination of DVD_ID/Friend_ID will be different for each DVD that friend takes. Where duplication could arise in that scheme is if a friend borrows a DVD, returns it, then borrows it again. This would result in multiple instances of that DVD_ID/Friend_ID combination in the Rentals table. I don't know how much of a problem that is.

Pat

[NeoPa]

zepphead80: As for your last point, if a friend takes more than one DVD, I don't see a problem with duplication because the combination of DVD_ID/Friend_ID will be different for each DVD that friend takes. Where duplication could arise in that scheme is if a friend borrows a DVD, returns it, then borrows it again. This would result in multiple instances of that DVD_ID/Friend_ID combination in the Rentals table. I don't know how much of a problem that is.

I don't see that would even be a problem Pat. If the [Rentals] table included the extra field [DVD_ID] (and the [Rental Details] table were eliminated) then the [Rental_ID] would still differentiate one rental from another, even where the linked items were the same as a previous rental.

[patjones]

@NeoPa
Oh, you're absolutely right. My query will have to evaluate the sub-query for each comparison! Poor performance for a large number of records...

[NeoPa]

zepphead80: Oh, you're absolutely right. My query will have to evaluate the sub-query for each comparison! Poor performance for a large number of records...

I don't think that's true Pat.

A Domain Aggregate function call would certainly have that effect, but as there are no links to the outer values (No filtering within the subquery that refers to data from the outer query) I suspect this would need only to run the once. I could be proven wrong, but that's my understanding.

My post was because I'd suggested earlier an idea that this illustrates. Also because I prefer filtering to be done at the JOIN level where possible, rather than in the WHERE clause. I think this provides more scope for the SQL engine for intelligent optimisation. I certainly prefer my version of the SQL, but that is not to say that yours is worse by any margin. It seems perfectly workable and usable to me.

It shows some pretty decent understanding of SQL concepts too. I can tell you're no beginner at this.

[groovygirl3003]

You all sound like you know what you're talking about. I hope I can be that good one day.

NeoPa, I had a quick go of your code and I get a message saying DVD_ID relates to more than one table in the FROM clause and I couldn't figure out which bit of code it was refering to.

I am still at work so was just a quick look (I'm not supposed to be working on it) and will try again tonight.

In terms of the availability box, this can be removed if I can get this code to work as there will be no use for it

[patjones]

@NeoPa
OK; my understanding of subqueries is still evolving. It's hard for me to tell sometimes what needs to be run once and what needs to be run on each record, but I see what you're saying now: nothing in my subquery depends on anything in the outer query...

[NeoPa]

groovygirl3003: NeoPa, I had a quick go of your code and I get a message saying DVD_ID relates to more than one table in the FROM clause and I couldn't figure out which bit of code it was refering to.

That makes sense. Both [DVDs] and the subquery [subQ] have a field called [DVD_ID]. Therefore the reference in line #1 must be qualified if it's to be unambiguously identified.

NB. That changes the old line #1

SELECT [DVD_ID]

to what is there now.

[groovygirl3003]

That still seems to show the same error :-(

[NeoPa]

Where is this in your database?

Maybe I can see for myself what's going wrong. Sometimes it's easier that way. I may see something that wasn't too obvious from the situation as related in text.

[groovygirl3003]

It will be under the combo box on the rental details subform.

I tried last night and managed to get the code working and the right results displayed however; when I select a DVD, it seem to remove it from all other fields in the rental details table where it has been used previously.

[NeoPa]

Perhaps you could try that again for me. I tried it on the copy of your database that you attached, and got the results below :

DVD_ID  DVD_Title
3       Top Gun
4       House
5       Prison Break
6       Saw

[NeoPa]

I just had a very strange experience.

I put that SQL (which works perfectly well within a query) into the ComboBox of the subform, and when it was saved and reloaded, the SQL had been changed by Access into non-working SQL. I've seen similar issues before with subqueries (Access QueryDefs Mis-save Subquery SQL), but never as serious as this. I've always been able to fix the other type, but this always converted whatever I put in to unusable SQL, giving the Syntax error in FROM clause message.

In a situation like this I'd save the subquery as a named query (QueryDef) and refer to that in the SQL instead.

[patjones]

@NeoPa
What is the exact SQL you are putting in the combo? Is it from post #35? I'd like to try it.

When I put mine in, from post #32, it worked fine and I got the same four results.

Pat

[NeoPa]

zepphead80: What is the exact SQL you are putting in the combo? Is it from post #35? I'd like to try it.

That's the one Pat.

zepphead80: When I put mine in, from post #32, it worked fine and I got the same four results.

Doh!!

[patjones]

@NeoPa
I don't know. Yours worked correctly as well when I put it in the combo!