• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

Re-Append or Delete Subform Records from Mainform without Closing Form

I have a Mainform with two command buttons - ADD and DELETE. The ADD button adds records to the subform based on unbound control values including a number field which determines the number of records added. The DELETE button loops through the subform records and deletes them.

The DELETE button code works when I open (or reopen) the form, but will not run if the user selects DELETE just after adding new records via the Add button. If the user closes and reopens the form, the code works fine.

The same code is utilized at the start of the ADD button's Click event to delete any previous records prior to appending new ones. Again, it works when the form is initially opened, but if the user clicks ADD a second time, the original records remain with the newly appended records. I have tried requery and save commands to no avail.

In summary, I need to be able to Add records to the subform, then Delete them if necessary prior to closing the form. Thanks. The code is pasted below.

Dim db As Database
Dim rstDelete As Recordset
Dim DeleteString As String

Set db = CurrentDb
Set rstDelete = Forms!frmSetsDetail!frmSetsDetailSub.Form.RecordsetClone
DeleteString = "[SetsRepID] = " & Me!StrengthID
    If IsNull(Me.frmSetsDetailSub.Form!SetsRepID) Then
        DisplayMessage ("There are no Sets to delete!")
        Exit Sub
    Do While Not rstDelete.EOF
       With rstDelete
               .FindFirst DeleteString
       End With
    End If

Open in new window

  • 2
  • 2
1 Solution
Simon BallCommented:
do you have

rstdelete.close or/and

and rstappend = nothing

rstdelete = nothing.

this will free up the recordsets for re-use.
skennellyAuthor Commented:
I just added the statements after the loop and got the same results. I thought that might have worked, but it didn't. Thanks for your reply.  I'll keep trying.
Have you tried to add
also in the beginning of code for delete button?
skennellyAuthor Commented:
I changed the recordset to the underlying query: Set rstDelete = db.OpenRecordset("qrySetsDetail", dbOpenDynaset) instead of using Recordsetclone and it appears to work with the Close and Nothing statements so I will award the points to Sudonim. Thanks for the input from both.
Simon BallCommented:
nice one.  glad it worked for you.

should have worked with recordset clone though :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now