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

[groovygirl3003]

Blonde me here didn't even think of putting it in a query alone, I was just putting it straight into the combo box.

I had the same syntax error and I managed to solve it last night. I can't remember how I did it now though and I haven't got my database with me at the mo (must have just been luck)

I'll have a look tonight, I am sure I am getting the correct results but it just keeps removing the previously stored data :-(

[patjones]

@groovygirl3003
You can either put it straight into the combo box, or put it in a query and then base the combo box upon the query.

[groovygirl3003]

In fact, I have just noticed my whole database appears to be messed up :-(

When I am looking at the tables, I inserted Rentals as a subdatasheet in DVDs and the data is wrong somehow.

The subdata sheet shows all rentals and not just the ones that relate to that DVD_ID.

What have I done?

[groovygirl3003]

@groovygirl3003
Sorry please ignore that last post, another blonde moment.

[NeoPa]

So where are we after all that then GroovyGirl? All done? Any outstanding issues? Will I regret even asking? :D

[groovygirl3003]

@NeoPa
Don't ask. It's still in the same position. I have given it to my tutor to look at. I have had my boss look at it and now I have sent it down for my brother to look at it.

I have made another query and sub query with the help of the boss that gets the right info but can't get it to work in the combo box.

The code for the query is:

SELECT DISTINCT DVD_ID
FROM DVDs
WHERE DVD_ID NOT IN 
(SELECT DISTINCT DVD_ID FROM [Unavailable DVD's]);
 

[patjones]

@groovygirl3003
I don't understand. That doesn't match up with either of the queries that we presented, both of which worked great when I inserted them in the rowsource entry of your combo box.

[groovygirl3003]

@zepphead80
The code that you wrote worked fine on the subform itself but as soon as I used it as part of the main form/subform and chose a value, it deleted all other instances where this dvd id appeared in the table and left the fields blank.

I'm still trying to find a solution to that bit so I can use that piece of code instead.

[patjones]

@groovygirl3003
Which one is the main form in the file that you posted? I will give it a try...

[groovygirl3003]

@zepphead80
It's the rentals form.

[patjones]

I have the main form open and I am able to make selections from the combo box just fine. I'm not really sure what it is that you are looking to have happen after a selection is made from the combo box.

It seems to me that we solved your initial problem, which was to get the box populated with only the available DVDs, and that now you have some other issue which may not be related to this. Am I right?

Pat

[groovygirl3003]

@zepphead80
Yes that is corret. I can select a DVD from the list and these are the correct ones showing yet when I look back over previous rentals the values have been removed.