Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Posts
    204

    Unanswered: Append / Delete queries, vb code help!

    Here is what I am trying to do:

    When a button is pressed, the original data will be moved to a separate table and the original data deleted. It works, as long as there is data. If there is not, I get a run time error.

    How do I make it so instead of getting a run time error, it ends the sub?




    Here is the code:

    Code:
    Private Sub Command41_Click()
    
    Dim Response As VbMsgBoxResult
    Beep
    Response = Msgbox("This will delete records out of the scanning table!  This cannot be undone!  Are you sure you want to do this?", vbQuestion + vbYesNo)
    If Response = vbNo Then Exit Sub
    CurrentDb.Execute "SE16ReceivingUpdateBulk", dbFailOnError
    CurrentDb.Execute "SE16ReceivingUpdateMezz", dbFailOnError
    CurrentDb.Execute "NameUpdateReceiving", dbFailOnError
    Me.Requery
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "ProductivityReceiving"
    DoCmd.OpenQuery "ProductivityReceiving2"
    DoCmd.OpenQuery "DeleteReceivingQuery"
    DoCmd.OpenQuery "AppendReceivingHoursQuery"
    DoCmd.OpenQuery "DeleteReceivingHoursQuery"
    DoCmd.SetWarnings True
    Me.Requery
    Me.Refresh
    Msgbox "Scanning data for Receiving has been deleted and relocated", vbInformation
    
    
    
    
    
    End Sub
    Thanks,
    Chris

  2. #2
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Do some error handling.

    Get the error # for the emtpy data return and check against it: if such and such error number do this action, else do that action.

    hth

    Just don't do what I always do: forget to exit the sub/function before my error handling appears....then it does it regardless and you end up feeling like a chucklehead when you go back and fix it...oops

    Sam, is it 4 yet?
    Good, fast, cheap...Pick 2.

  3. #3
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by SCrandall
    Do some error handling.

    Get the error # for the emtpy data return and check against it: if such and such error number do this action, else do that action.

    hth

    Just don't do what I always do: forget to exit the sub/function before my error handling appears....then it does it regardless and you end up feeling like a chucklehead when you go back and fix it...oops

    Sam, is it 4 yet?

    Unfortunately, I'm still a beginner at code and I'm not quite sure how to go about that. Can you tell me please what I am doing wrong here?

    The line the debugger catches is the one above "If Error = 7874 Then Exit Sub Else"



    Code:
    Private Sub Command41_Click()
    
    Dim Response As VbMsgBoxResult
    Beep
    Response = Msgbox("This will delete records out of the scanning table!  This cannot be undone!  Are you sure you want to do this?", vbQuestion + vbYesNo)
    If Response = vbNo Then Exit Sub
    CurrentDb.Execute "SE16ReceivingUpdateBulk", dbFailOnError
    CurrentDb.Execute "SE16ReceivingUpdateMezz", dbFailOnError
    CurrentDb.Execute "NameUpdateReceiving", dbFailOnError
    
    
    
    Me.Requery
    DoCmd.SetWarnings False
    
    DoCmd.OpenQuery "ProductivityReceiving"
    If Error = 7874 Then Exit Sub Else
    DoCmd.OpenQuery "ProductivityReceiving2"
    DoCmd.OpenQuery "DeleteReceivingQuery"
    DoCmd.OpenQuery "AppendReceivingHoursQuery"
    DoCmd.OpenQuery "DeleteReceivingHoursQuery"
    DoCmd.SetWarnings True
    Me.Requery
    Me.Refresh
    Msgbox "Scanning data for Receiving has been deleted and relocated", vbInformation
    
    
    
    
    
    End Sub

  4. #4
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Hiya,
    This is just an rough example, I'm sure you'll need to tweak it. My lines are bolded.

    Code:
    Private Sub Command41_Click()
    
       on error goto err_handler
    
    Dim Response As VbMsgBoxResult
    Beep
    Response = Msgbox("This will delete records out of the scanning table!  This cannot be undone!  Are you sure you want to do this?", vbQuestion + vbYesNo)
    If Response = vbNo Then Exit Sub
    CurrentDb.Execute "SE16ReceivingUpdateBulk", dbFailOnError
    CurrentDb.Execute "SE16ReceivingUpdateMezz", dbFailOnError
    CurrentDb.Execute "NameUpdateReceiving", dbFailOnError
    
    
    
    Me.Requery
    DoCmd.SetWarnings False
    
    DoCmd.OpenQuery "ProductivityReceiving"
    If Error = 7874 Then Exit Sub Else
    DoCmd.OpenQuery "ProductivityReceiving2"
    DoCmd.OpenQuery "DeleteReceivingQuery"
    DoCmd.OpenQuery "AppendReceivingHoursQuery"
    DoCmd.OpenQuery "DeleteReceivingHoursQuery"
    DoCmd.SetWarnings True
    Me.Requery
    Me.Refresh
    Msgbox "Scanning data for Receiving has been deleted and relocated", vbInformation
    
    Exit Sub
    
    Err_Handler:
    
    If Err.number = 7874 Then 
    Exit Sub
    Else
    msgbox "Error " &Err.number &": " &err.description, vbokonly +vbinformation, "Error In Command41_Click"
    exit sub
    End If
    
    
    End Sub
    Like I said, it's very basic, depending on your needs you could probably do a resume {something} in there. At this point any run time error will kick you out of the sub, however, if it's *not* the error you specified there's a message that will give you the error number and the error description.

    You will probably want to do quite a bit of hunting around on various methods of error handling/trapping, seems everyone has their own methods of handling different errors that arise.

    Sam, hth
    Good, fast, cheap...Pick 2.

  5. #5
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by SCrandall
    Hiya,
    This is just an rough example, I'm sure you'll need to tweak it. My lines are bolded.

    Code:
    Private Sub Command41_Click()
    
       on error goto err_handler
    
    Dim Response As VbMsgBoxResult
    Beep
    Response = Msgbox("This will delete records out of the scanning table!  This cannot be undone!  Are you sure you want to do this?", vbQuestion + vbYesNo)
    If Response = vbNo Then Exit Sub
    CurrentDb.Execute "SE16ReceivingUpdateBulk", dbFailOnError
    CurrentDb.Execute "SE16ReceivingUpdateMezz", dbFailOnError
    CurrentDb.Execute "NameUpdateReceiving", dbFailOnError
    
    
    
    Me.Requery
    DoCmd.SetWarnings False
    
    DoCmd.OpenQuery "ProductivityReceiving"
    If Error = 7874 Then Exit Sub Else
    DoCmd.OpenQuery "ProductivityReceiving2"
    DoCmd.OpenQuery "DeleteReceivingQuery"
    DoCmd.OpenQuery "AppendReceivingHoursQuery"
    DoCmd.OpenQuery "DeleteReceivingHoursQuery"
    DoCmd.SetWarnings True
    Me.Requery
    Me.Refresh
    Msgbox "Scanning data for Receiving has been deleted and relocated", vbInformation
    
    Exit Sub
    
    Err_Handler:
    
    If Err.number = 7874 Then 
    Exit Sub
    Else
    msgbox "Error " &Err.number &": " &err.description, vbokonly +vbinformation, "Error In Command41_Click"
    exit sub
    End If
    
    
    End Sub
    Like I said, it's very basic, depending on your needs you could probably do a resume {something} in there. At this point any run time error will kick you out of the sub, however, if it's *not* the error you specified there's a message that will give you the error number and the error description.

    You will probably want to do quite a bit of hunting around on various methods of error handling/trapping, seems everyone has their own methods of handling different errors that arise.

    Sam, hth
    Thank you, it worked like a charm!

    The only think I needed to tweak was, I wanted a message box telling the user that there was no data, so I got that in....All works perfectly.

    Thanks again!

  6. #6
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Great to know, glad I could help. One thing I would suggest: Give your command buttons and controls meaningful names, that way you can tell by the name of it what it's supposed to do in the code.

    Good luck,

    Sam
    Good, fast, cheap...Pick 2.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •