Solved

Subform not updating after deletion

Posted on 2004-10-20
33
255 Views
Last Modified: 2010-08-05
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.
0
Comment
Question by:Galisteo8
  • 19
  • 12
  • 2
33 Comments
 
LVL 4

Expert Comment

by:Gal_atea
ID: 12363321
A couple of questions:

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

2.) How are you making you calculation?
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12363485
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
 
LVL 4

Expert Comment

by:Gal_atea
ID: 12363509
Have you tried Me!Text10.Refresh?

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12363641
When I try that, I get:

"Runtime error '438': Object doesn't support this property or method."
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12363811
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
 
LVL 8

Author Comment

by:Galisteo8
ID: 12363981
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
 
LVL 4

Expert Comment

by:Gal_atea
ID: 12364028
What about taking a slightly different approach and using a DSum in your critical code instead of the value of the calculated field?
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12364333
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
 
LVL 8

Author Comment

by:Galisteo8
ID: 12371210
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
 
LVL 4

Expert Comment

by:Gal_atea
ID: 12371965
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
 
LVL 8

Author Comment

by:Galisteo8
ID: 12371971
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
 
LVL 4

Expert Comment

by:Gal_atea
ID: 12372084
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
 
LVL 8

Author Comment

by:Galisteo8
ID: 12372097
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
 
LVL 8

Author Comment

by:Galisteo8
ID: 12372129
Oooooh.... The button idea is also attractive. I'll report my progress (and any new questions) later on either of these suggestions.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12385154
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
 
LVL 8

Author Comment

by:Galisteo8
ID: 12401844
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 4

Expert Comment

by:Gal_atea
ID: 12401897
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
 
LVL 8

Author Comment

by:Galisteo8
ID: 12403775
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
 
LVL 8

Author Comment

by:Galisteo8
ID: 12404390
P.S. To execute code from a command button, the code goes in the OnClick event, right????? Mine isn't doing anything...
0
 
LVL 4

Expert Comment

by:Gal_atea
ID: 12404406
Can you post your code?
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12404469
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
 
LVL 4

Expert Comment

by:Gal_atea
ID: 12404561
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
 
LVL 8

Author Comment

by:Galisteo8
ID: 12412736
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
 
LVL 4

Expert Comment

by:Gal_atea
ID: 12412882
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
 
LVL 8

Author Comment

by:Galisteo8
ID: 12414923
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
 
LVL 4

Accepted Solution

by:
Gal_atea earned 50 total points
ID: 12415671
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
 
LVL 4

Expert Comment

by:Gal_atea
ID: 12415695
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
 
LVL 8

Author Comment

by:Galisteo8
ID: 12427436
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
 
LVL 8

Author Comment

by:Galisteo8
ID: 12435318
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
 
LVL 8

Author Comment

by:Galisteo8
ID: 12436760
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
 
LVL 8

Author Comment

by:Galisteo8
ID: 12436846
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
 
LVL 4

Expert Comment

by:Gal_atea
ID: 12437588
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
 
LVL 8

Author Comment

by:Galisteo8
ID: 12439345
Oh, that's right.  Been a while since I did SQL by itself...
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now