Progamatically limit the number of certain records

[HLCruz via AccessMonster.com]

I manage a camper registration database and we recently have come across a
couple situations where it would be great if we could limit how many campers
are registered.

For example, we allow 10 day campers and 100 traditional campers. When a
camper is registered they are given a code (DayCamp10 for day campers, Camp10
for traditional campers) and having that code is what the form uses to
display the appropriate camp registration form for more detailed data.

My thoughts where if I could control the number of DayCamp10 and Camp10
records that are created in my tblCodes to 10 and 100 respectively, I could
essentially then controll the number of campers that are registered.

I am somewhat experienced with VBA but haven't done anything like this. Does
anyone have suggestions on code I could use to do this?

Thank you

 

[Jack Leach]

My best guess would be to use the BeforeUpdate event of the form used for
data entry to these two tables... run a DCount() function to get the number
of existing records, put them in a conditional statement to see if they are
at (or over) the Max, and Cancel the Update if they are at or over
(presumably with a message box to let the user know).

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount(.....) >= 10 Then
MsgBox "Too many campers!"
Cancel = True
Exit Sub
End If
End Sub


hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[John W. Vinson]

My best guess would be to use the BeforeUpdate event of the form

Good code, Jack... but I'd suggest using the BeforeInsert event (which fires
when the user types the first keystroke into a new record) rather than
BeforeUpdate (which will object only after they've entered all the data).

 

[HLCruz via AccessMonster.com]

Thanks to both of you for the advice, I can't wait to try it out tomorrow.

 

[Jack Leach]

Good code, Jack... but I'd suggest using the BeforeInsert event

Funny you say that... I had first typed BeforeInsert, then changed it to
BeforeUpdate, because I wasn't sure if BeforeInsert ran as data began to be
entered, or at the start of the save operation (after BeforeUpdate).

Thanks for the clarification (you can tell I don't use BeforeInsert all that
often?)

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[vanderghast]

You can also do it at the design table level... well, using the Immediate
Window (not the table design User Interface) :

Assuming the table Campers, fields (theDay, camperType, camperID )

Wanted: no more that 10 camperType = 'A', for a given day.

Solution: Close the table Campers.
In the Immediate window, execute (it is a single long line) :


CurrentProject.Connection.Execute "ALTER TABLE campers ADD CONSTRAINT
noMoreThan10TypeACampersPerDay CHECK( 10 >= (SELECT COUNT(*) FROM campers AS
A WHERE campers.theDay = A.theDay AND A.camperType = 'A' )) "


You will then be unable to add more than 10 records, for a given day,
with camperType = "A", anywhere in the database, code, forms, without firing
an error, since the CHECK constraint will be invalid, like any other kind of
constraint is following the table. For a form, you can use the form_error
event handler to catch that kind of error.



Few notes:

- to drop the constraint, use:
CurrentProject.Connection.Execute "ALTER TABLE campers DROP CONSTRAINT
noMoreThan10TypeAPerDay"

- the constraint name must be UNIQUE for all the database (not by table,
hint: can add the table name somewhere in the constraint name).

- the User interface does NOT display that kind of constraint. You have to
use CurrentProject.Connection.Execute.

- That constraint does not work with MS SQL Server, only works in Jet. MS
SQL Server CHECK constraints do not allow to reference another table (or
itself, as we did here).

- That constraint fires on INSERT as well as on UPDATE.

- A CHECK constraint do not allow using VBA functions within its ( ) .

- The TEXT of the error message is not 'customizerable', I mean, you
cannot add a desired TEXT for the error message. The text of the error will
refer to the constraint name, so make the constraint name 'friendly'.



Vanderghast, Access MVP