Combo box in table definition

Discussion in 'Microsoft Access Database Table Design' started by Guest, Oct 17, 2007.

  1. Guest

    Guest Guest

    I have a table where the values in a field can be picked from a list - but
    this list depends on the value in another field in the same table.

    Example: I have a field called "kids". I can select a kid-value from a list
    of kids - but I only want to select among the kids in the "family" - i.e. I
    have a field specifying the family. This selection should limit my list of
    kids to only the reæevant ones.

    Can I set up a combo box where this relation is implemented?

    Alternatively I create a form with this logic - but at this stage it would
    be easier to do without.
     
    Guest, Oct 17, 2007
    #1
    1. Advertisements

  2. Guest

    Jeff Boyce Guest

    Lookup FIELDS, bad ... lookup TABLES, good (to paraphrase a colleague...)

    It sounds like you are working directly in the table(s). This is almost a
    necessity in Excel, but Access is a relational database. Access tables
    store data, Access forms (and reports) display it.

    Do the "lookup" work in your forms, using a combo box THERE.

    The major problem most folks encounter with trying to use lookup fields in
    tables is that what is stored does not match what is displayed ... this
    causes considerable confusion.

    Good luck!

    --
    Regards

    Jeff Boyce
    www.InformationFutures.net

    Microsoft Office/Access MVP
    http://mvp.support.microsoft.com/

    Microsoft IT Academy Program Mentor
    http://microsoftitacademy.com/

    "RSunday" <> wrote in message
    news:...
    > I have a table where the values in a field can be picked from a list - but
    > this list depends on the value in another field in the same table.
    >
    > Example: I have a field called "kids". I can select a kid-value from a

    list
    > of kids - but I only want to select among the kids in the "family" - i.e.

    I
    > have a field specifying the family. This selection should limit my list of
    > kids to only the reæevant ones.
    >
    > Can I set up a combo box where this relation is implemented?
    >
    > Alternatively I create a form with this logic - but at this stage it would
    > be easier to do without.
     
    Jeff Boyce, Oct 17, 2007
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    You are right about the confusion when one value is shown an another is
    actually displayed. But right now I am just prototyping a data structure -
    and once I have it right I'll make all the forms for data entry and display
    etc. - but right now I just want something that works.

    But I found out that I can just add data in the table views - and use the
    relations defined between the tables - i.e. open a child table by clicking
    the "+" to expand the hiearchy. When I add records in a child-table, the
    relations to the parent is automatically made.

    Rsunday

    "Jeff Boyce" wrote:

    > Lookup FIELDS, bad ... lookup TABLES, good (to paraphrase a colleague...)
    >
    > It sounds like you are working directly in the table(s). This is almost a
    > necessity in Excel, but Access is a relational database. Access tables
    > store data, Access forms (and reports) display it.
    >
    > Do the "lookup" work in your forms, using a combo box THERE.
    >
    > The major problem most folks encounter with trying to use lookup fields in
    > tables is that what is stored does not match what is displayed ... this
    > causes considerable confusion.
    >
    > Good luck!
    >
    > --
    > Regards
    >
    > Jeff Boyce
    > www.InformationFutures.net
    >
    > Microsoft Office/Access MVP
    > http://mvp.support.microsoft.com/
    >
    > Microsoft IT Academy Program Mentor
    > http://microsoftitacademy.com/
    >
    > "RSunday" <> wrote in message
    > news:...
    > > I have a table where the values in a field can be picked from a list - but
    > > this list depends on the value in another field in the same table.
    > >
    > > Example: I have a field called "kids". I can select a kid-value from a

    > list
    > > of kids - but I only want to select among the kids in the "family" - i.e.

    > I
    > > have a field specifying the family. This selection should limit my list of
    > > kids to only the reæevant ones.
    > >
    > > Can I set up a combo box where this relation is implemented?
    > >
    > > Alternatively I create a form with this logic - but at this stage it would
    > > be easier to do without.

    >
    >
     
    Guest, Oct 17, 2007
    #3
  4. Guest

    Jeff Boyce Guest

    I'll try once more ... do your work with the data in forms, not in tables.
    The subdatasheet ("+") feature, like the lookup field, encourages 'sheet
    thinking, and will only make it harder to make the transition over to Access
    as a not-spreadsheet tool.

    I'm not saying not to spend time in tables, but make it "quality time" <g>!
    Use your table time to define structure.

    Best of luck!

    Regards

    Jeff Boyce
    Microsoft Office/Access MVP

    "RSunday" <> wrote in message
    news:...
    > You are right about the confusion when one value is shown an another is
    > actually displayed. But right now I am just prototyping a data structure -
    > and once I have it right I'll make all the forms for data entry and
    > display
    > etc. - but right now I just want something that works.
    >
    > But I found out that I can just add data in the table views - and use the
    > relations defined between the tables - i.e. open a child table by clicking
    > the "+" to expand the hiearchy. When I add records in a child-table, the
    > relations to the parent is automatically made.
    >
    > Rsunday
    >
    > "Jeff Boyce" wrote:
    >
    >> Lookup FIELDS, bad ... lookup TABLES, good (to paraphrase a colleague...)
    >>
    >> It sounds like you are working directly in the table(s). This is almost
    >> a
    >> necessity in Excel, but Access is a relational database. Access tables
    >> store data, Access forms (and reports) display it.
    >>
    >> Do the "lookup" work in your forms, using a combo box THERE.
    >>
    >> The major problem most folks encounter with trying to use lookup fields
    >> in
    >> tables is that what is stored does not match what is displayed ... this
    >> causes considerable confusion.
    >>
    >> Good luck!
    >>
    >> --
    >> Regards
    >>
    >> Jeff Boyce
    >> www.InformationFutures.net
    >>
    >> Microsoft Office/Access MVP
    >> http://mvp.support.microsoft.com/
    >>
    >> Microsoft IT Academy Program Mentor
    >> http://microsoftitacademy.com/
    >>
    >> "RSunday" <> wrote in message
    >> news:...
    >> > I have a table where the values in a field can be picked from a list -
    >> > but
    >> > this list depends on the value in another field in the same table.
    >> >
    >> > Example: I have a field called "kids". I can select a kid-value from a

    >> list
    >> > of kids - but I only want to select among the kids in the "family" -
    >> > i.e.

    >> I
    >> > have a field specifying the family. This selection should limit my list
    >> > of
    >> > kids to only the reæevant ones.
    >> >
    >> > Can I set up a combo box where this relation is implemented?
    >> >
    >> > Alternatively I create a form with this logic - but at this stage it
    >> > would
    >> > be easier to do without.

    >>
    >>
     
    Jeff Boyce, Oct 17, 2007
    #4
  5. On Wed, 17 Oct 2007 05:42:02 -0700, RSunday
    <> wrote:

    >I have a table where the values in a field can be picked from a list - but
    >this list depends on the value in another field in the same table.
    >
    >Example: I have a field called "kids". I can select a kid-value from a list
    >of kids - but I only want to select among the kids in the "family" - i.e. I
    >have a field specifying the family. This selection should limit my list of
    >kids to only the reæevant ones.
    >
    >Can I set up a combo box where this relation is implemented?
    >
    >Alternatively I create a form with this logic - but at this stage it would
    >be easier to do without.


    I'm with Jeff - DON'T use table datasheets for data interaction.

    For one thing, it's very easy to have a dependent combo box on a Form; I do
    not believe that it is even *possible* in a table. So if you want this
    functionality you must use a Form (which you should be doing anyway!)

    How to set this up on a form depends on your table structure. You can set the
    RowSource of a combo box to a Query selecting only records from the desired
    family, by using

    =Forms!NameOfForm!NameOfControl

    as a criterion where NameOfControl is the name of a control on the form
    containing the FamilyID.

    John W. Vinson [MVP]
     
    John W. Vinson, Oct 17, 2007
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Annelie

    List Box or Combo Box Problem

    Annelie, Oct 17, 2003, in forum: Microsoft Access Database Table Design
    Replies:
    3
    Views:
    184
    Steve Schapel
    Oct 18, 2003
  2. Guest

    Table Object Definition

    Guest, Jun 30, 2005, in forum: Microsoft Access Database Table Design
    Replies:
    1
    Views:
    310
    Allen Browne
    Jun 30, 2005
  3. Guest

    Populate Field in Table with Combo box within same table

    Guest, Sep 18, 2006, in forum: Microsoft Access Database Table Design
    Replies:
    1
    Views:
    176
    Jeff Boyce
    Sep 18, 2006
  4. Steve

    Lost Table Definition

    Steve, Apr 25, 2007, in forum: Microsoft Access Database Table Design
    Replies:
    6
    Views:
    1,172
  5. Guest

    Defining data items via a data definition table?

    Guest, Oct 15, 2007, in forum: Microsoft Access Database Table Design
    Replies:
    2
    Views:
    197
    Guest
    Oct 17, 2007
Loading...

Share This Page