Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Requery a subform (2000 SR1)

    Good Morning,
    I thought that I could get the Requery of a subform to work properly, but is not the case. I have a main form - Log hrs - with two subforms - Log Hrs subform and Daily Hrs qry subform2. Log Hrs subform source is from a table and Daily Hrs qry subform2 source is from a query. The query gives me the total hours for a date.

    What I'm trying to do is when I enter hours for a date in the field Billing Hrs on the Log Hrs subform , to have the total hrs - Day Hrs - in the Daily Hrs qry subform2 to update. I am using the OnChange event for the field BillingHrs.

    I am using the following code along with many, but the Requery does not update the Daily Hrs query;

    Private Sub BillingHrs_Change()
    Forms![Log Hrs]![daily hrs qry subform2].Form![day hrs].Requery

    End Sub

    Thank you for Your assistance,
    Jim

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

    Re: Requery a subform (2000 SR1)

    If you are trying this, you have to do it in the after update event of the form. As long a the record has not been saved, the requery of the other form won't work because the record is not yet written into the database.
    Or you could issue a
    DoCmd RunCommand accmdsaverecord
    before the requery line. This will save the record but you won't be able to undo the changes with Escape.
    Francois

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

    Re: Requery a subform (2000 SR1)

    As Francois writes, it's best to use the AfterUpdate event of the first subform. You can refer to the main form as Parent.

    You may have to requery the second subform instead of just the Day hrs field:

    Private Sub Form_AfterUpdate()
    Parent![daily hrs qry subform2].Requery
    End Sub

    But wouldn't it be easier to have a textbox in the form footer section of the first subform to display the sum of hours? It would be updated automatically.

  4. #4
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery a subform (2000 SR1)

    Francois,
    Thank You. I am posting an additional question on Hans's reply which was an extension to your reply.
    Jim

  5. #5
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery a subform (2000 SR1)

    Hans,
    First, Thank You for your replay. Second, I had thought about the AfterUpdate, but felt it would be better to use the OnChange in that it would be a better trap for the data comparision that I wanted. I originally posted on 30 May about "Compare hrs worked to scheduled hrs" which you helped me with greatly.

    My question to OnChange vs. AfterUpdate, and I could think of OnExit also, how/where could I find what would be the best type of event to use? Or is this mainly a personal choice?

    Thanks again to You and Francois
    Jim

    P.S. How can a person reply to more that one post at the same time?

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

    Re: Requery a subform (2000 SR1)

    If you want to update Form B when something in Form A changes, the data in Form A must be saved.
    The OnChange event of a text box occurs when a user is typing in that text box. It seems impractical to save a record every time the user types or erases a character. The OnExit or AfterUpdate events of a text box might be a better place to save the record, but as Francois mentioned, the user loses the possibility to correct mistakes by using Undo.
    That's why he proposed to use the AfterUpdate event of the subform instead of an event of the text box.

    If you want to reply to two or more posts, you can reply to one of them, and mention in the first line that you are replying to several. You can mention the poster's user names, or the numbers of the posts you're replying to.

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

    Re: Requery a subform (2000 SR1)

    Jim,

    The OnChange event in a control fires every time you hit a keystroke that changes the value of the control. I don't think you want to keep requerying your other subform at every keystroke!

    OnExit occurs when you leave the control, but if you just tabbed thru the control without changing anything, you would still requery! Which again, I don't think you want to do.

    The control's AfterUpdate event fires only after your changed entry has been accepted. However, this entry has not yet been written back to the table, so that other query will not recognize it.

    The best place for what you want to do is your form's AfterUpdate event, which only fires after you have made changes to a record (or are adding a new record) AND those changes have been accepted and written back to the underlying table.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery a subform (2000 SR1)

    Thank you Hans,
    Mark also added to you explaination of when to use the different events. I am/was trying check the total hours after the new data was entered. As you and Mark stated, OnChange fires after EACH key stroke. In my case, they would not be able to enter a value over 9, which is not what I want/need. I'll have to work on using the AfterUpdate on the form or maybe LostFocus on the text field.

    Thanks to All again,
    Jim

Posting Permissions

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