Setting up an induction booking database

[OHSGoddess]

I am currently trying to set up a database that will allow me to book
contractors into or induction. We run an induction every Monday will
20 spots available. I think I have set up all the tables I need and
the relationships. I have also created a booking form. I am just
wondering how I can make it so that you can only book 20 people into
each induction?



The tables I have set up are



tblContract_Company

Company Name

Company Contact

Company Phone

Email



tblContractor_Details

Last Name

First Name

SGS Number

Company Name

Email

Phone Number

Induction Date



tblInduction_Date

Induction Date

Available Spots



Are you able to give me any ideas how to make this work. I am sure it
is probably simple but it has really got me stumped at the moment. I
am very new to access and am sturggling with this. Can anyone help???

 

[Guest]

Assuming each contractor can be induced only once firstly add an autonumber
ContractorID column to tblContractor_Details as its primary key; names can be
duplicated and therefore are unsuitable as keys. Then in the BeforeUpdate
event procedure of the Induction Date control on your form based on the
tblContractor_Details table put the following code:

Const conAVAILABLESPOTS = 20
Const conMESSAGE = "This induction session is fully booked."
Dim strCtriteria As String
Dim strDate As String

strDate = "#" & Format(Me.[Induction Date],"mm/dd/yyyy") & "#"

strCriteria = "[Induction Date] = " & strDate

If DCount("*", "[tblContractor_Details]", strCriteria) >= conAVAILABLESPOTS
Then
MsgBox conMESSAGE, vbExclamation, "Warning"
Cancel = True
End If

You might find that some single lines in the above code have been split over
two lines in your newsreader, so beware of this.

If it is possible for a contractor to go attend more than one induction then
you'd need to introduce an additional table to model the many-to-many
relationship between tblContractor_Details and tblInduction_Date.

Ken Sheridan
Stafford, England

 

[OHSGoddess]

Assuming each contractor can be induced only once firstly add an autonumber
ContractorID column to tblContractor_Details as its primary key; names can be
duplicated and therefore are unsuitable as keys. Then in the BeforeUpdate
event procedure of the Induction Date control on your form based on the
tblContractor_Details table put the following code:

Const conAVAILABLESPOTS = 20
Const conMESSAGE = "This induction session is fully booked."
Dim strCtriteria As String
Dim strDate As String

strDate = "#" & Format(Me.[Induction Date],"mm/dd/yyyy") & "#"

strCriteria = "[Induction Date] = " & strDate

If DCount("*", "[tblContractor_Details]", strCriteria) >= conAVAILABLESPOTS
Then
MsgBox conMESSAGE, vbExclamation, "Warning"
Cancel = True
End If

You might find that some single lines in the above code have been split over
two lines in your newsreader, so beware of this.

If it is possible for a contractor to go attend more than one induction then
you'd need to introduce an additional table to model the many-to-many
relationship between tblContractor_Details and tblInduction_Date.

Ken Sheridan
Stafford, England

I am currently trying to set up a database that will allow me to book
contractors into or induction. We run an induction every Monday will
20 spots available. I think I have set up all the tables I need and
the relationships. I have also created a booking form. I am just
wondering how I can make it so that you can only book 20 people into
each induction?

The tables I have set up are

Company Name

Company Contact

Company Phone


Last Name

First Name

SGS Number

Company Name

Phone Number

Induction Date

Induction Date

Available Spots

Are you able to give me any ideas how to make this work. I am sure it
is probably simple but it has really got me stumped at the moment. I
am very new to access and am sturggling with this. Can anyone help???- Hide quoted text -

- Show quoted text -

Thanks for the reply. I think this is all a bit too hard for me. I
might have to go do another course.