Results 1 to 9 of 9
  1. #1
    accessgm is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    10

    Populate Tables w/ Main Table Serial Numbers

    Hello - I have three tables in my database. I will be populating the main table with data containing unique serial numbers. I'd like to know if there is a way to automatically populate tables two and three with those same serial numbers once they are input in the main table. I'm assuming VBA but would like to know if anyone could assist. Thanks!

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    It's certainly possible and very easy. If you could elaborate on what information these tables contain and how they are related we could better assist. Are you using the serial number as the primary key field in your main table?

  3. #3
    accessgm is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    10
    Hi - Yes, Serial Number will be the primary key and is how they will be related.

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    The simplest way, which requires no vba, would be to use a main form that uses your main table as its recordsource, and subforms that use the children tables as their recordsource. You simply place these subforms on your main form, and set the "Link Master Fields" and "Link Child Fields" appropriately and Access will take care linking your data for you.

    Make sure you set up their relationships in the relationship window.

    I assume the Serial Number is NOT the primary key of the related tables?

  5. #5
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    A field that is autonumber type and designated as the key field - - would work well as the serial number field. Simply give it that name.

    However do note that for whatever reason, if a record is deleted - perhaps due to some data entry error - that number cannot be reused. It is gone forever. This would mean there are gaps in the Serial Number sequence. If this is not a biggie then it is a robust and simple solution.

    Otherwise one must make a Number field type; and with a little bit of VBA - you find the MAX existing value and +1. In a multi user environment however there are issues here also as to multiple people seeing the same max while they first enter data - and also the issue potentially dealing with deletes which could reset the MAX. The correct implementation in this case depends very much upon knowing the nature of the work flow in real life.

  6. #6
    accessgm is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    10
    Thank you for the suggestion. Is there a way to do without involving forms?

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by accessgm View Post
    Thank you for the suggestion. Is there a way to do without involving forms?
    To answer your question directly: Yes. You can use data macros and/or a subdatasheet.

    Beware most would frown on data entry directly into tables without using forms. And subdatasheets for that matter. And most don't care for macros either! But to answer your question, yes.

  8. #8
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Here is an example:

    Assuming your table structure looks something like this:
    Click image for larger version. 

Name:	dd1.png 
Views:	12 
Size:	6.5 KB 
ID:	33220

    Make sure you are enforcing referential integrity
    Click image for larger version. 

Name:	dd4.png 
Views:	9 
Size:	19.3 KB 
ID:	33223

    Go to the "MainTable" and edit it's After Insert data macro
    Click image for larger version. 

Name:	dd2.png 
Views:	10 
Size:	23.0 KB 
ID:	33221

    Make it's code look something like this:
    Click image for larger version. 

Name:	dd3.png 
Views:	10 
Size:	12.1 KB 
ID:	33222
    This says that after a new record is inserted into the MainTable it will create a record in TableTwo and define the SerialNumber field, and the same for TableThree. If there are any other required fields for these tables you will need to set them here as well.

    Here is the code from the pic above. You can copy and paste this into the Macro editor window and change names as needed.

    Code:
    <?xml version="1.0" encoding="UTF-16" standalone="no"?>
    <DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><DataMacro Event="AfterInsert"><Statements><CreateRecord><Data><Reference>TableTwo</Reference></Data><Statements><Action Name="SetField"><Argument Name="Field">TableTwo.SerialNumberFK</Argument><Argument Name="Value">[MainTable].[SerialNumberID]</Argument></Action></Statements></CreateRecord><CreateRecord><Data><Reference>TableThree</Reference></Data><Statements><Action Name="SetField"><Argument Name="Field">TableThree.SerialNumberFK</Argument><Argument Name="Value">[MainTable].[SerialNumberID]</Argument></Action></Statements></CreateRecord></Statements></DataMacro></DataMacros>

  9. #9
    accessgm is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    10
    Thanks for the responses! kd2017, your explanation and visuals were perfect. The macros are working as desired!! Thank you!

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

Similar Threads

  1. Replies: 3
    Last Post: 07-31-2017, 05:21 PM
  2. Autopopulating Serial Numbers
    By nkoenig34 in forum Programming
    Replies: 3
    Last Post: 01-19-2016, 11:07 AM
  3. Help with Serial numbers table
    By RandyP in forum Programming
    Replies: 2
    Last Post: 06-11-2014, 03:39 PM
  4. Replies: 2
    Last Post: 02-14-2014, 11:23 AM
  5. mutli serial numbers in one
    By wirelineuk in forum Queries
    Replies: 1
    Last Post: 10-11-2012, 01:26 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
  •  
Other Forums: Microsoft Office Forums