Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dropdown Box filtering another Dropdown Box (Access 2000)

    I am trying to make a form that has 3 dropdown boxes on it. When the user makes a selection from the first dropdown box, it filters the information found in the second box. Then when the user makes a selection from the second box, it filters what is available in the third box. I then would like the information selected from all three boxes to be entered into another form. I need help!! All the information for the 3 dropdown boxes comes from the same table, but where it will be entered on the second form is in another table.

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

    Re: Dropdown Box filtering another Dropdown Box (Access 2000)

    If you do a search for Cascading Combo in this forum, you will find threads related to this. For instance, <post#=132267>post 132267</post#> has a demo database attached. There, the combo boxes are used to filter a subform, but the methods used there can be adapted to displaying data in another form.

    See if that will help you on your way. If you have specific questions, post back.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dropdown Box filtering another Dropdown Box (Access 2000)

    That is exactly what I

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

    Re: Dropdown Box filtering another Dropdown Box (Access 2000)

    You could place the combo boxes in the form header (or footer) of the form with the registration information, along with a command button that sets the values:

    Private Sub cmdSetValues_Click()
    Me.DormNumber = Me.cboDorms
    Me.RoomNumber = Me.cboRooms
    End Sub

    (These names are just made up, of course, replace them by the ones you use)

    If you want to use two different forms, you can refer to values on one form from the other one, as long as both are open. For instance, if you have the combo boxes and command button on FormA, and the registration information on FormB, the code could be:

    Private Sub cmdSetValues_Click()
    Forms!FormB!DormNumber = Me.cboDorms
    Forms!FormB!RoomNumber = Me.cboRooms
    End Sub

  5. #5
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dropdown Box filtering another Dropdown Box (Access 2000)

    Thanks again. That will work great and get me over that hump in designing this application.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dropdown Box filtering another Dropdown Box (Access 2000)

    Hans,

    I have been able to incorporate what I learned off of the combo box demo and the sample code you indicated in your last posting, but I am having one problem.
    In the combo box demo there are four cascading combo boxes. You refer to two of them in the last code you posted.

    Forms!FormB!DormNumber = Me.cboDorms
    Forms!FormB!RoomNumber = Me.cboRooms

    In the demo, there are also fields that do not come from combo boxes, they are labeled, CriteiaField1, 2 and 3 in the demo.
    How would I post information out of one of those Fields at the same time I

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

    Re: Dropdown Box filtering another Dropdown Box (Access 2000)

    It depends. In the demo database, the user selects a single value in each of the combo boxes. This makes the subform display a variable number of records. So there is not necessarily a single value of e.g. CriteriaField1. If there is always a single value in your case, you can use it analogously to the way the combo boxes are used:
    Forms!FormB!SomeField = Me.SomeField
    where SomeField is one of the other fields.

  8. #8
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dropdown Box filtering another Dropdown Box (Access 2000)

    The information I'm trying to post to my form is from the "CriteiaField1" field in the demo, but that information could be many different names. It will however be just one name when the user makes their selections in all four combo boxes. Here are a few lines of code I have tried.

    Forms!frmEnterDormRecord!Counselor = Me.Counselor
    Forms!frmEnterDormRecord!Counselor = Me.sfrmdorm.Counselor

    "Counselor" is the rename of the "CriteiaField1" field from the demo. It is also the name of the field I'm trying to enter the information into on the form "frmEnterDormRecord"

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

    Re: Dropdown Box filtering another Dropdown Box (Access 2000)

    What was the result of these attempts? Do you get an error message, or do the text boxes display #Error, or do they display #Name, or do they stay empty?

  10. #10
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dropdown Box filtering another Dropdown Box (Access 2000)

    I get an error message that reads: Compile error. Method or data member not found.

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

    Re: Dropdown Box filtering another Dropdown Box (Access 2000)

    That probably means that one of the names you use is not correct. Check for spelling errors. One point that often causes problems is referencing a subform. You *must* use the name of the subform as a control on the main form; this is not necessarily the same as the name of the subform in the database window.

    There is a comprehensive overview of how to refer to controls and properties on subforms at Forms: Refer to Form and Subform properties and controls on Dev Ashish's Access Web.

  12. #12
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dropdown Box filtering another Dropdown Box (Access 2000)

    Thanks Hans! I have it working now. You were a great help.

Posting Permissions

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