Results 1 to 5 of 5
  1. #1
    DenisM is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Location
    Sleaford Lincs
    Posts
    29

    Code to populate Join Tables

    Hello,



    I have an Access 2016 database with several join tables. Rather than manually updating these tables, I was wondering if anybody knows of code that I could use to automatically
    do the updates.
    Thank you for any help

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You haven't given us very much detail to go on (what your data looks, what will be populated, how the calculation is done), but possibily take a look at using Update Queries (there are lots of tutorials you can find on these with a Google Search).
    If it is more complex, VBA might be required.

    Note that if it is something that can be calculated based on the other fields, then you shouldn't update/store it in a table field - just do it in a query in a calculated field. That way it is dynamic and automatic, and you don't need to run anything any time you add or change data.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    normally these would be populated automatically by defining relationships and using forms, so presume this is a one time 'catchup' exercise. Agree with Joe, not enough info to provide suggestions

  4. #4
    DenisM is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Location
    Sleaford Lincs
    Posts
    29
    Hello JoeM,
    Thanks for the response, sorry about the lack of detail.
    I have attached part of the ER diagram which should give you a better idea.
    The CountriesStatusJoin table is updated using forms and the StatusMonetarySystemsJoin table is also updated using forms so these are not a problem.
    My issue is with the CountriesMonetarySystemsJoin table, I need to manually key the Country_ID and Monetary_System_ID, is there a way to do this automatically or with code ?
    Attached Files Attached Files

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Can a country have several monetary systems at same time moment? When not, then tbl_CountriesMonetarySystemsJoin in this design is pointless!
    a) When a country has always a single monetary system, and it never does change, or only current system counts, add field CMSJ_Monetary_System_ID into tbl_Countries;
    b) When a country has always a single monetary system, but may change and you must know, which system was applied at any time moment, then you need instead a table like tbl_CMS_History: CMSH_ID, CMSH_Country_ID, CMSH_Monetary_System_ID, ValidFrom;
    c) Only when a country can have several monetary systems at once, you need your original table, but probably you need to change the design a bit. like tbl_CMS: CMS_ID, CMS_Country_ID, CMS_Monetary_System_ID, ValidFrom, Active.

    Now about applying monetary system to country.
    1. You need a single form, where user can select a country (fCountries);
    2. You need a continuous form based either on tbl_CMS_History, or on tbl_CMS (e.g. fCMS). Controls linked to primary key, and country ID are better set hidden, the control linked with monetary system ID must be a combo which selects monetary system ID from tlbl_Monetary_Systems, and displays its name;
    3. You insert continuous form into fCountries as subform (sfCMS). fCountries and sfCMS must be linked through country ID's in respective tables. Whenever you add a new record into subform, it will be linked to active country in fCountries.

    When a), then all you need is a combo to select a monetary system ID in fCountries from monetary system ID (like explained in 2. but the form is different).

    The case c) is most difficult one - to ensure all entries are made properly, and to read info.

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

Similar Threads

  1. Replies: 12
    Last Post: 02-21-2017, 08:33 PM
  2. Replies: 8
    Last Post: 06-04-2014, 10:01 AM
  3. Replies: 6
    Last Post: 11-19-2013, 01:38 AM
  4. Replies: 7
    Last Post: 09-06-2012, 06:04 AM
  5. Query Join, Expression, or Code
    By catguy in forum Queries
    Replies: 1
    Last Post: 08-17-2011, 10:52 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