help regading date tracking across tables

[NRakhesh]

It is required of me to validate a date of birth entry into a table
named adminmast between the limits given as udate and ldate in another
table named as classdetails.
I will specify the whole thing :
User is supposed to enter a class and then a date of birth into a
form/ table, as soon as he enters the DOB the value entered should be
validated in the following manner.
If it is class I then the Ldate and Udate from the table class details
should be treated as the lower and upper limits for the DOB recordwise
similarly for all other classes these should take place recordwise

the structure of the database is
Adminmast, classdetails, category.

thanx in advance

 

[Jim Bunton]

You could write a function which checks the validity of a dob entry
something like

if column class is null return false
otherwise select case class.column value
case 1
if not within the limits for this class in the return false else return
true
case 2
etc

Then use this function in the validation definition for the dob column in
the classdetails table

Jim Bunton

 

[Steve Schapel]

NRakhesh,

If I understand you correctly, the skeleton code, on the After Update
event of the DOB control on the form, could be something like this...

If Me.DOB < DLookup("[LDate]","classdetails","[class]='" & Me.class
& "'") Then
MsgBox "Too old"
ElseIf Me.DOB > DLookup("[UDate]","classdetails","[class]='" &
Me.class & "'") Then
MsgBox "Too young"
End If

 

[John W. Vinson]

NRakhesh,

If I understand you correctly, the skeleton code, on the After Update
event of the DOB control on the form, could be something like this...

If Me.DOB < DLookup("[LDate]","classdetails","[class]='" & Me.class
& "'") Then
MsgBox "Too old"
ElseIf Me.DOB > DLookup("[UDate]","classdetails","[class]='" &
Me.class & "'") Then
MsgBox "Too young"
End If

Steve, shouldn't that be the BeforeUpdate event (which happens before the data
is written to disk and can be cancelled)? AfterUpdate fires after it's too
late: the data has already been written.

 

[Steve Schapel]

Hi John,

Thanks for picking that up. To me, it depends on the functionality
required. If it is a strict validation requirement, and you want to
undo the entry of non-compliant data, then I agree that Before Update
would be best. However, in cases such as NRakhesh's example, I would
often tend to use After Update. That allows the user greater
flexibility of how to deal with the invalidity. For example, it may
have been a typo in the date entered, so they can just easily correct
it. Or, as is sometimes the case in school situations, there are
exceptions made to eligibility, so they may want to sometimes allow
registrations out of the age criteria. Or, when the message box pops
up, they may realise that in fact it is the Class that they have
accidentally entered wrongly, so they can edit that instead.

 

[John W. Vinson]

Hi John,

Thanks for picking that up. To me, it depends on the functionality
required. If it is a strict validation requirement, and you want to
undo the entry of non-compliant data, then I agree that Before Update
would be best. However, in cases such as NRakhesh's example, I would
often tend to use After Update. That allows the user greater
flexibility of how to deal with the invalidity. For example, it may
have been a typo in the date entered, so they can just easily correct
it. Or, as is sometimes the case in school situations, there are
exceptions made to eligibility, so they may want to sometimes allow
registrations out of the age criteria. Or, when the message box pops
up, they may realise that in fact it is the Class that they have
accidentally entered wrongly, so they can edit that instead.

ah... gotcha! Good points. Even if you use the BeforeUpdate you should leave
the option "yes, that's really what I meant, do it anyway".

 

[Steve Schapel]

Thanks, John. Yes, now that I think about it, that could be a good
approach. Something like this, on the Before Update event of DOB:

Dim strReason As String
If Me.DOB < DLookup("[LDate]","classdetails","[class]='" & Me.class
& "'") Then
strReason = "old"
ElseIf Me.DOB > DLookup("[UDate]","classdetails","[class]='" &
Me.class & "'") Then
strReason = "young"
End If
If Len(strReason) Then
If MsgBox("This person seems to be too " & strReason & "." &
vbCrLf & _
"Re-enter DOB?", vbExclamation + vbYesNo) = vbYes Then
Cancel = True
End If
End If

 

[NRakhesh]

Thanks for all of you out there for a prompt discussion!!
After trying out all your functional process I was left off with two
more problems:
1. The system shows some cancel error which is not existent at all.
2. My question that could I restrict entry into the table at table
view for the date of birth field based on other values between two
ranges still can't be resolved.
I will present the whole issue as a scenario:
Scenario 1: A system user enters the data through a form where he gets
the registration details of a student to be admitted and needs to be
validated before it is stored in the table. Here DOB field is to be
restricted between the age limits given in another table for each
class.

Scenario 2: A DB admin needs to edit the records in table view and
correct the table entry for DOB and he too has to be restricted within
the same limits for each class

may it works on

 

[Steve Schapel]

NRakhesh,

For the second question, I think the answer is No. In theory, you could
construct a Validation Rule in the table. But in practice, this would
be too complex to be a workable approach. If you are going to allow DBA
direct access to the tables, then you accept that this carries a risk,
and you assume that they will be super-competent and super-careful.

As I understand you for Scenario 1, this is exactly the situation we
have suggested the code to manage. Does it not achieve this purpose,
according to our suggested approach?

Can you please explain further what you mean by "the system shows some
cancel error which is not existent at all"?

 

[NRakhesh]

I had a work around with this same using excel software
there we handle each class registration details in a new sheet
and in the format validation we gave for entries in the DOB to look
for two cell values set as date format with the condition cell value
between this and that and if anything goes wrong then it formats the
cell with a red colour.

The same i am intending to achieve in access' s table view mode for
any data entry directly at the table.

For the error in my prev. post.
the error first says " Run time Error 2001 ' You cancelled the
previous operation. and then
when debugged fro error code I found nothing wrong with lang. ref. but
still the error keeps on appearing .
thanx in advance

 

[John W. Vinson]

I had a work around with this same using excel software
there we handle each class registration details in a new sheet
and in the format validation we gave for entries in the DOB to look
for two cell values set as date format with the condition cell value
between this and that and if anything goes wrong then it formats the
cell with a red colour.

The same i am intending to achieve in access' s table view mode for
any data entry directly at the table.

Tables are for DATA STORAGE.

They are not intended, and are not appropriate, for data entry or editing.

They look like spreadsheets *but they are not spreadsheets*!

For one thing, Tables lack most of the functionality - events, in particular -
that Forms provide.

Your users should *never see a table* - just a Form. You can do the checking
that you describe in a form, as suggested upthread; and that should be enough,
because your users should interact with the data only via the Form.