Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Nov 2011
    Posts
    103

    Unanswered: Requering or Refreshing a Form

    I need help to get my form to refresh or requery so that the calculation embedded through VBA would recalculate on open of form.

    The calculations are simple and they all consists of something like this.

    Private Sub MonthlyTotal_Enter()
    Me.TotalAccrued = Round(Me.MonthlyTotal * Me.CurrentPeriod, 2)
    End Sub

    I have query to change the CurrentPeriod at once to about 147 records. Once the change is made I want to open the form named "Facility Database" and for it to recalculate to reflect the changes.

    I've tried creating a button to refresh and requery through VBA and Macro, I tried to tied it in "On Open" through VBA, and tried the refresh button on the toolbar. I can't get the calculation to take affect until after I click or press enter on the field.

    I would paste some of the VBAs written below but honestly I've tried so many different combination after spending about 2 weeks of research that I don't remember now what all I've tried. Here's one that I do remember.

    Private Sub CmmdRefresh_Click()
    DoCmd.Requery
    End Sub

    Any help or suggestions would be greatly appreciated. I don't care if I've tried it before, I'll try it again. I just want this thing to work. And also I'm a beginner using Access 2010.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Did you try:
    Code:
    Private Sub Form_Current()
    
       ComputeTotalAccrued
        
    End Sub
    
    Private Sub CurrentPeriod_AfterUpdate()
    
       ComputeTotalAccrued
    
    End Sub
    
    Private Sub MonthlyTotal_AfterUpdate()
    
       ComputeTotalAccrued
    
    End Sub
    
    Private Sub ComputeTotalAccrued()
    
        Me.TotalAccrued = Round(Me.MonthlyTotal * Me.CurrentPeriod, 2)
    
    End Sub
    Also, you should refrain from naming a procedure the same as a field or a control (MonthlyTotal), it's confusing and can be a source of errors.
    Have a nice day!

  3. #3
    Join Date
    Nov 2011
    Posts
    103
    Thanks for the response Sinndho.

    No I have not tried that till just now and I'm getting an error message. Here's the message.

    The expression On Open you entered as the event property setting produced the following error: Ambiguous name detected: MonthlyTotal_AfterUpdate.

    I've didn't get this message prior to trying your suggestion. Any idea why? I'm thinking it's probably cause I don't have a good grasp of VBA and I'm entering the code incorrectly since I just copied and pasted what you suggested.

    Thanks.

    And I'll take your suggestion on naming my procedures and use them in my future projects.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Please try first to compile the module and see on which line the compiler stops with an error. Then report this line, the error message and the error code.

    Also check that you don't have the name MonthlyTotal used for 2 different things (control name, function name, module name, etc.)
    Have a nice day!

  5. #5
    Join Date
    Nov 2011
    Posts
    103
    Holy crap it worked. Once I complied and figured out the error the database totally requeried on open. Thank you so much Sinndho. I'm rejoicing in my cubicle right now. Small victory in my war against VBA!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  7. #7
    Join Date
    Nov 2011
    Posts
    103
    I just noticed that even though the calculation works and it updated on the form, it isn't updating the table without having to view each indiviual records. Any idea why and how I could resolve this issue so that once the calculations are updated the bounded table would also be updated?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    After updating, save the record immediately:
    Code:
    Private Sub ComputeTotalAccrued()
    
        Me.TotalAccrued = Round(Me.MonthlyTotal * Me.CurrentPeriod, 2)
        Me.Dirty = False
    
    End Sub
    Have a nice day!

  9. #9
    Join Date
    Nov 2011
    Posts
    103
    Didn't work. I have a form that initiates a query in which it changes a single field in all records. Once this is done and I open the form every single record reflects this change but then when I open up with table (after making the changes in the code above and saving the record immiedately) the change isn't reflected...

  10. #10
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Just to be sure I understand correctly:

    a) The RowSource property of the form is a query.

    b) The form changes the value of "TotalAccrued", using the function "ComputeTotalAccrued".

    c) Afterwards, when you open the table on which the query is based, the values in the column "TotalAccrued" are not changed.

    Is this correct?
    Have a nice day!

  11. #11
    Join Date
    Nov 2011
    Posts
    103
    A - no, the RowSource property of the form is a table.

    B - Yes

    C - No and Yes. No to opening the table in which query is based as I'm sure you already know from answer A that it's not based upon a query. Yes to the values in the column "TotalAccrued" are not changed in the table. They're fine in the forms.

  12. #12
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    If so, what's the query you mentioned previously:
    Quote Originally Posted by tobabygu View Post
    I have a form that initiates a query in which it changes a single field in all records...
    Moreover, I don't see how the values can be correct in the form and not in the table. The form only displays values fetched from that table. There's something that I don't understand, definitely.
    Have a nice day!

  13. #13
    Join Date
    Nov 2011
    Posts
    103
    Oh that query is just to change the data in the table itself. It'll be used like once a year to change the CurrentPeriod field in one big swoop. Seperate form initates that query... sorry for the confusion.

    Well the calculation is done on the form. Just like you, I thought if the changes were all updated on the form then table would also be updated. Maybe the data isn't updating in the form throughout all the record when the form is opened but only when I choose to view the specific record. If this is true I would have to go through all 150+ records to initiate the changes... Is there a work around on this???

  14. #14
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Now I understand. You don't want to change the current record of the form, which I thought from your initial request:
    Quote Originally Posted by tobabygu View Post
    ... so that the calculation embedded through VBA would recalculate on open of form.
    You actually want to perform a bulk update (i.e. update every line of the Table at once).

    You don't necessarily need a form to do that: a SQL statement is usually used in that case.

    1. Create a new Query.
    2. Switch to SQL view
    3. Remove everything that can be written in the text window.
    4. Paste this into it, replacing "<TableName>" by the name of the table you want to update:
    Code:
    UPDATE <TableName> SET [TotalAccrued] = Round([MonthlyTotal] * [CurrentPeriod], 2);
    5. Save the query then run it.

    If you want to do the same from a form:
    Code:
    Private Sub ComputeTotalAccrued()
        '
        ' Replace "<TableName>" by the name of the table you want to update.
        '
        Const c_SQL As String = "UPDATE <TableName> SET [TotalAccrued] = Round([MonthlyTotal] * [CurrentPeriod], 2);"
    
        CurrentDb.Execute c_SQL, dbFailOnError
        Me.Requery
    
    End Sub
    Have a nice day!

  15. #15
    Join Date
    Nov 2011
    Posts
    103
    Do I replace what's in the current ComputeTotalAccrued()?

    Private Sub ComputeTotalAccrued()

    Me.TotalAccrued = Round(Me.MonthlyTotal * Me.CurrentPeriod, 2)
    Me.Dirty = False

    End Sub

Posting Permissions

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