1. When a booking is made I need to have confirmation in a dialogue box that a place is available and when "OK" is pressed the number of places available is reduced by 1. Perhaps even telling me how many places are left.
First, define a field in Course_tbl called Size and store 20 in it. (If you store the course size this way, you can easily change it if future course bookings require a larger or smaller size.)
You should have the following relationships:
Customer_tbl::CustID = Booking_tbl::CustID
Course_tbl::CourseID = Booking_tbl::CourseID
Now you can create a calculation field in Course_tbl, TotalBooked. Define it as Count(Booking_tbl::CourseID)
Each time you book a customer for a course by adding a record in Booking_tbl, the TotalBooked will increment by one and the space available, Size - TotalBooked, will decrease by one.
With all that in place, the following expression will tell the user if there is room to book additional customers:
Course_tbl::TotalBooked < Course_tbl::size
You can use this in an if or case function in a calculation field to display text such as "Space Available" or in the If step of a script set to check on availability. It depends on how you choose to design your interface.
2. I also need to know if a customer has already booked on a particular course and if so a dialogue box appearing telling me this. eg "This customer is already booked on this course"
A simple way to do this is by setting up a validation rule. Define a text field in Booking_tbl to auto-enter the following text: CourseID & " " & CustID. Set the Unique value option on this field and enter your validation message "This customer is already booked on this course".
Thanks a lot. Works fine now.