Results 1 to 14 of 14

Update of Table, but not displaying in Subform

  1. #1
    Join Date
    Jul 2019
    Posts
    16

    Update of Table, but not displaying in Subform

    I have a main form that has 3 subforms. I enter data into mainform, subform 1, then subform2 and finally subform3. When I save the record(s) the tables update, but for some reason the data I entered into subform 3 does not stay displayed, but data is in the table.

    Any thoughts of why his would occur?

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,554
    not without more detail. Best guess is there is something in subform3 which is conditional on one of the other subforms or main form.

  3. #3
    Join Date
    Jul 2019
    Posts
    16
    It seems that when I enter the data the second time, it stays in the form but duplicates the information in the table. There does need to be data in Subform 2 (which there is) in order to complete Subform 3.

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,554
    still not enough to make suggestions. Think you will need to upload your db. Remove all tables/forms etc which are not relevant to the problem and provide a guide for how to replicate the issue

  5. #5
    Join Date
    Jul 2019
    Posts
    16
    If I enter data in Subform 2, save and move to the next record and then come back and enter into Subform 3, the data saves correctly in table and displays in the form, so I'm not sure why it is working that way.

    I think it has something to do with my SAVE Button where I run a bunch of update queries (My subform3 is the Ship Details):

    Private Sub Save_Record_Click()
    On Error GoTo Save_Record_Click_Err

    If Me.Dirty Then
    Me.Dirty = False
    End If

    CurrentDb.Execute "Product_Details_Table Update With SponsorID", dbFailOnError
    CurrentDb.Execute "Ship_Details_Table Update OrderDetailsID", dbFailOnError
    CurrentDb.Execute "Ship_Details_Table Update Sponsor", dbFailOnError
    CurrentDb.Execute "Product_Details_Table Update with ShipDetailID", dbFailOnError
    CurrentDb.Execute "Order_Details_Table Update with ShipDetailID", dbFailOnError

    Save_Record_Click_Exit:
    Exit Sub
    Save_Record_Click_Err:
    MsgBox Error$
    Resume Save_Record_Click_Exit
    End Sub

    Private Sub cmdExit_Click()
    If MsgBox("Exit Access?", vbYesNo, "Are You Sure?") = vbYes Then
    DoCmd.Quit
    End If
    End Sub

  6. #6
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    278
    Can you zip and post a copy of the database? It would make it far easier to try to troubleshoot if we could look at what is going on and when. For example, what if you try to enter an additional record in subform 3, is the first record save and displayed correctly?
    Dave

    Learn Something new everyday. A day in which you learn nothing is a wasted day.

  7. #7
    Join Date
    Jul 2019
    Posts
    16

    Update of Table, but not displaying in Subform

    Attached is stripped down version of database.
    Attached Files Attached Files

  8. #8
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    278
    I am not seeing the behavior you speak of. I have entered 2 records, saved them and they both still show in the main form as I would expect. If I move off the record to the next or previous, everything looks as I would expect.
    Dave

    Learn Something new everyday. A day in which you learn nothing is a wasted day.

  9. #9
    Join Date
    Jul 2019
    Posts
    16
    Enter in Main Form, SubFrm1 and SubFrm2 and select Save. Then enter SubForm3, Save. Go to next record and then back to see that it is not displayed in the subform3.

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,465
    In table "Key_Table", "Key" is a reserved word in Access and shouldn't be used for object names.

    Advise removing ALL Look-up FIELDS in tables.



    Why are the fields with the RED arrows in the tables?? Those fields are unnecessary.
    Click image for larger version. 

Name:	Fields1.png 
Views:	15 
Size:	83.0 KB 
ID:	39956
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  11. #11
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    278
    Enter in Main Form, SubFrm1 and SubFrm2 and select Save. Then enter SubForm3, Save. Go to next record and then back to see that it is not displayed in the subform3.
    I followed the steps above and it still seems to be working properly unless I am not following the steps exactly but I believe I am.

    Also, I didn't even look at the table structure, names etc but SSANFU is correct, you should listen to his advice regarding Keywords and redundant fields.
    Dave

    Learn Something new everyday. A day in which you learn nothing is a wasted day.

  12. #12
    Join Date
    Jul 2019
    Posts
    16
    Why remove look-up fields, they help with Data Integrity. Thanks for the input on table structure...I have to keep moving forward at this point and will have to recreate in the background.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,465
    Quote Originally Posted by grasshopper212 View Post
    Why remove look-up fields, they help with Data Integrity.
    Actually, Look-up FIELDS only "help with Data Integrity" if you are entering data directly into tables. If the dB is non-critical and the dB is for personal use, then you could use Look-up FIELDS (I don't/won't).
    Otherwise, data changes (Adds, Edits,Deletes) should be done using FORMs. Forms allow for better verification

    Most Access developers do not use Look-up FIELDS.

    The biggest problem is that Look-up fields hide the actual data
    If you ever decide to convert from an Access BE to a bigger RDBMS BE, like MySQL, SQL Server, Oracle, etc., you will have to re-write your dB because Look-up FIELDS are not supported in MySQL, SQL Server, Oracle, etc.




    See:
    The Evils of Lookup Fields in Tables and
    The Ten Commandments of Access


    Good luck with your project....
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  14. #14
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    166
    I added 1 record with no issues at all.

    All Forms are linked correctly.

    You should definitely remove all lookup fields from tables. See this explanation http://access.mvps.org/access/lookupfields.htm

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-04-2019, 07:04 AM
  2. Replies: 2
    Last Post: 10-14-2016, 05:05 AM
  3. Subform table doesn't update
    By hilian in forum Forms
    Replies: 4
    Last Post: 07-27-2012, 04:24 PM
  4. Update field in table after update on a subform
    By jpkeller55 in forum Queries
    Replies: 3
    Last Post: 09-30-2010, 08:02 PM
  5. Replies: 0
    Last Post: 03-31-2010, 07:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums