-
Here is what I'm trying to do. I have a very large recordset, with about 200 fields from a SQL database. I want to write a program that validates the records based of some criteria that is stored in another table. I can't implement constraints on the database because the criteria could change frequently. I want to be able to get a record and then validate it field by field based on a criteria loaded from another table (ex. Value="C"). Such as
Function Validate(Value as Variant, Criteria as String) as Boolean
Validate=CBool(Criteria)
End Function
This gives me a type mismatch. I've tried changing the Criteria to variant and doing a Criteria=Replace(Criteria,"Value",Value), but I get the same error.
I thought it had to do with double quotes so I tried
CBool("1=1") and again same error. The documentation says it evaluates any valid string but only CBool(1=1) works not my previous example.
My only other option is to build a huge Select Case statement, segregate it into a class module and have the user modify it. But the user has to know VB(A couple do) and it is very slow, out of the 200 fields more than half have criteria.
Any help would be greatly appreciated. Thanks
-
Hi,
I may be going the wrong route here but. I'm assuming any fields that dont meet the criteria fail and you then change the value/delete the record all together. This being the case it would be far more efficient to give the user a screen with a combo containing available fields and another with operators (<, >, <> etc) and a textbox to enter values. Next take this and build an SQL statement using the criteria to build your where clause. that way you would find immediatly all records where a field does not match with one DB call. You can then cycle through and change as necassary or even a single update against the DB.
