Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Lounger
    Join Date
    Oct 2001
    Location
    Bristol, Bristol, United Kingdom
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Boxes (Access 97)

    I would like a combo box that only shows data relating to the selection I have made in a prevoius combo box. For example, if i selected "London" from the first combo box I would like the second box to show only the managers in London. If I selected "Paris" I would like only the Paris managers shown, and so on.
    Can I do this and what is the simplist way? (I currently have two sepatate tables, one with the place names and the other with the managers names).
    Thanks,
    Al.

  2. #2
    New Lounger
    Join Date
    Oct 2002
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes (Access 97)

    Would it work to make the row source for the 2nd combo be something like "SELECT MANAGER FROM MANAGERS WHERE CITY = '" & ME!COMBO1 & "'" ?

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Combo Boxes (Access 97)

    You need one extra step. In the afterupdate event for combo1 you need code to requery combo2.
    Regards
    John



  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Boxes (Access 97)

    The previous replies combined tell you what to do. If you want more information, have a look at ACC: How to Synchronize Two Combo Boxes on a Form. It contains instructions and a link to a downloadable sample database with many other useful examples.

  5. #5
    Lounger
    Join Date
    Oct 2001
    Location
    Bristol, Bristol, United Kingdom
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes (Access 97)

    Thanks for these tips - I'll go and give it all a go! Sounds simple enough.....

  6. #6
    Lounger
    Join Date
    Oct 2001
    Location
    Bristol, Bristol, United Kingdom
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes (Access 97)

    I'm back!! I have followed the example in the link HansV gave and I have looked at questions others have posted on this topic (e.g. post 190432 - Cathy on 22 Oct) but I still can't get it to work!!
    In the example "How to syncronize 2 combo boxes" that HansV offered it shows how to create a query...I have done this (replacing the example field names with "office" and "contact") to give the criteria:
    [Forms]![frmOffice / Contact Practice]![[Office]
    ......but all i get when i select from the first (office) combo box is an "Enter Parameter Value" box with this criteria in and nothing appearing in the second combo box....I seem to be stuck in some loop! Help!!

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo Boxes (Access 97)

    Alastair
    I have had this problem before. I had a database which required postal codes ( or zip codes ) and then the postal district. when clicked on past code combo the district codes changed in the second combo.

    I cheated by creating two look up tables tblPostCode and tblDistrict and made a 1 to many relationship between them. I then created a query with them so I got a list of postcodes and the associated postal districts called qryAll.

    I then created a form with two combos on them the first looked up tblPostalCode and the second was driven by parameter query in qryAll in the PostCode field. it read from the postaldistrict field.

    I then did a requery in VBA on after update

    You could do the same for your districts and managers problem
    Jerry

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Boxes (Access 97)

    If you get an "Enter Parameter Value" box, Access hasn't recognized your parameter as a valid reference to a control on a form.

    You have to make absolutely sure that the spelling is correct (there is a double bracket before Office in <font face="Georgia">[Forms]![frmOffice / Contact Practice]![[Office]</font face=georgia> in your post).

    Is "frmOffice / Contact Practice" the exact name of the form?

    Is "Office" the exact name of the control on the form containing the office? (You must refer to the control name, not to the field name)

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes (Access 97)

    Not meaning to butt in but thought this might help.
    I've converted to A97, perhaps it will help.
    I've based the combo's around one table.
    Regards
    Dave
    Attached Files Attached Files

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo Boxes (Access 97)

    As well as all the above, make sure your control names are not the same as the field names, otherwise Access gets confused. Unfortunately the Access Form Wizards give controls the same names as their underlying fields. You need to change them.
    David Grugeon
    Brisbane Australia

  11. #11
    Lounger
    Join Date
    Oct 2001
    Location
    Bristol, Bristol, United Kingdom
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes (Access 97)

    OK - I have nearly got this thing working!!! (Thought it'd be a 5 minute job!). I have got the column headers to appear in the 2nd combo box, but no data appears. I guess the query must be wrong, but I don't know why.....
    (Jezza - post code is fine by me mate!!)

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo Boxes (Access 97)

    Alastair, can you post the SQL for your query so we can have a look at it.
    David Grugeon
    Brisbane Australia

  13. #13
    Lounger
    Join Date
    Oct 2001
    Location
    Bristol, Bristol, United Kingdom
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes (Access 97)

    Hi again - sorry it took a couple days to reply - i work for an electric company and we've just recoved from the biggest storm in 10 years!
    I'm not an SQL expert (you've probably guessed that!), but this is what i have:

    SELECT [EW Contacts].[Contact],[EW Contacts].[EW Office]
    FROM [EW Office] INNER JOIN [EW Contatcs] ON [EW Office].[EW Office]=[EW Contacts].[EW Office]
    WHERE ((([EW Contacts].[EW Office])=[Forms]![frmOffice / Contact Practice]!Office))
    ORDER BY [EW Contacts].[EW Office];

    I hope this makes sense to someone! I have two tables - one called EW Contacts that has two columns, EW Office and Contact. The other table is EW Office and has the one column of the 5 offices. (Not all the offices have a contact yet so they are not in the EW Contacts table). I have linked the two EW Office fields together in my query. Like I said, with my combo boxes I can select an office from the list of 5, but only the two column headings appear in the second combo dropdown.
    Can anyone offer any help??

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Boxes (Access 97)

    I hope the Contatcs is just a typo. If you copied the SQL into your post, you should correct this first.
    Apart from that, your SQL looks OK. If the reference to the form is correct and if the current value of Office on the form is an existing one, it should work.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Boxes (Access 97)

    The "control name" is the Name property.

    For bound controls (controls that have a field name in their Control Source property), Access by default assigns the field name to the Name property too, so the field name is both the Name and the Control Source of the control. Sometimes, this can lead to confusion, so many experts advise to make the Name different from the Control Source. This is often done by prefixing the field name by a three letter abbreviation of the control type. So the name of a text box bound to City would be txtCity, and the name of a check box bound to Married would be chkMarried.

    Although your combo box named Office is not bound to a field, the name Office may still confuse Access, so try changing the Name property to for instance cboOffice. Of course, you must then change the reference in the Control Source of the other combo box too:

    ... WHERE ((([EW Contacts].[EW Office])=[Forms]![frmOffice / Contact Practice]!cboOffice)) ...

    Note: this post was in reply to post 192678 by Alastair but that post had disappeared by the time I posted this one...

Page 1 of 2 12 LastLast

Posting Permissions

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