Force Write of Detail Data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form where, in the detail section I display and allow the user to
enter/edit time records. I want to have a total at the bottom that updates
as they enter the data. How do I force the writing of data to the datafile
so that I can run a 'dsum' function in the 'AfterUpdate' subroutine of that
field?
 
Bob,

As is often discussed here, storing calculations in a table field is
redundant, slows your application, and risks error if the fields included in
the calculation are changed outside the context of your form. You can always
do this calculation in a query accurately & faster whenever you need it.

If you give more detail about what you're trying to do, I'll try to help you
implement it.

Sprinks
 
Take a look at the Orders form and the Orders sub form in Northwind.mdb that
ships with Access. There is a good example of how that is done.
 
I see that they use a 'sum' function in Northwind.mdb. My problem is that I
have to use a function which reads data from the underlying file, not the
fields on the form. The reason is that I'm totaling hours/day and there may
be more than one record per day. I think that I still need a way to force
the save of the record to the database but without disrupting the screen
(e.g. moving the cursor to the top record)
 
Okay, I got it now. I was not fully understanding what you are doing. You
can try just saving the current record with:
Me.Dirty = False
Then you will need to Requery your form to ensure it is in the table.
Now is the tricky part. When you requery, the form will not stay on the
current record, but will go back to the first record in the table based on
the order of the table or the OrderBy in your form. So, what you have to do
is save the primary key of the current record, Do the requery, then use the
variable you saved the key in to reposition you to the record you were on.
Then call the function to to you Dsum.

So, some untested air code:

Application.Echo False
Me.Dirty = False
strSaveKey = Me.txtKeyField
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[KeyField] = '" & strSaveKey & "'"
Me.Bookmark = rst.Bookmark
set rst = Nothing
Me.txtSumTotal = DSum(whatever)
Application.Echo True
 
Back
Top