Eval is a function that will convert equations stored as text to actual formulas that will calculate. For instance, a field (or a variable in VBA) has string "100 * 0.5". Eval([fieldname]) or Eval(x) will return 50.
It cannot resolve table/query/form/report references embedded within strings, they are just text characters.
Why do you need to do this? Where are you using this expression? What are you really trying to accomplish? Sample data could be helpful.
As I cannot attach a conditional formatting on a Table, I need an abstract function to chech if a set of records or all records have errors inside, and show these errors into forms and/or reports.
Because, to achieve this goal in the 'standard' mode, I have to define the rule for a field of a table every time I use that field in a control or report, and this means the need to repeate the same things an annoying lot of times, not to tell about introducing errors and resulting in a maintenance nightmare.
So, my idea is to define all the check for all the tables and their rows in an CheckError-table, like the following fragment related to the table 'Persone':
Code:
[FONT="Courier new"][COLOR="Red"][B]TableName | FieldName | TestNumber | TestCode | TestMessage | ErrorType[/B][/COLOR]
Persone | CAP | 4 | len([CAP]) = 0 or isnull([cap]) | CAP mancante | warning
Persone | Codice Fiscale | 1 | len([Codice Fiscale]) < 16 | Codice fiscale nullo o mancante | error
Persone | Data di nascita | 2 | (now() - [Data di nascita]) < 18 * 365 | Minorenne | info
Persone | mail | 5 | len([mail)] = 0 or isnull([mail] | email mancante | warning
Persone | mail | 6 | (len([mail)] = 0 or isnull([mail]) | richiesto l'invio dei referti via e- mail, | error
| | | and [modalità ritiro referti] = ""e-mail"" | ma l'indirizzo e-mail è mancante |
Persone | Via | 3 | len([Via]) = 0 or isnull([Via]) | Indirizzo mancante | warning[/FONT]
Now, in each form or report which use the table Persona, I want to set an 'onload' property to a function
Code:
< ' to validate all fields in all rows and set the appropriate bg and fg color
Private Sub Form_Open(Cancel As Integer)
Call validazione.validazione(Form, "Persone", 0)
End Sub
' to validate all fields in the row identified by ID and set the appropriate bg and fg color
Private Sub Codice_Fiscale_LostFocus()
Call validazione.validazione(Form, "Persone",[COLOR="red"] ID[/COLOR])
End Sub
So, the function validazione, at a certain point, as exactly one row for the table Persone, and the set of expressions described in the column TestCode above.
Now, I need to logically evaluate the TestString against the table row, to obtain a true or a false.
If true, I'll set the fg and bg color of the field as normal
if false, I'll set the the fg and bg color as per error, info or warning, as defined by the column ErrorType above.
As I cannot attach a conditional formatting on a Table, [...]
Because, to achieve this goal in the 'standard' mode, I have to define the rule for a field of a table every time I use that field in a control or report, [...]
If you carefully read your own statement above, it might lead you to a solution.
Create the conditional formatting (by code) on the form (or report) you are displaying. If the relevant fields are in the record source of the form you can use your expression ("TestCode") in the condition without any further need to evaluate the string expression.
@June7, good answer, thank you.
I solved quite all, except a little thing. I googled all the day without an answer.
Given the following
<function x (aForm as Form)>
how can I access by name same of the controls belonging to aForm?
This is the code
Code:
Sub setFormats(aForm As Form)
Dim TableName As String, t() As String, ErrSQL As String, ctlName As String
Dim ErrRst As DAO.Recordset, FormCtl As Control, ctl As Variant
Dim frmtCount As Integer, Cnt As Integer
Dim fcdSource As FormatCondition, fcdDestination As FormatCondition
Dim varOperator As Variant, varType As Variant, x As Variant
Dim varExpression1 As Variant, varExpression2 As Variant
Dim intConditionCount As Integer, intCount As Integer
If Len(aForm.Tag) > 0 And Mid$(aForm.Tag, 1, 1) = "§" Then
t = Split(aForm.Tag, "§", 1)
If Len(t(0)) > 0 Then TableName = t(0)
TableName = Replace(TableName, "§", "")
ErrSQL = "SELECT * FROM [Q Errori per tabella] WHERE ([TableName] = """ & TableName & """);"
Set ErrRst = CurrentDb.OpenRecordset(ErrSQL, , dbReadOnly)
If ErrRst.EOF Then Exit Sub
With ErrRst
.MoveFirst
Do Until .EOF
x = ErrRst.Fields.Count
For Each x In ErrRst.Fields
Debug.Print x.Name, x
On Error GoTo fine
[COLOR="Red"] FormCtrl = aForm.ControlName("Codice fiscale")
FormCtrl = aForm.ControlName(x)
FormCtrl = .Controls.Item(x)
[/COLOR]
On Error GoTo 0
If FormCtl.ControlType = acTextBox Or FormCtl.ControlType = acComboBox Then
' Add the FormatCondition
Cnt = FormCtl.FormatConditions + 1
FormCtl.FormatConditions.Add acExpression, , .Fields.Item(Cnt).Value
' Reference the FormatCondition to apply formatting.
' Note: The FormatCondition cannot be referenced
' in this manner until it exists.
Set fcdDestination = ctl.FormatConditions.Item(Cnt).Value
With FormCtl.FormatConditions.Item(Cnt)
.BackColor = Eval("RGB" & ErrRst.item("Background").value)
.ForeColor = Eval("RGB" & ErrRst.item("pen").value)
End With
End If
fine: On Error GoTo 0
Next x
.MoveNext
Loop
End With
End If
I worked with VBA between 1991 and 1995, then I passed to other languages, more modern.
I didn't remember how complicated VBA was, otherwise I have chosen whatever else! The on error goto label is unwatchable
:banghead:
pdipietro is passing aForm as a formal argument to his subroutine. He doesn't need to use Forms!aForm.name-of-control because he can, in that subroutine, use aForm.name-of-control directly; i.e. without the prefix. In that subroutine, aForm is already a form object that has been instantiated by the call sequence. He didn't instantiate the argument as a form name but rather as a form OBJECT.
HOWEVER, pdipietro: You have a SERIOUS flow error in the subroutine you showed us. It is possible to enter the code segment referred to by label Fine: both by falling through from the prior statement, the end of the With block that starts with With FormCtl.FormatConditions.Item(Cnt) AND by an On Error GoTo fine statement from an earlier part of the same routine. This CANNOT happen.
The reason is that whatever you call via an On Error statement triggers as a software-generated TRAP, whereas a flow-through is not a triggered situation. An error routine must be terminated by a Resume destination statement (similar to a GoTo) or a simple Resume Next (similar to a Return). That is because a TRAP builds a context on the stack because it is like an interrupt. It has to remember what it was doing when the interrupt occurred.
The problem is the other option. If you "fell into" code that terminated the trap correctly, either of the possible Resume syntaxes would get VERY confused because in that case there would be no TRAP pending. The Resume would attempt to unwind a TRAP context that wasn't there. And of course, in the "fall through" case you didn't BUILD a context because there was no error that led you there.