Need to end sub (stop everything from happening and just exit)

[wrayman]

Got a situation where if it is true need the option of ending the sub



I know this isn't working because I never get the msgbox STOP and then after pressing NO the code runs normally

Here is a code snipet.

I have used this before, I must be forgetting something.

Anyway if the msgbox response is vbNo I need to stop the sub, go to End Sub , or Exit Sub

ume = Parsed("results")(1)("items")(1)("uid")

pn = "41290"
Debug.Print pn
'MsgBox "Do you wish to continue?", vbYesNo, "Continue"
rs4.MoveFirst
Do Until rs4.EOF
If rs4!UID = ume Then
MsgBox "Exists: Is this an UPDATE?", vbYesNo
If Response = vbNo Then
MsgBox "stop"
Exit Sub

[CJ_London]

perhaps something like

If rs4!UID = ume Then
    if MsgBox ("Exists: Is this an UPDATE?", vbYesNo)= vbNo Then
        MsgBox "stop"
        Exit Sub
    end if
...
...

[wrayman]

Yep works great, if if if...., lol, Thanks so much!

[ssanfu]

The If...Then...Else Statement has 2 forms: single line and block.

It looks like you are using the block form but are missing the "End If" line for the 2 IF statements. And I would guess you do not have the line "Option Explicit" at the top of the module.

On top of that, you have a DO loop but never move to the next record.
Maybe try:

    ume = Parsed("results")(1)("items")(1)("uid")

    pn = "41290"
    Debug.Print pn
    'MsgBox "Do you wish to continue?", vbYesNo, "Continue"
    rs4.MoveFirst
    Do Until rs4.EOF
        If rs4!UID = ume Then
            If MsgBox("Exists: Is this an UPDATE?", vbYesNo) = vbNo Then
                MsgBox "stop"
                Exit Sub
            End If
        End If
        rs4.MoveNext
    Loop

[wrayman]

I had it, but didn't "copy it" here is the full snip

[wrayman]

Sorry for the slop, was inserting code into another statement: here is the full code

It seems to work, thanks! See anything else wrong with it?

ume = Parsed("results")(1)("items")(1)("uid")Debug.Print ume


pn = "735075"
Debug.Print pn


  'MsgBox "Do you wish to continue?", vbYesNo, "Continue"
    rs4.MoveFirst
    Do Until rs4.EOF
        If rs4!UID = ume Then
            If MsgBox("Exists: Is this an UPDATE?", vbYesNo) = vbNo Then
                MsgBox "stop"
                Exit Sub
            End If
        End If
        rs4.MoveNext
    Loop


rs4.MoveFirst
Do Until rs4.EOF
If rs4!PartNumber = pn Then
rs4.Edit
rs4!UID = ume
rs4.Update
End If
rs4.MoveNext
Loop

[ssanfu]

Nope. Ajax nailed it again...

[wrayman]

This just doesn't look right, but it works....

Anyway I need to delete records from several table in the ELSE clause, I have no clue without starting a cascade of if statements?

Any help?

ume = Parsed("results")(1)("items")(1)("uid")
Debug.Print ume
 
pn = "735075"
Debug.Print pn
 
  'MsgBox "Do you wish to continue?", vbYesNo, "Continue"
    rs4.MoveFirst
    Do Until rs4.EOF
        If rs4!UID = ume Then
            If MsgBox("Exists: Is this an UPDATE?", vbYesNo) = vbNo Then
                MsgBox "stop"
                Exit Sub
            Else
                rs2.MoveFirst
                Do Until rs2.EOF
                    If Not IsNull(rs2!UID) And rs2!UID = ume Then
                        rs2.Delete
                        End If
                        rs2.MoveNext
                Loop
            End If
        End If
    rs4.MoveNext
    Loop
 
rs4.MoveFirst
    Do Until rs4.EOF
            If rs4!PartNumber = pn Then
                rs4.Edit
                rs4!UID = ume
                rs4.Update
            End If
        rs4.MoveNext
    Loop

[ssanfu]

Anyway I need to delete records from several table in the ELSE clause, I have no clue without starting a cascade of if statements?

Without knowing a lot more about your dB, do all of the tables have a field "ume"?

Not sure what you are trying to do, but maybe you could execute delete queries?

CurrentDb.Execute "DELETE rs2.* FROM rs2 WHERE rs2.UID = " & ume & ";", dbFailOnError

Change "rs2" to the table name/query name if many tables.......

[wrayman]

Yes all the fields during the update have the same ume., I guess I could just stack the delete query?

[ssanfu]

By "stack", you mean execute one query after another? Yes, I have....

[wrayman]

Good idea, but I can't get it to work:

 CurrentDb.Execute "DELETE pricing.* FROM pricing WHERE pricing.UID =  " & ume & ";", dbFailOnError
           
         CurrentDb.Execute "DELETE rs2.* FROM rs2 WHERE rs2.UID = " & ume & ";", dbFailOnError

I get

ume is String , tried several options but keep getting errors, that UID is in the table pricing.

Any suggestions?

[pbaldy]

Try

CurrentDb.Execute "DELETE pricing.* FROM pricing WHERE pricing.UID = '" & ume & "';", dbFailOnError

[wrayman]

Thanks for the ' " rescue " ' , appreciated

[pbaldy]

No problem, it looked like Steve was offline. Too cold up there probably.