Database field as condition in IF statement 1

[MattN]

I want to use a database field as a condition in an IF statement.

This has obvious maintenance benefits to the program as I can add conditions and msgbox strings to the recordset without having to update the program.

For Example;-

rs_Conditions contains the following records

- Database Field Condition MsgboxString
- Record1 "txtOrder = 10" "Value Too High"
- Record2 "txtOrder = 4 "Value Too Low"

Can anyone tell me what the syntax is for the IF statement as I keep getting Type Mismatch errors.

Example;-

If rs_Conditions!Condition Then msgbox rs_Conditions!MsgboxString

This would really help me a lot.

King Regards

MattN

 

[vb5prgrmr]

MattN, have you read FAQ222-2244 item 15 yet?

If you are wanting to make a database driven dynamic program as you are describing you are going to need to add more information to your database. In your example you are wanting to evaluate a controls contents to a value and then notify the user based upon that evaluation, but what if the value entered into the control does not meet any criteria that you have entered.

So with that said I would think you would need to add a field or two ...
[tt]
ID ControlName Operation Value Action Message
1 txtOrder Equals 10 Notify Value too high
[/tt]
Now with that information you can create a loop that finds the control and gets a reference to it. Then you can use that reference to figure out the operation and compare the referenced control to the value. Then if that returns true you can then take the action prescribed.
[tt]
For Each C In Me.Controls
If C.Name = rs.Fields("ControlsName&quot.Value Then
...
[/tt]
There are of course other fields that you can add to even further enhance your flexibility but it will take you some more forethought in what you actually want to do.

Good Luck

 

[Golom]

Your rs_Conditions!Condition database field needs to be a Boolean (i.e. Type Yes/No) for this to work ... but it's a string. You're getting the type mismatch because VB can't convert "txtOrder = 10" to a Boolean.

If the field is always "txtOrder" and the comparison operator is always "=" then just store the value (e.g. "10&quot in "Condition" and use

   If txtOrder = Val(rs_Conditions!Condition) Then ...

What you are attempting to do is dynamically build a VB statement that is not defined until run-time and that's not supported.

 

[vb5prgrmr]

MattN, have you read FAQ222-2244 item 15 yet?

Did either Golom or I help in any way? Do you have any more questions that you need to ask to clairify our answers or to further define your question?

 

[MattN]

I would have liked to be able to store the crieria contents of an IF statement in a string but it seems that Golom's response answers my question - VB does not support this feature (unless anyone knows any different?)
============================================================
strCriteria = "txt1.text = 1"

if strCriteria then msgbox "YIPPEE!"

(if the value in txt1 = 1 then display message box "YIPPEE!)
============================================================

This is a real pity as this would have given mass flexability.

Thanks for your help anyway!

Regards

Matt Norris

 

[vbSun]

I think you can do it in another way. Try this db example and code.

CtrlName Value Operator MessageBoxString
txtOrder 10 = "Value Too High"

Function getText(frm As Form, strTxtName As String) As String

Dim ctrl As Control

For Each ctrl In frm.Controls
If TypeOf ctrl Is TextBox Then
If ctrl.Name = strTxtName Then
getText = ctrl.Text
Exit Function
End If
End If
Next
End Function

And while checking...

If getText(rst.Fields("CtrlName&quot) = rst.Fields("Value&quot Then
MsgBox rst.Fields("MessageBoxString&quot
End If

Also please note that i had included the Operator Field in the db, so you should be able to check the operator in a select cse statment to code you if statement, just in case you want to extend this.

------------------------------------------
The faulty interface lies between the chair and the keyboard.