?
Solved

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

Posted on 2011-03-23
5
Medium Priority
?
332 Views
Last Modified: 2012-05-11
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
    Else
    Do While Not rstDelete.EOF
       With rstDelete
               .FindFirst DeleteString
               .Edit
               .Delete
       End With
    rstDelete.MoveNext
    Loop
    End If
    
Forms!frmSetsDetail!frmSetsDetailSub.Form.Requery

Open in new window

0
Comment
Question by:skennelly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 15

Accepted Solution

by:
Simon Ball earned 2000 total points
ID: 35205295
do you have

rstdelete.close or/and
rstappend.close

and rstappend = nothing

rstdelete = nothing.

this will free up the recordsets for re-use.
0
 

Author Comment

by:skennelly
ID: 35205637
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.
0
 
LVL 40

Expert Comment

by:als315
ID: 35205704
Have you tried to add
Forms!frmSetsDetail!frmSetsDetailSub.Form.Requery
also in the beginning of code for delete button?
0
 

Author Comment

by:skennelly
ID: 35206140
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.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35206382
nice one.  glad it worked for you.

should have worked with recordset clone though :)
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question