Table Field Validation Rules triggering in Forms

[ions]

Dear Access Expert.

I have noticed that

If Me.dirty then Me.Dirty = False

doesn't trigger Table field validation Rules.

Why not?

How can I trigger Table Field validation Rules using VBA when the user clicks the Save button?

Thank you.

 

[missinglinq]

What type of Validation rule are you talking about?

Table level validation is enforced when data is entered into a form control that is bound to field in the table, not when the record is saved.

If you want to insure, at the table level, that a field actually has data entered, you have to set the Required Property to Yes at the table level, in which case saving the record will pop a warning from Access.

Many experienced developers, rather than doing validation at the table level, do it at the form level, either in the control's BeforeUpdate event or the form's BeforeUpdate event, depending on the exact type of validation.

Linq ;0)>

 

[ions]

Missingling,

Thank you for your response.

I figured it out. I didn't realize that broken validation rules throw errors.

My original code was this.

Private Sub cmdSave_Click()
    
On Error GoTo Error_Handler
    
    'Flag to indicate not to ask to Save
    blnSave = True
    
    If Me.Dirty Then Me.Dirty = False
    
    'Reset the Flag
    blnSave = False

Exit_Sub:
    Exit Sub

Error_Handler:
               
         'Save failed because the User decided to Cancel or Table Level 
        'The Record is Still Dirty and focus remains on the record.
End Sub

The working code is the following.

Private Sub cmdSave_Click()
    
On Error GoTo Error_Handler
    
    'Flag to indicate not to ask to Save
    blnSave = True
    
    If Me.Dirty Then Me.Dirty = False
    
    'Reset the Flag
    blnSave = False

Exit_Sub:
    Exit Sub

Error_Handler:
               
    If Err.Number = 2101 Then
        'Save failed because the User decided to Cancel or Table Level Validations were not met.
        'The Record is Still Dirty and focus remains on the record.
    Else

        MsgBox Err.Description, vbExclamation
    End If
      
End Sub

 

[ions]

Missingling,

Many experienced developers, rather than doing validation at the table level, do it at the form level, either in the control's BeforeUpdate event or the form's BeforeUpdate event, depending on the exact type of validation.

Validation Rules have caused me much confusion because there are so many places one can put them in MS Access. I did research on the the topic and I came up with two rules.

1) Table Level Validations comparing field vs field will go on the Form's Before Update event since you can only really place one Validation rule at the Table level without complicating the Validation Text.

2) Regular (none field vs field ) Field validations will be put at the table field level because they are at the table level, and propagate to every query and form that uses the field.


Thanks

 

[missinglinq]

1) Table Level Validations comparing field vs field will go on the Form's Before Update event since you can only really place one Validation rule at the Table level without complicating the Validation Text.

Validations done in the Form_BeforeUpdate event are Form level validations of course, not Table level validations. And field vs field validations do, indeed, have to be done at the form level.

2) Regular (none field vs field ) Field validations will be put at the table field level because they are at the table level, and propagate to every query and form that uses the field.

While it is true that table level validation propagates to every query and form that uses the field, in practice data for a given field is normally only entered thru a single location.

Having all validation done in one location, i.e. the Form_BeforeUpdate sub, as opposed to having some validation done at the table level and some done at the form level, is considered preferable by many from an organizational standpoint.

But to each his or her own! Glas you got it working for you.

 

[ions]

THank you for your response Missling. Yes I think Validation Rules are very individual that's why they are so confusing.

While it is true that table level validation propagates to every query and form that uses the field, in practice data for a given field is normally only entered thru a single location.

How about when I do update / append Queries? The table field validation rules will help catch errors. I think this on it's own is worth putting the table validation rules in place.

 

[whdyck]

Hello,

Can you point me to any examples of how I might implement this type of form-level validation in a BeforeUpdate event of a form or control?

Thanks.

Wayne

What type of Validation rule are you talking about?

Many experienced developers, rather than doing validation at the table level, do it at the form level, either in the control's BeforeUpdate event or the form's BeforeUpdate event, depending on the exact type of validation.

Linq ;0)>

 

[vjimmers]

<<Many experienced developers, rather than doing validation at the table level, do it at the form level, either in the control's BeforeUpdate event or the form's BeforeUpdate event, depending on the exact type of validation.>>

More experienced developers put validation at the lowest level possible constraints and/or triggers for Server based data (MS SQL Server)
record validation and Before Change data macros for Access web apps.
That being said I'm new to access web apps but here are a few ideas.

Table ConstraintName IsUnique ConstraintType
-----------------------------------------------------------------------------------------------------------------------------------
Comments IX_01 FALSE Calculated Field [ContactID_calc] & [CommentDate] & [CommentSubject] & [CommentType]
Comments IX_02 FALSE Calculated Field [ContactID_calc] & [CommentDate] & [CommentSubject] & [CommentType]
Comments Record Validation FALSE Record Validation Not (IsNull([ContactID_calc]))
Comments Before Change FALSE Before Change "SetField : ContactID_calc = ContactID

LookupAFieldIn: Comments
WHERE: [Comments].[IX_01]=[t1].[IX_01]
Alias: t1
Action: Raise Error"
Contacts DisplayName FALSE Calculated Field IIf(IsNull([LastName]),"",[LastName] & "-") & IIf(IsNull([FirstName]),"","," & [FirstName]) & IIf(IsNull([CompanyName]),"","-" & [CompanyName])

 

[Pat Hartman]

I like to build applications that are as flexible as possible and that means that my validation code has to go in the form rather than in the table. Several apps that I developed are sold with either SQL Server or ACE BE's. The customer's choice and so unless I want to duplicate my effort, anything that declarative RI can't handle, has to go into forms.

You may see postings with validation code in many form and control events but the two "correct" places are the individual control's BeforeUpdate event if the validation involves only the single field and the form's BeforeUpdate event which you can think of as "the buck stops here". This is the last event fired before a record is actually saved. So, this is the event where you would put any multi-field validation or any not-null rules that can't be implemented by RI.

 

[missinglinq]

Let's hear an "Amen!" for Brother Pat!

Linq ;0)>

 

[vjimmers]

I slapped some code up in one of my last posts. Here is a fix for the table trigger / Before Change macro. I am expericed at SQL Server but this is my first
Access Web app so I'm playing around seeing how and what can be done.

/* implement muti-column unique record constraint*/
If [IsInsert] Or Updated("Table") Or Updated("ConstraintType") Or Updated("ConstraintName") then
LookupARecord TableConstraint
WHERE [TableConstraint].

= [t1].

AND [TableConstraint].[ConstraintType] = [t1].[ConstraintType]
AND [TableConstraint].[ConstraintName] = [t1].[ConstraintName]
Alis t1
RaiserError
End If

Here is another method I'm playing with. I'm using secondary fields to double store lookupIds (I know! Bad schema design but until I find a better implementation method ... ) so I can build muti-column unique record constraints as Before Change macro targeting calculated Column(s) constructed as as string of contatinated fields. No isnull statements requred as evidently access handles that for us.Another reason I like doing this is because of the ongoing MS Access database corruption problems (all versions since 1.0) in this case SharePoint list related I've lost my data update macros more than once. I'm putting all the code into a RecordConstraints table now so it doesn't get lost. Microsoft hasn't figured out how to keep the corruption from occuring during development yet so even with the MS Access web apps I'm finding it better to have development (not published) and test versions (published) and lot's of un-published backups as usuall. Lots of detached backups!

/* Keep copy of the column [TableId] synchronized so it can be used in calculated columns. IX_01 is a calculated Column constructed as as string of contatinated fields */
If IsNull(

) Or Updated("TableId") then
SET

= [TableId]
End If
/* Keep copy of the column [ConstraintTypeId] synchronized so it can be used in calculated columns.*/
If IsNull([ConstraintType]) Or Updated("ConstraintType")
SET [ConstraintType] = [ConstraintTypeId]
End If
/* implement muti-column unique record constraints*/
If [IsInsert] Or Updated("IX_01") then
LookupARecord TableConstraint
WHERE [TableConstraint].[IX_01]=[t1].[IX_01]
Alis t1
RaiserError
End If

 

[vjimmers]

Re: Table Field Validation Rules triggering in Forms

<<So, this is the event where you would put any multi-field validation or any not-null rules that can't be implemented by RI.>>
"that can't be implemented by RI" is the key phrase.

If people could die or lose billions of dollars from any bad data then one
would have to omit almost every possibility of the data becoming corrupted.
To this end "it has to be" at the lowest level possible! In Access web tables
that would be with required fields, field validation, record validation and
table triggers/macros (Before Change macro). Unfortunatly since we can pretty much count on not everyone working with the data knowing what they are doing it is likely that human interaction will corrupt it or at least try to corrupt the data at some pount in time. To that avail our last effort at controlling the data is though audit log tables again written at the lowest level possible.

 

[Pat Hartman]

Up until A2010, Access did not support triggers. Therefore, there was no way to implement anything outside of declarative RI at the table level and extremely simplistic validation rules. As it is now, they have chosen, non-standard methods - ie MACROS. I will not use Access Web apps as they currently exist, nor would I recommend them to anyone else. They have elected to make a propritary, non-standard method when they had the opportunity to implement the equivalent of T-SQL which would have been portable and given developers a real option to use ACE now and an easy path to upsize later.

The "can't be implemented by RI" statement was a reference to the occassional situation where data is required in some cases but optional in others so you can't simply say the field is required at the table level and let it go at that.

 

[vjimmers]

You have a point about the SQL versions. IDD10Ts! I don't really care what my data is displayed with as long as all the business logic is in SQL Server or the lowest level possible and that every table has a real data unique record constraint. SharePoint lists don't exactly instill confidence in me. Azure would be a better data source but it costs so for personal projects O365-SP is sufficient.

 

[vjimmers]

Some guy (me) posted some code above before enough testing.
The code below works as intended! I'm new at this but there are hardly any examples anywhere online so hopefully this will help someone get going in less time than it is taking me. I'm not entirely sure where we have to account for NULL values since calculated columns appear to return empty strings instead when concatenated.

/* Keep copy of a lookup column synchronized so it can be used in calculated columns and for query parameters which do not appear to work on foreign keys*/.

If [IsInsert] Or IsNull([Contact]) Or [Contact]<>[ContactID] then
Setfield Contact = ContactID
End if

/* implement muti-column unique record constraint. */

/* IX_01 = "[" & [Contact] & "].[" & [CommentDate] & "].[" & [CommentSubject] & "].[" & [CommentType] & "]" */
If [IsInsert] Or (Updated("IX_01") And [IX_01]<>[Old].[IX_01]) then
LookupARecordIn Comment
WHERE [Comment].[Contact]=[t1].[Contact]
And [Comment].[CommentDate]=[t1].[CommentDate]
And [Comment].[CommentSubject]=[t1].[CommentSubject]
And [Comment].[CommentType]=[t1].[CommentType]
Alias t1
RaiseError
End if

 

[Steve@trop]

I have a similar situation. I wondered if I should have started a seperate thread instead of replying to this one???

I have several tables that will be updated by operators using forms. When I first started working on this database I noticed I was inadvertently creating lots of blank records as I was testing various forms so I picked one field and made it required at the table level. As I'm now beginning to test with actual users entering data I'm finding that this is causing usability problems in some areas. If they open a form by mistake or change their mind about which form they need, they get an error message which is sometimes difficult to get rid of. In the version that has been upsized to SQL, the error is very cryptic and also tends to crash the runtime program if they don't respond in exactly the correct way. I'm thinking of removing the required property at the table level and using data validation in the forms instead. When I've tried this in the past, it doesn't seem to work consistently. I've found that it doesn't always require entry into the field.

What is the best way to accomplish this? Ideally, users should be given a choice between going back to the form and completing the record or closing the form without creating a record.

The forms all have a few fields that are pre-populated (DateTime, Operator Name, equipment number, etc) so they don't start out completely blank. I wonder if this complicates this issue or not.

Thanks,

Steve

 

[Neruda]

Hi, few years have passed but..
wanted to ask: If I wanted to validate a field on a form, could I use a before update on the form plus "Required filed" on the table?? In this case table validation will inhibit the before update on the form which does not run. So, on an ordinary "add Product form" would you forget about table validation and unclick "Required field"?? data validation is my biggest headache and I have gone, slowly, insane.
Thanks

 

[Pat Hartman]

When I can implement a rule using simple table validation (Not Data macros), I do. The table level validation is extremely limited but it is a failsafe should someone append data using a query. One table level rule I always implement is to set the Allow ZLS to false. For some reason, MS switched the default from false to true, probably to "help" novices. When the AllowZLS is set to true, which is the current default, it defeats the purpose of making the text field required since customerName for example would be allowed to be "" (a ZLS). which of course displays a blank. It makes no sense at all to allow a customer name to be "empty". Plus when you allow both "" and null in the same field, you always have to remember that when creating queries. In code behind the form, you need to worry about ZLS even though they are not allowed because if a person starts typing in a field and changes his mind and just back spaces, that leaves a ZLS rather than the original null. So when I do my "is present" validation, I include ZLS:
If Me.SomeField & "" = "" Then -----error
Another way is:
If Len(Me.SomeField) = 0 Then ------error

I happen to use the first method rather than the second because it doesn't use a function. This is from YEARS of developing transactions on the mainframe where we needed sub second response for most queries. It was important to know the timing of your instructions. In Access functions take more time than native expressions. Not really enough to worry about but old habits die hard and there is no reason to use a slower expression once you understand

In the forms, I do the vast majority of my validation in the form's BeforeUpdate event. On rare occasions I use the control's BeforeUpdate event but because you can't do all validation for the control in that event, I just gravitated to putting the validation all in one place. It makes it simpler for me.