Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Combo Box Requery (Access 2000)

    The database is recording data about people. There is a form for managing the people with a tab control and one of the tabs contains a sub form. The sub form refers to a table of activities people are interested in. There is one control on the sub form and that is the description of the activity (the primary key for this table is Autonumber). I have created an On Event procedure for double click that opens up another form (with modal set to Yes) that allows the user to add new activities.

    My problem is that when I close this last form and return to the sub form I cannot get the control to requery the table and list in the combo box the new list. I have created an On GotFocus event for this control that performs a requery on the control but it does not do the job.

    Can anyone suggest a method that will work?

  2. #2
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box Requery (Access 2000)

    Please clarify, Peter.
    Where does the combobox fit in? I don

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Combo Box Requery (Access 2000)

    Yes I agree the NotInList event is an excellent way of adding data to a combo box list. However as the help tells us, the problem is that you have to deal with all the fields in the underlying table. So what I have been doing with my forms is to have a OnDoubleClick event in the combo box that automatically opens up another form to allow the user to add the new record with all fields displayed to the table underlying the combo box. Then when the user closes this form and returns to the combo box the OnGotFocus event requeries the control and the list now shows the new record. This has worked fine when the combo box is on the main form, and if I open this sub form by itself still works fine.

    However when I open the main form and thereby the subform, and even though I am using the correct addressing for the control (code shown below) the list does not seem to be refreshed.

    Private Sub Activity_ID_GotFocus()
    Dim ctlList As Control

    ' Return Control object pointing to list box.
    Set ctlList = Forms![frmContacts].[frmContact_Activities]![Activity_ID]
    ' Requery source of data for list box.
    ctlList.Requery
    End Sub

  4. #4
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box Requery (Access 2000)

    Peter,
    The Activity_ID_GotFocus event for your combobox is not triggered when you close the form used for added activities to your activity table. It would be for a control on the main form.You may verify my statement by including a (Debug.Print

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Combo Box Requery (Access 2000)

    Thank you very much for your help. Your suggestion of NotInList makes much more sense.

Posting Permissions

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