Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Delete Record from unbound lstbox with a cmd(2003)

    Hi, I

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete Record from unbound lstbox with a cmd(2003)

    The On Click event procedure of the DeleteNote button runs the append query appNotesDrop, but then it quits because of the line Exit Sub before it runs a delete query. In fact, the delete query is called UpdateNotes in the code while it is actually named qryUpdateNotes in the database, but you don't notice this error because that part of the code never gets executed. The code should look like this:

    Private Sub DeleteNote_Click()
    On Error GoTo Err_DeleteNote_Click

    DoCmd.SetWarnings False

    If Me.lstNotes.ListIndex > -1 Then
    If MsgBox("The note selected will be removed from this record. Are you sure?", _
    vbYesNo + vbQuestion) = vbYes Then
    DoCmd.OpenQuery "appNotesDrop"
    DoCmd.OpenQuery "qryUpdateNotes"
    Me.lstNotes.Requery
    End If
    End If

    Exit_DeleteNote_Click:
    DoCmd.SetWarnings True
    Exit Sub

    Err_DeleteNote_Click:
    MsgBox Err.Description
    Resume Exit_DeleteNote_Click
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Delete Record from unbound lstbox with a cmd(2003)

    Hans, That is awesome it works perfect, I did not notice the error on the delete query. One thing i also forgot. i am using the Me.fieldName = CurrentUser and Me.FieldDate = Now () afte the last DoCmd.Openquery to capture who deleted the file. Do I have to specify the location of the fields in the code, all three queries have the same fields.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete Record from unbound lstbox with a cmd(2003)

    I don't see fields called FieldName and FieldDate in your database. Do you mean ChangedBy and ChangedDate? If you use Me.ChangedBy and Me.ChangedDate, you will set those values in the current record of tblDataPersonnel in frmPersonnel, because Me refers to frmPersonnel. If you want to capture who deleted this particular note and when, you shouldn't do that in the VBA code, but in the append query appNotesDrop.

  5. #5
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Delete Record from unbound lstbox with a cmd(2003)

    Hans, Thank you for the advice. I make the two fields on appNotesDrop posting the CurrentUser() and Now() then their are appended to the NotesDropData. It works perfect.

Posting Permissions

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