Compare a recordset against a set of rules

[tuxalot]

Hello all. I need some help building VBA code to loop through two recordsets. I have a local table like this:

Question Response
1 2
2 2
3 1
4 1

I want to evaluate each entry in that table based on a set of rules. The rules are created by my users and as an example, might look like this:

Question 1 <> 3 (cannot equal 3)
Question 2 = 2
Question 4 <> 1

The local rules table has a variable amount of rows. The questions and responses to be evaluated are queried from a table, and that recordset will have approx. 250 rows.

So I would like to loop through the question recordset with each rule and if a rule is violated, note that and at the end of the loop present a msgbox telling a user the rules that were violated, along with the rule(s) so they can make corrections.

I found this code and thought it may work with modifications:

Set rst1 = db.OpenRecordset("qselQuestions")
Set rst2 = db.OpenRecordset("qselRules")

rst2.MoveFirst

lngTotalRowsExist = 0
lngTotalsRowsMatched = 0

Do Until rst2.EOF

   lngTotalRowsExist = lngTotalRowsExist + 1

   ' this bit below will need to be altered to handle the not-equal rules

   strCriteria = "[question] = & rst2("question") & _
                 " AND [response] = rst2("response")

   rst1.FindFirst strCriteria

   If rst1.NoMatch = False Then
      lngTotalRowsMatched = lngTotalRowsMatched + 1
   End If

   rst1.MoveNext

Loop

If lngTotalsRowsMatched > 0
   strMsg = lngTotalRowsMatched & " rules of the " & _
            lngTotalRowsExist & " total rules in the rule set have been " & _
            "violated and need to be corrected!"

   ' then added to this I would like to show the rules violated

   MsgBox strMsg

else

  ' proceed with processing

End If

rst1.Close
rst2.Close

Is this the best approach? If you could help with the missing bits commented above it would be much appreciated.

 

[mdlueck]

So I would like to loop through the question recordset

Is this the best approach? If you could help with the missing bits commented above it would be much appreciated.

Since you mention looping through records, I prefer to use a three method design:

Example of SQL SELECT using ADODB.Recordset object to Access FE temp table to scan the FE temp table and perform operations
http://www.access-programmers.co.uk/forums/showthread.php?p=1214730 #post1214730

If it is necessary to UPDATE the record, then I would use a separate ADO object so as to not goof up the cursor position of the Recordset which is looping through all records:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

 

[tuxalot]

Thanks Michael. Seems like a more complex way to achieve the same result, no? Pardon my ignorance, I am still learning Access.

 

[mdlueck]

I prefer to code such loops in three methods rather than letting one method keep growing and growing. Also, a bit of the verbosity is due to double checking the adoRS object... such as "adoRS object did not indicate the end, move next, did the adoRS object actually get there? no, rrrr???"

If you are comparing string data, then be sure to look up StrComp in the VBA reference. Example:

  'Next make sure the strDefaultFASARRule is an EXACT match of what it is suppose to be
  If [COLOR=Blue][B]StrComp([/B][/COLOR]Mid(Me.fasarrule, 1, Len(strDefaultFASARRule)), strDefaultFASARRule, [COLOR=Blue][B]vbBinaryCompare) <> 0[/B][/COLOR] Then
    flgBadfasarrule = True
  Else
    flgBadfasarrule = False
  End If

 

[tuxalot]

If you are comparing string data

No, just comparing integer data but thanks for the tip.