Subform not updating after deletion

I have a form which is used as a subform on a main form.

The subform consists of payments, which are entered by a user. There is a running total of the payment amounts, stored in a control called Text10 in the footer of the subform. As payments are entered, the total is recomputed and then Text10.Value is picked up and displayed on the main form in a control called TotalPaid. The subform also has an event procedure which must run AFTER the Text10.Value is reflected in the TotalPaid control. All of this works fine as payments are being ENTERED.

However, if a user DELETES a payment from the subform, I still need the event procedure to run AFTER the new Text10.Value is reflected in the TotalPaid control. And my problem is that Text10 is NOT recomputing automatically when I delete a payment record from the subform. I have tried setting up an AfterDelConfirm event with a Me.Requery and then resto fhte event code.... but the Me.Requery doesn't seem to force Text10 to update itself and get it's value pushed out to TotalPaid. So I dont know what to do... Text10 MUST be accurate before the rest of the event procedure runs.
LVL 8
Galisteo8Asked:
Who is Participating?
 
Gal_ateaConnect With a Mentor Commented:
Hi Galisteo,
    If you were using the CurrentDB.Execute method I would say that the "The Type Mismatch " is occurring because you are in effect passing a string value to your where statement for your date field by surrounding it with the single quotes. However I do not believe you can delete from a ADO recordset this way. The correct syntax for this should be something like:

    wRS_AuctPmt.Delete 'To delete the current record of the recordset
Or:
    wRS_AuctPmt.Delete, 3 'To delete all the records in the recordset

You could try:
'*************************************
wRS_AuctPmt.Open "select * from [Auct-PMT] where EbayNum = '" & Me.EbayNum.Value & "' AND [Date-Time] = " & Me.AuctionPmt_Subform![Date-Time] & ";"

cn.BeginTrans

wRS_AuctPmt.Delete, 3
wRS_AuctPmt..Update
wRS_AuctPmt.Close
'*************************************

Or:
'*************************************
Private Sub Command155_Click()
    Dim strSQL As String
    strSQL = "DELETE * FROM [dbo.AUCT-PMT] WHERE EbayNum ='" & Me!EbayNum & "' and where [Date-Time] =" & Me.AuctionPmt_Subform![Date-Time]
    DoCmd.RunSQL strSQL
    RecalcRequery
End Sub
0
 
Gal_ateaCommented:
A couple of questions:

1.) Is Text10 bound to a field in your table?

2.) How are you making you calculation?
0
 
Galisteo8Author Commented:
Each record in the subform has an Amount field. In the hidden footer of the subform is a control (Text10 - I didn't give it a 'real' name) that sums up the Amounts -- its Control Source is =Sum([Amount]).
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Gal_ateaCommented:
Have you tried Me!Text10.Refresh?

0
 
Galisteo8Author Commented:
When I try that, I get:

"Runtime error '438': Object doesn't support this property or method."
0
 
stevbeCommented:
Me.Repaint will not only force Access to finish any screen painting but also to complete and calculations that are pnding .. from the help file ...

The Repaint method completes any pending screen updates for a specified form. When performed on a form, the Repaint method also completes any pending recalculations of the form's controls.

Remarks
Microsoft Access sometimes waits to complete pending screen updates until it finishes other tasks. With the Repaint method, you can force immediate repainting of the controls on the specified form. You can use the Repaint method:

When you change values in a number of fields. Unless you force a repaint, Microsoft Access might not display the changes immediately, especially if other fields, such as those in an expression in a calculated control, depend on values in the changed fields.

When you want to make sure that a form displays data in all of its fields. For example, fields containing OLE objects often don't display their data immediately after you open a form.
This method doesn't cause a requery of the database, nor does it show new or changed records in the form's underlying record source. You can use the Requery method to requery the source of data for the form or one of its controls.
0
 
Galisteo8Author Commented:
Hmmm... Repaint didn't do it either.  It appears that Text10 doesn't bother to update itself until AFTER all the code runs -- basically at the end of the AfterDelConfirm sub. Here's what happens with the following code if I had $200 of payments and I was deleting one of them (a $50 payment):

Private Sub Form_AfterDelConfirm(Status As Integer)

MsgBox ("Text10 is currently " & Me.Text10)     '<-- Text10 is 200, and TotalPaid is 200)

Me.Repaint

MsgBox ("Text10 is now " & Me.Text10)     '<-- Text10 is STILL 200, and TotalPaid is still 200

Form_AfterUpdate '<-- During this code, the erroneous TotalPaid value is used for calculations

End Sub

After the sub runs, I see that TotalPaid is finally $150 -- but it happens AFTER all the critical code.
0
 
Gal_ateaCommented:
What about taking a slightly different approach and using a DSum in your critical code instead of the value of the calculated field?
0
 
stevbeCommented:
I assume that "must run AFTER the Text10.Value is reflected in the TotalPaid control" is because you are using the value in TotalPaid for the other process, you could reference the Text10.Value directly instead of TotalPaid.

Steve
0
 
Galisteo8Author Commented:
Gal_atea -- I cannot use DSum in an Access Database Project (ADP) file, which is what I've got..

Stevbe -- Even I referenced Text10 directly I'd still be using an erroneous value, as neither one of them is accurate until the sub finishes.
0
 
Gal_ateaCommented:
Galisteo8,
If you cannot use DSum then you could pull the information from an ADO recordset object in code. Something like this:

    Dim rs As Object
    Dim strSQL as String
    Dim MySum As Double

    Set rs = CreateObject("ADODB.Recordset")
    strSQL = "SELECT Sum(Ammount) AS AmtSum FROM YourTable;"

    rs.Open strSQL, CurrentProject.Connection, 2, 1

    If Not rs.BOF And Not rs.EOF Then
        MySum = rs!AmtSum
    End If

    rs.Close
    Set rs = Nothing
0
 
Galisteo8Author Commented:
At the moment, this subform has both an AfterUpdate event and the AfterDelConfirm event, since apparently deleting a record from the subform is not considered an "update".  SO.... since the AfterDelConfirm is giving me such a headache, is there some other event that would recognize that a deleteion has taken place, commit the deletion, and THEN run its event code?
0
 
Gal_ateaCommented:
My personal favorite is to disable "Allow Deletions" in the form and rather delete records via a command button in code. this method avoids using the forms events to monitor deletions and allows you to code in the same event. Something like this:

Private Sub cmdDelete_Click()
    Dim strSQL As String
    strSQL = "DELETE * FROM YourTable WHERE PKID =" & Me!PKID
    CurrentDB.Execute StrSQL
    Form_AfterUpdate    '<< Calls the code in the After Update Event
End Sub
0
 
Galisteo8Author Commented:
Hey, I was also pondering a SQL connection to do this, since anything I tried doing in the front-end wasn't getting anywhere. I even tried calc'ing what the new TotalPaid should be and just assigning it to the control, but it wouldn't let me do it in the VB code (since the control's data source is already a formula, I can't "over-ride" it).

Let me try your rs suggestion...
0
 
Galisteo8Author Commented:
Oooooh.... The button idea is also attractive. I'll report my progress (and any new questions) later on either of these suggestions.
0
 
Galisteo8Author Commented:
Wait a second -- It occurs to me that if I make the navigation buttons visible on the subform, then the user could simply delete a payment record from the subform using the little "red X" delete button. HOWEVER.... it is only available when there are no actual payments in the subform.    ??? Does this make sense?

Say, I go to main form record #10, and the subform lists three payments associated with that main record. Even if I specifically click on any of those three payments records, the "red X" navi button of the subform does not activate so I can use it to delete that subform record.

Then, if I move on to main form record #11 and there are no payments in the subform (that is, the subform datasheet shows only the "blank" row where a new record could be created), well then the subform's "red X" delete button is available!

Help me out here - why won't the simple solutions work? :( That's what the friggin' delete navi button is for, isn't it??
0
 
Galisteo8Author Commented:
Hello? Can anyone give me some feedback on my previous post? I'm quite confused as to why the "normal" Delete button (one of the navigational buttons) won't work on my subform...
0
 
Gal_ateaCommented:
Sorry Galisteo8,
I don't know the answer to your question. In my suggestion above I mentioned disabling allow delete for your form and deleting the record via a query. This would disable the delete button as far as I know in all cases.
0
 
Galisteo8Author Commented:
I haven't made any changes to the properties; at this point, Allow Delete is still Yes -- and the Delete button is not available (unless the subform has no records in it).  I'll go back to the command button idea you had suggested...
0
 
Galisteo8Author Commented:
P.S. To execute code from a command button, the code goes in the OnClick event, right????? Mine isn't doing anything...
0
 
Gal_ateaCommented:
Can you post your code?
0
 
Galisteo8Author Commented:
Forget my prior post!  :)
When the command button runs, I get the following error:

Run-time error '91':
Object variable or With block variable not set.

Here's the event code:

Private Sub Command155_Click()
MsgBox ("hello world")
    Dim strSQL As String
    strSQL = "DELETE * FROM dbo.AUCT-PMT WHERE EbayNum =" & Me!EbayNum & "and where Date-Time =" & [AuctionPmt Subform].[Form]![Date-Time]
    CurrentDb.Execute strSQL    '<< Here's what the de-bugger highlights as the problem statement
    RecalcRequery
End Sub


Bear in mind I've got an ADP -- an Access front-end and a SQL back-end, if that makes a difference in how I "connect" to the SQL db and execute the delete.
0
 
Gal_ateaCommented:
You could try:

Private Sub Command155_Click()
MsgBox ("hello world")
    Dim strSQL As String
    strSQL = "DELETE * FROM dbo.AUCT-PMT WHERE EbayNum =" & Me!EbayNum & "and where Date-Time =" & [Me]![AuctionPmt Subform]![Date-Time]
    CurrentDb.Execute strSQL
    RecalcRequery
End Sub
 
 
0
 
Galisteo8Author Commented:
LOL - I didn't realize I'd left my MsgBox in there.

I had to rewrite the reference as Me.AuctionPmt_Subform![Date-Time], and I have verified that it is returning the proper value. But I am still getting the "Run-time error '91': Object variable or With block variable not set."

My code is as follows:

Private Sub Command155_Click()
    Dim strSQL As String
    strSQL = "DELETE * FROM dbo.AUCT-PMT WHERE EbayNum =" & Me!EbayNum & "and where Date-Time =" & Me.AuctionPmt_Subform![Date-Time]
    CurrentDb.Execute strSQL  '<< this is still the line where the error is generated
    RecalcRequery
End Sub
0
 
Gal_ateaCommented:
It could be that Access isn't recognizing your table/field names properly due to the hyphens, so you could try putting them in brackets, like this:

Private Sub Command155_Click()
    Dim strSQL As String
    strSQL = "DELETE * FROM [dbo.AUCT-PMT] WHERE EbayNum =" & Me!EbayNum & "and where [Date-Time] =" & Me.AuctionPmt_Subform![Date-Time]
    CurrentDb.Execute strSQL
    RecalcRequery
End Sub
0
 
Galisteo8Author Commented:
I kept getting tripped up on the CurrentDb.Execute line, so I have restructured my code to set up and execute a SQL transaction, using ADO.
Now, I'm getting a type mismatch (error #13) on the noted line:
******************************
Private Sub Command155_Click()
   
Dim cn As ADODB.Connection
Dim wRS_AuctPmt As ADODB.Recordset
Dim strSQL As String

Set cn = CurrentProject.Connection
cn.CursorLocation = adUseServer

Set wRS_AuctPmt = New ADODB.Recordset
Set wRS_AuctPmt.ActiveConnection = cn
wRS_AuctPmt.LockType = adLockPessimistic  'No one else will be using this record
wRS_AuctPmt.CursorType = adOpenDynamic
wRS_AuctPmt.Open "select * from [Auct-PMT] where EbayNum = '" & Me.EbayNum.Value & "'"

cn.BeginTrans

wRS_AuctPmt.Delete "DELETE * FROM Auct-PMT where [Date-Time] = '" & Me.AuctionPmt_Subform![Date-Time] & "'"  '<< The Type Mismatch occurs on this line
wRS_AuctPmt.Close
Set wRS_AuctPmt = Nothing

cn.CommitTrans
Set cn = Nothing

RecalcRequery

End Sub
******************************

ANyone know what is causing the error?
0
 
Gal_ateaCommented:
If the above suggestions do not work, could you host your database (or a dummy sample) on a web page that I can download it from? So far I've been working in theory and using "air" code, but I might be able to get to the bottom of the problem faster if I could actually work with the data.
0
 
Galisteo8Author Commented:
Reucing the command to simply     wRS_AuctPmt.Delete     did the trick. I obviously had the syntax all wrong, since I assumed I would need to include the actual SQL command as a string, similar to the one used with the      wRS_AuctPmt.Open      command.

I originally put quotes around the date filed after I first got the Type Mismatch error, since the date field also includes a timestamp. That didn't help, apparently, but it also doesn't seem to have hurt. :)
0
 
Galisteo8Author Commented:
I spoke too soon.  I am trying to use this code....

   wRS_AuctPmt.Open "select * from [Auct-PMT] where EbayNum = '" & Me.EbayNum.Value & "' AND [Date-Time] = " & Me.AuctionPmt_Subform![Date-Time] & ";"

   wRS_AuctPmt.Delete

... but I am having another problem elsewhere in the code that's preventing me from determining if this works for me.

By the way, why ddid you end the Select statement with a semi-colon?
0
 
Galisteo8Author Commented:
I don't know what the problem was. It very nearly went away by itself...  <scratching head>

Anyway, you code suggestion seems to be working just fine. Thaknks for the help.
0
 
Galisteo8Author Commented:
Actually, even though I am using a connection to the databast to ensure that the deleted record actually deletes, I still need to have the data in the Access Form get updated even BEFORE the RecalcRequery function kicks in.

So, for anyone curious, my final code for this "Delete" command button looks like this:

*************************
Private Sub Command155_Click()
   
Dim cn As ADODB.Connection
Dim wRS_AuctPmt As ADODB.Recordset
Dim oF2 As Form_AuctionForm
Dim auPK As String

Set cn = CurrentProject.Connection
cn.CursorLocation = adUseServer

Set wRS_AuctPmt = New ADODB.Recordset
Set wRS_AuctPmt.ActiveConnection = cn
wRS_AuctPmt.LockType = adLockPessimistic
wRS_AuctPmt.CursorType = adOpenDynamic
' Select the record that matches both the EbayNum of the main form,
' and the date-time of the selected subform record
wRS_AuctPmt.Open "select * from [Auct-PMT] where EbayNum = '" & Me.EbayNum.Value & "' and [Date-Time] = '" & Me.AuctionPmt_Subform![Date-Time] & "'"

cn.BeginTrans
wRS_AuctPmt.Delete ' Deletes the selected record
wRS_AuctPmt.Update
wRS_AuctPmt.Close
Set wRS_AuctPmt = Nothing
cn.CommitTrans
Set cn = Nothing

'Need to refresh AuctionForm to force TotalPaid to reflect deleted payment
   Set oF2 = goContext.anAuctionForm
   auPK = oF2!EbayNum
   oF2.Requery
   oF2.GotoAuction auPK

RecalcRequery ' Calls separate code to update forms

End Sub
0
 
Gal_ateaCommented:
Thanks for the points. Glad I was able to help.

>>By the way, why did you end the Select statement with a semi-colon?

Technically the correct way to end an SQL statement is with a semi-colon. I should have ended the Delete statement with it as well, but I missed that. Access can usually handle an SQL statement that does not end with a semi-colon, but to be safe it's best to do it properly.
0
 
Galisteo8Author Commented:
Oh, that's right.  Been a while since I did SQL by itself...
0
All Courses

From novice to tech pro — start learning today.