Restricted Number of Records

[Andrew Johnson]

I am currently writing a database for a swim scheme. I've achieved every
objective except one, which is constantly eluding me. Any help possible in
doing so would be very gratefully appreciated!

Each pupil is entered into TabPupils. Each lesson that is taught has an
entry in TabLessons.

Field [TabPupils.CurrentLesson] is related to [TabLessons.LessonID].

I need to restrict the number of pupils that can be allocated to each to a
variable number, specified in [TabLessons.Size].

How can I do this? I've looked at various validation rules when allocating
the pupil to a lesson, but it simply fails to work.

Again, any help most appreicated!

Andy

 

[Brendan Reynolds]

You'll need some code in the BeforeInsert event procedure of a form to do
this. Something like the following ...

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim lngMaxNum As Long
Dim lngNumRecs As Long
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT TestNumber FROM tblTest2")
lngMaxNum = rst("TestNumber")
rst.Close
Set rst = db.OpenRecordset("SELECT Count(*) AS NumRecs FROM tblTest1")
lngNumRecs = rst("NumRecs")
rst.Close
If lngNumRecs >= lngMaxNum Then
MsgBox "The maximum number of records has been added."
Cancel = True
End If

End Sub

 

[Andrew Johnson]

Thanks a lot...I can't seem to get it working...it seems to be counting
columns (?)...I'm guessing my explanation wasn't very good...or I can't
implement code properly!

I have a number of Pupils (tabPupils, which unique ID PupilID) who are
assigned to a lesson (LessonID in tabLessons). I want to only be able to
allocate X number of pupils to each lessons (where X is determined by field
"Size" in tabLessons). If that isn't possible...I'd like to be able to
restrict the number of pupils to a set number (eg 12).

Thanks again!!!

Andy

You'll need some code in the BeforeInsert event procedure of a form to do
this. Something like the following ...

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim lngMaxNum As Long
Dim lngNumRecs As Long
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT TestNumber FROM tblTest2")
lngMaxNum = rst("TestNumber")
rst.Close
Set rst = db.OpenRecordset("SELECT Count(*) AS NumRecs FROM tblTest1")
lngNumRecs = rst("NumRecs")
rst.Close
If lngNumRecs >= lngMaxNum Then
MsgBox "The maximum number of records has been added."
Cancel = True
End If

End Sub

--
Brendan Reynolds (MVP)

I am currently writing a database for a swim scheme. I've achieved every
objective except one, which is constantly eluding me. Any help possible in
doing so would be very gratefully appreciated!

Each pupil is entered into TabPupils. Each lesson that is taught has an
entry in TabLessons.

Field [TabPupils.CurrentLesson] is related to [TabLessons.LessonID].

I need to restrict the number of pupils that can be allocated to each to a
variable number, specified in [TabLessons.Size].

How can I do this? I've looked at various validation rules when allocating
the pupil to a lesson, but it simply fails to work.

Again, any help most appreicated!

Andy

 

[Brendan Reynolds]

No, it's not counting columns, it's counting records.

First, it looks up the maximum number of records. In my example, the table
that contained this information contained just one record, but it sounds
like your table contains multiple records. So you'll need to add to the
first SQL statement a WHERE clause that specifies which record to get the
maximum number of records from.

The second SQL statement gets the number of records already in the table
that is to be restricted - again, you may need to add a WHERE clause here,
to count only records with the specified LessonID, or whatever - and sets
the Cancel argument of the BeforeInsert event procedure to True if the
number equals or exceeds the maximum. (In theory, it should never exceed the
maximum, but it's safer that way. If I used = instead of >=, and the number
already exceeded the maximum (perhaps someone added records directly to a
table, by-passing the form) the test would never return True.

To use a fixed number, you'd replace the first part of the code, that looks
up the maximum number, with something like 'lngMaxNum = 12'. The second part
of the code would remain the same.

There really isn't any reliable way to do this with a JET database without
digging into VBA. In a server database like SQL Server, you could do it with
a trigger, which does have the advantage of being enforced at the level of
the database (can't be bypassed by editing the table directly) but writing
triggers in T-SQL isn't any easier (and can be quite a bit harder) than
writing VBA code.

--
Brendan Reynolds (MVP)

Thanks a lot...I can't seem to get it working...it seems to be counting
columns (?)...I'm guessing my explanation wasn't very good...or I can't
implement code properly!

I have a number of Pupils (tabPupils, which unique ID PupilID) who are
assigned to a lesson (LessonID in tabLessons). I want to only be able to
allocate X number of pupils to each lessons (where X is determined by field
"Size" in tabLessons). If that isn't possible...I'd like to be able to
restrict the number of pupils to a set number (eg 12).

Thanks again!!!

Andy

You'll need some code in the BeforeInsert event procedure of a form to do
this. Something like the following ...

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim lngMaxNum As Long
Dim lngNumRecs As Long
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT TestNumber FROM tblTest2")
lngMaxNum = rst("TestNumber")
rst.Close
Set rst = db.OpenRecordset("SELECT Count(*) AS NumRecs FROM tblTest1")
lngNumRecs = rst("NumRecs")
rst.Close
If lngNumRecs >= lngMaxNum Then
MsgBox "The maximum number of records has been added."
Cancel = True
End If

End Sub

possible

in

doing so would be very gratefully appreciated!

Each pupil is entered into TabPupils. Each lesson that is taught has an
entry in TabLessons.

Field [TabPupils.CurrentLesson] is related to [TabLessons.LessonID].

I need to restrict the number of pupils that can be allocated to each

to

a

variable number, specified in [TabLessons.Size].

How can I do this? I've looked at various validation rules when allocating
the pupil to a lesson, but it simply fails to work.

Again, any help most appreicated!

Andy

 

[Andrew Johnson]

Ok cheers...sorry for the confusion...I'll have a play!

Thanks again

Andy

No, it's not counting columns, it's counting records.

First, it looks up the maximum number of records. In my example, the table
that contained this information contained just one record, but it sounds
like your table contains multiple records. So you'll need to add to the
first SQL statement a WHERE clause that specifies which record to get the
maximum number of records from.

The second SQL statement gets the number of records already in the table
that is to be restricted - again, you may need to add a WHERE clause here,
to count only records with the specified LessonID, or whatever - and sets
the Cancel argument of the BeforeInsert event procedure to True if the
number equals or exceeds the maximum. (In theory, it should never exceed the
maximum, but it's safer that way. If I used = instead of >=, and the number
already exceeded the maximum (perhaps someone added records directly to a
table, by-passing the form) the test would never return True.

To use a fixed number, you'd replace the first part of the code, that looks
up the maximum number, with something like 'lngMaxNum = 12'. The second part
of the code would remain the same.

There really isn't any reliable way to do this with a JET database without
digging into VBA. In a server database like SQL Server, you could do it with
a trigger, which does have the advantage of being enforced at the level of
the database (can't be bypassed by editing the table directly) but writing
triggers in T-SQL isn't any easier (and can be quite a bit harder) than
writing VBA code.

--
Brendan Reynolds (MVP)

Thanks a lot...I can't seem to get it working...it seems to be counting
columns (?)...I'm guessing my explanation wasn't very good...or I can't
implement code properly!

I have a number of Pupils (tabPupils, which unique ID PupilID) who are
assigned to a lesson (LessonID in tabLessons). I want to only be able to
allocate X number of pupils to each lessons (where X is determined by field
"Size" in tabLessons). If that isn't possible...I'd like to be able to
restrict the number of pupils to a set number (eg 12).

Thanks again!!!

Andy

You'll need some code in the BeforeInsert event procedure of a form to do
this. Something like the following ...

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim lngMaxNum As Long
Dim lngNumRecs As Long
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT TestNumber FROM tblTest2")
lngMaxNum = rst("TestNumber")
rst.Close
Set rst = db.OpenRecordset("SELECT Count(*) AS NumRecs FROM tblTest1")
lngNumRecs = rst("NumRecs")
rst.Close
If lngNumRecs >= lngMaxNum Then
MsgBox "The maximum number of records has been added."
Cancel = True
End If

End Sub

--
Brendan Reynolds (MVP)

I am currently writing a database for a swim scheme. I've achieved every
objective except one, which is constantly eluding me. Any help

possible

in
doing so would be very gratefully appreciated!

Each pupil is entered into TabPupils. Each lesson that is taught has an
entry in TabLessons.

Field [TabPupils.CurrentLesson] is related to [TabLessons.LessonID].

I need to restrict the number of pupils that can be allocated to

each

to

a
variable number, specified in [TabLessons.Size].

How can I do this? I've looked at various validation rules when allocating
the pupil to a lesson, but it simply fails to work.

Again, any help most appreicated!

Andy

 

[Erik Oosterop]

Try calling this code before assigning a new pupil to a class (returns false
if there is no space). It uses your tables and fields (plus a PupilID field
in your TabPupils - the code could (but probably should not) do without
though). Hope this helps.

Function CheckLessonSpace(lessonId As Long) As Boolean
Dim rst As DAO.Recordset
Dim strSQL As String
Dim ClassSpace As Long

ClassSpace = 0
CheckLessonSpace = False
strSQL = "SELECT * FROM TabLessons WHERE LessonID=" & lessonId
Set rst = CurrentDb.OpenRecordset(strSQL)

If Not (rst.BOF And rst.EOF) Then
rst.MoveFirst
ClassSpace = rst("Size")
End If
rst.Close
Set rst = Nothing

strSQL = "SELECT Count(PupilID) AS CountOfPupilID FROM TabPupils WHERE
CurrentLesson=" & lessonId
Set rst = CurrentDb.OpenRecordset(strSQL)

If Not (rst.BOF And rst.EOF) Then
rst.MoveFirst
ClassSpace = ClassSpace - rst("CountOfPupilID")
End If
rst.Close
Set rst = Nothing

If ClassSpace > 0 Then CheckLessonSpace = True
End Function

 

[Erik Oosterop]

Alternatively/in addition, the following query may help. It will give you
the number of spaces available for each lesson (without VBA).

SELECT TabLessons.LessonID, TabLessons.Size, (Select Count(PupilID) as
MyCount FROM TabPupils WHERE TabPupils.CurrentLesson=TabLessons.LessonID) as
Enrollment, Size-Enrollment as Openings
FROM TabLessons;