Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2016
    Posts
    8

    Answered: Copying field in one form to multiple tables

    I have a form that has 3 sub forms within it.

    I need one of the fields in the main form to be copied to the other three sub form tables as i am using it as a unique id.

    Can anyone help me in how i would be able to do this? What code to use?

    Thanks, sorry more likely really easy.

  2. Best Answer
    Posted by Missinglinq

    "Use the unique ID Field (which I assume is the Primary Key for the Table the Main Form is based on as a Foreign Key in each of the Tables the Subforms are based on, then use this Field to link each Subform to the Main Form, and Access will insert this into each Subform.

    If the unique ID Field in the main Table is an Autonumber, define those Fields in the other Tables as a Number Datatype.

    Linq ;0)>"


  3. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Use the unique ID Field (which I assume is the Primary Key for the Table the Main Form is based on as a Foreign Key in each of the Tables the Subforms are based on, then use this Field to link each Subform to the Main Form, and Access will insert this into each Subform.

    If the unique ID Field in the main Table is an Autonumber, define those Fields in the other Tables as a Number Datatype.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #3
    Join Date
    Mar 2016
    Posts
    8

    Still not working

    Quote Originally Posted by Missinglinq View Post
    Use the unique ID Field (which I assume is the Primary Key for the Table the Main Form is based on as a Foreign Key in each of the Tables the Subforms are based on, then use this Field to link each Subform to the Main Form, and Access will insert this into each Subform.

    If the unique ID Field in the main Table is an Autonumber, define those Fields in the other Tables as a Number Datatype.

    Linq ;0)>
    Thanks for the quick response, I have tried that then tried to create a new record in the main form and hit save but the unique Id field didn't copy to the other sub form tables to create that Id within them to allow them to join up.

    Do you have any other ideas?

  5. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by hambees View Post
    I have tried that then tried to create a new record in the main form and hit save but the unique Id field didn't copy to the other sub form tables.
    Do you have the Subforms joined to the Main Form by each Subform's Link Master Field/Link Child Field Properties by way of the unique Field?

    In Form Design View of the Main Form, you'll find the Link Master /Link Child Properties by selecting each Subform Control, in turn, then going to Properties - Data.

    The ID won't be inserted into any of the Subform Tables until New Records are entered into each of the Subforms, of course.

    Linq ;0)>
    Last edited by Missinglinq; 03-30-16 at 18:44.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #5
    Join Date
    Mar 2016
    Posts
    8

    Cool Nearly there

    Quote Originally Posted by Missinglinq View Post
    Do you have the Subforms joined to the Main Form by each Subform's Link Master Field/Link Child Field Properties by way of the unique Field?

    In Form Design View of the Main Form, you'll find the Link Master /Link Child Properties by selecting each Subform Control, in turn, then going to Properties - Data.

    The ID won't be inserted into any of the Subform Tables until New Records is entered into each of the Subforms, of course.

    Linq ;0)>
    Okay so i have got it talking to a degree (BTW - i haven't used access for a while and my brain in somewhat cobwebbed!).

    Thanks for helping me thus far as well!

    So my question is: is there a way to get the ID to save in all of the tables? otherwise i need to go to the table add the number and then it pulls through.

    Thanks again!

  7. #6
    Join Date
    Mar 2016
    Posts
    8

    Almost

    Hi

    I almost have it working apart from one sub form isn't allowing it to show any data get a blank screen.

    This sub form table has links to OBDC tables.

    If the table has an id row for the main form it will then show just not those without or new.

  8. #7
    Join Date
    Mar 2016
    Posts
    8

    Bump

    Bump, still need help on this please??

  9. #8
    Join Date
    Mar 2016
    Posts
    8

    Sub forms not working

    Hi

    I have a form with two sub forms looking at two tables, i have managed to get one of the sub forms to display with the main subform and they appear to link correctly.

    However i have the second subform that won't display when there is no data for the main form entry which means i can't add the data to relate to the main main.

    This subform in question has links to a table which has links to other tables via ODBC and i wondered if that could be causing the problem and if anyone knows how to get around this?

    Thanks in advance! I haven't used Access for years and just trying to remember bits - sorry if it is an easy answer!

  10. #9
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    Iif there is no data in the main form you CANT enter data. There is nothing to add to.
    You MUST have a master record before you can add the sub records.

  11. #10
    Join Date
    Mar 2016
    Posts
    8
    Quote Originally Posted by ranman256 View Post
    Iif there is no data in the main form you CANT enter data. There is nothing to add to.
    You MUST have a master record before you can add the sub records.
    Hi

    I do have data in my main form and it allows me to enter into one of the subforms just not the second one, see attached images.

    Click image for larger version. 

Name:	image1.JPG 
Views:	4 
Size:	21.5 KB 
ID:	16832Click image for larger version. 

Name:	image2.JPG 
Views:	4 
Size:	31.1 KB 
ID:	16833Click image for larger version. 

Name:	image3.JPG 
Views:	4 
Size:	40.0 KB 
ID:	16834

    Thanks

  12. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Threads merged
    I'd rather be riding on the Tiger 800 or the Norton

  13. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So if one subform works as intended and one doesnt what have you done differently?
    Have you done a line by line comparison of yhe properties in both forms to see what is different
    I'd rather be riding on the Tiger 800 or the Norton

  14. #13
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by healdem View Post
    Threads merged
    Thank you for that, Jaded One...I thought I'd taken too many pain killers!

    *******************************

    The errant Subform is completely blank, i.e. not even the Controls are visible!

    Controls don't appear in Form View when three conditions exist at the same time:

    • The Form is Bound to a Table or Query
    • There are No Records in the underlying Recordset or the Recordset cannot be accessed
    • The Form cannot have New Records added

    The reasons that a Bound Form cannot have New Records added include:

    1. AllowAdditions for the Form is set to No
    2. The underlying Query the Form is based on is Read Only
    3. User doesn't have Read and/or Write Permission for the Folder where the Data resides or there is a connection problem with the network.
    4. Folder the File resides in (in versions 2007/2010/2013/2016) not having been declared as 'Trusted'
    5. Form's Recordset Type is set to Snapshot


    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •