Resume Without Error Message

D

DoveArrow

I'm trying to run the following event, but whenever I have a location
that has active advisors, in addition to running the delete query that
I want it to run, I get an error message that says "Resume Without
Error." What's going on?

Private Sub Inactive_AfterUpdate()
Dim db As Database
Dim rs As Recordset
Dim str As String

Set db = CurrentDb
Set rs = db.OpenRecordset("jtblLocationAdvisor", dbOpenDynaset,
dbSeeChanges)

On Error GoTo Err_Inactive_Click

If Me.Inactive = True Then
rs.MoveFirst
Do Until rs.EOF
If rs![Location ID] = Forms![frmLocationAdvisors]![Location
ID] Then
GoTo Duplicate_Inactive_Click
Else
rs.MoveNext
End If
Loop
End If

Exit_Inactive_Click:
Set db = Nothing
Set rs = Nothing
Exit Sub

Err_Inactive_Click:
MsgBox Err.Description
Resume Exit_Inactive_Click

Duplicate_Inactive_Click:
Select Case MsgBox("There are active advisors listed for this
location. If you wish to delete these advisors from this location,
click OK to continue.", vbOKCancel)
Case vbOK
DoCmd.OpenQuery "qdelLocationAdvisors"
Me.subfrmLocationAdvisors.Requery
Case vbCancel
Me.Inactive = False
End Select
Resume Exit_Inactive_Click

End Sub
 
B

Bob Quintal

I'm trying to run the following event, but whenever I have a
location that has active advisors, in addition to running the
delete query that I want it to run, I get an error message that
says "Resume Without Error." What's going on?
Click to expand...
Your code is NOT an event, it's a stew of two different events.
Your error handler ends with Resume Exit_Inactive_Click
but you have a label and what should be a separate sub or inline
statement block which ends with another Resume Exit_Inactive_Click
Change that second resume to Goto Exit_Inactive_Click.
Private Sub Inactive_AfterUpdate()
Dim db As Database
Dim rs As Recordset
Dim str As String

Set db = CurrentDb
Set rs = db.OpenRecordset("jtblLocationAdvisor", dbOpenDynaset,
dbSeeChanges)

On Error GoTo Err_Inactive_Click

If Me.Inactive = True Then
rs.MoveFirst
Do Until rs.EOF
If rs![Location ID] =
Forms![frmLocationAdvisors]![Location
ID] Then
GoTo Duplicate_Inactive_Click
Else
rs.MoveNext
End If
Loop
End If

Exit_Inactive_Click:
Set db = Nothing
Set rs = Nothing
Exit Sub

Err_Inactive_Click:
MsgBox Err.Description
Resume Exit_Inactive_Click

Duplicate_Inactive_Click:
Select Case MsgBox("There are active advisors listed for this
location. If you wish to delete these advisors from this location,
click OK to continue.", vbOKCancel)
Case vbOK
DoCmd.OpenQuery "qdelLocationAdvisors"
Me.subfrmLocationAdvisors.Requery
Case vbCancel
Me.Inactive = False
End Select
Resume Exit_Inactive_Click

End Sub
Click to expand...
 
D

Dirk Goldgar

In
DoveArrow said:
I'm trying to run the following event, but whenever I have a location
that has active advisors, in addition to running the delete query that
I want it to run, I get an error message that says "Resume Without
Error." What's going on?

Private Sub Inactive_AfterUpdate()
Dim db As Database
Dim rs As Recordset
Dim str As String

Set db = CurrentDb
Set rs = db.OpenRecordset("jtblLocationAdvisor", dbOpenDynaset,
dbSeeChanges)

On Error GoTo Err_Inactive_Click

If Me.Inactive = True Then
rs.MoveFirst
Do Until rs.EOF
If rs![Location ID] = Forms![frmLocationAdvisors]![Location
ID] Then
GoTo Duplicate_Inactive_Click
Else
rs.MoveNext
End If
Loop
End If

Exit_Inactive_Click:
Set db = Nothing
Set rs = Nothing
Exit Sub

Err_Inactive_Click:
MsgBox Err.Description
Resume Exit_Inactive_Click

Duplicate_Inactive_Click:
Select Case MsgBox("There are active advisors listed for this
location. If you wish to delete these advisors from this location,
click OK to continue.", vbOKCancel)
Case vbOK
DoCmd.OpenQuery "qdelLocationAdvisors"
Me.subfrmLocationAdvisors.Requery
Case vbCancel
Me.Inactive = False
End Select
Resume Exit_Inactive_Click

End Sub
Click to expand...

You're using a GoTo statement to transfer control to the code block at
Duplicate_Inactive_Click. Then that block of code attempts to transfer
control back to Exit_Inactive_Click by using a Resume statement. But
the Resume statement is only valid when used in an error-handling
procedure; that is, only if it is reached as the result of an error
having been raised.

You could solve this problem in an ugly fashion by just replacing that
Resume statement with a GoTo statement, but I'd recommend that you
restructure your code to get rid of the GoTo statements entirely.
Incidentally, it would be a good idea to explicitly close your
recordset.

I could be wrong, but it looks to me like code along the following lines
would accomplish what you're trying to do more efficiently:

'----- start of code -----
Private Sub Inactive_AfterUpdate()

Dim rs As Recordset
Dim str As String

On Error GoTo Err_Inactive_Click

If Me.Inactive = True Then

Set rs = CurrentDb.OpenRecordset( _
"SELECT * FROM jtblLocationAdvisor " & _
"WHERE [Location ID] = " & _
Forms![frmLocationAdvisors]![Location ID], _
dbOpenSnapshot, dbSeeChanges)

If Not rs.EOF Then
Select Case MsgBox( _
"There are active advisors listed for this " & _
"location. If you wish to delete these " & _
"advisors from this location, click OK "& _
"to continue.", _
vbOKCancel, _
"Active Advisors Found")
Case vbOK
DoCmd.OpenQuery "qdelLocationAdvisors"
Me.subfrmLocationAdvisors.Requery
Case vbCancel
Me.Inactive = False
End Select
End If

rs.Close

End If

Exit_Inactive_Click:
Set rs = Nothing
Exit Sub

Err_Inactive_Click:
MsgBox Err.Description
Resume Exit_Inactive_Click

End Sub
'----- end of code -----

Note: the above code assumes that [Location ID] is numeric. If it's
text, the constructed SQL statement must be modified to enclose the
value in quotes.
 
D

DoveArrow

In




DoveArrow said:
I'm trying to run the following event, but whenever I have a location
that has active advisors, in addition to running the delete query that
I want it to run, I get an error message that says "Resume Without
Error." What's going on?
Click to expand...
Private Sub Inactive_AfterUpdate()
Dim db As Database
Dim rs As Recordset
Dim str As String
Click to expand...
Set db = CurrentDb
Set rs = db.OpenRecordset("jtblLocationAdvisor", dbOpenDynaset,
dbSeeChanges)
Click to expand...
On Error GoTo Err_Inactive_Click
Click to expand...
If Me.Inactive = True Then
rs.MoveFirst
Do Until rs.EOF
If rs![Location ID] = Forms![frmLocationAdvisors]![Location
ID] Then
GoTo Duplicate_Inactive_Click
Else
rs.MoveNext
End If
Loop
End If
Click to expand...
Exit_Inactive_Click:
Set db = Nothing
Set rs = Nothing
Exit Sub
Click to expand...
Err_Inactive_Click:
MsgBox Err.Description
Resume Exit_Inactive_Click
Click to expand...
Duplicate_Inactive_Click:
Select Case MsgBox("There are active advisors listed for this
location. If you wish to delete these advisors from this location,
click OK to continue.", vbOKCancel)
Case vbOK
DoCmd.OpenQuery "qdelLocationAdvisors"
Me.subfrmLocationAdvisors.Requery
Case vbCancel
Me.Inactive = False
End Select
Resume Exit_Inactive_Click
Click to expand...
End Sub
Click to expand...

You're using a GoTo statement to transfer control to the code block at
Duplicate_Inactive_Click. Then that block of code attempts to transfer
control back to Exit_Inactive_Click by using a Resume statement. But
the Resume statement is only valid when used in an error-handling
procedure; that is, only if it is reached as the result of an error
having been raised.

You could solve this problem in an ugly fashion by just replacing that
Resume statement with a GoTo statement, but I'd recommend that you
restructure your code to get rid of the GoTo statements entirely.
Incidentally, it would be a good idea to explicitly close your
recordset.

I could be wrong, but it looks to me like code along the following lines
would accomplish what you're trying to do more efficiently:

'----- start of code -----
Private Sub Inactive_AfterUpdate()

Dim rs As Recordset
Dim str As String

On Error GoTo Err_Inactive_Click

If Me.Inactive = True Then

Set rs = CurrentDb.OpenRecordset( _
"SELECT * FROM jtblLocationAdvisor " & _
"WHERE [Location ID] = " & _
Forms![frmLocationAdvisors]![Location ID], _
dbOpenSnapshot, dbSeeChanges)

If Not rs.EOF Then
Select Case MsgBox( _
"There are active advisors listed for this " & _
"location. If you wish to delete these " & _
"advisors from this location, click OK "& _
"to continue.", _
vbOKCancel, _
"Active Advisors Found")
Case vbOK
DoCmd.OpenQuery "qdelLocationAdvisors"
Me.subfrmLocationAdvisors.Requery
Case vbCancel
Me.Inactive = False
End Select
End If

rs.Close

End If

Exit_Inactive_Click:
Set rs = Nothing
Exit Sub

Err_Inactive_Click:
MsgBox Err.Description
Resume Exit_Inactive_Click

End Sub
'----- end of code -----

Note: the above code assumes that [Location ID] is numeric. If it's
text, the constructed SQL statement must be modified to enclose the
value in quotes.

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -
Click to expand...

Your solution worked beautifully Dirk. Thank you.
 

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

Email Using Continuous Form 1
Cmd Button Does not work on first click 2
ACCESS 2002 enters some kind of endless loop and crashes 1
drop down list: how to open on cursor 2
Recordsets and Forms 13
How to have a variable in a recordset field name 1
MS Access setting outlook reminders from a DAO recordset 0
Opening Switchboard Maximized 2

Top