Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2011
    Location
    Indianapolis, IN, USA
    Posts
    41

    Unanswered: Updating a Table

    I have a table that I update using a form (continuous). I use a SQL in the form’s record source which selects all fields in the underlying table except the ID, then sorts in descending order by date (the purpose of this sort is to get the newest records to the top of the form).

    The records in the table are monetary transactions. In addition to Description and TransDate fields, I have BudAmt and ActAmt fields (Budget amount and Actual amount) and AdjBudBal and ActBal fields. As the BudAmt and ActAmt fields are added or changed, I have code to update all the AdjBudBal and ActBal fields in the table. I must update all records because a date change can put the records in a different sequence or a change in BudAmt or ActAmt can change all balances with a later date.

    I have a subroutine that computes the balances. At the end of the subroutine, I do a Me.Requery, Me.Repaint and Me.Refresh. I call this subroutine from the AfterUpdate Event of the BudAmt and ActAmt fields. I also have a command button that has an OnClick event which calls the same subroutine.

    I set a breakpoint at each place where the subroutine is called. They all go to the subroutine and process just the same. However, the two AfterUpdate Events do not update the balances as I would expect them to but when I click the command button, it works exactly as I want it to.

    Another strange thing is that when I change the BudAmt field or the ActAmt field then tab out of the field, it jumps to the top record on the form rather than the next field.

    I would rather have the balances update when the BudAmt and ActAmts are changed then stay on the same record.

    I have a test database with only a few records in it that I have attached.
    Attached Files Attached Files

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You "must update" because you're saving calculated values, which most of us wouldn't do under most circumstances. It's a normalization issue. If you "must", your first problem is likely because the record isn't saved yet. You can use one of these methods to force a save and see if it fixes the problem:

    Code:
    DoCmd****nCommand acCmdSaveRecord
    or
    If Me.Dirty Then Me.Dirty = False
    The Requery causes your other problem. See if this helps:

    Requery and stay on record

    Edit: I forgot this site doesn't like the dot-r-u, so the first method is

    DoCmdDOTRunCommand acCmdSaveRecord

    replacing DOT with .
    Last edited by pbaldy; 01-05-12 at 13:40. Reason: add clarity
    Paul

  3. #3
    Join Date
    Aug 2011
    Location
    Indianapolis, IN, USA
    Posts
    41
    I'm aware of the inadvisability of saving calculated data but haven't worked out a way to do what I want and not save it temporarily.

    I tried using a table without the balance fields for file maintenance and then when I wanted the balance fields, I'd dump the data into another table with balance fields then delete all the records in the table when I closed the form. This got tedious because I have to jump from one form to the other to check things. Using the form that I attached, I plan to delete the balances from the table when I close the form. I don't think it will make much difference because this is a personal use db and I don't have that much data. In effect, it's a check register but I can go back and fix errors and put things in date order more easily. It also ties in with some other stuff I'm doing.

    I used MS Money for a long time but they no longer support it and it won't work on Win 7. I tried Quicken too but both Quicken and Money are more for people with investments etc. As a retired 75-year old accountant, I've always wanted to build an app to do what I want. Working on Access is fun for me and also keeps me out of the refrigerator in the afternoon.

    Thanks for your help. I'll try your suggestions and see if they work for me. Again, many thanks.

  4. #4
    Join Date
    Aug 2011
    Location
    Indianapolis, IN, USA
    Posts
    41
    I just had a thought about the form I'm using. Is it possible to create a continuous form with the fields from the table without balances then have two unbound text boxes for each record to hold the balances? I'll try it and let you know.

  5. #5
    Join Date
    Aug 2011
    Location
    Indianapolis, IN, USA
    Posts
    41
    The things suggested by pbaldy worked great. I now have what I want except I'm still having to save a calculated field in the table. I don't need it in the table at all. When I open the form, I recalculate the balances then when I change an amount, I recalculate again.

    I tried to come up with a query with non-table data that I could use as the basis for a form but can't make it happen. I also tried adding unbound controls to my continuous form but can't get them to update properly. I also tried to come up with a way to create a recordset from the form with several table fields and two unbound text boxes but couldn't get it to work.

    Any help would be appreciated. I have attached a new database that is a little simplified.
    Attached Files Attached Files

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Harmanj1 View Post
    I tried to come up with a query with non-table data that I could use as the basis for a form but can't make it happen.
    You could use a running sum query for this (let's call it "Qry_PNCChecking"):
    Code:
    SELECT a.ID, 
           a.NrCode, 
           a.ActDate, 
           a.SS, 
           a.Description, 
           a.ActAmt, 
           SUM(b.ActAmt) AS ActBal,
           a.ClrDate
    FROM   tblPNCChecking AS a INNER JOIN
           tblPNCChecking AS b ON  b.ID <= a.id
    GROUP BY a.Id, 
             a.NrCode, 
             a.ActDate, 
             a.SS, 
             a.Description, 
             a.ClrDate, 
             a.ActAmt
    ORDER BY a.ActDate DESC, 
             a.SS DESC
    You could then use this query as the RecordSource of the form "frmPNCChecking". The problem is that such queries are "read-only" (i.e. you cannot change any value), which would prevent you from updating any value in the form.

    Another solution would consist in building a limited version of the running sum query (let's call it "Qry_RunningSum"):
    Code:
    SELECT a.ID, 
           SUM(b.ActAmt) AS ActBal
    FROM  tblPNCChecking AS a INNER JOIN 
          tblPNCChecking AS b ON b.ID <= a.id
    GROUP BY a.Id, 
             a.ActDate, 
             a.SS
    ORDER BY a.ActDate DESC , 
             a.SS DESC;
    Now the RecordSource of the form "frmPNCChecking" remains the table "tblPNCChecking" , so the form is updatable (except for "ActBal"), but the ControlSource property of the TextBox ""ActBal" becomes:
    Code:
    =DLookUp("ActBal";"Qry_RunningSum";"ID=" & [ID])
    and the procedure for the AfterUpdate event of the TextBox "ActAmt" becomes:
    Code:
    Private Sub ActAmt_AfterUpdate()
    
        Me.Recalc
    
    End Sub
    Which is all the code that remains in the module of the form.

    The advantages are:

    1. Your database is more normalized because you don't store computed values into a table. This prevents possible discrepancies between the amount of each transaction (ActAmt) and the balance (ActBal) which now is dynamic.

    2. For the price of a query and a rather complex expression in the ControlSource property of one control, you only need three lines of VBA code in the class module of the form (instead of 57).

    In the debit column (if I may say so), the time needed to compute the running sum is a little longer which causes the form to need more time to load.
    Have a nice day!

  7. #7
    Join Date
    Aug 2011
    Location
    Indianapolis, IN, USA
    Posts
    41
    Thanks for the queries, Sinndho. I also posted my question on another forum and got some answers that were very similar to yours but the others were not as explanatory as yours. Thanks again.

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

Posting Permissions

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