Stop Code

G

Guest

Hi Groupies!

I need some help with making my code stop processing anything else.

I need to be able to delete information about a student while on the student
form, but I do not want any other code or tests to run after deleting the
student. At the moment, after pushing the delete button, I will receive
numerous warnings that the record was deleted by another user or the debugger
will get hung up on a different subroutine for the Current event of the
student form that I am on.

Here is the critical part of the code for my command button:

<snip>
If MsgBox(strMsg, vbYesNo + vbExclamation, "DELETE CHILD") = vbNo Then
Exit Sub

Else
docmd.SetWarnings False
docmd.Hourglass True

docmd.OpenQuery "qdelAppointments"
docmd.OpenQuery "qdelChildOT"
docmd.OpenQuery "qdelChildPT"
docmd.OpenQuery "qdelStudentDetails"
docmd.OpenQuery "qdelStudent"

docmd.Hourglass False

Me.cmbChildSearch.Requery

MsgBox "Child Successfully Deleted!", vbOKOnly, "DELETE CHILD"
docmd.SetWarnings True

End If

Exit_cmdDeleteChild_Click:
Exit Sub

Err_cmdDeleteChild_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteChild_Click
<snip>

I think part of the problem may be that not all students will show up in
some of the delete queries that I ask to run. However, I do not know how to
program in a test for that.

Thanks for the brainwaves!
 
G

Guest

Hi CJ,

The easiest way is to have an On Error Resume Next just before teh first of
your delete queries... it won't address the underlying issue as to why you
are getting errors, but it will hide them from you!! From your post, you
don't seem too concerned about the errors, so this might be the way to go!

Hope this helps...

Damian.
 
G

Guest

Well, I tried your suggestion Damian but unfortunately, I still have the same
problem.

"Record in tblChildOT was deleted by another user" is what I am getting.

Any other ideas?
 
G

Gina Whipp

CJ,

I think it would help if you copy/print your entire code (snipping part
ssometimes leaves out 'critical need to know information'). Then you state
that "or the debugger will get hung up on a different subroutine for the
Current event of the student form that I am on.", well no one here can see
what or where your code gets hung up so you might want to copy/print that
also.

HTH,
Gina Whipp
 
D

David F Cox

My not-yet-woken-up hunch is that you might have cascading deletes in
operation
 
G

Guest

OK, here is all of the code for the command button that deletes the student:

Private Sub cmdDeleteChild_Click()
On Error GoTo Err_cmdDeleteChild_Click

Dim strMsg As String

' strMsg = "Are You Sure You Want To Delete" & vbCrLf
strMsg = "Delete All Of The Personal Data" & vbCrLf
strMsg = strMsg & " AND" & vbCrLf
strMsg = strMsg & " Delete All Of The History" & vbCrLf
strMsg = strMsg & " For This Child?"

If MsgBox(strMsg, vbYesNo + vbExclamation, "DELETE CHILD") = vbNo Then
Exit Sub

Else
docmd.SetWarnings False
docmd.Hourglass True

On Error Resume Next
docmd.OpenQuery "qdelAppointments"
docmd.OpenQuery "qdelChildOT"
docmd.OpenQuery "qdelChildPT"
docmd.OpenQuery "qdelStudentDetails"
docmd.OpenQuery "qdelStudent"

docmd.Hourglass False

Me.cmbChildSearch.Requery

MsgBox "Child Successfully Deleted!", vbOKOnly, "DELETE CHILD"
docmd.SetWarnings True

End If

Exit_cmdDeleteChild_Click:
Exit Sub

Err_cmdDeleteChild_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteChild_Click

End Sub

After using the button I get the message boxes that I put in, then I receive:

"Record is Deleted"

Then a bunch of run time errors that either say "Record in tblChildPT was
deleted by another user" or "Record in tblChildOT was deleted by another
user". The child I just deleted did not have any data in either of these two
tables.

When I debug these errors, I get held up on this code which is similar in
tblChildPT:

Private Sub lngTherapistID_Exit(Cancel As Integer)

If Me.lngTherapistID = "6" Then
Me.strOTActiveID = "OT03"
docmd.GoToControl "datOTDC"
Else
Me.strOTActiveID = "OT01" <BREAK POINT>
docmd.GoToControl "lngPUFHrsOT"
End If

End Sub

I don't know why the delete query is running through the code after it
deletes the student. I guess if I could change the tblChildOT and tblChildPT
code to only apply to new records the problem could correct itself.


Not sure what to do!!
 
G

Gina Whipp

Two things I noticed, though not sure if this is the problem but fix and
let's see what's happens.
Move the 'Error Resume Next', rem line 'On Error GoTo
Err_cmdDeleteChild_Click' and get rid of everything between End If and End
Sub see below.

Also what is... Me.cmbChildSearch.Requery used for? The Requery might
trigger a few of those unwanted messages.

HTH,
Gina Whipp

Private Sub cmdDeleteChild_Click()
'DO NOT USE On Error GoTo Err_cmdDeleteChild_Click
On Error Resume Next

Dim strMsg As String

' strMsg = "Are You Sure You Want To Delete" & vbCrLf
strMsg = "Delete All Of The Personal Data" & vbCrLf
strMsg = strMsg & " AND" & vbCrLf
strMsg = strMsg & " Delete All Of The History" & vbCrLf
strMsg = strMsg & " For This Child?"

If MsgBox(strMsg, vbYesNo + vbExclamation, "DELETE CHILD") = vbNo Then
Exit Sub

Else
docmd.SetWarnings False
docmd.Hourglass True

'NOT THE PLACE TO USE THIS On Error Resume Next
docmd.OpenQuery "qdelAppointments"
docmd.OpenQuery "qdelChildOT"
docmd.OpenQuery "qdelChildPT"
docmd.OpenQuery "qdelStudentDetails"
docmd.OpenQuery "qdelStudent"

docmd.Hourglass False

Me.cmbChildSearch.Requery

MsgBox "Child Successfully Deleted!", vbOKOnly, "DELETE CHILD"
docmd.SetWarnings True

End If
End Sub
 
G

Gina Whipp

Okay I have 2 suggestions below... If they both error out the only other
thing I can thing of is make sure your tables are set for cascading deletes
(but I think you already did this) OR rem out each query seperately and run
until you find the query that it is erroring out on. Let me know, I'm on a
mission now...

HTH,
Gina Whipp

1)

Private Sub cmdDeleteChild_Click()
On Error Resume Next

Dim strMsg As String

strMsg = "Delete All Of The Personal Data" & vbCrLf
strMsg = strMsg & " AND" & vbCrLf
strMsg = strMsg & " Delete All Of The History" & vbCrLf
strMsg = strMsg & " For This Child?"

If MsgBox(strMsg, vbYesNo + vbExclamation, "DELETE CHILD") = vbNo Then
Exit Sub

Else
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdelAppointments"
DoCmd.OpenQuery "qdelChildOT"
DoCmd.OpenQuery "qdelChildPT"
DoCmd.OpenQuery "qdelStudentDetails"
DoCmd.OpenQuery "qdelStudent"
'rem for now Me.cmbChildSearch.Requery
DoCmd.SetWarnings True

MsgBox "Child Successfully Deleted!", vbOKOnly, "DELETE CHILD"

End If
End Sub

OR

2)

Private Sub cmdDeleteChild_Click()
On Error Resume Next
Dim Msg, Style, Title, Response, MyString

Msg = "Delete All Of The Personal Data AND Delete All Of The History
For This Child?"
Style = vbYesNo + vbExclamation + vbDefaultButton2
Title = "DELETE CHILD"

Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then
MyString = "Yes"
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdelAppointments"
DoCmd.OpenQuery "qdelChildOT"
DoCmd.OpenQuery "qdelChildPT"
DoCmd.OpenQuery "qdelStudentDetails"
DoCmd.OpenQuery "qdelStudent"
DoCmd.SetWarnings True
Else
MyString = "No"
DoCmd.CancelEvent
End If
End Sub
 
D

David F Cox

I quote:-

"No cascading anywhere."

My first thought was that there were complex relationships and cascading
deletes were set, so that the same record was selected more than once for
deletion and, of course, only the first delete could succeed.
 
G

Gina Whipp

David,

I saw that... that's why I put "...(but I think you already did this)..."
but it can never hurt to check again. I know sometimes I overlook such
things and aside from your suggestion of looking at the SQL for the queries
(which was next on my list if the two examples I gave didn't work) I was at
a lose.

BTW... Glad to see you back, I saw a message that, in essence, said
good-bye!

Gina Whipp
 
G

Guest

Gina and David
Thanks for your help, I have solved the problem. The trouble was in the
piece of code for tblChildOT and for tblChildPT.

I'm not sure why, but the query was running through the code and hanging
when it got to making strOTActiveID = "01" (see below).

Private Sub lngTherapistID_Exit(Cancel As Integer)

If Me.lngTherapistID = "6" Then
Me.strOTActiveID = "OT03"
docmd.GoToControl "datOTDC"
Else
Me.strOTActiveID = "OT01" <BREAK POINT>
docmd.GoToControl "lngPUFHrsOT"
End If

End Sub

I changed the above code to run on the Change event instead of Exit and No
Errors!!!! The TherapistID is not changing because the record has been
deleted.

I'm still not sure why it runs through the code but I guess I'll just chalk
it up to experience.

Thanks for all of your help!
 

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

trouble with requery 3
coding questions 6
Requery not good on delete 5
Why does 'set warnings' throws debugger? 6
I have an If, Then, Else problem 11
Q: Run 2 queries from one button problem 1
Simplify Code-Delete Query 3
Code fails, but then works! 2

Top