Results 1 to 7 of 7
  1. #1
    The Dad is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    5

    Multiple Look-up

    I am currently in the process of creating a database using Access 2007 and in the need of help. The database will be tracking employee production.

    I have two tables of look-up information that is related to my main table. Basically what I have is a table which lists "Work Items" and a sub table which lists "Work Item Descriptions". (i.e., some work items have only one associated description, whereas some have several, up to 20).

    What I would like to do is when in the main table, by selecting a "Work Item", only the associated "Work Item Descriptions" will populate in the next field.

    I am stumped. I know it is possible, but can't find out how because I do not know what this would be called. Any help?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    It sounds like this type of thing:

    Baldy-Cascading Combos

    To my knowledge, it can't be done directly in a table. Most of us don't let users anywhere near the tables anyway.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    The Dad is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    5
    Paul, thank you for the direction you have provided. I am stumped though. I have the row source for the first drop down to correctly display, but I can't get the second to populate.

    I used the following for the first:

    [SELECT Work Item Description].[Work Item Description] FROM Work Item Description WHERE (([Work Item Description]

    I used the following for the second:

    [SELECT Work Item Description].[Work Item Description] FROM Work Item Description WHERE (([Work Item Description;]

    What I did was basically reverse engineer your example from Baldy Web (which is great by the way). There is a string in your example which is
    ![cboState1] and I don't understand it, as that "cbo" part does not show in the form nor the table.

    Could you explain it to me?

    Thank you in advance.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    cboState1 is a reference to the first combo box on the form (that's the name of the combo). A WHERE clause has to have some value to compare the field to, like

    WHERE Parent = 'Dad'

    You just have

    WHERE
    Parent

    You also have brackets around the SELECT and such. You generally don't need them at all, but you will because of the inadvisable spaces in your names. The brackets just go around the table or field name though:
    [Work Item Description].
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    The Dad is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    5
    Thanks Paul. I try that.

  6. #6
    The Dad is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    5

    UGH! Paul, I need help.

    I can't seem to make it work. Will you take a look and see where I am going wrong?

    The data all points to "Main Table" but I made copies with "1" after them.

    I sent you a copy of the database via g-mail since it is too big to put here.

    Any help would be greatly appreciated.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    I only have a minute, but I see a couple of things right off. You have SQL in the Control Source properties, which won't work. Those either need to be the name of a field in the table or blank. Second, you don't have a requery of the second combo in the after update event of the first combo (that's what makes it refresh its selections). I see the row source of the combo is invalid, as there's no such table as "Work Item". I'll let you fix that.

    I have to go pick up my wife, so let me know if you're still stuck.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update Multiple fields according multiple criterias
    By denis.m in forum Programming
    Replies: 1
    Last Post: 02-21-2011, 11:03 AM
  2. Replies: 4
    Last Post: 09-22-2010, 01:47 AM
  3. Replies: 2
    Last Post: 05-25-2010, 02:45 PM
  4. Replies: 5
    Last Post: 12-10-2009, 10:33 PM
  5. Replies: 1
    Last Post: 12-10-2009, 08:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums