Results 1 to 10 of 10
  1. #1
    carletonw
    Guest

    Synchronizing 2 Combo Boxes (Access 2000 SR1)

    Awhile ago the lounge helped me figure out how to set up two combo's to filter students based on the school selection of the first combo. It works until you leave the current record.
    The first box is an unbound combo that pulls the school names and id number from the schools table (2) column query. In the after update event I've added the following lines of VB code to update the student combo box and show students for the selected school:
    Private Sub Schools_AfterUpdate()
    Me!frmSubStudents.Form!StudentID.Requery
    Me!frmSubStudents.Form!StudentID.SetFocus
    Me!frmSubStudents.Form!StudentID.Dropdown
    End Sub
    The student field is in a subform connected by StudentID to the parent form. The studentID field is a bound combo box that in the query makes reference to the first School unbound control in its criteria. Not sure why the selected student disapears when you move off the current record. Any help or ideals would be great.

    Carleton

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Synchronizing 2 Combo Boxes (Access 2000 SR1)

    You should requery the 2nd combo box on the OnCurrent event of your form.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    carletonw
    Guest

    Re: Synchronizing 2 Combo Boxes (Access 2000 SR1)

    I tried requerying from the main form. I'm still loosing the values from the combo box. mainly if I select more than one student on the sub form. I must be missing something?

    Carleton

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Synchronizing 2 Combo Boxes (Access 2000 SR1)

    Do I understand that at least one of the comboboxes is on a subform? Is the second combobox based on a query that references the selection in the first combobox? Then what do you mean by selecting more than one student in the subform? Are you trying to use a multiselect combobox or is the subform a continuous form? If the latter, you have to remember that any item you select populates the current record, but the same combobox will appear blank in the other existing records because they apply to different students and so don't match the ID for the student in the curretn record. The value is still there in the field, but comboboxes don't look the way you might expect in continuous forms because there is really only one of them.
    Charlotte

  5. #5
    carletonw
    Guest

    Re: Synchronizing 2 Combo Boxes (Access 2000 SR1)

    charlotte,

    I see you are correct. I am using a subform and the field in it is bound. However the first combo box in the parent form is unboud. Is there any way that as the students are filtered by the first combo box, I can see cleanly all the students that I select in the sub-form (continuous) without that side effect. The sub-form also records attendance hours per student. Is this the best way to utilize this or do I need to come up with something more elaborate. Thanks for you usual great guidance.

    Carleton

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Synchronizing 2 Combo Boxes (Access 2000 SR1)

    You're running into the normal behavior of a bound combobox on a continuous form. It doesn't matter that the combo on the parent is unbound. The problem is that when you choose a single student on the subform, the combobox, which is the only one there, can only display the value that matches that student ID. Since the other records belong to other students, they don't match and so display blanks.

    What I usually do in these circumstance (besides avoiding using comboboxes on continuous forms), is to use a bound textbox and an unbound combobox instead. I put the textbox on top of the textbox portion of the combobox and I shrink the combobox down until only the dropdown arrow is visible. Then I use code to populate the textbox when a selection is made from the combobox using AfterUpdate event of the combobox and then set the focus back to the textbox.

    It sounds more complicated than it is, but since the textbox is always visible, you see the actual values but still have the ability to choose from the combobox. Does that make sense to you?
    Charlotte

  7. #7
    carletonw
    Guest

    Re: Synchronizing 2 Combo Boxes (Access 2000 SR1)

    It makes really good since to me, it really does. What would the basic code look like to populate the textbox initially, since it's bound? I can experiment to figure it out but it sounds like you've done it many times. I have several solutions I'm working on where I need this kind of process. Any sugestions would be really cool. However, thanks again for your great insight Charlotte.

    Carleton

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Synchronizing 2 Combo Boxes (Access 2000 SR1)

    The point is that you don't use a bound combobox on the subform, you use a bound textbox and an unbound combobox. The combobox doesn't actually show up with the data in it, the textbox does that. All the combobox does is display a list of students to select form. Then in the Afterupdate event of the combobox, you write the information to the textbox. It gets complicated if you're displaying something like the student name and actually saving the student ID, though. In that case, you may need an additional textbox that's locked and used for nothing but to display the student name. Then you could use a bound and an unbound combobox on the subform instead of the single one you have now. The unbound combo would be used to dropdown the list for selection, which the bound combo would not be visible and would be used only to provide a student name column to base the textbox on.

    Is that confusing enough? I've attached a sample database with a continuous form that illustrates using an unbound textbox and a bound and unbound combobox to do what I've described.
    Attached Files Attached Files
    Charlotte

  9. #9
    carletonw
    Guest

    Re: Synchronizing 2 Combo Boxes (Access 2000 SR1)

    Charlotte,

    Thanks for the sample it really helped me. I've almost got things working. The subform is working great. However, I am still having trouble getting the initial combo box (that selects the school from the parent form) to stay in synch with the subform. I'm not sure how to have the subform with students filter to only the students it the parents form's school selection. I can't use the first invisible combo box in code because of the visible property is set to "false". The second combo box is connected to the first combo, and of course the text box has the value "pushed" from the second cbo box. Help!!!

    Carleton

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Synchronizing 2 Combo Boxes (Access 2000 SR1)

    Hi Carleton,

    I've been out of town, so I haven't been able to look at your problem for the last few days.
    <hr>I am still having trouble getting the initial combo box (that selects the school from the parent form) to stay in synch with the subform<hr>
    I'm sorry, but I don't understand what you mean by this. If you've selected the school already, what are you trying to sync with the subform? What exactly are you trying to do? If you want to limit students in the subform to those associated with the selected school, you may the value in the school combobox one of the criteria for the rowsource for the comboboxes in the subform. Is that what you're trying to do?
    Charlotte

Posting Permissions

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