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])

 

[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.

 

[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