Lookup Field in a Table set to required

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • samdev

    Lookup Field in a Table set to required

    I have set up a table with a few fields that are set to
    required....all work fine except the fields that are Lookup from
    another table.

    What am I forgetting to set?

    Thanks!!
  • Tom van Stiphout

    #2
    Re: Lookup Field in a Table set to required

    On Wed, 19 Mar 2008 22:17:48 -0700 (PDT), samdev
    <laura@noblesof twaresolutions. comwrote:

    What you are forgetting is to tell us more about what is not working.

    Presumably you are speaking about a form, and for the lookup fields
    you have a dropdown that lists the values from the lookup table. So
    far that is pretty standard. How does your implementation differ from
    that?
    You do have 2-column dropdowns, right? A hidden ID value and a visible
    Description value. Several examples of this in the Northwind sample
    application.

    -Tom.


    >I have set up a table with a few fields that are set to
    >required....al l work fine except the fields that are Lookup from
    >another table.
    >
    >What am I forgetting to set?
    >
    >Thanks!!

    Comment

    • samdev

      #3
      Re: Lookup Field in a Table set to required

      On Mar 20, 1:26 am, Tom van Stiphout <no.spam.tom7.. .@cox.netwrote:
      On Wed, 19 Mar 2008 22:17:48 -0700 (PDT), samdev
      >
      <la...@noblesof twaresolutions. comwrote:
      >
      What you are forgetting is to tell us more about what is not working.
      >
      Presumably you are speaking about a form, and for the lookup fields
      you have a dropdown that lists the values from the lookup table. So
      far that is pretty standard. How does your implementation differ from
      that?
      You do have 2-column dropdowns, right? A hidden ID value and a visible
      Description value. Several examples of this in the Northwind sample
      application.
      >
      -Tom.
      >
      >
      >
      I have set up a table with a few fields that are set to
      required....all work fine except the fields that are Lookup from
      another table.
      >
      What am I forgetting to set?
      >
      Thanks!!- Hide quoted text -
      >
      - Show quoted text -
      Sorry....

      I have set the field to required at the Table level and yes it is drop-
      down as you described and it allows me to input a complete record even
      if I have left the lookup field blank. Other fields that I have set to
      required, if I leave blank, Access will not allow me to complete/save
      the record. I need all required fields to work properly - so not allow
      a user to save the record unless all required fields are complete.

      Thx,

      Comment

      • Tom van Stiphout

        #4
        Re: Lookup Field in a Table set to required

        On Wed, 19 Mar 2008 22:29:40 -0700 (PDT), samdev
        <laura@noblesof twaresolutions. comwrote:

        I don't think it quite works that way. You may need to double-check.
        If a field does not allow nulls, it doesn't. It MAY accept an empty
        string, but not null. Whether it is a lookup field or not has no
        bearing on this.
        Do you have a default value on this field?

        -Tom.

        >On Mar 20, 1:26 am, Tom van Stiphout <no.spam.tom7.. .@cox.netwrote:
        >On Wed, 19 Mar 2008 22:17:48 -0700 (PDT), samdev
        >>
        ><la...@nobleso ftwaresolutions .comwrote:
        >>
        >What you are forgetting is to tell us more about what is not working.
        >>
        >Presumably you are speaking about a form, and for the lookup fields
        >you have a dropdown that lists the values from the lookup table. So
        >far that is pretty standard. How does your implementation differ from
        >that?
        >You do have 2-column dropdowns, right? A hidden ID value and a visible
        >Description value. Several examples of this in the Northwind sample
        >application.
        >>
        >-Tom.
        >>
        >>
        >>
        >I have set up a table with a few fields that are set to
        >required....al l work fine except the fields that are Lookup from
        >another table.
        >>
        >What am I forgetting to set?
        >>
        >Thanks!!- Hide quoted text -
        >>
        >- Show quoted text -
        >
        >Sorry....
        >
        >I have set the field to required at the Table level and yes it is drop-
        >down as you described and it allows me to input a complete record even
        >if I have left the lookup field blank. Other fields that I have set to
        >required, if I leave blank, Access will not allow me to complete/save
        >the record. I need all required fields to work properly - so not allow
        >a user to save the record unless all required fields are complete.
        >
        >Thx,

        Comment

        • Larry Linson

          #5
          Re: Lookup Field in a Table set to required


          "samdev" <laura@noblesof twaresolutions. comwrote in message
          news:44349fe0-1a70-40c2-990a-296bf727285d@13 g2000hsb.google groups.com...
          >I have set up a table with a few fields that are set to
          required....all work fine except the fields that are Lookup from
          another table.
          >
          What am I forgetting to set?
          If you truly mean they are defined as LookupFields in the Table, back slowly
          away, making signs to ward off evil.

          Replace the LookupFields with a Foreign Key to the Table where key and value
          for the Lookup is stored. Now, if you insist on playing novice user and
          looking at the datasheet view, you'll see the foreign key value, not the
          descriptive information (but you'll be seeing exactly what is stored) and
          you'll know you can create a query joining the two tables to see the full
          information.

          What you won't get are the "varagies and eccentricities" of Lookup Fields
          which store one value and display another... for anyone who's not doing a
          novice-level database and needs to flatten their files in the tables, Lookup
          Fields end up being more trouble than they are worth, and should be replaced
          with Lookup Tables, as I described above..

          Larry Linson
          Microsoft Office Access MVP


          Comment

          • Ron2006

            #6
            Re: Lookup Field in a Table set to required

            Be sure to heed Larry's advice. It will save you headaches and
            bottles and bottles of asprin.

            Comment

            • samdev

              #7
              Re: Lookup Field in a Table set to required

              On Mar 20, 3:58 pm, "Larry Linson" <boun...@localh ost.notwrote:
              "samdev" <la...@noblesof twaresolutions. comwrote in message
              >
              news:44349fe0-1a70-40c2-990a-296bf727285d@13 g2000hsb.google groups.com...
              >
              I have set up a table with a fewfieldsthat are set to
              required....all work fine except thefieldsthat are Lookup from
              another table.
              >
              What am I forgetting to set?
              >
              If you truly mean they are defined as LookupFields in the Table, back slowly
              away, making signs to ward off evil.
              >
              Replace the LookupFields with a Foreign Key to the Table where key and value
              for the Lookup is stored. Now, if you insist on playing novice user and
              looking at the datasheet view, you'll see the foreign key value, not the
              descriptive information (but you'll be seeing exactly what is stored) and
              you'll know you can create a query joining the two tables to see the full
              information.
              >
              What you won't get are the "varagies and eccentricities" of LookupFields
              which store one value and display another... for anyone who's not doing a
              novice-level database and needs to flatten their files in the tables, LookupFieldsend up being more trouble than they are worth, and should be replaced
              with Lookup Tables, as I described above..
              >
               Larry Linson
               Microsoft Office Access MVP
              Thanks - sorry for all the confusion - I am using lookup tables! I'm
              still having some issues but most I have worked out.

              Comment

              • Ron2006

                #8
                Re: Lookup Field in a Table set to required

                The question is not the use of lookup tables, but rather using the
                lookup tables option in the actual table as opposed to using a lookup
                query/table for a dropdown on a form.

                Comment

                Working...