Can't Print Calculated FIeld Expression's Value to the Microsoft Access Immediate Window-CodePudding

Folks:

Did a little Microsoft Access DAO stuff to create a [FullName] Calculated Field (cFld As Field2 object) in one of my tables (tblEmpls) as a concatenation of [FirstName] and [LastName] in the table via the statement: cFld.Expression = "[FirstName] & ' ' & [LastName]".

Then used the TableDef.Fields.Append method on the cFld object to add it to the table. All good.

Now, when I try to print that expression out to the Immediate window via 'Debug.Print "The FullName field is: " & cFld.Expression', the Immediate window output is: "The Calculated Field is: [FirstName] & ' ' & [LastName]", not say, "The Calculated Field is: David Bailey" like I want.

Er, I guess I need a way to syntactically 'dereference' my cFld.Expression object so it's value not its name get printed out in the Access VBA's Immediate window? I don't use he Immediate window much at all, so I really don't know its ins and outs well at all.

By the way, the fields in the database appear as expected: [FirstName]: "David": [LastName]: "Bailey": and [FullName]: "David Bailey"

Okay, now here comes the silly question: How do I get the concatenated [FullName] value, say, "David Bailey" to print in the Immediate window, and not the field names construct: "[FirstName] & ' ' & [LastName]". Searched around quite a bit, but have not uncovered anything to alleviate my 'ignorance' as yet. (:>)

Thanks in advance for your help. Cheers,

David

P.S Thanks Micke. The TableDef object created the Calculated Field in the table, but yes I needed a RecordSet object for iteration through the table's records. Minimum set of code showing this ...

Sub AddCFld()
    Dim db As Database
    Dim tdef As TableDef
    Dim rst As Recordset2
    Dim fld As Field2
    Dim strFldName As String
    Set db = CurrentDb
    strFldName = "FullName"
    Set tdef = db.TableDefs("tblEmpls")
    On Error Resume Next
    Set fld = tdef.Fields(strFldName)
    On Error GoTo 0
    If Not fld Is Nothing Then
        Debug.Print "Oops, the Calculated Field already exists: " & strFldName
        Set fld = Nothing
        Set tdef = Nothing
        Exit Sub
    End If
    Set fld = tdef.CreateField(strFldName, dbText, 200)
    fld.Expression = "[FirstName] & ' ' & [LastName]"
    Debug.Print "The Calculated Field expression is: " & fld.Expression
    tdef.Fields.Append fld
    Set rst = db.OpenRecordset("tblEmpls")
    Do While Not rst.EOF
        Debug.Print "The Calculated Field value is: " & rst!FullName
        rst.MoveNext
    Loop
    db.Close
    Set fld = Nothing
    Set tdef = Nothing
End Sub

Immediate window output ...

AddCFld
The Calculated Field expression is: [FirstName] & ' ' & [LastName]
The Calculated Field value is: Nancy Davolio
The Calculated Field value is: Andrew Fuller
The Calculated Field value is: Janet Leverling
The Calculated Field value is: Margaret Peacock
The Calculated Field value is: Steven Buchanan
The Calculated Field value is: Michael Suyama
The Calculated Field value is: Robert King
The Calculated Field value is: Laura Callahan
The Calculated Field value is: Anne Dodsworth

CodePudding user response：

Ok, changed my answer a little bit :)

In the Immediate window you should use .value instead of .Expression

Debug.Print "The FullName field is: " & rs!Fullname.Value

Here is an example code:

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
    ' Loop Recordset with Do-Until
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("tblEmpls")
    Do Until rs.EOF
        Debug.Print "The fullName value is: " & rs!FullName.Value
        rs.MoveNext
    Loop
    rs.Close
End Sub

CodePudding user response：

Um, thanks very much, Micke. I guess I was trying to get the TableDef object to do what the RecordSet object was designed for! Oops! The TableDef object created the Calculated Field in the table, but yep I needed a RecordSet object for iteration through the table's records and print out the Calculated Fields to the Access Immediate window. Ignorance now assuaged! (:>) Minimum set of code showing this ...

Sub AddCFld()
    Dim db As Database
    Dim tdef As TableDef
    Dim rst As Recordset2
    Dim fld As Field2
    Dim strFldName As String
    Set db = CurrentDb
    strFldName = "FullName"
    Set tdef = db.TableDefs("tblEmpls")
    On Error Resume Next
    Set fld = tdef.Fields(strFldName)
    On Error GoTo 0
    If Not fld Is Nothing Then
        Debug.Print "Oops, the Calculated Field already exists: " & strFldName
        Set fld = Nothing
        Set tdef = Nothing
        Exit Sub
    End If
    Set fld = tdef.CreateField(strFldName, dbText, 200)
    fld.Expression = "[FirstName] & ' ' & [LastName]"
    Debug.Print "The Calculated Field expression is: " & fld.Expression
    tdef.Fields.Append fld
    Set rst = db.OpenRecordset("tblEmpls")
    Do While Not rst.EOF
        Debug.Print "The Calculated Field value is: " & rst!FullName
        rst.MoveNext
    Loop
    db.Close
    Set fld = Nothing
    Set tdef = Nothing
End Sub

Immediate window output ...

AddCFld
The Calculated Field expression is: [FirstName] & ' ' & [LastName]
The Calculated Field value is: Nancy Davolio
The Calculated Field value is: Andrew Fuller
The Calculated Field value is: Janet Leverling
The Calculated Field value is: Margaret Peacock
The Calculated Field value is: Steven Buchanan
The Calculated Field value is: Michael Suyama
The Calculated Field value is: Robert King
The Calculated Field value is: Laura Callahan
The Calculated Field value is: Anne Dodsworth