Reference a user defined type with a variable.

[ToMonkey]

I am writing some code where I want to reference a user defined type via a fieldname which is held in a variable.

Does anyone know how to do this i.e.

Type TestType
desc as string
code as string
End Type

Dim FldName as String
Dim AUDT as TestType

FldName = "desc"

[AUDT.FldName] = "Description"

instead of

AUDT.desc = "Description"

I am using an array to hold a list of field names which I want to use to reference data held in my user defined type.


Any Ideas????

 

[vbaInet]

It's similar to trying to reference a variable by text. You could try using the Eval() function but that means you will need to create a function for it to work.

Your best best is to use a Collection or Dictionary object.

 

[ToMonkey]

How would I go about doing this, I am trying to develop some dynamic forms/tables but without being able to do this it just won't work.

 

[ToMonkey]

Here is the code

Public Function BlankSTlRecord(STlData As SEF3BlkQltData) As SEF3BlkQltData

Dim intIndex As Integer
Dim FldName As String

For intIndex = LBound(STLQltDataStructure) To UBound(STLQltDataStructure)

FldName = "STlData." & STLQltDataStructure(intIndex).FieldName

Select Case STLQltDataStructure(intIndex).FieldType
Case "String"
'Let [STlData.STLQltDataStructure(intIndex).FieldName] = ""
Let Eval(FldName) = ""
Case "Number"
'Let [STlData.STLQltDataStructure(intIndex).FieldName] = 0
Let Eval(FldName) = 0

Case "Boolean"
'Let [STlData.STLQltDataStructure(intIndex).FieldName] = False
Let Eval(FldName) = False
Case "Date"
'Let [STlData.STLQltDataStructure(intIndex).FieldName] = NullDate()
STlData.d3cdate = NullDate() '<===== This bit works
Let Eval(FldName) = NullDate() ',====== This fails but it has the right name in
End Select

Next intIndex

'This is a flag and not part of the recordset
STlData.exists = False
BlankSTlRecord = STlData

End Function

 

[vbaInet]

Eval() always a string or a numeric value. You don't use Eval() to assign a value to a control that's passed to it.

Let Me.Controls(FldName) = NullDate()

Fyi, Let is optional

 

[gemma-the-husky]

i just tried a few things, and I couldn't find a way to do this directly.

the only use seemed to be the . construct

-----
what about actually implementing the structure as a class. is that more likely to work?

i am sure vbaInet will know the answer .....

 

[vbaInet]

How would I go about doing this, I am trying to develop some dynamic forms/tables but without being able to do this it just won't work.

E.g., aircode:

Dim dict As Scripting.Dictionary

Set dict = New Scripting.Dictionary

dict.Add "desc", "Description"

' To amend the value
dict.Item FldName = "New Value"

what about actually implementing the structure as a class. is that more likely to work?

You can't declare a Type in a Class Module, one of its shortcomings.

 

[vbaInet]

And here's the function (or sub) I was referring to:

Private Audt As TestType
Type TestType
    desc As String
    code As String
End Type


Public Sub SomeSub()
    Dim FldName As String

    FldName = "desc"
    
    Application.Run "Audt" & "_" & FldName, "Description"
    
    Debug.Print Audt.desc
End Sub


Public Sub Audt_desc(strDesc As String)
    Audt.desc = strDesc
End Sub