Results 1 to 10 of 10
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Field names renamed by Lookup Wizard (Access 2003)

    Hi,

    When I use the Lookup Wizard to create a combo box that gets values from another table, the final prompt of the wizard asks if I want to specify a name for the field. If I change the name in the wizard promt I notice that it changes the field name in design view (not the caption name of that field). I have never picked up problems with the rename, so I am assuming that it somehow is just a caption being changed. I want to know how this works and where is it changing it as I do not see the caption change in design view but the actual field name in the top pane of table design.

    Any clarification will be appreciated.
    TX
    Regards,
    Rudi

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

    Re: Field names renamed by Lookup Wizard (Access 2003)

    I don't understand the problem. The name of the field has nothing to do with the lookup - i.e. there is no need whatsoever for the field to have the same name as the lookup field. You can see the SQL statement used for the lookup if you activate the Lookup tab in the lower part of the table design window.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Field names renamed by Lookup Wizard (Access 2003)

    Hans,

    In the attachment I have the last step of the wizard. You will see I am naming it Category Of Product, yet the current field name is Category. When I finish the wizard, the field is now called Category of product. There is no entry in the caption property. Yet even though the name of the field is now Category of product, it has no influences (or there are no concequences in the rest of the database) due to the rename. So my deduction is that it is NOT the field name being changed, but some caption or alias. This is where I need clarification.

    Hope this is clearer??
    Attached Images Attached Images
    Regards,
    Rudi

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

    Re: Field names renamed by Lookup Wizard (Access 2003)

    Access does exactly what you describe before you try to deduct something. You originally named the field Category, then rename it to Category of Product in the Lookup Wizard. When you're finished, the field *is* named Category of Product, as is clearly shown in the bottom part of the screenshot. Where do you get the idea from that the field name is not changed if the evidence is before your eyes? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Field names renamed by Lookup Wizard (Access 2003)

    I can see it is changed in the table, thats not the issue I am raizing though. Say I have an existing DB with various objects. Now I go back to the table and change a field name. Now I have problems in forms and reports and calcs due to renaming a field. but when the wizrds renames the field, there are no problems with references in calcs or forms/reports? So how does this work? Why can the wizard change a field name in a table and not cause naming problems in other objects and calcs.
    Regards,
    Rudi

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

    Re: Field names renamed by Lookup Wizard (Access 2003)

    You normally use the Lookup Wizard to create a NEW field, so there are no queries, forms or reports depending on the field yet, and hence no problems.
    If you apply the Wizard to an existing field, you may run into the same problems as when you manually rename a field.
    (Note: if Name AutoCorrect is on, Access might take care of updating the name in other object. But I'm not sure, since I always turn Name AutoCorrect off)

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Field names renamed by Lookup Wizard (Access 2003)

    Thanks for being so patient with this.

    My PC has office 2007 so I cannot check this at the moment. I ran into this scenario today whilst working in Office 2003. I have not fully tested the issue but spotted this and started to query the context of it on my way home. When I am back at work tomorrow I will try it out in 2003 and verify the actions of Access. I will reply with my findings.

    TX
    Regards,
    Rudi

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Field names renamed by Lookup Wizard (Access 2003)

    Good morning Hans,

    I tested this out in Access 2003 and you have clarified my query. Whether the Lookup wizard changes a field name (based on supplying a label in the last step) or if I change it manually in the table, it is being updated throughout the other object by the "Perform Name AutoCorrect" option. I reran the same test again after switching off the "Perform Name AutoCorrect" option, and all the objects threw up a parameter error because it could not find a field name.

    According to memory, do you know when this feature became available; ie in which version ofAccess did it first appear? And why do you say you disable it? Is it better to have it off, or is it just personal opinion?

    TX
    Regards,
    Rudi

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

    Re: Field names renamed by Lookup Wizard (Access 2003)

    The Name AutoCorrect feature was introduced in Access 2000.

    This feature makes Access perform slower, it is not 100% dependable and it is known to cause various problems, among which making reports lose their page setup, so I always turn Track Name AutoCorrect off in each database. Unfortunately, there is no way to set a default for new databases, it's always on.

    I use Rick Fisher's Find and Replace for Microsoft Access add-in (not free) to perform global name changes in a database.

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Field names renamed by Lookup Wizard (Access 2003)

    Thanks for the advice. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,
    Rudi

Posting Permissions

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