Searching For Rules Depending On Field

[SEAN DI''''ANNO]

(Final Part Of Loop Within a Loop)

Good morning all, This is proving to be very frustating for me becasue my
understanding of code is quite obviously rubbish. I know what I want to do
but don't know how to carry it out. I was kindly given the code listed below
to look at values in a contacts table and check for obvious errors. As it
stands the errors are done by building in cases against specific fields and
checking specific values What I would like to do is have a table of fauly
items such as;

Faut Rules Table
Fault Field Name Fault Descrption Fault Type Score Deduction
Contact Name xxx Begins With 0.25
Contact Name sean Contains 0.75
Contact Name
Address 1 Exact 1
(i.e. nothing entered)

When the Datascore unction scans through the fields, it will then loop
through the fault rules table to see if any of the rules match the field
name.

This method would allow users to maintain the rules themselves and allow the
score duduction to be controlled dependin on the severity of the entry. I am
sorry to ask so many questions. I have ordererd a book to help me for future
reference.

'DataScore() returns the number of valid fields in
' the selected record in the [ContactList] Table
Public Function DataScore( _
ByVal RecordID As Long) _
As Integer

'ByVal RecordID As Long 'Primary key of record

Dim CurrentRecord As Recordset 'Record identified _
by RecordID key value
Dim fldField As Field 'One of the data fields

'Grab the selected record
Set CurrentRecord = CurrentDb.OpenRecordset _
("SELECT * FROM [ContactList] " _
& "WHERE [ContactList_ID] = " _
& RecordID & ";", _
dbOpenSnapshot)

'Start by assuming all fields to be valid
DataScore = CurrentRecord.Fields.Count

For Each fldField In CurrentRecord.Fields

'Look for whatever might be wrong in the field




Select Case fldField.Name


Case "Contact Name"
If Right$(fldField.Value, 5) = "troyd" _
Then DataScore = DataScore - 1
If UCase$(Left$(fldField.Value, 1)) = "X" _
Then DataScore = DataScore - 1

Case "Address 1"
If InStr(fldField.Value, "Baker") > 0 _
Then DataScore = DataScore - 1

Case "Post Code"
If Left$(fldField.Value, 3) = "2V5" _
Then DataScore = DataScore - 1

End Select 'Case fldField.Name

Next fldField

End Function 'DataScore()
Was this post helpful to you?

 

[Vincent Johns]

(Final Part Of Loop Within a Loop)

Good morning all, This is proving to be very frustating for me becasue my
understanding of code is quite obviously rubbish. I know what I want to do
but don't know how to carry it out. I was kindly given the code listed below
to look at values in a contacts table and check for obvious errors. As it
stands the errors are done by building in cases against specific fields and
checking specific values What I would like to do is have a table of fauly
items such as;

Faut Rules Table
Fault Field Name Fault Descrption Fault Type Score Deduction
Contact Name xxx Begins With 0.25
Contact Name sean Contains 0.75
Contact Name
Address 1 Exact 1
(i.e. nothing entered)

When the Datascore unction scans through the fields, it will then loop
through the fault rules table to see if any of the rules match the field
name.

This method would allow users to maintain the rules themselves and allow the
score duduction to be controlled dependin on the severity of the entry. I am
sorry to ask so many questions. I have ordererd a book to help me for future
reference.

[...]

What you apparently want to do is kind of ambitious, and it will require
a lot of programming (well, maybe not "a lot", but certainly more than
just a few lines). For each "Fault Type" you'll have to write some code
that will evaluate the field contents according to that specification.
For example, your first line will need to generate a test something like

left$( MyFieldValue, 3 ) = "xxx"

where MyFieldValue contains the string your user is testing. For
"Contains", you can use the InStr() function.

If you can get it working, it could be very convenient for your users.
If you have trouble, people on this newsgroup should be happy to help.
Good luck.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.