Solved

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

Posted on 2011-03-23
5
328 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 500 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

730 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