Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2004
    Posts
    1

    Unanswered: Lookup field in access

    I have an access table, ITEM, with two fields MACHINE and METHOD. I would like the user to choose from a list of machines (1-5). If the user chooses machine 1 they should then be able to choose from methods a,b,c, or d. If the user chooses machine 2 they should then be able to choose from methods a,b,x,y, or z. All the possible combinations of MACHINEs and their METHODs are in a table called MACHINE_METHOD.

    I tried implementing this as lookup fields on the ITEM table, but couldn't get the METHOD to change dynamically. In other words, when choosing machine 2, I was presented with all methods, not just those for machine 2. I want to make sure that only the method options for machine 2 are presented in the dropdown.

    Below is the line I have on the ITEM.METHOD lookup field:

    SELECT [MACHINE_METHOD].[METHOD] FROM MACHINE_METHOD

    I tried adding the line below to the query to make it dynamic:
    WHERE MACHINE=[ITEM].[MACHINE];

    Access didn't like that. It just requested the parameter, rather than use what was in the MACHINE field.

    Eventually, I want this to go on a form as a series of dropdowns, but there will actually be many more fields. And each dropdown's options will depend on the previous several dropdown's answers.

    I'm not really sure that lookups are the answer. Does anyone have any ideas what the syntax would be to get the lookup query to refer to the ITEM.MACHINE value? Or any other ideas how to make this work on a form?

  2. #2
    Join Date
    Jun 2004
    Posts
    92
    I never use lookup fields. On a form, make two combo boxes. cbomachine and cbomethod. Set the rowsourcetype on cbomachine as valuelist and for rowsource put 1;2. Next, on the onchange event for cbomachine, put the following code. It will automatically put in the appropriate values for cbomethod. When you have that all set up and see how the code works. Change the values etc.. to appropriate ones for your db.

    Dim Number
    Number = cbomachine.Value ' Initialize variable.
    Select Case Number ' Evaluate Number.
    Case 1
    cbomethod.RowSourceType = "Value List"
    cbomethod.RowSource = "a;b;c;d"
    Case 2 ' Number between 6 and 8.
    cbomethod.RowSourceType = "Value List"
    cbomethod.RowSource = "a;b;c;x;y;z"
    End Select

  3. #3
    Join Date
    Oct 2013
    Posts
    6

    Very New to access

    I am very new to access. I have created a database with access 2010 for our central records room. I am learning on the fly with the help of everything posted on these forums. I have 2 fields. One is Department and one is Category.
    What I would like to be able to do is select a department (there are 5 listed in a combo box) and that selection would determine which choices would be available in the Category section.
    I don't even know if this is possible. I have 5 departments and each department has 4 categories. Is it possible to have the Department field selection determine which choices are available in the Category field? Any and all suggestions appreciated. Thank you

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Yes

    Look up cascading or linked list boxes

    Also pleases do not tack your question onto other posts. The original post is over 9 years old. Its highly unlikely the original contribiutors are still around
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Yes

    Look up cascading or linked list boxes

    Also pleases do not tack your question onto other posts. The original post is over 9 years old. Its highly unlikely the original contribiutors are still around
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Oct 2013
    Posts
    6

    Thank you

    I will look up cascading or linked list boxes thank you. I apologize for tacking onto this thread but I just signed up for this forum and I have not yet figured out how to start a new thread.

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Although the exact same thing applies to Listboxes with the MultiSelect Property set to None, and the code is interchangeable, the concept is more often referred to as "Cascading Comboboxes." I suspect you'll probably get more hits using 'Comboboxes' rather than 'Listboxes.'Here are a couple of good links with examples:

    Access Tips: Cascading Lists for Access Forms

    Cascading Combo/List Boxes - Microsoft Access / VBA


    And doing this in a Form, rather than using Lookup Fields at the Table-level is the way to go.

    The Evils of Lookup Fields in Tables

    Linq ;0)>
    Last edited by Missinglinq; 10-17-13 at 23:01.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Oct 2013
    Posts
    6

    Thank you

    Thank you very much I will use those thinks you provided.

Posting Permissions

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