On Current Event Runs 3 times when using a requery and delete

[hwkranger]

I don't know why, but when I do the following, the ONCURRENT event is run 3 times.

Private Sub Delete_Click()
'On Error GoTo DeleteClick_err
Dim Answer As Integer
Answer = MsgBox("Are you sure you want to delete this ticket?", 4, "Delete Ticket&quot
DoCmd.SetWarnings False
If Answer = 6 Then
If IsNull(Me.ID) = True Then
MsgBox ("You can't delete an Entry that doesn't exist!&quot
Exit Sub
End If
fDeleteTicketOffsets (wProofAdj)
DoCmd.RunCommand acCmdDeleteRecord
fRequeryScreen ("BalanceBranch&quot
End If
DoCmd.SetWarnings True
Exit Sub
DeleteClick_err:
MsgBox ("Deletion of this Entry is not permitted at this time.&quot
Resume Next
End Sub

THE FOLLOWING ARE THE 2 FUNCTIONS CALLED FROM THE SUB ABOVE:

Public Function fDeleteTicketOffsets(DeleteFromScreen As String)
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim prm As DAO.Parameter
Dim TicketNumber As Long

MsgBox ("Deleting Tickets...&quot
Set db = CurrentDb()

With CodeContextObject
TicketNumber = .ListID

'This is for proofadj's that has offset in the 'other' table, and for cash which has MTA tickets
If DLookup("[id]", "[tbDailyOtherTickets]", "[offsettoid] = " & TicketNumber) Then
Set qd = db.QueryDefs("qryDeleteTicketsOtherOffset&quot
If qd.ReturnsRecords = True Then
qd!deleteid = TicketNumber
qd.Execute
fShadelbls (DeleteFromScreen)
End If
Set qd = Nothing
End If

'This is for Proof tickets joined on a cash Adjustment
If DLookup("[id]", "[tbDailyProofTickets]", "[offsettoid] = " & TicketNumber) Then
Set qd = db.QueryDefs("qryDeleteTicketsProofOffset&quot
If qd.ReturnsRecords = True Then
qd!deleteid = TicketNumber
qd.Execute
fShadelbls (DeleteFromScreen)
End If
Set qd = Nothing
End If

End With
Set qd = Nothing
Set db = Nothing
End Function

Public Function fRequeryScreen(frmName As String)
'MsgBox ("requery&quot
'checks to see if the screen is open, if so it requeries the form
If SysCmd(acSysCmdGetObjectState, acForm, frmName) <> 0 Then
If Forms(frmName).CurrentView <> 0 Then
'DoCmd.RunCommand acCmdSaveRecord
Forms(frmName).Requery
End If
End If
End Function

I cant really fathom why the oncurrent is run 3 times, I dont' want it to run 3 times - just once after a delete. I understand the the requery method does trigger the oncurrent, but for the other 2 times the oncurrent event is triggered is a mystery to me. Can anyone explain to me why? I greatly appreciate this.

I want to thank everyone thas has helped me over the past few days, If you follow the posts I've been posting, I haven't had the easiest time with the work I'm currently engaged doing.

You guys are great! I try to post helpful tips when I get a chance.

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[vbajock]

DoCmd.RunCommand acCmdDeleteRecord
will trigger an oncurrent when the pointer is reset after the delete.

I didn't see the fShadelbls (DeleteFromScreen) code anywhere. The other one might be in there.

Why do you care if the OnCurrent is firing?

 

[hwkranger]

ok i'm losing it.

I spend 2 hours yesterday trying to figure out why it was firing 3 times. I didnt want it to fire 3 times because it runs a function that takes a second and I dont' want my users to wait 3 seconds everytime the oncurrent is run from a delete.

it's working now - just firing once.

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[bonneville88sse]

Hi hwkranger, i'm having the exactly same problem, how did you fix it?

 

[hwkranger]

I forget! LoL - I'm still working on this project, I've been on it for over a year now, and I wish - really wish I could remember how and why the event was firing multiple times.

but looking back - I did change my code. This is what I changed it to:

Private Sub Delete_Click()
'On Error GoTo DeleteClick_err
Dim Answer As Integer
Dim SaveControl As String

Answer = MsgBox("Are you sure you want to delete this ticket?", 4, "Delete Ticket")
DoCmd.SetWarnings False
SaveControl = Me.BeforeUpdate
Me.BeforeUpdate = vbNullString

If Answer = 6 Then
If IsNull(Me.ID) = True Then
MsgBox ("You can't delete an Entry that doesn't exist!")
Exit Sub
End If

'check to see if this ticket is used as a recovery ticket.
Dim rst As New ADODB.Recordset
Dim intID As Long
rst.Open "Select top 1 P.ID from tbdailyprooftickets as P where (p.offsettoid = " & Me.ID & ");", CurrentProject.Connection, adOpenStatic, adLockReadOnly
If Not rst.EOF Then
intID = rst!ID
rst.Close
rst.Open "Select top 1 P.ID from tbdailyprooftickets as P where (p.matchid = " & intID & ");", CurrentProject.Connection, adOpenStatic, adLockReadOnly
If Not rst.EOF Then
MsgBox ("You must first unlink this ticket in the research bucket before trying to delete it.")
Exit Sub
End If
End If
rst.Close

fDeleteTicketOffsets (wCashAdj)
fShadelbls (wCashAdj)
DoCmd.RunCommand acCmdDeleteRecord
fRequeryScreen ("BalanceBranch")
Me.BeforeUpdate = SaveControl
End If
DoCmd.SetWarnings True
Exit Sub
DeleteClick_err:
MsgBox ("Deletion of this Entry is not permitted at this time.")
Resume Next
End Sub


Public Function fIsWindowOpen(frmName As String) As Boolean
On Error GoTo fIsWindoOpen_Err
'fIsWindowOpen = False
'************** OLD CODE ********************
'If SysCmd(acSysCmdGetObjectState, acForm, frmName) <> 0 Then
' If Forms(frmName).CurrentView <> 0 Then
' fIsWindowOpen = True
' End If
'End If
'************** END OF OLD CODE *************

fIsWindowOpen = (CurrentProject.AllForms(frmName).IsLoaded)
Exit Function
fIsWindoOpen_Err:
If Err.Number = 2467 Then
fIsWindowOpen = False
Err.Clear
Resume Next
End If
End Function


Public Function fRequeryScreen(frmName As String)
'MsgBox ("requery")
'*****checks to see if the screen is open, if so it requeries the form**********
'MsgBox ("Requery Function")
If frmName = wOSCorrecting Then
'MsgBox ("Please Wait..")
Forms(frmName).Requery
'MsgBox ("Requery was Successful.")
Exit Function
End If

'MsgBox ("Check for open window")
If fIsWindowOpen(frmName) Then
' MsgBox ("Window is open - check for research")
'MsgBox ("True")
If frmName = wResearch Then
' MsgBox ("Research open - move to requery")
Forms(wResearch).List.Requery
' MsgBox ("Requery fin - exit")
Else
' MsgBox ("Requery other type than research")
Forms(frmName).Requery
' MsgBox ("requery fin - exit (2)")
End If
End If
'MsgBox ("End of function")
End Function

I do know that I had problems with the way I was checking to see if a window was open.... but hopefully maybe something here helps?

Randall Vollen
National City Bank Corp.

 

[willir]

Randall - Still working on your ticketing system I see. Great stuff

 

[hwkranger]

Seems like the App never stops growing...

It began as a flat Access 97 File.
It grew to a monsterour Access 97 File (flat)
Was normalized into a Access 2k File
Was Split into an Access2k FE MDB and SQL2k Backend
It is not an MDE linked to SQL2k.

The next step? Either an ADP/ADE or building a real app.

I missed the first 2 years on this project and picked up right before the normalization - but overall, this baby has grown from a table of a few hundred records, into 7 SQL databases (about half a gig each) - which still have to be combined.

But isn't that life? You still have all the answers and no questions?

Randall Vollen
National City Bank Corp.