?
Solved

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

Posted on 2011-03-23
5
Medium Priority
?
330 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

752 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