Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo box requery on subform (Access 97 SR-2)

    I have a database that keeps track of what classes instructors are certified to train. I have a main form (based on a table called tblInstructors) and subform (tblEvents). On the main form is the instructor's Name and Employee number. On the subform is the list of all classes in TABULAR FORMAT. In order to become certified for a class, the user must enter a date and an instructor. I've wanted to have a combo box where users can choose the instructor but have ran into one snag.

    The instructor that certifies you has to be certified himself to train the class. This can be checked if the Date field in tblEvents, where the certifications are being tracked, is not null and where the ClassID (from the Subform) is used as criteria for the Row Source Property of the combo box. In other words, the combo box should show me all instructors that match the ClassId on the subform and whose Date value is not blank. I've even made sure that an instructor cannot certify himself to train a course. Easy enough.

    Now, when I open the main form, this will work for the very first event I edit. When I move to the next record in the subform, the combo needs to requery to display a correct list. If I requery using the Current Record Event for the subform or On Enter for the combo box, it works. BUT since the form is in tabular format, the previous values for the instructor control appear blank. So the user would think the data was deleted all of the sudden. Once I click in the control for any of the previous values, the original value reappears.

    Any thoughts? Sorry so long. Thanks in advance for any help.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box requery on subform (Access 97 SR-2)

    Can't you do a requery in the AfterUpdate event of ClassId ? Or do I not understand what you want ?
    Francois

  3. #3
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box requery on subform (Access 97 SR-2)

    Hi Francois,
    Sorry if this is more information than you need.

    The class id doesn't get updated. Whenever an Instructor is created into the database, an append query takes their EmpID and matches it with a ClassID to make a record in the Events table. So if there are 25 classes, it pairs the EmplD with the ClassID and makes 25 Events. In that way, all classes are listed in the subform, so the user can see what the person has and has not taken. The users have requested this design.

    The user then never has to choose the class, and in fact I've disabled it. If there is anything else you need, please let me know. And I appreciate your help.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box requery on subform (Access 97 SR-2)

    Sorry, I reread your first post and understand better now, I think.
    The trick is to put an unbound textbox over the combobox, leaving the arrow of the combobox visible.
    The controlsource of the textbox is a DLookup function.
    See attached sample.
    The only thing I can't do is get rid of the #error for the last (new) record
    Attached Files Attached Files
    Francois

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

    Re: Combo box requery on subform (Access 97 SR-2)

    Hi Francois,

    Using a text box on top of the combo box (leaving the dropdown arrow free) is an excellent solution. I think you can avoid the error by setting the control source of the text box in your demo to
    <code>=[Combo0].[Column](1)</code>
    The text box will be empty if the combo box is empty.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box requery on subform (Access 97 SR-2)

    If I replace the dlookup function with =[combo0].[column](1), we are back to the beginning of the problem as the combo is requeried and the row is not any more in the combolist.
    Francois

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

    Re: Combo box requery on subform (Access 97 SR-2)

    Oops, you're correct. I was only looking at your demo instead of at the original question. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    How about this then?

    <code>=DLookUp("naam","table1","id = " & Nz([combo0],0))</code>

    where 0 is a value that does not occur in id field.

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box requery on subform (Access 97 SR-2)

    A rapid try and that appears to work very well. Thanks. I hope Stephen is still following the thread (I'll add a post hoping he is reading his post).
    BTW the idea of the textbox is an idea of <!profile=Charlotte>Charlotte<!/profile> in a thread a long time ago.
    Francois

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box requery on subform (Access 97 SR-2)

    Stephen,

    Have a look at the solution from <!profile=HansV>HansV<!/profile> for the #error problem
    Francois

  10. #10
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box requery on subform (Access 97 SR-2)

    Thanks guys! I'll take a look this and get back to you.

  11. #11
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box requery on subform (Access 97 SR-2)

    Very sneaky solution. I like it. You guys are a big help.

  12. #12
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box requery on subform (Access 97 SR-2)

    Just thought that I would also add that if a person chooses an instructor for an event, the text box with the Dlookup function appears blank. If an instructor is changed for the record, the old value will appear in its place. The text box will update only when the user moves from the record. I solved this by using the After Update event of the combo box to have the form recalculate itself. (Me.Recalc)

    Just wanted to solve another wrinkle with this post. Thanks again, Francois and Hans.

Posting Permissions

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