Right I have given up on the idea of a check box for my equipment out on loan in my booking system, I am going to focus on the booking table to stop any duplicate bookings (maniky concering the equipment) to be made. But Im not sure where abouts in my booking form I should do this and what cde to use???
Can anyone push me in the right direction please??
I haven't been following your progression here.. what schema did you end up going with? In essence you're going to be running a check against the db before committing your records. My recommended schema would involve a booking table with in and out dates. If you go with that schema, you could run a DCOUNT(), or a flat out query to discover whether an item is available or not. dcount would be roughly like so:
DCount("item", [booking], "item_id = " & ItemIdHere & " AND [in] Is Null")
That returns a count of the field "item" from any records in the booking table where the item_id is = to whatever item you're wanting to look at, and [in] is null, meaning it hasn't been returned. If DCount returns 1, the item is out, if it returns 0, the item is in.
here is my current db to show how things would progress....the teacher would make a booking using the booking form and then save it but at present its possibl to book the same equipment out to different people where this cannot happen in real life
Sorry I havnt been following this either, I would create a field in the products table that says in stock / Out of stock and the in the drop down on the booking form add the In Stock / Out of Stock field to the drop down, make the columns visible = 2 but make the second column 0 cm wide then on the after update update a field on the booking form with the value of whether it is in stock or out of stock.
me.field = combo1.column(1)
it is equal to column 1 because it starts from column 0, that will tell you whether it is in stock or out of stock to save booking it out twice
If you want more detail on that then i will go into more description but i am not sure if that is what you want?
The thing is i am trying to create an instant where if the teacher selected a the equipment ID (or soon to equipment name) when he selects it from the drop down box an box will appear telling the teacher that the equipment asked for is currently on loan to another student. Then the teacher can access another form which allows him to make a reservation for the student requiring the equipment which is already out on loan.
Put the following code in the AfterUpdate event of your EquipmentID field in you Booking form. It does the check you want and asks the user if they want to make a reservation using the method suggested by Teddy. The check criteria is based on the fact if a DateTakenOut exists and a DateDueBack doesn't then we can assume the equipment is still on loan (I think this logic is correct )
You will have to add code to open your reservation form e.g. DoCmd.OpenForm "myReservationForm" etc.
Dim response, strEquipment
strEquipment = [Forms]![Booking].EquipmentID
If DCount("[BookingID]", "booking", "EquipmentID = " & strEquipment & " and (isnull(DateDueBack)=-1) and (isnull(DateTakenOut)=0)") <> 0 Then
response = MsgBox("Equipment selected is already on loan, do you wish to make a reservation", vbYesNo, "Loan Problem")
If response = vbYes Then
'Launch required reservation form here e.g.
'DoCmd.OpenForm "myReservationForm" etc.
'also, you can put the EquipmentID into your reservation form e.g.
'[Forms]![myReservationForm].myEquipField = strEquipment
'Must set EquipmentID field back to null otherwise user can save invalid record.
[Forms]![Booking].EquipmentID = Null