Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Record refresh (2002)

    I've got a main form containing properties, with a sub form containing property jobs. On the main form I have a command button that launches a form that allows me to maintain lookup values that will populate a combo box located on the sub form. When I add a new area type to the areatypes table through the form, the new value is not available in my combo box on the sub form. I've tried a couple of different things in several events on the form, sub form, and even the on click event of the combo box I expect to contain the new value. Below are the commands I have triend:

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acRefresh, , acMenuVer70
    Application.RefreshDatabaseWindow

    I'd like to know the best way to handle this situation.

    Any help would be greatly appreciated. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

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

    Re: Record refresh (2002)

    If the "form that allows me to maintain lookup values" is opened as a modal form (either because you set its Modal property to Yes, or because you open it with acDialog as argument to DoCmd.OpenForm), you can requery the combo box in the On Click procedure of the command button, after opening the form:

    Me.SubFormName.Form.ComboBoxName.Requery

    where SubFormName is 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. And ComboBoxName is the name of the combo box.

    If you don't open the other form as a modal form, you can requery the combo box from that form, for instance in the On Click procedure for a "Close" or "OK" button. In that case, the syntax is:

    Forms!MainFormName.SubFormName.Form.ComboBoxName.R equery

    where MainFormName is the name of the main form, and the rest is as above.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record refresh (2002)

    Hans,

    The area type look up form is a modal form and is called via a command button on the main form. Once I modify the data in the areatype table via the form, it's not reflected in the combo box located on the subform. I tried the requery command in the on_click event of the combo box, in several different ways, but no luck. I tried making the area types form a non-modal form and tried it from the area types lookup form, but it didn't seem to work either. I have a feeling I'm missing something obvious. Maybe I'm not understanding the syntax for the control hierarchy in the command or something else. If I click on Refresh from the Records menu, the list is updated. I pasted the code I tried below. Any help is appreciated.

    Private Sub Combo42_Click()

    'Me.subfrmPrprtyJobs.Form.Combo42.Requery
    'Me.Combo42.Requery
    'Combo42.Requery
    'Requery

    End Sub

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

    Re: Record refresh (2002)

    You have a command button on the parent form that opens the area types form. If probably contains a statement of the form

    DoCmd.OpenForm "frmAreaTypesLookup"

    You should add the requery statement below this:

    DoCmd.OpenForm "frmAreaTypesLookup"
    Me.subfrmPrprtyJobs.Form.Combo42.Requery

    This assumes that "frmAreaTypesLookup" is modal, or that DoCmd.OpenForm has acDialog at the end.

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

    Re: Record refresh (2002)

    If you put the line
    <pre>Me.subfrmPrprtyJobs.Form.Combo42.Requery
    </pre>

    in the on Activate event for the main form then the combo will be updated whether the area type lookup form is modal or not..
    Regards
    John



  6. #6
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record refresh (2002)

    Thanks that worked, I actually put Me.Combo42.Requery in the GotFocus event of the combo box and it worked as well. Is there an advantage to using one control/event or the other? I'm new to Access, I've ordered a couple of books, but for now I'm kind of flying blind.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record refresh (2002)

    Thanks Hans, finally got it working. I have another post later in the thread if you're interested.

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

    Re: Record refresh (2002)

    I think it just a matter of finding an event that will always occur when you need it to, and won't occur so often that you will be forced to wait for unnecessary requering to take place.

    If you were entering a series of records in a row without changing the row source of combo42 there would be no need to requery for every new record because nothing will have changed. Putting requery in the gotfocus event will requery more often than is needed, but unless the data sets are large you won't notice any delay.
    Regards
    John



Posting Permissions

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