Change field in one table (1 or 0) auto by (Y or N) of another table!

[yandoo]

Hi,

 

Please help me im having a nightmare.

 

I have a simple database that records the loaning of laptops! It consists of 4 tables:

 

user table - user details

 

Laptop table - laptop details (that are loaned)

 

Teacher table - teachers details (that laptops are loaned too).

 

Loan table - details the loans of the laptops to teachers

 

The structure of the tables can be viewed on attachement!!!

 

Heres my problem.......

 

When a loan is created for a laptop a form is filled out that holds the followiing data:

 

Loan ID

Username

Laptop Name

Teachers Name

Date

Time

Loan completed (Y/N)

 

When the form is filled out and a loan is arranged, the completed field is entered as N (for uncompleted loan). As soon as the laptop is returned the completed field of the loan form is changed to Y (loan complete).

 

The field " Laptop name" (in the loan table) is a dynamic list that pulls all the laptop names from the laptop name table.

 

The problem is that when a loan has been initiated and the details in loan form have been entered i want to make it so that if a NEW loan was initiated that when the user came to selecting the laptop names (from dynamic list menu) it would ONLY display the laptops that are currently NOT on loan or avaliable....!!!!!

 

To do this, so far i have added another field into the laptop table that displays the avaliability of the laptops by either a 1 or 0!!!!

 

I need to make it that when a laptop loan is entered, on the updating of the form that somehow the "completed" field on the loan table is checked to see if it is either Y or N and that if it is N (to signal loan NOT complete) the laptop table also updates the avaliability field of that specific laptop which has just been loaned!

 

This would then mean that once a laptop has been loaned, the laptop table would mark the avaliability of that specific laptop to 0 (in the laptop table)

 

....Then when the dynamic menu is used in the loan form, it would ONLY display the laptops with avaliable 1 in the field.

 

This would mean that the same laptop could never be loaned twice!!!

 

I hope i have been clear enough. Please please help me this is doing my head in now!!!

 

Thanks

 

Tom

 

p.s. this isk inda of what im looking for:  ONLOAD update laptop name table to 1 WHERE loan table completed field = Y

 

 

 

 

 

 

 

[attachment deleted by admin]