Quote:
Originally Posted by Harmanj1
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.
dBforums
>
Updating a Table
01-04-12, 15:42
Linear Mode
