MS Access help needed

[Fluffy]

Trying to set up a simple booking system
Table 1 - list of courses
Table 2 - Customers
Table 3 - Bookings Linking Customers and bookings

I've set up a query which counts how many of each course has been booked.

So far so good.

What I need is a (simple) way of checking that no course has more than 20 people booked on it.
IE when I fill in the booking form, it checks.

This is where I'm getting stuck.

Any help would be gratefully received.

Thanks

Carl

[pdV6]

select count (*) from Bookings where CourseID=x

[ymwoods]

yeah pretty much as above, I would have it run as a macro on clicking submit to firstly count the number on the course for which you are making the booking for, passing the booking number from (I assume) a dropdown box where you select the course by name and then running an if statement that bassicly says if the course is more than or equal to 20 booked then to not add to the database and display an error.

Alternativly you could have the macro run when you select the course on the drop down box and make it enable or disable the submitt button as required along with a message explaining why the button is not enabled.

Do you know how to code macros? (VB.NET)

if not, post back and will give you some more help.

[Fluffy]

Many thanks for both replies.
I'd managed to count the number of bookings, and addedd a field with an expression to calculate how many places left.

Bit stuck on the IF statement though.

My VB and Macro are both a bit rusty.

Just need it to flag up the fact that there are no places left when the user tries to enter it on the form.

Any help would be much appreciated.

[JayBM]

How does the customer select the course? If it's from a combobox you could add some VB code to the On Change event of that box.

i.e.

Private cmbCourse_OnChange()

If Dcount("BookingID","[Bookings]","[CourseName] = '" & me.cmbCourse.value & "'" < 20 Then
   'Use this to enable Submit button
   me.cmdSubmit.enabled = True
   Exit Sub
Else
   Msgbox "Unfortunately that course is fully booked, please select another course!"
   'Use this to disable Submit button
   me.cmdSubmit.enabled = False
End If

End Sub

Obviously you'd need to change the control/field names to the appropriate ones for your form/database.

HTH
Jay

Edited by JayBM on Wednesday 20th January 13:56

Edited by JayBM on Wednesday 20th January 13:57

[Fluffy]

Excellent!

Many thanks.