Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Referring to another field in the same table

Discussion in 'Information Technology' started by Marianne, Jul 28, 2006.

  1. Marianne

    Marianne
    Expand Collapse
    Guest

    Is it possible to refer to a field in another table to set the default value?

    I am trying to set up a default value for a field in a table. I am referring
    to another field in a different table. This is my calc:

    =("AB" &[Table1]![Field2]& (Format(Date(),"mmyy")))

    I receive this error when trying to save the table:

    Could not find field "[Table1]![Field2]. (Error 3799)
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    No. That won't work.

    Access assigns the Default Value *before* any of the fields have received an
    entry, so you cannot use the entry in another field as your default value.

    Instead, you could use the AfterUpdate event of each control on your form
    that this depends on.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Marianne" <Marianne@discussions.microsoft.com> wrote in message
    news:88CC45B1-E2CA-489B-B14E-86999D8E0FFF@microsoft.com...
    > Is it possible to refer to a field in another table to set the default
    > value?
    >
    > I am trying to set up a default value for a field in a table. I am
    > referring
    > to another field in a different table. This is my calc:
    >
    > =("AB" &[Table1]![Field2]& (Format(Date(),"mmyy")))
    >
    > I receive this error when trying to save the table:
    >
    > Could not find field "[Table1]![Field2]. (Error 3799)
     
  4. fredg

    fredg
    Expand Collapse
    Guest

    On Mon, 24 Jul 2006 08:19:02 -0700, Marianne wrote:

    > Is it possible to refer to a field in another table to set the default value?
    >
    > I am trying to set up a default value for a field in a table. I am referring
    > to another field in a different table. This is my calc:
    >
    > =("AB" &[Table1]![Field2]& (Format(Date(),"mmyy")))
    >
    > I receive this error when trying to save the table:
    >
    > Could not find field "[Table1]![Field2]. (Error 3799)


    You would use DLookUp.
    I'll guess that Table1 has only the one record, as I don't see any
    criteria in your sample.

    ="AB" & DLookUp("[Field2]","Table1") & Format(Date(),mmyy"))

    If the value of Field2 is 678 the result will appear as AB6780706.

    --
    Fred
    Please respond only to this newsgroup.
    I do not reply to personal e-mail
     

Share This Page