LOOP WITHIN A LOOP THE SAGA CONTINUES....

[SEAN DI''''ANNO]

Vincent Johns gave me some invaluable advice in the code for loop within a
loop. It also introduced me to a union query which I did not know existed
but is obviously very useful for the purpose I need. He also kindly gave me
some sample code for looking at fields and deciding a data score. This code
has set of a brain wave for me..(highly unusal) and I would like to know if
my thoughts are realistic.

The current code relies on the validation rules being very much down to me
to keep updated. I.e. Now some keeps entering "Not important" for address_1.
From the code I could just add it under case_1 but it would ne nice if the
validation checks were in some way maintanable by the users who will
eventually use the database.

Therefore, I have a database which uses a switchboard method of menues..i.e.
switchboard items. Could I use this method within my database; So instead
of switchboard items, I would have fault items and the case values would be
looking for an item?


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

 

[Vincent Johns]

Vincent Johns gave me some invaluable advice in the code for loop within a
loop. It also introduced me to a union query which I did not know existed
but is obviously very useful for the purpose I need. He also kindly gave me
some sample code for looking at fields and deciding a data score. This code
has set of a brain wave for me..(highly unusal) and I would like to know if
my thoughts are realistic.

The current code relies on the validation rules being very much down to me
to keep updated. I.e. Now some keeps entering "Not important" for address_1.
From the code I could just add it under case_1 but it would ne nice if the
validation checks were in some way maintanable by the users who will
eventually use the database.

Yes. You could place the specifications for field values into a Table
that includes in each record a field name and a specification. The
specification itself might look like a Unix-style regular expression, or
like the specifications in Format$(), &c. But you'd have to determine
how to use it in evaluating an input value; all you'd get from the Table
would be a string identifying the pattern.

If your users are entering "Not important" for [address_1], and you
don't like that, I'm not sure that changing the database is going to
help. I sometimes answer questionnaires that require me to answer some
questions to which I do not want to provide an answer. So, if they
won't accept a null answer, I simply conclude that the data-entry form
has a bug in it and I am therefore not required to give a truthful
answer in such cases. What will you do if your users start entering
"123 Main St." all the time instead of "Not important"? I think you'll
find yourself in a war in which you'll come out second best. You might
want to have a conversation with the users about what they should do
when some information is not readily available.

Therefore, I have a database which uses a switchboard method of menues..i.e.
switchboard items. Could I use this method within my database; So instead
of switchboard items, I would have fault items and the case values would be
looking for an item?

Yes, and in VBA you could open a Table and read its contents to compare
them with the value you're checking. You'd be able to maintain the
Table's contents via a Form, rather than by having to modify and
recompile VBA source code.

[...]

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

Oops... I just now noticed a bug in the above code. This field could
have a value that would reduce the score by 2 for the same record, so
you'll probably want to do something different here. Sorry. (But this
wasn't intended to be useful, just illustrative. I hope it's not too
misleading.)

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