Refer to user-defined type elements via variables?

Posted on 2010-01-04
Last Modified: 2013-11-29
I have a user-defined type:

Public Type TaxRecord
  Tax1 as Long
  Tax2 as Long
  Tax3 as Long
  Tax4 as Long
  Tax100 as Long
End Type

If I create a variable of type TaxRecord, such as:

Dim MyTax as TaxRecord

How can I refer to the ELEMENTS of the UDT via variables - or can I do that at all?

I would like to be able to do something like this:

For ctr = 1 to 100
  varElement = "MyTax.Tax" & ctr
  Debug.Print Eval(varElement)
Next ctr

But that does not work for me - I get an error "Microsoft Access cannot find the name MyTax".  In my experience with Access, Eval() rarely works at all for anything.

Is there any way I can refer to the elements of a UDT via variables?

Thanks in advance for your help.
Question by:smiley_strat
    LVL 84
    You might try this:

    varElement = MyTax.Tax & Cstr(ctr)

    Author Comment

    Changing the ctr to a string did not work.  The Eval() still comes back with "Microsoft Office Access cannot find the name 'MyTax' you entered in the expression"

    Yet MyTax is absolutely defined as being of type TaxRecord and I can refer to it elsewhere in the code with no problems.
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Hello smiley_strat,

    Instead of a user defined type, why not just use an array dimensioned 1 to 100?  Then you can refer to any
    given element rather easily by passing the index...


    LVL 84
    What is your overall goal of using this? Perhaps there is an easier way, as Patrick has pointed out.

    Author Comment

    The Tax1, Tax2, .... TaxN fields are actually only part of the UDT I'm trying to work with.  The UDT matches fields in a table, and in order to streamline the I/O process I am attempting to use the UDT.

    I know that using a Recordset object would allow me to use the Fields collection, in which case I could refer to .Fields("Tax" & ctr) and that would work fine.

    However, I inherited this code to some extent and am trying to make use of the UDTs, if possible.
    LVL 84
    I see.

    I've always used UDTs as nothing more that a data structure within a class, but I'm sure others have different experiences with them. I would be surprised if you could access them dynamically, as you're trying to do, but I could be wrong. A UDT doesn't expose an "item" collection, so I don't see any way to do it, but then I've never really gotten that deep into them.
    LVL 92

    Accepted Solution


    I've never been a fan of UDTs.  You can usually accomplish the same thing with classes.

    In any event, if the UDT contains other members beside Tax1, Tax2, ..., TaxN, could you recast it so that one
    member of the UDT is an array containing Tax1, Tax2, ..., TaxN?


    Author Comment

    matthewspatrick: That sounds like a workable idea, and unless it turns out that there really is a way to refer to the elements of a UDT variably (though it looks like that just isn't possible) I will use your suggestion as the solution.  But I'll leave the question open a little longer to see if any other potential solutions come along.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now