Results 1 to 6 of 6
  1. #1
    Star Lounger PStepanas's Avatar
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    90
    Thanks
    4
    Thanked 2 Times in 2 Posts

    Reference a field in a table design (2000)

    I know this is probably really elementary, but Access's help is useless as a reference and I just don't use the program often enough to remember such details.

    What I want to do is create a Number field in a *table* design whose Default Value is 10 times another field (type: AutoNumber).

    The help system explains how to do this in a form, but not in a table.

    Typing in [ID]*10 into the Default Value property (where ID is the name of the AutoNumber field) generates an error on saving the design: "The database engine does not recognize either the field 'ID' in a validation expression, or the default value in the table 'xxx'."

    Any help would be greatly appreciated, both specific to this problem, and that might help me more easily solve or understand these types of problems in future.

    Thanks,
    Paulius

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

    Re: Reference a field in a table design (2000)

    It isn't elementary, it's impossible. The help files have become especially mute on this in Access 2000, but the fact is that you can't use another field in the table in the default value of a field. You can only reference an external object or a built-in function. No user-defined functions and no other fields in the same table.
    Charlotte

  3. #3
    Star Lounger PStepanas's Avatar
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    90
    Thanks
    4
    Thanked 2 Times in 2 Posts

    Re: Reference a field in a table design (2000)

    Well, that explains that. I won't ask why Microsoft decided to do things this way, although I can almost begin to understand. Perhaps all the default values have to be calculated *before* any values (such as an AutoNumber) are assigned.

    I guess I can get around this by building a reference into my data entry form (something like tblXxx!ID*10), but could I do the same thing using a second table? And is my syntax then correct?

    Thanks again,

    Paulius

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

    Re: Reference a field in a table design (2000)

    The problem is that you don't have any way to specifically reference a field. What you really want to do is say, "give me the value in that field in this same record." But table design doesn't know about records, nor does it know about fields in other tables. You can do that in a query but not in a table in design view. This is the sort of thing you can do in SQL Server with triggers but in Access, you have to do it from the form or query you use to populate the table. In a form, you can set a default value of a control bound to that field and make the default value equal the value in another control or some other expression. That would be your best workaround for this.
    Charlotte

  5. #5
    Star Lounger PStepanas's Avatar
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    90
    Thanks
    4
    Thanked 2 Times in 2 Posts

    Re: Reference a field in a table design (2000)

    I'm very grateful for such an excellent explanation!

    If only the documentation provided such information. But then, I guess, the Lounge would be out of a job!

    Thanks again,

    Paulius

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

    Re: Reference a field in a table design (2000)

    Well, you got the non-scientific answer, so there's a discount on your Lounge dues for that! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Charlotte

Posting Permissions

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