UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Crashes - When Trying To Copy A Set Of Records, Access 2016    
 
   
hemant
post Apr 7 2020, 12:30 PM
Post#1



Posts: 79
Joined: 19-February 20
From: Mumbai, India


In an app one of the part is Holiday Calendar. The company can have multiple holiday calendars depending on office location.

I have this table called "Holidays" with below fields
ID - Autonumber
Location - ShortText
Holiday - Date
Desc - ShortText

One of the functionality to be given is to clone an existing location's holiday calendar. This is because the new location calendar may need just a few changes. I tried the below code. The problem I face is mentioned below the code.

Imp Note: I have two subforms on the main form (pls see attached image). Both of them have the same record source - "Holidays" table - but with diff query.
Subform1:
This one on the left is a query that shows unique list of Locations in the table.
There is a hidden textbox (tbMainFormLocn) on the mainform that gets its value from the On Enter event of the Location control on this subform.

Subform2:
On this subform control I am using Link Master Fields as tbMainFormLocn and Link Child Fields as "Location". Hence, this subform shows the Holidays filtered for the active Location on subform1.


CODE
Private Sub btnCloneCal_Click()
    Dim db As Database
    Dim rst As Recordset, rstClone As Recordset
    Dim fld As Field
    Dim LocnToClone As String, NewLocn As String, StrSQL As String
    Dim rcnt As Long, rcnt2 As Long

    LocnToClone = Me.Location
  
    NewLocn = InputBox("Please entter the new Holiday Calendar location name.", _
                       "New Holiday Calendar Location")
    If NewLocn = "" Then Exit Sub
    If DCount("[Location]", "Holidays", "[Location] ='" & NewLocn & "'") > 0 Then
        MsgBox "The location name entered already exists.", vbCritical, "Duplicate Name"
        Exit Sub
    End If

    Me.AllowAdditions = True
    Set db = CurrentDb
    StrSQL = "SELECT Holidays.* FROM Holidays WHERE (((Holidays.Location)='" & LocnToClone & "'));"
    Set rst = db.OpenRecordset(StrSQL)
    Set rstClone = rst.Clone  

    rcnt = rst.RecordCount                'this for just for me to check the recordcounts - not really reqd in the code
    rcnt2 = rstClone.RecordCount
    
    If rst.RecordCount > 0 Then
        rst.MoveFirst
        Do
            rstClone.AddNew
            For Each fld In rst.Fields
                With fld
                    If .Attributes And dbAutoIncrField Then
                        'skip for Autonumber or GUID field
                    ElseIf .Name = "Location" Then
                        ' for Location field insert new locn name
                        rstClone.Fields(.Name).Value = NewLocn
                    Else
                        'for all other fields copy original content
                        rstClone.Fields(.Name).Value = .Value
                    End If
                End With
            Next fld
            rstClone.Update
            rst.MoveNext
            If rst.EOF Then Exit Do   ' THIS LINE DOES NOT encounter EOF
        Loop
    End If
    Me.AllowAdditions = False

    Me.Requery
    MsgBox "The Holiday Calendar for " & NewLocn & " was created.", vbInformation, "Holiday Calendar Cloned"

End Sub



I tried to clone the Holiday Calendar of Location=Maharashtra. As seen in the image it has only three holidays listed(I am just testing this code).

Now, the SQL should give the recordset with three records. Which it does.
When I step thru the code, I find that the code does not meet the criteria rst.EOF. It keeps going into the loop. The three entries for Maharashtra gets duplicated over and over again. Why?

Ideally, it should loop thru the three records of rst and then hit rst.EOF and exit DO - right?

Can someone help?



Attached File(s)
Attached File  Holiday_Calendar_Form.jpg ( 127.49K )Number of downloads: 7
 
Go to the top of the page
 
tina t
post Apr 7 2020, 02:04 PM
Post#2



Posts: 6,587
Joined: 11-November 10
From: SoCal, USA


seems like it would be a whole lot easier to just use an Append query. you already have a SQL statement to pull the target Holiday records. just turn that into an Append query. personally, my freehand SQL skills are poor; something like this, i usually create the query in query Design view and then copy the SQL statement from the SQL pane and put it in the VBA code, adding in the reference to the chosen Location, and the variable holding the "new name". since the primary key of the destination table is Autonumber, just leave that field out of the query, the Autonumber value will be added to each new record automatically.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
JeffK
post Apr 7 2020, 02:34 PM
Post#3


UtterAccess VIP
Posts: 1,613
Joined: 19-December 02
From: Lansing, MI


The default Type argument for the DAO OpenRecordset method will see any record additions made to its clones. So when you add a record to the clone it is now visible in the original, hence the infinite loop. The fact that your new record does not match the criteria of the original SQL statement doesn't matter since that is only evaluated on open.

If you decide to keep the recordset processing here instead of an append query, you can a open forward-only, read-only ADODB recordset as the source. Open the destination recordset separately with a keyset cursor and optimistic lock type. Records added to the destination will not be visible in the source in that case.
Go to the top of the page
 
projecttoday
post Apr 7 2020, 05:18 PM
Post#4


UtterAccess VIP
Posts: 12,166
Joined: 10-February 04
From: South Charleston, WV


Why did you use recordset coding?

--------------------
Robert Crouser
Go to the top of the page
 
hemant
post Apr 8 2020, 12:50 AM
Post#5



Posts: 79
Joined: 19-February 20
From: Mumbai, India


Thanks a lot for your replies. I am going to use Append Query. I am surprised why I did not think of that, because I did use that method in one Questionnaire Automation project - in a slightly different way though - in that case the records to be added(questions) were in a separate table. smile.gif
Go to the top of the page
 
Gustav
post Apr 8 2020, 04:53 AM
Post#6


UtterAccess VIP
Posts: 2,234
Joined: 21-February 07
From: Copenhagen


You can't use EOF as you constantly add new records. Count the records first and use that count.

However, you have got the concept a little wrong.
The trick is to use the RecordsetClone of both the main form and the subform.

This is much faster and simpler than running a set of queries. But perhaps the greatest advantage is, that the forms update instantly without a requery.

Here is my original and generic code due to the absence of field names - apart from the key fields which, of course, are needed:

CODE
Private Sub CopyButton_Click()

    Dim rst         As DAO.Recordset
    Dim rstAdd      As DAO.Recordset
    Dim fld         As DAO.Field
    Dim Count       As Integer
    Dim Item        As Integer
    Dim Bookmark    As Variant
    Dim OldId       As Long
    Dim NewId       As Long
    
    ' Copy parent record.
    Set rstAdd = Me.RecordsetClone
    Set rst = rstAdd.Clone
    
    ' Move to current record.
    rst.Bookmark = Me.Bookmark
    OldId = rst!Id.Value
    With rstAdd
        .AddNew
        For Each fld In .Fields
            With fld
                If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or GUID field.
                Else
                    .Value = rst.Fields(.Name).Value
                End If
            End With
        Next
        .Update
        ' Pick Id of the new record.
        .MoveLast
        NewId = !Id.Value
    End With
    ' Store location of new record.
    Bookmark = rstAdd.Bookmark
    
    ' Copy child records.
    ' If a subform is present (replace subChild with the name of the actual subform control):
    Set rstAdd = Me!subChild.Form.RecordsetClone
    ' If a subform is not present, retrieve records from the child table:
    ''Set rstAdd = CurrentDb.OpenRecordset("Select * From tblChild Where FK = " & OldId & "")

    Set rst = rstAdd.Clone

    If rstAdd.RecordCount > 0 Then
        rstAdd.MoveLast
        rstAdd.MoveFirst
    End If
    Count = rstAdd.RecordCount
    For Item = 1 To Count
        With rstAdd
            .AddNew
            For Each fld In .Fields
                With fld
                    If .Attributes And dbAutoIncrField Then
                        ' Skip Autonumber or GUID field.
                    ElseIf .Name = "FK" Then
                        ' Skip master/child field.
                        .Value = NewId
                    Else
                        .Value = rst.Fields(.Name).Value
                    End If
                End With
            Next
            .Update
        End With
        rst.MoveNext
    Next
    rst.Close
    rstAdd.Close
    
    ' Move to the new recordcopy.
    Me.Bookmark = Bookmark
    
    Set fld = Nothing
    Set rstAdd = Nothing
    Set rst = Nothing

End Sub

As you can see, it will be close to a copy-paste. Only minor changes are needed: The Id/FK and the subform control name.

--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 
projecttoday
post Apr 8 2020, 04:57 AM
Post#7


UtterAccess VIP
Posts: 12,166
Joined: 10-February 04
From: South Charleston, WV


Gustav, hemant has said that he/she is going to go with an append query. (Good idea.)

--------------------
Robert Crouser
Go to the top of the page
 
Gustav
post Apr 8 2020, 08:06 AM
Post#8


UtterAccess VIP
Posts: 2,234
Joined: 21-February 07
From: Copenhagen


No Robert, it's not a good idea, because he/she is close and deserves an explanation why his/her code misbehaves.

Running queries for this simple task will be clumsy. The form already has the records you need, so there is no reason to pull them once more and to deal with a requery of the form.

--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 
projecttoday
post Apr 8 2020, 10:19 AM
Post#9


UtterAccess VIP
Posts: 12,166
Joined: 10-February 04
From: South Charleston, WV


The task is to create new records.

--------------------
Robert Crouser
Go to the top of the page
 
Gustav
post Apr 11 2020, 08:27 AM
Post#10


UtterAccess VIP
Posts: 2,234
Joined: 21-February 07
From: Copenhagen


Sure, Robert - new, but not empty, if that's what you mean:

QUOTE
One of the functionality to be given is to clone an existing location's holiday calendar. This is because the new location calendar may need just a few changes.

--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th May 2020 - 05:19 PM