Save parent form after transaction update

G

Guest

I've seen this question posted a few times in the last year, but never an
answer. Maybe there isn't one! But...here's my scenario...
I am creating a simple banking application for our life skills class.

For new accounts there is a main form to capture account holder info, then a
subform to capture account info, and it has a subform to capture the initial
deposit transaction.

I want the account balance in the account info subform/parent to update when
the deposit amount is entered into the transaction subform.

I use the AfterUpdate event in the transaction subform to update the balance
field in the table, now I need it to show on the screen.

I tried Forms!frmNewAccount!subfrmAccount.Requery but I get that message
about field needing to be saved before you can requery. I tried a couple of
variations on using Dirty but it didn't like that either.

I think I just want to refresh the parent form from the AfterUpdate event of
a field in the child form, but I can't figure out how. Any suggestions?

Thanks much,
Kelly
 
J

John Vinson

I've seen this question posted a few times in the last year, but never an
answer. Maybe there isn't one! But...here's my scenario...
I am creating a simple banking application for our life skills class.

For new accounts there is a main form to capture account holder info, then a
subform to capture account info, and it has a subform to capture the initial
deposit transaction.

I want the account balance in the account info subform/parent to update when
the deposit amount is entered into the transaction subform.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.
I use the AfterUpdate event in the transaction subform to update the balance
field in the table, now I need it to show on the screen.

I tried Forms!frmNewAccount!subfrmAccount.Requery but I get that message
about field needing to be saved before you can requery. I tried a couple of
variations on using Dirty but it didn't like that either.

I think I just want to refresh the parent form from the AfterUpdate event of
a field in the child form, but I can't figure out how. Any suggestions?

If you REALLY want to do this (and, again, It's A Bad Idea), you may
be able to use code like

Parent!txtAccountBalance = Me!txtCurrentBalance

in the Subform's AfterUpdate event, to "push" the value into a textbox
on the parent form.

John W. Vinson[MVP]
 
G

Guest

Without fully justifying why I don't think it is so terribly bad to keep a
running account balance instead of having to always recalculate it by
enumerating and adding/subtracting every transaction, I tried the following:

Parent!AccountBalance = Parent!AccountBalance + me.DepositAmount
but got another error - something about the record being changed.

The AccountBalance text box was not updated, and Access did what it does on
my other transaction screen -- when you leave the subform (which the above
code caused, in the same way an Update button does on my other form) the
transaction record gets saved and the data entry subform refreshes to a new
entry without keeping the transaction data just entered. Now I have no
visibility of my transaction or of my current balance.

I am interested in knowing why Access behaves this way, at least a
superficial knowledge. (This is another occurrance I have seen posted on the
board with no answer.) Since I am currently working on a class exercise, it
is not critical to make the system completely smooth and user friendly. But
I'd like to understand what is happening -- and why I can't refresh the
parent form.

Thanks for any help.
 
G

Guest

Ok - this just gets wierder.
I didn't do anything different, and now after the AfterUpdate code (below)
the parent form is refreshed.

Private Sub DepositAmount_AfterUpdate()

Dim AccountBalance
Dim strSQL As String
DoCmd.SetWarnings False
Me.Dirty = False
'Update account balance
If Not IsNull(Me.DepositAmount) And Me.DepositAmount > 0 Then
strSQL = "UPDATE tblBankAccounts SET tblBankAccounts.AccountBalance
= (tblBankAccounts.AccountBalance+"
strSQL = strSQL & Me.DepositAmount
strSQL = strSQL & ") WHERE tblBankAccounts.AccountID = "
strSQL = strSQL & Forms!frmNewAccountHolders!frmNewAccounts!AccountID
DoCmd.RunSQL strSQL
End If

DoCmd.SetWarnings True

End Sub

But...the child form seems to be refreshed as well (which may be triggering
the parent refresh)...and I don't want that to happen - I need the
transaction data to stay on the screen; it's like the subform reloads itself
- it's in data entry mode with no records. I think I'll just quit at this
point and let the class live with that anamoly. But...if you know why it does
that...

Thx
 
G

Guest

Sorry to keep replying to myself, but I found this on the board somewhere - I
changed the transaction subform to set DataEntry to No and the record doesn't
go away now. Go figure!
Thanks again!
 
J

John Vinson

Sorry to keep replying to myself, but I found this on the board somewhere - I
changed the transaction subform to set DataEntry to No and the record doesn't
go away now. Go figure!

AHA.

Read the Help on the DataEntry property.

It very specifically HIDES any existing data (it's there in the table
just not displayed on the form), showing you only the "blank" new
record.

John W. Vinson[MVP]
 
G

Guest

Yes, but usually only when it opens, not during use.
Normally when a form is open with DataEntry set to Yes, it opens with no
records but you can browse through the records you enter during that session.
After my code runs, it's like the 'session' ends and the form re-opens.
 

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

Top