Results 1 to 6 of 6
  1. #1
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Dynamic Field Names in VBA (2000/2002)

    I think I may be trying to do something that requires code to modify itself, but thought I would throw it out.
    I'm attempting to take a number of fields from a record in a DAO recordset and write the contents out to a Word document. As I am trying to generalize it, I thought I would create a table that had all the field names in it and run it through a loop. But I can figure out how to refer to the value of a field using a variable for the field name. The statement that has me stumped looks like this:
    <font color=blue><pre>Do Until myFields.EOF = True
    myFieldName = myFields!PIQCategory
    myDoc.Bookmarks(myFields!PIQCategory).Select
    objWord.Selection.TypeText DLookup("[PIQResponse]", "tblPIQResponse", "[PIQCategory]= " _
    & myFields!PIQCategory & " And [PIQResponseNumber]= " & myRec!myFieldName)
    Loop</pre>

    </font color=blue>
    The challenge seems to be find a way to modify the value of the DLookup function parameters. Any suggestions?
    Wendell

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Field Names in VBA (2000/2002)

    You can probably use the syntax myRec.Fields(myFieldName), or do you mean something different?

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Dynamic Field Names in VBA (2000/2002)

    I'll go back and try that approach again - for some reason it didn't seem to want to work, but I may well have messed the syntax up.
    Wendell

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Dynamic Field Names in VBA (2000/2002)

    Or would myRec(myFieldName) be correct?

    BTW, what is myRec?

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Dynamic Field Names in VBA (2000/2002)

    That did the trick - though I couldn't do it all on one line - I had to have a separate line determine the value and use that in the DoLookup. Thanks for helping me through the brick wall again.
    Wendell

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Dynamic Field Names in VBA (2000/2002)

    No - I actually tried that several different ways. Sorry I was being pretty cryptic - myRec is/was a DAO Recordset. And Hans nailed it - for what ever reason, I couldn't get the .Fields Help to given anything other than methods.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •