How to evaluate a string containing column names? (1 Viewer)

pdipietro

New member
Local time
Today, 19:10
Joined
Apr 12, 2019
Messages
8
In a MS-Access 2016 DB VBA, I have the following:

1: A table T

2: an expression containing References to table T fields like the following one

> len([Codice Fiscale]) < 16

3: an eval function
> eval ("len([Codice Fiscale]) < 16")

correctly returning 'Impossible to find the name [Codice Fiscale]' because there is no connection between the eval string and the table T

Q: How can I eval it?

:banghead:
 

June7

AWF VIP
Local time
Today, 10:10
Joined
Mar 9, 2014
Messages
5,423
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.
 

pdipietro

New member
Local time
Today, 19:10
Joined
Apr 12, 2019
Messages
8
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.

-----------------------------------------------------------------------------------

All the above is easy, ready, and running, except for the red statement above:

How can I evaluate the teststring against the table row, to obtain a result?

Thank yiu

Paolo

:banghead:
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 18:10
Joined
Feb 19, 2013
Messages
16,553
based on this example

eval ("len([Codice Fiscale]) < 16")

not tested but you could try substitution (replace)

e.g. this should work

eval ("len(" & [Codice Fiscale] & ") < 16")

so if you modify your testcode to say

len([fldName]) <16

you would then modify your eval to

eval (replace(testcode,"[fldName]", fldName))
 

sonic8

AWF VIP
Local time
Today, 19:10
Joined
Oct 27, 2015
Messages
998
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.
 

pdipietro

New member
Local time
Today, 19:10
Joined
Apr 12, 2019
Messages
8
@CJ_London: your suggestion run, but not with date. But it helped me.
 

pdipietro

New member
Local time
Today, 19:10
Joined
Apr 12, 2019
Messages
8
I need just a little hint to solve the problem:

How can I set the conditionalProgramming rules by code?
 

pdipietro

New member
Local time
Today, 19:10
Joined
Apr 12, 2019
Messages
8
@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:
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:10
Joined
Feb 19, 2013
Messages
16,553
you refer to controls in another form like this

FormCtrl = Forms!aForm.[Codice fiscale]

or

FormCtrl = Forms!aForm("Codice fiscale")

or

FormCtrl = Forms!aForm.controls("Codice fiscale")

having spaces in field names is never a good idea. Also aForm needs to be open
 

pdipietro

New member
Local time
Today, 19:10
Joined
Apr 12, 2019
Messages
8
@CJ_London
The onl6 difference between your suggestion and my attempts, is that I didn’t prefix the statement with Forms!

This is because The function received aForm as Form that represent the current opened form.

I’ve no way to access my computer for the next10 days. Do you think this could be the reasons? It sound really strange to me ....

Anyway, as soon as I’ll be hand over again, I’ll test the solution suggested.

Many thanks.

Paolo
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:10
Joined
Feb 28, 2001
Messages
26,999
CJ - I believe your advice was in error.

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.
 

Users who are viewing this thread

Top Bottom