Limiting Reservations

[mike1975]

Help please!

I am trying to create an application for our organisation that will allow people to book onto various courses. I have created following tables;
1/ Employee records
2/ Courses - courses that are available, date, time, location, MAXIMUM NUMBER OF SPACES
3/ Bookings table, contains booking ref, employee no, course number.

Here lies my problem, how can I get the system to recognize that a course is full ie the maximum number of spaces from table 2 has been reached and then stop people from booking any one else onto that course??????

Any help would be greatly appreciated!

MIKE

 

[allanon]

My suggestion to you would be to simply set up a table keyed on course and booking time (or however you distinguish that) and keep a running total of how many people you have for that course in the table. ie:

COURSE TABLE:
course_table_key
course_id
course_description
course_start_date
course_end_date
course_max_spaces

ATTENDANCE TABLE:
attendance_table_key
course_table_key
current_course_attendance

Now it is just a matter of linking the two tables and you will always know how many poeple you have booked into a course.

P.S. There are many ways to do this. This is just one.

 

[LittleSmudge]

In the form that displays the courses you should add some code that runs On_Current like
Dim CountBooked As Integer
CountBooked = DCount("BookingId","tblBookings","CourseNo = " & CurrentCourseNumberOnForm)
' You could then display CountBooked to show people how many others have booked on the course

If CountBooked < Dlookup(&quot;MaxNoSpaces&quot;, &quot;tblCourses&quot;, &quot;CourseNo = &quot; & CurrentCourseNumberOnForm) Then
cmdBookMeOnCourseButton.Enabled = True
Else
cmdBookMeOnCourseButton.Enabled = False
End If


QED ?

G LS

 

[mike1975]

Thanks for the ideas guys! In an ideal world I would like the application to do the following,

1/ User adds a new course to courses table, that table contains number of spaces;

2/ On the bookings form have a section that would have number of booking spaces for that course -once it was full there would be no room where a course attendee could be added.

Again many thanks!

MIKE

 

[LittleSmudge]

Adapt the code I published earlier

If CountBooked < Dlookup(&quot;MaxNoSpaces&quot;, &quot;tblCourses&quot;, &quot;CourseNo = &quot; & CurrentCourseNumberOnForm) Then
subFormBooking.AddNew = True
Else
subFormBooking.AddNew = False
End If

That will then prevent the form accepting new entries.


G LS