Results 1 to 8 of 8

Access 2007 Sub Field

This is a discussion on Access 2007 Sub Field within the Microsoft Access forums, part of the Question Forums category; I have a form and I want a sub field to display certain content or choices based on the first ...

  1. #1
    Board Regular
    Join Date
    Jan 2008
    Posts
    736

    Default Access 2007 Sub Field

    I have a form and I want a sub field to display certain content or choices based on the first field choice.

    Example

    Field 1 would have several categories (Fruit, Vegies, Meat, Chicken etc)
    Field 2 would have the names of the category chosen

    Fruit--> Apples, Orange, Bannanna
    Vegies--> Squash, potato etc...

    Any leads or Tutorials out there?

    Thanks,

  2. #2
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,213

    Default Re: Access 2007 Sub Field

    Do a search on the Board for Cascading combo -- there are plenty of examples. Also, think about the structure. If the second field is one to one with the first (eg, pick a Category and NEVER more than one member), you can keep the second field in the same table. If you will have multiple items, you need a related table that will let you add one record per item.
    Access 2007 does have multi-value fields (to make it compatible with some of the list features in Sharepoint) but this feature is not compatible with earlier versions of Access.

    Denis
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  3. #3
    Board Regular
    Join Date
    Jan 2008
    Posts
    736

    Default Re: Access 2007 Sub Field

    I found a video tutorial and I am stuck at the section at 1:55 on the video.

    Basically I have my 2 tables and created a query for the Sub-option. In the Query under filter I am unclear on what the follow is referring to.

    Please advise.

    [FORMS]![CustomerF]![StateCombo]

    Is the FORMS tell the query to look at the FORM called "CustomerF" and the Field "StateCombo"

    ??

    Heres the video
    http://www.599cd.com/tips/access/two-combo-boxes/

  4. #4
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    2,746

    Default Re: Access 2007 Sub Field

    You have hit it on the head. If your form has a different name, then you must put your form name in for [CustomerF] and your field name that houses the combo box for the stateCombo field. The formula shown is placed in the criteria section of the query.

    Here is another tutorial that shows a slightly different method.

    http://www.fontstuff.com/access/acctut10.htm

    Good Luck

    Alan
    Alan Sidman
    Win 7--Office XP, 2007, 2010

    Be Kind--Use Code Tags. Highlight your code and click on the # sign at the top of the thread window.


  5. #5
    Board Regular
    Join Date
    Jan 2008
    Posts
    736

    Default Re: Access 2007 Sub Field

    Ok, I am still on the first one an almost have it. But its still not working.

    This is what I have.

    IF this matters I have created an Excel Sheet with the Selections in Combo1 and Combo2 for the ease of the user updating the fields and the sheets feed the combo boxes.

    I then connected the sheets to 2 tables one for Combo1 and Combo2

    Combo 2 table has the categories and sub categories like in the video (State-->City)

    When I select Combo1, Combo2 populates the State Category, not the corralating Sub Categories or (Cities) in the example. Its like access is looking at the wrong column.

    Please advise.

  6. #6
    Board Regular
    Join Date
    Jan 2008
    Posts
    736

    Default Re: Access 2007 Sub Field

    Would Bound Column have anything to do with it??

  7. #7
    Board Regular
    Join Date
    Jan 2008
    Posts
    736

    Default Re: Access 2007 Sub Field

    I was able to get it finally. HOWEVER....

    The table that pulls the values for Combo box1 is pulling the value, when I select the option I get a pop up to run the query, If I retype the exact same text in Combo box 1, then Combo box 2 populates.

    Its like the text format or something is not the exact same.

    Got any ideas?

  8. #8
    Board Regular
    Join Date
    Jan 2008
    Posts
    736

    Default Re: Access 2007 Sub Field

    It was the bound field I spoke of earlier. Finally got it. What a pain!!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com