Table of Rules (1 Viewer)

[micks55]

My problem is that data checking can get complicated and every time a new rule is needed I have to add another If statement in the vba module and re-issue the program so I'm trying to see a way of storing the If statements in a table so that I can edit/delete existing rules and add new ones.

Currently I open a recordsetclone and loop through looking for errors with If Then statements such as:
If Nz(rs!Qty) < 1 Then ErrTxt = ErrTxt & vbCrLf & rs!Line & " No Qty"
If ErrTxt <>"" the MsgBox ErrTxt
There are many lines of these statements so it would help to store "If Nz(rs!Qty) < 1 Then ErrTxt = ErrTxt & vbCrLf & rs!Line & " No Qty"" in a table then loop through the "Rules" table and Execute or Evaluate or Run each one at run time.

My latest failure is
Dim VbaString, testtxt
testtxt = "If Nz(Me.IValu) > 1 Then ErrTxt = ErrTxt & ' Very expensive'"
VbaString = testtxt
Eval (VbaString)
MsgBox "oops " & ErrTxt

I'm hoping someone can point me in the right direction. Many thanks, Mike

 

[bob fitz]

perhaps:
"If Nz(Me.IValu,0)

 

[micks55]

Hi Bob, Many thanks for your reply.
I've written the vba that checks the data in the recordsetclone and it works ok. It's looping through the recordsetclone doing about 90 checks on each record (they're all If statements) and then shows a MsgBox if any errors are found.

My problem is that these checks keep changing (due to constant material test notices) so I am often editing the vba and issuing new versions.

It would be great if I could put all of those If Then Else vba lines in a table so that I can edit/add/delete them without editing the vba and issuing a new program.

With the statements stored in a table (maybe called ChecksTable) I could replace the 90 lines of vba code with an inner loop reading each record in the ChecksTable and running the vba stored in there.

If you guys say that I'm chasing a rainbow I'll give up but I don't know if I should be looking into the Eval() function or Execute or something else.

Many thanks again.

 

[arnelgp]

whether you will use Eval or a function/sub, you will still run it (eval or function/sub) 90 times.
so better use your Error free code.

 

[micks55]

Thanks for the advice. I'll probably give it up. I would have preferred to edit lines of code stored in a table rather than edit the vba and re-issue the program every time but if it can't be done then I'll just have to accept defeat. Mike

 

[LarryE]

Look into using SELECT CASE. It will act like If-Then but fewer lines of code. Also, consider using data validation rules for each of your form fields. Each form field has a Validation Rule and Validation Text property. You can set data validation rules and error text messages if the rule is violated.
You can also set error messages for each form field using their On Exit property. For example, if you have a field named TodayDate, you can write VBA code just one time for that field as follows if you don't want the field left blank or the field must be todays date:

Private Sub TodayDate_Exit(Cancel As Integer)
If IsNull(Me.TodayDate) Or Me.TodayDate <> Now Then
    DoCmd.CancelEvent
    MsgBox "Field Must Be Completed With Todays Date"
    DoCmd.GoToControl "TodayDate"
End If
End Sub

Use the same logic for each field you wish to check. The logic could be >0, <>0, IsNull, or whatever. The point is, you do it just one time but each time a criteria needs to be added, you add it to just the form field using the OR operator.

Also, my people recommend using the form fields Before Update property to check data validation.

Each field in a table also has a "Required" property. You could set this property to "Yes" if you need something in that field. That may eliminate some of your code writing.

Don't give up. Maybe it can be made easier for you.

 

[GPGeorge]

Thanks for the advice. I'll probably give it up. I would have preferred to edit lines of code stored in a table rather than edit the vba and re-issue the program every time but if it can't be done then I'll just have to accept defeat. Mike

I don't think you should consider it a defeat, but a learning experience. Progress comes from trial and error. Remember Edison's assertion that he hadn't failed many times. Instead, he'd identified multiple ways that don't work.

 

[Pat Hartman]

I've written the vba that checks the data in the recordsetclone and it works ok. It's looping through the recordsetclone doing about 90 checks on each record (they're all If statements) and then shows a MsgBox if any errors are found.

This is not a process that should be run on a recordsetclone. Validation is done ONE record at a time in the BeforeUpdate event of the form before the record is saved. That allows you to prevent BAD DATA from being saved. Your method seems to be to save the bad data and then try to find it later. Pretty scary.

Since you are unfamiliar with Form events, you might want to spend a half hour to watch this video about the BeforeUpdate event.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[jdraw]

Further to the comments you have received, can you describe/post 2 or 3 of the rules that are causing concern?
As George said, this is a learning opportunity!

 

[The_Doc_Man]

My brief stint in text parsing has taught me that there are limited ways to handle this kind of "looking for syntax or semantics errors" problem. I know of only two ways to do this and neither one is pretty at all.

Way number one is an enumerated list of rules, which you have execute in some particular order, running through the complete list for each record, checking for specific combinations to be present. The only way to optimize this is to decide which errors are most common and put that test first in the list, then the next most likely, and so on down to the least likely error. This can be tedious to set up but COULD be loaded to a table. Unfortunately it is unlikely to be very efficient.

Worse, let's say you have 90 rules and 10,000 records. You will run 900,000 rules if ever record is correct, and only fewer rules if there exists at least one error of unknown probability.

Way number two is to parse the record one parsing token (could be a letter, a word, a digit, a number, a punctuation mark, or even a space) at a time, and have a list of rules that says "If you see X after Y it is OK, but X after Z is bad" - and there you enumerate all the rules for where X can occur. Here, the problem is deciding what such rules would look like. This is less likely to be definable using table strategies and it ties in to some fairly hairy issues like automata theory and issues with Finite or Non-Finite Automata.

Also, like Pat mentioned, the place to run this can be during entry or after the fact - but in terms of how it is perceived, doing testing immediately after each individual record's data entry is less likely to seem to take forever because one run of 90 tests should still be fairly quick. Doing it after all data entry is complete will be more problematic AND also loses the chance to have the person who was doing the entry (incorrectly) to fix the problem on the spot.

Not only that: Looking at If Nz(rs!Qty) < 1 as a thing to be tested after the fact is not a syntax check, but a semantics check - which means that all items to be tested must be in the record as literals. E.g. if your test were IF NZ( rs!Qty, "" ) = "" ... then you are looking a syntax check for a null argument. Testing for the numeric value of the argument is a data check, not a syntax check, and has wider implications.

 

[MajP]

I would be interested in the 90 rules, I assume a lot are just check for Null or empty fields. I think you could do this with a table, but not a 100% solution. You could maybe get an 80% solution and then write code for what cannot be done in the table. It just depends on how complex are the rules. You will not be able to write a string and expect to evaluate it.
This is one of those things you would have to try to see if it is feasible or if the bang is not worth the buck. As you try to add a rule you will find you need to add fields to the below tables.

I would make these tables

tblRuleDataType tblRuleDataType

RuleDataTypeID	DataType
1​	Boolean
2​	Numeric
3​	DateTime
4​	Text

tblRuleType tblRuleType

RuleTypeID	RuleType
1​	Equal
2​	Greater Than
3​	Greater Than or Equal
4​	Less Than
5​	Less Than or Equal
6​	Not Equal
7​	Between
8​	Not Null

Example of rules. This will allow me to compare a field against up to 2 literals or 2 other fields or combination

tblRules tblRules

RuleID	TableName	FieldName	RuleType1ID_FK	LiteralCompare1	TableCompare1	FieldCompare1	RuleCombiner	RuleType2ID_FK	LiteralCompare2	TableCompare2	FieldCompare2	RuleDataTypeID_FK
1​	Orders	OrderDate	5​		Orders	ShipDate		​				3​
2​	Orders	OrderDate	3​	#1/1/2022#			AND	4​	#1/1/20233			3​
3​	Orders	ShipDate	7​		Orders	OrderDate		​		Orders	RequiredDate	3​
4​	Orders	SupplierID	8​					0​				1​

With relatively short code I can interpret the above table to
Order.Orderdate <= Order.ShipDate
Orders.OrderDate > #1/1/2022# and <= #1/1/2023#
Orders.ShipDate between Orders.Orderdate AND Orders.RequiredDate
Orders.SupplierID Is Not NULL

However that will not be done by creating a string and trying to evaluate it. It will be done with code and a series of Select Cases.

What may get difficult is adding and evaluating VBA functions Such as
Orders.OrderDate >= Now()
left(SupplierName,3) <> "ABC"

If you can provide a form and the rules, I will see how much can be done in a table through code.
My first guess is that you have some kind of design issue. I have written very complex data validation and and never reached 90 validation rules. What is not shown here is the code that would be needed, but I can envision it to be relatively short and not too taxing to build.

 

[CJ_London]

your string won't work because you have hardcoded the values. For it to be evaluated


testtxt = "If Nz(Me.IValu) > 1 Then ErrTxt = ErrTxt & ' Very expensive'"

would need to be
if eval("Nz(" & Me.IValu & ",0) > 1") Then ErrTxt = ErrTxt & ' Very expensive'"
MsgBox "oops " & ErrTxt

In your test table the tstText string might be something like

"Nz([param],0) > 1"

and your code would use the replace function so it could be applied to a different fields

if eval(replace(tstText,"[param]",Me.IValu)) Then ErrTxt = ErrTxt & ' Very expensive'"

 

[micks55]

Many thanks for all of the comments. The_Doc_Man and MajP have some great ideas for me to pursue and I've always said Access can do anything so I still think I will find a solution. I just need to find it soon because this will be my last re-write as I can see the tunnel at the end of the light (I started with A97 and Dbase2 before that).

We sell Fire and Acoustic Doors so a Technical Setter has to set the job out for manufacture and ensure that regulations are complied with. The existing code (with lots of If's) has been working since 2008 but now that I'm getting closer to not being in the office at 5:30 every day I was thinking about a way for others to add new rules without me.

The checks are limited to the current job on screen by use of the recordsetclone of the subform and it would not be appropriate to work with the whole table which is 14 years of data with 174,000+ records.

Thanks to George, Pat and The_Doc_Man for the encouragement and wise comments.

I agree that Select Case would have a speed advantage but working on just the job in hand is typically 10-100 records so multiple If End If's run pretty much instantly although you've made me think that maybe using Select Case could make the flow of the code easier to understand and maintain in the future.

Thanks to MajP who is exactly right, it's mostly looking for missing data with just a few compliance checks in there and the compliance stuff doesn't change very often so just getting the missing data checks in a table would be more elegant and far easier to maintain.

A typical missing data check ensures we have 4 dimensions for a Cut Out.
If Nz(rs!D1Ah) = 0 Or Nz(rs!D1Aw) = 0 Or Nz(rs!D1Au) = 0 Or Nz(rs!D1Ai) = 0 Then ErrTxt = ErrTxt & vbCrLf & rs!Line & " D1 CO-A Size"
D1 is a single or the left side of a pair of doors with up to 5 cut outs (ABCDE) each needs 4 dims high, wide, up, in. Cut Outs are for Vision Panels (glass) or Air Transfer Grills (which auto close in the event of a fire).

A sample compliance check would be where we want to achieve a high Decibel rating we must fit a "Drop Seal" in the bottom of the door.
If Nz(rs!DB) > 29 Then
If Nz(rs!iD1BottomA) = 0 Or Nz(rs!iD1BottomB) = 0 Then ErrTxt = ErrTxt & vbCrLf & rs!Line & " DB Drop Seal"
End If

I will concentrate on the missing data checks hoping to find a way of using a table because if I can do that then I should be able to duplicate the method but use a separate table for the compliance rules which would likely need a slightly different table structure.

Thanks to all. KR-Mike

 

[CJ_London]

did you see post #12?

 

[Pat Hartman]

doing testing immediately after each individual record's data entry

Validating AFTER the record has been saved is 100% wrong. You validate before you save the data. So that you do not ever save bad data.

The only time you would validate existing data is if you are doing a conversion and you are trying to weed out bad data. But then you need a rational interface that allows the user to correct the errors, one at a time. You are talking about how to run the validation but you are not addressing how to fix the bad data.

 

[RogerCooper]

You might want to try a different approach and use queries rather than VBA code. Each query can be tested separately and then the name of the query (or the code of the query) could be saved in a table. It is easy to make a mistake when you are writing code.

 

[Pat Hartman]

every time a new rule is needed I have to add another If statement in the vba module and re-issue the program

That is what programmers do If we knew all the rules we'd need in 2030, we'd add them now but although being an optimist is a prerequisite for the job, we are not omniscient. We do what we can to ensure data entered is rational. For example, ALL dates are validated to some extent but the logic changes based on what the date represents. So, Dates of birth and death cannot be > today. If you are entering the birth date of an employee, the date most likely needs to be not more than 65 years ago. If it is, you can prompt for an override but you probably won't allow the date if the employee were less than 16 since that is the minimum legal age to work in the US.

Here's a sample that ensures fields are not empty. It relies on the Tag property of the controls on a form. It is called from the form's BeforeUpdate event. If you want to use a table to record the Control Names, then you don't have to worry about setting the Tag property.

Option Compare Database
Option Explicit

Private Sub Name_BeforeUpdate(Cancel As Integer)
    If EnsureNotEmpty(Me) = False Then
        Cancel = True
        Exit Sub
    End If
    
    '''.... other validation
End Sub

In order to be used for any form, this code needs to go in a standard module.

Public Function EnsureNotEmpty(frm As Form) As Boolean
Dim ctl As Control

    For Each ctl In frm.Controls
       Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox, acOptionGroup
                If ctl.Tag = "R" Then
                    If ctl & "" = "" Then
                        ctl.SetFocus
                        EnsureNotEmpty = False
                        MsgBox ctl.Name & " is required.", vbOKOnly
                        Exit Function
                    End If
                End If
            Case Else
        End Select
    Next ctl
    
    EnsureNotEmpty = True
End Function

 

[micks55]

Again, many thanks to all.

Validation has it uses but the user will typically work on the job over a number of days so lots of messages popping up to nag him/her is not an option. The time to remind them of their failings is when they try to print the reports.

Validation at table level is not the answer because that's the same as hard coding in that it can't be changed without a re-write or kicking users out while I mess with the table structures.

Validation is not simple because there are no fields where validation does not rely on the contents of another field or usually a combination of the contents of multiple other fields.

As suggested by MajP, I'm going to hard code the unchanging stuff then try looping through a table (or tables) taking on board CJ_London's syntax advice in post #12.

Thanks, Mike

 

[Pat Hartman]

Validation is not simple because there are no fields where validation does not rely on the contents of another field or usually a combination of the contents of multiple other fields.

That's why you do it in the FORM's BeforeUpdate event.

lots of messages popping up to nag him/her

Your people must be really bad at entering data.

 

[CJ_London]

@Pat Hartman - don't disagree with the before update event but I have had situations where data is coming in from multiple sources at different times to different tables by different methods. Validation is frequently based on comparing data from those different tables.

The way I handle it is to have a separate query that scans the data and returns a list of all validation errors. Some errors are passed back to whoever provided the data (may be more than one source) for correction and some can be resolved based on agreed business rules, either via automation or by passing to an 'adjudicator'. Agree some can be identified at time of import which would be the equivalent to a before update event.

Simple example (not related to the OP's requirement)- list of customers provided by a crm system and a sales ledger. Requirement is to match on account number (first validation), address (second validation) and net sales. Net sales are imported to the crm system and the sales ledger from multiple sales channels (retail, web, manual invoice, etc) so the third validation is that they agree. Validation can fail because of timing issues so usually looking at date-3 to give a chance for everything to catch up - but they can still occur. And sometimes data goes missing or is misinterpreted so needs to be resolved. This may involve corrections to any of the 3 systems