Validation Rule

[Mirkin Mirksta]

Hi,

I have 3 tables as shown below.

Clan Events run from July - September each year.

How can i set a validation rule so that an event cant be registered until
the next year july-sept peroid if the clan is signed up after the start of
july.

i.e if the clan is registered after the start of the event peroid it must
wait until the next year event peroid before it can participate in an event

I.E They have to wait until the next year.

It would have to be a table validation rule.. but im not sure if this kind
of rule is possible..

i.e

TABLE 1: USER

USER_ID, USER_NAME, CLAN_ID

TABLE 2: CLAN
CLAN_ID, CLAN_NAME, CLAN_SIGNUPDATE

TABLE 3: EVENT

EVENT_ID, CLAN_ID, EVENT_DATE

 

[Jeff Boyce]

Why does it have to be a table validation rule? Access tables offer very
little opportunity for events and triggers, where Access forms are design
specifically to display and validate, via events.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[RoyVidar]

Hi,

I have 3 tables as shown below.

Clan Events run from July - September each year.

How can i set a validation rule so that an event cant be registered
until the next year july-sept peroid if the clan is signed up after
the start of july.

i.e if the clan is registered after the start of the event peroid it
must wait until the next year event peroid before it can participate
in an event

I.E They have to wait until the next year.

It would have to be a table validation rule.. but im not sure if this
kind of rule is possible..

i.e

TABLE 1: USER

USER_ID, USER_NAME, CLAN_ID

TABLE 2: CLAN
CLAN_ID, CLAN_NAME, CLAN_SIGNUPDATE

TABLE 3: EVENT

EVENT_ID, CLAN_ID, EVENT_DATE

As far as I've understood, Access doesn't offer table level validation,
it offers field level, and record level validation. For this kind of
validation, most will probably use form events in stead of engine
level validation.

However, the Jet engine, which is the database engine used by Access,
does support such, though it is then called "check constraints". The
downside, is that you can't maintain it through the Access interface,
but the Access interface will give you validation errors, when such
occurs.

If your table and field names are accurate, you may try something like
the following (the commented lines, can be used to remove the check
constraint again - or experiement with different syntax) - this will
simply ensure the event date is later than the signup date.

Sub ChekcConstraint()

Dim SQL As String
Dim cn As ADODB.Connection

Set cn = CurrentProject.Connection

'SQL = "ALTER TABLE event DROP CONSTRAINT ch_later_than_july"
'cn.Execute SQL, , adCmdText + adExecuteNoRecords

SQL = "ALTER TABLE event " & _
"ADD CONSTRAINT ch_later_than_july " & _
"CHECK (event_date > " & _
"(SELECT clan_signupdate " & _
"FROM clan c " & _
"WHERE c.clan_id = event.clan_id))"

cn.Execute SQL, , adCmdText + adExecuteNoRecords

End Sub