Results 1 to 3 of 3

Thread: Delete

  1. #1
    Join Date
    Apr 2009
    Posts
    85

    Unanswered: Delete

    Hello, I have a bound form(autonumber), form has navagation buttons to scroll through records. User's want to be able to delete records,

    From wizard
    Private Sub Command151_Click()
    On Error GoTo Err_Command151_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

    Exit_Command151_Click:
    Exit Sub

    Err_Command151_Click:
    MsgBox Err.Description
    Resume Exit_Command151_Click

    End Sub

    This works except I need to alter it to this

    Private Sub cmdDelete_Click()
    On Error GoTo Err_cmdDelete_Click
    If Cancel = True Then GoTo aaa

    Me.Status.Value = "Available" 'Puting equipment back in to stock
    Me.StorageLocation.Value = Me.PhoStorage.Value


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70


    Exit_cmdDelete_Click:

    aaa:
    Me.StorageLocation.Value = "OUT" 'taking equipnent back out of stock
    Me.Status.Value = "Committed"
    Me.Refresh
    Exit Sub

    Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click

    End Sub
    but when I do, if I delete the last record then I get an error, access tries to move to next record but there is no next record. Not sure why when I add my code access is moving forword after deleting. Any guidence is welcome
    Last edited by Timothyl; 07-03-09 at 10:33.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Timothyl
    Private Sub cmdDelete_Click()
    On Error GoTo Err_cmdDelete_Click
    If Cancel = True Then GoTo aaa

    Me.Status.Value = "Available" 'Puting equipment back in to stock
    Me.StorageLocation.Value = Me.PhoStorage.Value


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70


    Exit_cmdDelete_Click:

    aaa:
    Me.StorageLocation.Value = "OUT" 'taking equipnent back out of stock
    Me.Status.Value = "Committed"
    Me.Refresh
    Exit Sub

    Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click

    End Sub
    but when I do, if I delete the last record then I get an error, access tries to move to next record but there is no next record. Not sure why when I add my code access is moving forword after deleting. Any guidence is welcome
    To be honest I'm not at all sure of what you're trying to do here!

    If Cancel = True Then GoTo aaa

    To begin with, there is no Cancel in a click event! If there were you'd see it in the sub header that Access creates! It would be


    Private Sub cmdDelete_Click(Cancel as Integer)

    and, of course, it isn't! You can't simply place it there! Next

    Me.Status.Value = "Available" 'Puting equipment back in to stock
    Me.StorageLocation.Value = Me.PhoStorage.Value


    you're assigning values to two fields in the current record then

    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

    you immediately deleting the current record! This makes no sense, either!

    You say you have navigation buttons, but then you talk as if Access is trying to move to the next record after your code deletes a record, which it does not do, on its own.

    If you have AllowAdditons set to No, are on the last record then try to move to the next record, you will, indeed, get this error. IF the problem is being caused by a "Next Record" button, you need to check to see if you're on the last record and only execute the code if this is not true.

    Perhaps you need to give us some explanation of what you're actually trying to do, in order for us to be able to help you.
    Last edited by Missinglinq; 07-03-09 at 17:13.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Apr 2009
    Posts
    85
    Hello missingling, you answered here as well. I had three ways of deleting I had written but had questions on all of them and did not want to burden any one form with all three questions. The above was my first attempt at using and altering a delete statement(therefore at least in my case, bumbling). It's true when you use the wizard you do not advance when you delete so you can delete the last record, but when I added my bad code it caused access to advance on to the next record after deleting except for the advancing it all worked fine.

    The Cancel had to do with canceling the deletion

    Me.Status.Value = "Available" 'Puting equipment back in to stock
    Me.StorageLocation.Value = Me.PhoStorage.Value

    This was me updating the equipment table, with the values in these controls, putting things back into stock that had been taken out of stock before the deletion, since the deletion would remove where things needed to go and then setting that particular piece of equipment = Available.
    The other two methods using a SQL delete statement(which you gave much appreciated advice on) , ADO delete
    both work well know and offer greater flexibility in there use. In the last few days due to the gracious input from you, aceman, php, Mr.B have clearer understanding of deleting, requery, if then, error handling.

    Here's what works


    Private Sub cmdDelete_Click()
    On Error GoTo Err_cmdDelete_Click

    If MsgBox("Are you sure you want to continue with deletion?", _
    vbQuestion + vbOKCancel, _
    "User Confirmation Required! . . .") = vbCancel Then Exit Sub


    Me.Status.Value = "Available" 'Puting equipment back in to stock
    Me.StorageLocation.Value = Me.PhoStorage.Value


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70


    Exit_cmdDelete_Click:
    Me.Refresh
    Exit Sub

    Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click

    End Sub

    Thanks again for your input
    Last edited by Timothyl; 07-05-09 at 00:26.

Posting Permissions

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