Update Table Problem

[Jessy]

I have a table called [Commodities] and another table called[Reservations]. The relationship is one to many [one commodity can have many reservations]

In [Commodities] table I have a field called [status] which tells if a commodity is reserved or not reserved.

What I want to do is when I make a reservation (using the RESERVATIONS FORM) for a commodity I want the [status] field of the reserved commodity to change to reserved automatically.

How do I do that?

Thanks for your help.

 

[cogent1]

If one commodity has many reservations, shouldn't the status field be in the reservations table? Or does the first reservation lock the commodity out?

 

[Jessy]

The reason I want the status to be in the commodities table is because i have a query in the reservations form which shows only the non reserved [commodities ids] and the user can choose the one he/she wants to reserve.

Commodities Form its separate from the Reservations Form.
If Reservations was a Subform of Commodities I could update the status field easily. But I want 2 separate forms thats why I dont know how to do it.

 

[cogent1]

Now, do you mean that on any given occasion, one commodity can be reserved once only?(which is what I originally asked?).

Obviously, a commodity can be reserved more than once on different occasions.

Sounds more like a many-to-many relationship to me, in that many commodities can be reserved many times.

What fields are in both tables, please?

 

[Jessy]

Maybe you are right. It can be many to many relationship.

The fields in commodities are quite many. (commodityID, address, ownername, furniture status, rent price, status etc)

In reservations table the fields are (reservationID, reservationdate, bookedby, payby, etc)

When a reservation s finished I want to call a function/whatever to update the status field of the reserved commodity.

 

[Rich]

Surely if you store the start of reservation and end of reservation dates you can see when a commodity is available without an availablility field?

 

[cogent1]

Rich is right, of course. By using the date information, you can put: "reserved until 1/1/2099" . This is much more informative than just "Reserved" .

And given what you have told me, it is a one to many relationship between Commodity and Reservation. And the Reserved field should be in the reservations table. There should be a CommodityID field in your Reservations table as the many side of a one-to many relationship.

Create a form based on a query which includes both tables. The wizard will invite you to make a form/subform. Each commodity will be listed on the main form and all its associated reservations and statuses on the subforms.

To automatically update the reserved status, put Me!MyStatusCheck =True on the AfterUpdate event of the reservation date. To add sophistication, you should write code to uncheck the status for any reservation that has run its course.

Better to follow Rich's advice and have start and finish dates. Then you can write a query to show only current reservations anyway.