Sub Program initially not showing data

I have a subform that has data inserted with this code:

        Dim lngOldID As Long
        Dim lngNewID As Long
        Dim strSQL As String
        lngOldID = Me.ItemNumber*************************** Keep original key field id number
        DoCmd.RunCommand acCmdRefresh
        DoCmd.OpenQuery "DuplicateSet", acNormal, acEdit  *************** This creates a duplicate of the main form data
        DoCmd.Requery
       
        DoCmd.GoToRecord , , acLast  ****************Move to the duplicate
        lngNewID = Me.ItemNumber

        strSQL = _
            "INSERT INTO VisionPanels (ItemNumber,  DFT, IFL1, CH1, CW1, CD1)" & _
            "SELECT " & lngNewID & " AS ItemNumber,  DFT, IFL1, CH1, CW1, CD1" & _
            "FROM VisionPanels " & _
            "WHERE ItemNumber=" & lngOldID
        CurrentDb.Execute strSQL, dbFailOnError

All works great except the new data in the subform is not shown. It's as if it has the data but has moved the sub program on to the next blank record. I have to press escape a couple of times to get rid of a "the record could not be added because it would cause a duplicate in the key field  (ItemNumber)" message. How ever when I move to a previous record and back again the duplicated record data in the subform is there. I have tried requery, refresh and me.dirty all to no avail. Any ideas. Been on it 2 days can't see the cause of the problem
Derek BrownMDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
I have a subform that has data inserted with this code:
1. Forms don't hold data.  Tables hold data.
2. Refresh asks Access to update the recordset bound to the form to show new values.  Deleted records will be flagged as #DELETED# but new records will not be shown.  For that, you would need to use Requery.  Requery reruns the query completely so deleted rows actually disappear and new rows will be displayed.  Both of these methods have as a SIDE EFFECT, before executing their stated function, Access will save the current record if it is dirty.  It is poor practice to use Refresh or Requery when what you are actually trying to do is to save the current record.  Instead, you would use the standard method - DoCmd.RunCommand acCmdSaveRecord or the "trick" that is sometimes recommended which is Me.Dirty = False which is supposed to get around some error which I have never encountered.  

I think you existing problem is that you appear to be Requerying the form BEFORE you insert the child records.

FYI - On the surface this looks like a design flaw.  Perhaps there should be a third table that sits between the current parent table and the current child table.  The new table would hold the data that stays the same thereby eliminating the need to copy it.
0
Gustav BrockCIOCommented:
It is way simpler - and faster - to use the RecordsetClones:

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 NewId       As Long
    
    ' Copy parent record.
    Set rstAdd = Me.RecordsetClone
    Set rst = rstAdd.Clone
    
    ' Move to current record.
    rst.Bookmark = Me.Bookmark
    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.
    Set rstAdd = Me!subChild.Form.RecordsetClone
    Set rst = rstAdd.Clone

    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

Open in new window

Also, no requery is needed. The new record(s) are available at once.
0
Derek BrownMDAuthor Commented:
Thanks Pat. That is useful info!

If I have a table between the Main and subform's tables  presumably I cannot change data in the new table without altering it for all other linked main and subform tables. The actual subform used hold a lot more data than shown. I cut it short as it was not necessary for the question. My actual subform holds 56 fields but only one or two fields normally get changed. So it is only a duplicate to get the large volume of common data inserted so a simple change does not involve re-entering all the rest of the data. But any of the common data can also need changing. So there are no fields that can be consistent across all records

Thanks Gustav I will give that a try
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Derek BrownMDAuthor Commented:
Still struggling.THis is wired Subform after running existing code and Docmd.GoTo Record,,ACLast where subform and main form are linked by the itemnumber circled in blue. here it looks like there is no data in the subform but it does exists in the underlying table but does not show in subform despite refresh and docmd save and requery. It forces me to press escape twice and move main form record to any other record and when I go back to record 2707  on the main form the corresponding 2707 appears in the subform with the correct data for record 2707
Capture
Press escape, twice move to another record and back to 2707 and I get this
Capture1All is well except for the damned performance to get here
0
Gustav BrockCIOCommented:
And how did you copy the data?
0
Derek BrownMDAuthor Commented:
At the moment it is still with the original code. I cannot let this beat me. I have your code on another DB just testing. Getting errors but I know it's me.

The main form is duplicated by a standard ApendQuery. It is the VPSubform that needs copying and inserting into a table called VisionPanels. So Iguess that when I get error at:

    rst.Bookmark = Me.Bookmark
    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

It's because I am trying to copy main form data?
0
Gustav BrockCIOCommented:
My posted code does it all - copy the main record, then copy the child records.

First, it requires no requery of the forms.
Second, it takes care of the new primary of the main record that you will need for the foreign key of the child records.
Third, it runs very fast.
0
Derek BrownMDAuthor Commented:
Found the problem guys. Found this burried in 6,800 lines of code:


    Call Me.VPSubForm.Form.VPGlassCheck

In the main form's oncurrent, a call to the subform's GlassCheck where data is programmatically  entered and saved.

3 days work. Silly mistake!!

Still like clone code for subprogram. It does look simpler Gustav

So I would like to allocate points
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Derek BrownMDAuthor Commented:
This system does not allow me to enter points. I ticked solutions were helpful but no option to assign points. I know I found the problem but both of you helped!!

Is EE now run by "young" people
0
Derek BrownMDAuthor Commented:
Cheers both!
0
Derek BrownMDAuthor Commented:
Yes Mr. Moderator that was it. My thanks
0
PatHartmanCommented:
Derek,
Gus gave you the code to get past the problem you are having but as I suspected, you do have design issues.  Much of the data on the form you displayed forms several repeating groups.  Each of those groups should be a separate table.  You should be able to work with the database as is but you have thousands of lines of code behind the form because your data is not structured correctly.  Based on what I see, this could be difficult to restructure to normalize the schema depending on how far along you are with development.  As long as the business does not change at all, you will probably be OK but even minor changes in the business could cause the application to become useless over night.  What if the company decided to expand into something other than doors?  What if they introduced doors with more than 6 panels (pretty likely)  The app would be pretty much useless.  Do you think Amazon has to redesign its database, if they add a new type of product?  Do you think Boeing would need to redesign their applications if they started developing smaller corporate jets?

Designing a database schema requires a different mindset than designing a spreadsheet which this form very likely started as.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.