Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete record automatically if a field is blank (2002/SR-1)

    I have tables "tblPayments" and "tblPaymentNotes" with a one-to-one relationship via primary key "PaymentID". Each payment can have 1 note and each note can be for only 1 payment. I have the notes in a separate table due to the fact that approximately only 1/3 of the payments will have a related note. In my form I have these 2 tables each as a subform on the same parent form. When I add a new payment and enter a note in the other subform everything is fine. Now my question. If the user goes back to the note later and decides a note is not needed and removes the text from the box, is there a way to automatically delete it, like in the exit event or something? What would be the best way to handle this? I don't want to have to add a delete button to delete a note. Any suggestions would be great!
    Thanks
    Don

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete record automatically if a field is blank (2002/SR-1)

    How about deleting the record through code, when the record loses focus or is exited. Use decison code to test if the field is Null or equals an empty string.
    Although, to be frank, I would hesitate to allow users to delete data after the fact. Have you thought about entering a meaningful string like "Deleted on" & Date() (convert to a string), so that you would maintain some history of the action?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete record automatically if a field is blank (2002/SR-1)

    I have tried this code:

    Private Sub txtPaymentNote_Exit(Cancel As Integer)
    If IsNull(Me.txtPaymentNote) And Not IsNull(Me.PaymentID) Then
    DoCmd.RunCommand acCmdDeleteRecord
    End If
    End Sub

    But this is deleting the related record in the payments subform not the paymentnote subform.
    Suggestions?
    Thanks Thomas!
    Don

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete record automatically if a field is blank (2002/SR-1)

    I don't understand how your forms/subforms are ordered, but when you exit txtPaymentNote, where is the focus?
    You may need to explicitly place it on the Paymentnote subform record before the deletion line.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete record automatically if a field is blank (2002/SR-1)

    Thomas,
    I have included a screenshot to give you an idea of how my subforms are set up. The payment note subform only has one control....a textbox called txtPaymentNote. I'm going to work on altering the focus upon exit like you suggested but I just wanted you to see how it my suforms look in case you had any other suggestions while I'm working on it.
    Thanks,
    Don
    Attached Images Attached Images

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete record automatically if a field is blank (2002/SR-1)

    You can't use DoCmd.RunCommand to delete anything but the current record. If you run this code from your payments subform it will delete the current record in the payments subform. Why not just execute a delete query from code?
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete record automatically if a field is blank (2002/SR-1)

    I had implemented jhermiz's idea regarding opening a recordset up and executing a delete but was running into other problems. Your idea to run a delete query seems to be working great. This is what I have in place now:

    Private Sub txtPaymentNote_Exit(Cancel As Integer)
    '--If a note was removed for a given payment
    '--delete the record that it created in tblPaymentNotes
    If IsNull(Me.txtPaymentNote) And Not IsNull(Me.PaymentID) Then
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qdelBlankPaymentNotes"
    DoCmd.SetWarnings True
    End If
    End Sub

    Would I be better off building an SQL statement on the fly? Or would I get better performance using my saved delete query?

    Thanks for every ones help!
    Don

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete record automatically if a field is blank (2002/SR-1)

    You'll get better performance with a saved query, at least in Access 2000 and later.
    Charlotte

Posting Permissions

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