Edit records in a subform

B

Bill

I have a subform "TransAcctSubform" whose
RecordSource contains records all of whose
field names begin with "Trans". For purposes
of explanation here, envision a transaction
ledger in which each record contains expense,
income and running total amounts.

The code below is intended to loop through
the records and update the running totals by
algebraically adding the expense and income
to the previous total and saving the new total
in the current record, (see the statement next
in sequence to the ".Edit").

The code runs without any errors, but has no
effect on the totals. It's as though the addition
statement doesn't execute. Since this is my
first shot at attempting updates to recordsets
within a subform, I have to assume here that
I'm not addressing the record elements
correctly...........or something like that.

Any glaring issues jump out?

Dim tempAmt As Currency
If Me!TransAcctSubform.Form.Dirty Then
Me!TransAcctSubform.Form.Dirty = False
End If

Me!TransAcctSubform.Form.RecordsetClone.MoveFirst
Me!TransAcctSubform.Form.Bookmark =
Me.TransAcctSubform.Form.RecordsetClone.Bookmark
tempAmt = Me!TransAcctSubform.Form.[TransBal]

With Me!TransAcctSubform.Form.RecordsetClone
.MoveNext
While Not (.EOF)
.Edit
.[TransBal] = .[TransBal] + .[TransExp] + .[TransInc]
.MoveNext
Wend
End With
End Sub
 
S

Stuart McCall

Bill said:
I have a subform "TransAcctSubform" whose
RecordSource contains records all of whose
field names begin with "Trans". For purposes
of explanation here, envision a transaction
ledger in which each record contains expense,
income and running total amounts.

The code below is intended to loop through
the records and update the running totals by
algebraically adding the expense and income
to the previous total and saving the new total
in the current record, (see the statement next
in sequence to the ".Edit").

The code runs without any errors, but has no
effect on the totals. It's as though the addition
statement doesn't execute. Since this is my
first shot at attempting updates to recordsets
within a subform, I have to assume here that
I'm not addressing the record elements
correctly...........or something like that.

Any glaring issues jump out?

Dim tempAmt As Currency
If Me!TransAcctSubform.Form.Dirty Then
Me!TransAcctSubform.Form.Dirty = False
End If

Me!TransAcctSubform.Form.RecordsetClone.MoveFirst
Me!TransAcctSubform.Form.Bookmark =
Me.TransAcctSubform.Form.RecordsetClone.Bookmark
tempAmt = Me!TransAcctSubform.Form.[TransBal]

With Me!TransAcctSubform.Form.RecordsetClone
.MoveNext
While Not (.EOF)
.Edit
.[TransBal] = .[TransBal] + .[TransExp] + .[TransInc]
.MoveNext
Wend
End With
End Sub
Click to expand...

You're missing an update call.

While Not (.EOF)
.Edit
.[TransBal] = .[TransBal] + .[TransExp] + .[TransInc]
.Update
.MoveNext
Wend

.... so nothing's being updated. Don't kick yourself too hard :)
 
B

Bill

Stuart, while I too would think that would
resolve the issue, it didn't make any difference.
So, I'm still scratching my head!
Bill


Stuart McCall said:
Bill said:
I have a subform "TransAcctSubform" whose
RecordSource contains records all of whose
field names begin with "Trans". For purposes
of explanation here, envision a transaction
ledger in which each record contains expense,
income and running total amounts.

The code below is intended to loop through
the records and update the running totals by
algebraically adding the expense and income
to the previous total and saving the new total
in the current record, (see the statement next
in sequence to the ".Edit").

The code runs without any errors, but has no
effect on the totals. It's as though the addition
statement doesn't execute. Since this is my
first shot at attempting updates to recordsets
within a subform, I have to assume here that
I'm not addressing the record elements
correctly...........or something like that.

Any glaring issues jump out?

Dim tempAmt As Currency
If Me!TransAcctSubform.Form.Dirty Then
Me!TransAcctSubform.Form.Dirty = False
End If

Me!TransAcctSubform.Form.RecordsetClone.MoveFirst
Me!TransAcctSubform.Form.Bookmark =
Me.TransAcctSubform.Form.RecordsetClone.Bookmark
tempAmt = Me!TransAcctSubform.Form.[TransBal]

With Me!TransAcctSubform.Form.RecordsetClone
.MoveNext
While Not (.EOF)
.Edit
.[TransBal] = .[TransBal] + .[TransExp] + .[TransInc]
.MoveNext
Wend
End With
End Sub
Click to expand...

You're missing an update call.

While Not (.EOF)
.Edit
.[TransBal] = .[TransBal] + .[TransExp] + .[TransInc]
.Update
.MoveNext
Wend

... so nothing's being updated. Don't kick yourself too hard :)
Click to expand...
 
S

Stuart McCall

Bill said:
Stuart, while I too would think that would
resolve the issue, it didn't make any difference.
So, I'm still scratching my head!
Bill


Stuart McCall said:
Bill said:
I have a subform "TransAcctSubform" whose
RecordSource contains records all of whose
field names begin with "Trans". For purposes
of explanation here, envision a transaction
ledger in which each record contains expense,
income and running total amounts.

The code below is intended to loop through
the records and update the running totals by
algebraically adding the expense and income
to the previous total and saving the new total
in the current record, (see the statement next
in sequence to the ".Edit").

The code runs without any errors, but has no
effect on the totals. It's as though the addition
statement doesn't execute. Since this is my
first shot at attempting updates to recordsets
within a subform, I have to assume here that
I'm not addressing the record elements
correctly...........or something like that.

Any glaring issues jump out?

Dim tempAmt As Currency
If Me!TransAcctSubform.Form.Dirty Then
Me!TransAcctSubform.Form.Dirty = False
End If

Me!TransAcctSubform.Form.RecordsetClone.MoveFirst
Me!TransAcctSubform.Form.Bookmark =
Me.TransAcctSubform.Form.RecordsetClone.Bookmark
tempAmt = Me!TransAcctSubform.Form.[TransBal]

With Me!TransAcctSubform.Form.RecordsetClone
.MoveNext
While Not (.EOF)
.Edit
.[TransBal] = .[TransBal] + .[TransExp] + .[TransInc]
.MoveNext
Wend
End With
End Sub
Click to expand...

You're missing an update call.

While Not (.EOF)
.Edit
.[TransBal] = .[TransBal] + .[TransExp] + .[TransInc]
.Update
.MoveNext
Wend

... so nothing's being updated. Don't kick yourself too hard :)
Click to expand...
Click to expand...

Try changing the dots to bangs:

![TransBal] = ![TransBal] + ![TransExp] + ![TransInc]

(shouldve spotted that before, sorry)
 
B

Bill

Stuart, I must have some sort of DB conflict going
here. The scenario is that I have a table of
transactions from which a filtered set is currently
displayed in a subform. One of the actions for the
user is to specify a transfer of funds from the
current account (the filtered set in the subform)
to another account, which would be a different
filtered set from the SAME table. Two different
subs are employed to independently update the
balance figures after the "TO" and "FROM"
transactions are inserted into the table via
SQL INSERT INTO table........etc. in code.

Then, using the code you've been looking at,
I attempt to ripple through the updated filtered
set in the subform to reflect affected balances.
That still does not work even using the bang
notation as you already suggested.

Finally, the "TO" account, the second filtered
subset of the Transactions table, is essentially
updated the same similar code that accesses
the table using a connection and recordset
object model with the filtered table SELECT.
This too is NOT successful in effecting the
changes in the DB.

If I've created a DB conflict here, is there
anyway to resolve it other than to separate
the accounts into separate tables?




Stuart McCall said:
Bill said:
Stuart, while I too would think that would
resolve the issue, it didn't make any difference.
So, I'm still scratching my head!
Bill


Stuart McCall said:
I have a subform "TransAcctSubform" whose
RecordSource contains records all of whose
field names begin with "Trans". For purposes
of explanation here, envision a transaction
ledger in which each record contains expense,
income and running total amounts.

The code below is intended to loop through
the records and update the running totals by
algebraically adding the expense and income
to the previous total and saving the new total
in the current record, (see the statement next
in sequence to the ".Edit").

The code runs without any errors, but has no
effect on the totals. It's as though the addition
statement doesn't execute. Since this is my
first shot at attempting updates to recordsets
within a subform, I have to assume here that
I'm not addressing the record elements
correctly...........or something like that.

Any glaring issues jump out?

Dim tempAmt As Currency
If Me!TransAcctSubform.Form.Dirty Then
Me!TransAcctSubform.Form.Dirty = False
End If

Me!TransAcctSubform.Form.RecordsetClone.MoveFirst
Me!TransAcctSubform.Form.Bookmark =
Me.TransAcctSubform.Form.RecordsetClone.Bookmark
tempAmt = Me!TransAcctSubform.Form.[TransBal]

With Me!TransAcctSubform.Form.RecordsetClone
.MoveNext
While Not (.EOF)
.Edit
.[TransBal] = .[TransBal] + .[TransExp] + .[TransInc]
.MoveNext
Wend
End With
End Sub

You're missing an update call.

While Not (.EOF)
.Edit
.[TransBal] = .[TransBal] + .[TransExp] + .[TransInc]
.Update
.MoveNext
Wend

... so nothing's being updated. Don't kick yourself too hard :)
Click to expand...
Click to expand...

Try changing the dots to bangs:

![TransBal] = ![TransBal] + ![TransExp] + ![TransInc]

(shouldve spotted that before, sorry)
Click to expand...
 
B

Bill

Stuart, I isolated the update code for the "TO"
account (filtered) and ran it in a general module
to see if it worked independently. It DOES NOT!
Here's the code:
(Note that TransExp & TransInc are exclusively
NULL to each other, i.e., one or the other will
be NULL.)

Option Compare Database
Options Explicit

Private Sub RippleTargetBal()
Dim rs As DAO.Recordset
Dim strSQL As String
Dim tempBal As Variant
Dim ID As String

ID = "S12-2012BS"
' Open the "TO" account (Filtered Transactions)
strSQL = "SELECT * FROM Transactions"
strSQL = strSQL & " WHERE [FolioID] = " & """" & ID & """"
strSQL = strSQL & " ORDER BY [TransDate] & """","""" & [TransSer];"
Debug.Print strSQL
Set rs = DBEngine(0)(0).OpenRecordset(strSQL)

tempBal = rs!TransBal
rs.MoveNext

While (Not (rs.EOF))
rs.Edit
rs!TransBal = tempBal + rs!TransExp + rs!TransInc
rs.Update
tempBal = rs!TransBal
rs.MoveNext
Wend


' Close the recordset and the database.
rs.Close
Set rs = Nothing


End Sub



Bill said:
Stuart, I must have some sort of DB conflict going
here. The scenario is that I have a table of
transactions from which a filtered set is currently
displayed in a subform. One of the actions for the
user is to specify a transfer of funds from the
current account (the filtered set in the subform)
to another account, which would be a different
filtered set from the SAME table. Two different
subs are employed to independently update the
balance figures after the "TO" and "FROM"
transactions are inserted into the table via
SQL INSERT INTO table........etc. in code.

Then, using the code you've been looking at,
I attempt to ripple through the updated filtered
set in the subform to reflect affected balances.
That still does not work even using the bang
notation as you already suggested.

Finally, the "TO" account, the second filtered
subset of the Transactions table, is essentially
updated the same similar code that accesses
the table using a connection and recordset
object model with the filtered table SELECT.
This too is NOT successful in effecting the
changes in the DB.

If I've created a DB conflict here, is there
anyway to resolve it other than to separate
the accounts into separate tables?




Stuart McCall said:
Bill said:
Stuart, while I too would think that would
resolve the issue, it didn't make any difference.
So, I'm still scratching my head!
Bill


I have a subform "TransAcctSubform" whose
RecordSource contains records all of whose
field names begin with "Trans". For purposes
of explanation here, envision a transaction
ledger in which each record contains expense,
income and running total amounts.

The code below is intended to loop through
the records and update the running totals by
algebraically adding the expense and income
to the previous total and saving the new total
in the current record, (see the statement next
in sequence to the ".Edit").

The code runs without any errors, but has no
effect on the totals. It's as though the addition
statement doesn't execute. Since this is my
first shot at attempting updates to recordsets
within a subform, I have to assume here that
I'm not addressing the record elements
correctly...........or something like that.

Any glaring issues jump out?

Dim tempAmt As Currency
If Me!TransAcctSubform.Form.Dirty Then
Me!TransAcctSubform.Form.Dirty = False
End If

Me!TransAcctSubform.Form.RecordsetClone.MoveFirst
Me!TransAcctSubform.Form.Bookmark =
Me.TransAcctSubform.Form.RecordsetClone.Bookmark
tempAmt = Me!TransAcctSubform.Form.[TransBal]

With Me!TransAcctSubform.Form.RecordsetClone
.MoveNext
While Not (.EOF)
.Edit
.[TransBal] = .[TransBal] + .[TransExp] + .[TransInc]
.MoveNext
Wend
End With
End Sub

You're missing an update call.

While Not (.EOF)
.Edit
.[TransBal] = .[TransBal] + .[TransExp] + .[TransInc]
.Update
.MoveNext
Wend

... so nothing's being updated. Don't kick yourself too hard :)
Click to expand...

Try changing the dots to bangs:

![TransBal] = ![TransBal] + ![TransExp] + ![TransInc]

(shouldve spotted that before, sorry)
Click to expand...
Click to expand...
 
B

Bill

FOUND IT!!!

The addition string, by the nature of the amounts
involved, contained NULL values. I just added
code to use temporary variables substituting zero
(0) for NULL, and performing the addition using
the temporary variables.

What a frustrating pain this was.

Thanks for all your thoughts.

Bill



Bill said:
Stuart, I isolated the update code for the "TO"
account (filtered) and ran it in a general module
to see if it worked independently. It DOES NOT!
Here's the code:
(Note that TransExp & TransInc are exclusively
NULL to each other, i.e., one or the other will
be NULL.)

Option Compare Database
Options Explicit

Private Sub RippleTargetBal()
Dim rs As DAO.Recordset
Dim strSQL As String
Dim tempBal As Variant
Dim ID As String

ID = "S12-2012BS"
' Open the "TO" account (Filtered Transactions)
strSQL = "SELECT * FROM Transactions"
strSQL = strSQL & " WHERE [FolioID] = " & """" & ID & """"
strSQL = strSQL & " ORDER BY [TransDate] & """","""" & [TransSer];"
Debug.Print strSQL
Set rs = DBEngine(0)(0).OpenRecordset(strSQL)

tempBal = rs!TransBal
rs.MoveNext

While (Not (rs.EOF))
rs.Edit
rs!TransBal = tempBal + rs!TransExp + rs!TransInc
rs.Update
tempBal = rs!TransBal
rs.MoveNext
Wend


' Close the recordset and the database.
rs.Close
Set rs = Nothing


End Sub



Bill said:
Stuart, I must have some sort of DB conflict going
here. The scenario is that I have a table of
transactions from which a filtered set is currently
displayed in a subform. One of the actions for the
user is to specify a transfer of funds from the
current account (the filtered set in the subform)
to another account, which would be a different
filtered set from the SAME table. Two different
subs are employed to independently update the
balance figures after the "TO" and "FROM"
transactions are inserted into the table via
SQL INSERT INTO table........etc. in code.

Then, using the code you've been looking at,
I attempt to ripple through the updated filtered
set in the subform to reflect affected balances.
That still does not work even using the bang
notation as you already suggested.

Finally, the "TO" account, the second filtered
subset of the Transactions table, is essentially
updated the same similar code that accesses
the table using a connection and recordset
object model with the filtered table SELECT.
This too is NOT successful in effecting the
changes in the DB.

If I've created a DB conflict here, is there
anyway to resolve it other than to separate
the accounts into separate tables?




Stuart McCall said:
Stuart, while I too would think that would
resolve the issue, it didn't make any difference.
So, I'm still scratching my head!
Bill


I have a subform "TransAcctSubform" whose
RecordSource contains records all of whose
field names begin with "Trans". For purposes
of explanation here, envision a transaction
ledger in which each record contains expense,
income and running total amounts.

The code below is intended to loop through
the records and update the running totals by
algebraically adding the expense and income
to the previous total and saving the new total
in the current record, (see the statement next
in sequence to the ".Edit").

The code runs without any errors, but has no
effect on the totals. It's as though the addition
statement doesn't execute. Since this is my
first shot at attempting updates to recordsets
within a subform, I have to assume here that
I'm not addressing the record elements
correctly...........or something like that.

Any glaring issues jump out?

Dim tempAmt As Currency
If Me!TransAcctSubform.Form.Dirty Then
Me!TransAcctSubform.Form.Dirty = False
End If

Me!TransAcctSubform.Form.RecordsetClone.MoveFirst
Me!TransAcctSubform.Form.Bookmark =
Me.TransAcctSubform.Form.RecordsetClone.Bookmark
tempAmt = Me!TransAcctSubform.Form.[TransBal]

With Me!TransAcctSubform.Form.RecordsetClone
.MoveNext
While Not (.EOF)
.Edit
.[TransBal] = .[TransBal] + .[TransExp] + .[TransInc]
.MoveNext
Wend
End With
End Sub

You're missing an update call.

While Not (.EOF)
.Edit
.[TransBal] = .[TransBal] + .[TransExp] + .[TransInc]
.Update
.MoveNext
Wend

... so nothing's being updated. Don't kick yourself too hard :)

Try changing the dots to bangs:

![TransBal] = ![TransBal] + ![TransExp] + ![TransInc]

(shouldve spotted that before, sorry)
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filter main form to records with no related records in subform,maintain updateable recordset 4
can't lock a subform in subform current event 2
How to position records in a scrolling subform 5
wrong subform total 2
Locate record in a subform 0
Basing a subform on a selection from either of two subforms 3
Can't edit data in a subform 1
How programmatically Delete all records in a subform 3

Top