Results 1 to 4 of 4

Primary key field update in form doesn't transfer subform data.

  1. #1
    Degs29 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    May 2013
    Posts
    38

    Primary key field update in form doesn't transfer subform data.

    I have a form/subform (one-to-many) combination with the following fields (and other irrelevant ones):

    Form:

    -JobNbr

    Subform:

    -JobNbr (invisible)
    -PartNbr
    -Description
    -Quantity

    They are linked through JobNbr. My problem is that if you wish to change JobNbr in the form, the subform data is still stored using the old JobNbr value. How do I ensure all data in the subform is carried over to the corrected JobNbr?

  2. #2
    Dal Jeanis is online now VIP
    Windows XP Access 2003
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,740
    So, you have is a subform that is linked to a query against the original JobNbr, and you want to change all the sub records over?

    The typical linkage is that when you change the JobNbr in the Form, you are usually telling the subform to go look for the subrecords for the NEW JobNbr, not telling them to all CHANGE to the new JobNbr.

    So, if you want to alter the key on the records in the subform, you'll need a control on the form that explicitly causes an update query to make the change. I'd have a second control for the new JobNbr, label a command button "Move records to new JobNbr", and have code that ran a query to update from the old to the new, then reset the value of the JobNbr field and blanked out the NewJobNbr field and requeried the subform.

  3. #3
    Degs29 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    May 2013
    Posts
    38
    Quote Originally Posted by Dal Jeanis View Post
    So, you have is a subform that is linked to a query against the original JobNbr, and you want to change all the sub records over?

    The typical linkage is that when you change the JobNbr in the Form, you are usually telling the subform to go look for the subrecords for the NEW JobNbr, not telling them to all CHANGE to the new JobNbr.

    So, if you want to alter the key on the records in the subform, you'll need a control on the form that explicitly causes an update query to make the change. I'd have a second control for the new JobNbr, label a command button "Move records to new JobNbr", and have code that ran a query to update from the old to the new, then reset the value of the JobNbr field and blanked out the NewJobNbr field and requeried the subform.
    Ahh, that's the only way, huh? Thanks.

  4. #4
    Dal Jeanis is online now VIP
    Windows XP Access 2003
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,740
    I wouldn't say it's the only way, but it's a simple way that (1) will work, (2) won't confuse your user, and (3) won't confuse anybody who has to support the code later.

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

Similar Threads

  1. Replies: 1
    Last Post: 04-24-2013, 11:12 AM
  2. Data Transfer from one field to another
    By Sahara May in forum Access
    Replies: 1
    Last Post: 07-31-2012, 11:30 PM
  3. Subform table doesn't update
    By hilian in forum Forms
    Replies: 4
    Last Post: 07-27-2012, 03:24 PM
  4. Transfer ID from Form to Query Subform field
    By smcfadden777 in forum Forms
    Replies: 4
    Last Post: 03-11-2012, 07:06 PM
  5. Replies: 2
    Last Post: 01-03-2011, 03:17 PM

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