Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Requery all controls and subforms (Access 2000)

    I want to put a line of code in my VBA procedure that will update all the controls - including all subforms - in one efficient pass.
    I know I can use the requery method but what is the best way to do this?
    For example, say you had three independent subforms linked to the main form - how can you update the main form and all three
    subforms by clicking on a single button on the main form. If I put a Requery statement for each subform control, it takes forever.
    It's obviously something simple, but what is it?
    Thanks!

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

    Re: Requery all controls and subforms (Access 2000)

    If you really need to requery every control and subform (why?), you might close and reopen the form:

    Private Sub cmdRefreshEverything_Click()
    Dim strFormName As String
    strFormName = Me.Name
    DoCmd.Close acForm, strFormName, acSaveNo
    DoCmd.OpenForm strFormName
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery all controls and subforms (Access 2000)

    Thanks Hans.
    I can't really understand why you wouldn't want the entire form, with all its subforms and controls
    to show the latest information. Why would you want PART of the form to be out of date?
    Again, there might be a really good reason but it's hard to imagine what it would be - can you tell me why one would want a form that has
    even just one subform with inaccurate infromation?

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

    Re: Requery all controls and subforms (Access 2000)

    When the user moves to another record in the main form, the subforms will update themselves automatically, so they don't need to be requeried explicitly. The situation I encounter most often is something like "user selects an item in combo box A, and then combo box B or subform C needs to be requeried", or "user moves to another record, and a combo box must be requeried to reflect this". I can't remember situations where everything had to be requeried explicitly.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery all controls and subforms (Access 2000)

    Yes, I see what you mean. I guess the latter is just my situation.
    I've got a tab control and a subform on a main form and I want the subform's data source to vary
    according to the tab page chosen as well as a combo box on the main form.
    Thanks for your help.

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

    Re: Requery all controls and subforms (Access 2000)

    One alternative is to use a single subform control and simply change the sourceobject depending on the tab page. Then you don't have to requery, just load a different source object for the subform control.
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery all controls and subforms (Access 2000)

    Nice idea, will try this. Thanks

Posting Permissions

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