Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Box code (Access 2000)

    Hi everyone way smarter than me!

    I have a question, that has recieved similar answers in other threads, but I still don't understand exactly what I should do...

    I have a form with two combo boxes cmbRegion and cmbCounty...as it is the user can select one of three regions and then select the corresponding county out of five choices. I was wondering if there is a way to limit the choices in cmbCounty based on the initial selection in cmbRegion.

    For instance, if the user selects Region1 from cmbRegion, I would like County1 to be the choice in cmbCounty...selecting Region2 would limit the cmbCounty choices to County2 and County4, selecting Region3 would limit the list to County3 and County5. These are all text values. I am just learning this Access coding stuff and so I have a hunch that there's something to do with If...Then statements and After Update...but that's as far as my knowledge goes.

    Thanks for any and all help and explanations!

    Sarah <img src=/S/baby.gif border=0 alt=baby width=15 height=15>

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

    Re: Combo Box code (Access 2000)

    You must have a table (I name it tblCounties here for illustration purposes) that has (at least) a field County and a field Region. This table defines which counties belong to which region.

    Open the form in design view.
    Select the cmbRegion combo box.
    Activate the Properties window (if it isn't visible, select View/Properties).
    Activate the Events tab of the Properties window.
    Click in the After Update event.
    Select Event Procedure from the dropdown list.
    Click the Builder button (the three dots ...)
    You are switched to the Visual Basic Editor; the first and last line of the event procedure are already there.
    Type the following in the procedure (substitute the correct table name for tblCounties):<pre> Me.cmbCounty.RowSource = "SELECT County FROM" & _
    " tblCounties WHERE Region = '" & Me.cmbRegion & "'" & _
    " ORDER BY County"
    Me.cmbCounty = Me.cmbCounty.ItemData(0)</pre>

    This will make the cmbCounties display only the counties that belong to the selected region.

  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 Box code (Access 2000)

    Hans has given you an answer, while I have been building a working demo.
    I attach a file with it working.
    The critical features are:
    * the counties combos is based on a query that used the first combo as a parameter,
    * the second combo is requeried in the after update of the first combo,
    * the second combo is requeried in the oncurrent event of the form, in case you look back through old data and want to modify something.
    Attached Files Attached Files
    Regards
    John



  4. #4
    New Lounger
    Join Date
    Feb 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box code (Access 2000)

    Thanks John for your demo,

    I tried to implement it and I got this message "Run Time Error '438': Object doesn't support this propery or method. When I hit the debug button it highlighted Me!County.Requery. I then tried to change it to Me!cmbCounty.Requery but no luck.

    I attached part of my DB if you could see what is causing my error? The table and combo names are slightly different from what I posted earlier.

    THANKS SOOOOOO much for your help!

    Sarah <img src=/S/baby.gif border=0 alt=baby width=15 height=15>
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Feb 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box code (Access 2000)

    Thanks Hans for your super quick reply! I started on your suggestion and then switched over to implementing the code and whatnot from JohnHutchinson's example.

    Sarah <img src=/S/baby.gif border=0 alt=baby width=15 height=15>

  6. #6
    New Lounger
    Join Date
    Feb 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box code (Access 2000)

    Wow! Thanks John for that post number. I took a look at the code and I think that I'm going to shut down the computer now and try to decipher it tomorrow morning. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Sarah <img src=/S/baby.gif border=0 alt=baby width=15 height=15>

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combo Box code (Access 2000)

    You should firstly change your SQL behind the cmbCounty button to test MPO = [Forms]![frmFedFund]![cmbMPO].

    As for the error it is probably a MISSING reference.

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box code (Access 2000)

    <P ID="edit" class=small>(Edited by charlotte on 05-Mar-03 20:10. to activate post link)</P>Hi Sarah

    Thanks to Hans, there is a good demo at the following post:

    Re: Cascading Combo Box Demo Question? (A2k (9.0.3821) SR-1 )
    Post: <!post=132267,132267>132267<!/post> re: 132255 from Support4John

    HTH

    John

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

    Re: Combo Box code (Access 2000)

    What you posted had Me!cmbCounty.Requery in the afterpdate for a nonexistent region control.

    As soon as it put into the afterupdate for MPO it worked.

    Have you got it working?
    Regards
    John



  10. #10
    New Lounger
    Join Date
    Feb 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box code (Access 2000)

    Thanks everyone for your help,

    Pat, I did find the missing reference and changed the SQL as you advised. After I fixed the error John pointed out it still wouldn't work but I had left that pesky naming convention prefix off the AfterUpdate control. It does work now.

    Sorry that these are such minor things but they didn't require computer programming classes for my major in college!

    Many Thanks,
    Sarah

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combo Box code (Access 2000)

    No need for apologies.

    Glad you got it working.

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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