Solved

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

Posted on 2011-03-23
5
327 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
  • 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

839 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