Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    coding standards - dot field names (access/vba)

    How to avoid hard-coded field identifiers in my procedure code?


    I hate being dependant on literal values in code. here's a snippet of a Word97VBA template making use of an Access97 database:
    <pre> With rst
    .AddNew
    !Email = strAr(0, intAr)
    </pre>


    I'd like to be rid of the !Email thing and have it up near the head of the code.

    I have essayed briefly with a string constant, to no avail.

    I could make a tiny database-field-access function and place all field-name specific code in there, isolating it to assist future maintenance.

    I worry that when the field names change, as change they will, I'll be left with scattered uses throughout my code, difficult to change.

    I'd rather have a table of definitions up front that make it easy to reference to change.


    I have also realised that where I am looping through an internal array to load fields of an Access table I could use a construct such as rst.Fields(intField) or similar, but that doesn't solve the annoyance of hard-coded identifiers from another application sitting in the middle of my code.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: coding standards - dot field names (access/vba)

    It's early here, Chris, and I'm trying without success to get my head around
    <hr>I'd like to be rid of the !Email thing and have it up near the head of the code<hr>
    and
    <hr>I'd rather have a table of definitions up front that make it easy to reference to change.<hr>
    If you want to write to a particular field, you have to reference that specific field. Of course, if you know the field's index in its parent collection, you can use the number, but you may not want to do that either, since the index can change as well. You could dim a Field variable and set it sequentially to the fields in your recordset, then insert the variable into your code instead of the named field, but I don't see what you'd gain by that.

    Personally, I prefer to see what I'm dealing with rather than have to ferret it out somewhere else in my code when I need to debug it. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: coding standards - dot field names (access/vba)

    Chris,
    I'm not sure I see what your problem is with the rst.fields(intField) syntax (though I guess rst.fields(strFieldName) would be of more use to you)? It enables you to use string constants rather than a hardcoded field name if you really need to.
    Of course, there's always Name AutoCorrect if you upgrade to A2K...... <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> <img src=/S/devil.gif border=0 alt=devil width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: coding standards - dot field names (access/vba)

    > I prefer to see what I'm dealing with rathe

    Me too, but I try to draw the line at literals in program code.

    I don't mind seeing something symbolic that lets me see that I'm moving something into (or out of) "the email field", but I'd rather have the definition of "the email field", what it is, where it is, held in a special place where I can easily make changes by editing one lin eof code (the definition of 'the email field") rather than have to make changes throughout the code - even using Edit ReplaceAll.

    This becomes more important when I'm working in (a) areas new to me or ([img]/forums/images/smilies/cool.gif[/img] areas new to a client. Even more so if I'm sketching out a little prospectus code.

    I have read Rory's response and like the idea of a string constant; I'm going to give that a truy before replying to his suggestion.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: coding standards - dot field names (access/vba)

    > rst.fields(strFieldName) would be of more use to you

    Perfect, Thank you!

    I knew about using string constants as indices in other lists; I hadn't thought about using it in fields of a record set.

    My brain seems to need this kind of a nudge every now and then.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: coding standards - dot field names (access/vba)

    Just be advised that it's about the slowest possible way to reference a field in a recordset. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: coding standards - dot field names (access/vba)

    OK. Thanks. I can stand slow running when I'm processing 100 text files, one by one. It's the business of accommodating changes to the data base that concern me here.

Posting Permissions

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