Validating Data

[joanne.hon]

I have created a database which manages registrations for training
courses. There is a limit of 20 registrations per course. I would
like a 'pop up' message to appear if the user tries to register a
person for a course which already has 20 people registered.

How do I do this? Is this when Data Validation is used?

The only place in database where I calculate the number registered is
in a query and then an associated querysubform.

 

[Allen Browne]

Use DCount() to count the number of people alredy registered.

This example will prevent the user saving another record in your form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim lngCount As Long
Dim strWhere As String
If Me.CourseID = Me.CourseID.OldValue Then
'do nothing
Else
strWhere = "CourseID = " & Nz([CourseID],0)
lngCount = Nz(DCount("*", "tblRegistration", strWhere),0)
If lngCount > Me.[MaxAllowed] Then
Cancel = True
MsgBox "You already have " & lngCount & " people.", _
vbExclamation, "Too many"
'Me.Undo
End If
End If
End Sub

 

[joanne.hon]

Where do I insert the example? Is that a macro?

Use DCount() to count the number of people alredy registered.

This example will prevent the user saving another record in your form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim lngCount As Long
Dim strWhere As String
If Me.CourseID = Me.CourseID.OldValue Then
'do nothing
Else
strWhere = "CourseID = " & Nz([CourseID],0)
lngCount = Nz(DCount("*", "tblRegistration", strWhere),0)
If lngCount > Me.[MaxAllowed] Then
Cancel = True
MsgBox "You already have " & lngCount & " people.", _
vbExclamation, "Too many"
'Me.Undo
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have created a database which manages registrations for training
courses. There is a limit of 20 registrations per course. I would
like a 'pop up' message to appear if the user tries to register a
person for a course which already has 20 people registered.

How do I do this? Is this when Data Validation is used?

The only place in database where I calculate the number registered is
in a query and then an associated querysubform.

 

[Allen Browne]

1. In form design view, open the Properties box.
Make sure its title reads Form (so you are looking at the properties of the
form, not of a text box.)

2. On the Data tab, set the BeforeUpdate property to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.

3. Set up the code so it looks like the example.

You will need to change names like CourseID, MaxAllowed, and tblRegistration
so they match your table and field names.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Where do I insert the example? Is that a macro?

Use DCount() to count the number of people alredy registered.

This example will prevent the user saving another record in your form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim lngCount As Long
Dim strWhere As String
If Me.CourseID = Me.CourseID.OldValue Then
'do nothing
Else
strWhere = "CourseID = " & Nz([CourseID],0)
lngCount = Nz(DCount("*", "tblRegistration", strWhere),0)
If lngCount > Me.[MaxAllowed] Then
Cancel = True
MsgBox "You already have " & lngCount & " people.", _
vbExclamation, "Too many"
'Me.Undo
End If
End If
End Sub

I have created a database which manages registrations for training
courses. There is a limit of 20 registrations per course. I would
like a 'pop up' message to appear if the user tries to register a
person for a course which already has 20 people registered.

How do I do this? Is this when Data Validation is used?

The only place in database where I calculate the number registered is
in a query and then an associated querysubform.

 

[onedaywhen]

I have created a database which manages registrations for training
courses. There is a limit of 20 registrations per course. I would
like a 'pop up' message to appear if the user tries to register a
person for a course which already has 20 people registered.

How do I do this? Is this when DataValidationis used?

Approach 1: use referential integrity:

CREATE TABLE Students (
student_ID INTEGER NOT NULL UNIQUE
)
;
CREATE TABLE Classes (
class_ID INTEGER NOT NULL UNIQUE,
seating_capacity INTEGER NOT NULL,
CHECK (seating_capacity > 0),
UNIQUE (seating_capacity, class_ID)
)
;
CREATE TABLE Enrolment (
class_ID INTEGER NOT NULL,
seating_capacity INTEGER NOT NULL,
FOREIGN KEY (seating_capacity, class_ID)
REFERENCES Classes (seating_capacity, class_ID),
student_ID INTEGER NOT NULL
REFERENCES Students (student_ID),
UNIQUE (class_ID, student_ID),
seat_number INTEGER NOT NULL,
UNIQUE (class_ID, seat_number),
CONSTRAINT row_level_CHECK_constraint
CHECK (seat_number <= seating_capacity)
)
;

The pros include ease of implementation in Access because the row-level
CHECK constraint can be replaced by a record-level Validation Rule. The
cons include the otherwise-redundant repeating seating_capacity on each
row and the burdensome need to maintain a sequence for seat_number...

Approach 2: 'hide' the need for both the repeating seating_capacity and
the sequence of seat numbers in a table-level CHECK constraint:

CREATE TABLE Enrolment (
class_ID INTEGER NOT NULL
REFERENCES Classes (class_ID),
student_ID INTEGER NOT NULL
REFERENCES Students (student_ID),
UNIQUE (class_ID, student_ID),
CONSTRAINT table_level_CHECK_constraint
CHECK ((
SELECT C1.seating_capacity
FROM Classes AS C1
WHERE Enrolment.class_ID = C1.class_ID
) >= (
SELECT COUNT(*)
FROM Enrolment AS E1
WHERE Enrolment.class_ID = E1.class_ID)
)
)
;

http://groups.google.com/group/micr...c10b79f28f/1d4430dc8b50ba8f?#1d4430dc8b50ba8f

Jamie.

--