Multiple If Then Statements Stored In table

[tkeith3556 via AccessMonster.com]

I have a number of conditional If...then statements that can be updated or
changed at any time. I was thinking of putting these If...then statements in
a table that could be updated externally without having to change the file
itself. Then cycling through these conditonal statments through code, but I
don't know how to build a VBA statement that would read the If...then
statement as executable code and not just a returned value for a variable.
Has anyone ever tried this or am I way off base.

for example:

Dim cdb As DAO.Database, rs As Recordset, el1 As Variant, sql As String
Dim el2 As String
Set cdb = CurrentDb
If Me.TabCtl248 = 8 Then
sql = "SELECT * From EtblCBOValFunc"
Set rs = cdb.OpenRecordset(sql)
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF = True
el1 = rs!EndValFunc
el2 = rs!EndValFuncDes
'el1 and el2 pulls the conditional statements then passes it to the If...then
statement below
If el1 Then
el2
End If
rs.MoveNext
Loop

Of course that doesn't work because the variables are returned as values and
not executable code.
is there a way to do this?

 

[Dirk Goldgar]

I have a number of conditional If...then statements that can be updated or
changed at any time. I was thinking of putting these If...then statements
in
a table that could be updated externally without having to change the file
itself. Then cycling through these conditonal statments through code, but
I
don't know how to build a VBA statement that would read the If...then
statement as executable code and not just a returned value for a variable.
Has anyone ever tried this or am I way off base.

for example:

Dim cdb As DAO.Database, rs As Recordset, el1 As Variant, sql As String
Dim el2 As String
Set cdb = CurrentDb
If Me.TabCtl248 = 8 Then
sql = "SELECT * From EtblCBOValFunc"
Set rs = cdb.OpenRecordset(sql)
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF = True
el1 = rs!EndValFunc
el2 = rs!EndValFuncDes
'el1 and el2 pulls the conditional statements then passes it to the
If...then
statement below
If el1 Then
el2
End If
rs.MoveNext
Loop

Of course that doesn't work because the variables are returned as values
and
not executable code.
is there a way to do this?

No, you can't read and execute code. You might be able to evaluate a stored
IIf() function expression using the Eval function, but it would be very
cumbersome, and you couldn't execute a stored assignment statement.

Would you like to explain in more detail what is the specific problem you
are trying to solve? Maybe there's enough regularity in the conditions that
have to be evaluated that they can be abstracted into some representation
that can be evaluated at run time.

 

[Rob Wills]

As Dirk suggest - it may help if you can expand on exactly what your end
product should be....

Sounds to me as if you could be talking about using mapping tables....

 

[tkeith3556 via AccessMonster.com]

Hmmm...what if I used IIF as an evaluation of the validation and returned a
boolean or vb value to a variable and determined if the variable was true or
false?

Like:
Dim cdb As DAO.Database, rs As Recordset, el1 As Variant, sql As String
Dim el2 As String
Set cdb = CurrentDb
sql = "SELECT * From EtblCBOValFunc"
Set rs = cdb.OpenRecordset(sql)
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF = True
el2 = rs![Validation Statement]
el1 = IIf(el2,vbYes,vbNo)
if el1 = vbNo then
msgbox rs![Validation Message]
exit sub
end if
rs.movenext
loop

 

[tkeith3556 via AccessMonster.com]

this still isn't working. I can't get that variable to run as executable code.
Arrgghhh!!!

Hmmm...what if I used IIF as an evaluation of the validation and returned a
boolean or vb value to a variable and determined if the variable was true or
false?

Like:
Dim cdb As DAO.Database, rs As Recordset, el1 As Variant, sql As String
Dim el2 As String
Set cdb = CurrentDb
sql = "SELECT * From EtblCBOValFunc"
Set rs = cdb.OpenRecordset(sql)
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF = True
el2 = rs![Validation Statement]
el1 = IIf(el2,vbYes,vbNo)
if el1 = vbNo then
msgbox rs![Validation Message]
exit sub
end if
rs.movenext
loop

As Dirk suggest - it may help if you can expand on exactly what your end
product should be....

Sounds to me as if you could be talking about using mapping tables....

 

[tkeith3556 via AccessMonster.com]

I am getting a type mismatch error in the below code. The first recordset
"rs1" gets the references to validate and the second recordset "rs" gets the
validations and right when it gets to el1 = IIf(el2, vbYes, vbNo) I get the
"Type Mismatch" error. I have tried Eval(), I have tried returning the code
in another function and passing the value back and still get the Type
Mismatch. Would setting it as ByVal first help? I don't know what to do.

Dim cdb As DAO.Database, rs As Recordset, el1 As Variant, sql As String
Dim el2 As String, ssql As String, rs1 As Recordset, el4 As String
Set cdb = CurrentDb
ssql = "SELECT * From dbo_EtblInitRefer where dbo_EtblInitRefer.EndNum = '" &
gEndNum & "'"
Set rs1 = cdb.OpenRecordset(ssql)
rs1.MoveLast
rs1.MoveFirst

sql = "SELECT * From EtblCBOValFunc"
Set rs = cdb.OpenRecordset(sql)
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF = True
el2 = rs!EndValState
el1 = IIf(el2, vbYes, vbNo)
If el1 = vbNo Then
MsgBox rs!EndValMsg
Exit Function
End If
rs.MoveNext
Loop

this still isn't working. I can't get that variable to run as executable code.
Arrgghhh!!!

Hmmm...what if I used IIF as an evaluation of the validation and returned a
boolean or vb value to a variable and determined if the variable was true or

[quoted text clipped - 22 lines]

 

[Douglas J. Steele]

Not sure whether this is the problem, but I'd suggest ensuring you're
getting DAO recordsets, since that's what your code requires.

Change your declarations to

rs As DAO.Recordset and rs1 As DAO.Recordset

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am getting a type mismatch error in the below code. The first recordset
"rs1" gets the references to validate and the second recordset "rs" gets
the
validations and right when it gets to el1 = IIf(el2, vbYes, vbNo) I get
the
"Type Mismatch" error. I have tried Eval(), I have tried returning the
code
in another function and passing the value back and still get the Type
Mismatch. Would setting it as ByVal first help? I don't know what to do.

Dim cdb As DAO.Database, rs As Recordset, el1 As Variant, sql As String
Dim el2 As String, ssql As String, rs1 As Recordset, el4 As String
Set cdb = CurrentDb
ssql = "SELECT * From dbo_EtblInitRefer where dbo_EtblInitRefer.EndNum =
'" &
gEndNum & "'"
Set rs1 = cdb.OpenRecordset(ssql)
rs1.MoveLast
rs1.MoveFirst

sql = "SELECT * From EtblCBOValFunc"
Set rs = cdb.OpenRecordset(sql)
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF = True
el2 = rs!EndValState
el1 = IIf(el2, vbYes, vbNo)
If el1 = vbNo Then
MsgBox rs!EndValMsg
Exit Function
End If
rs.MoveNext
Loop

this still isn't working. I can't get that variable to run as executable
code.
Arrgghhh!!!

Hmmm...what if I used IIF as an evaluation of the validation and returned
a
boolean or vb value to a variable and determined if the variable was true
or

[quoted text clipped - 22 lines]

Sounds to me as if you could be talking about using mapping tables....

 

[John Spencer]

Could you post some the data that you are trying to evaluate?

For instance, in the VBA immediate window this works.

str = "IIF(1=3,""It's True"",""It's False"")"
?Eval(Str)

So you would need to have a string stored that read
IIF(1=3,"It's True","It's False")

This works
strX = "IIF(Forms!FAQ!txtFilter is Null,True,False)"
?Eval(strX) and returns true or false

And so does this check of a field value on a form
strX = "IIF(Forms![FAQ]![fsubject] is Null,True,False)"
?Eval(strX)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[tkeith3556 via AccessMonster.com]

Thanks for the input John. I did try this before in the immediate window and
it did work in the immediate window but when I stepped the code in the
procedure itself, it still didn't work, but I will try it again. Maybe I
just didn't have my quotes right or somethign like that. I will let you know.


PS: Sorry didn't back on sooner, but I've been off the PC for a bit. Thanks
Again and I will let you know.

Could you post some the data that you are trying to evaluate?

For instance, in the VBA immediate window this works.

str = "IIF(1=3,""It's True"",""It's False"")"
?Eval(Str)

So you would need to have a string stored that read
IIF(1=3,"It's True","It's False")

This works
strX = "IIF(Forms!FAQ!txtFilter is Null,True,False)"
?Eval(strX) and returns true or false

And so does this check of a field value on a form
strX = "IIF(Forms![FAQ]![fsubject] is Null,True,False)"
?Eval(strX)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Not sure whether this is the problem, but I'd suggest ensuring you're
getting DAO recordsets, since that's what your code requires.

Change your declarations to

rs As DAO.Recordset and rs1 As DAO.Recordset

 

[tkeith3556 via AccessMonster.com]

Hey John,

Thanks again for the ideas. So here's what i found out through some further
testing. I guess that when I was nesting a varialbe within the statement for
the Eval() function it wasn't able to go that far to come up with value of
the variable when I did the Eval(). I would post the code and all, but it's
not quit solid yet and I don't want to embarass myself with posting ugly code,
but the answer lies in the fact that by using functions that called the
values from current form as you suggested and not trying to create a dynamic
recordset variable and nest quotes in the string statement that would
basically nest a variable, i got it to work. Thanks for the help everyone
and hopefully we can get this one done.

P Troy

Thanks for the input John. I did try this before in the immediate window and
it did work in the immediate window but when I stepped the code in the
procedure itself, it still didn't work, but I will try it again. Maybe I
just didn't have my quotes right or somethign like that. I will let you know.

PS: Sorry didn't back on sooner, but I've been off the PC for a bit. Thanks
Again and I will let you know.

Could you post some the data that you are trying to evaluate?

[quoted text clipped - 27 lines]