Results 1 to 2 of 2
  1. #1
    pedge1059 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    1

    Auto-populate Junction table based on equal fields in different tables

    Beginner to Access

    Is there an easy way to auto-populate a Junction table [in access 2010] given the following two tables with a many-to-many relationship for Tasks? The two tables are

    Table 1) tblTasks (TaskID (PK), Description), and

    Table 2) tblMeasures (MeasureID (PK), Description, Tasks)

    If JCTN table is JCTN_Tasks_Measures (TaskID, MeasureID), is there a way to populate when tblTasks(TaskID) == tblMeasures (Tasks)?




    Hope this is clear. Thanks in advance.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You can use DAO or you can execute SQL to append records. Either way you will probably want to use DAO to loop through a recordset, a recordset that dictates the values to append to your junction table.

    For instance, add records to your tblMeasures via User Input and a Data Entry form. Disable the ability for the User to close the form. Have the user click a button to Save. Then some code like this

    if me.dirty then me.dirty = false
    Dim rsClone as DAO.recordset
    set rsClone = me.recordsetclone

    if rsclone.eof then
    msgbox "Please create at least one record before saving."
    set rsclone = nothing
    exit sub
    end if

    dim rsNew as dao.recordset
    set rsnew = CurrentDB.Openrecordset("JCTN_Tasks_Measures", dbopendynaset)

    rsClone.Movefirst

    while rsclone.eof = false
    rsNew.addnew
    rsnew![TaskID] = rsClone![TaskID]
    rsnew![MeasureID] = rsClone![MeasureID]
    rsNew.Update

    rsclone.Movenext
    Wend

    rsNew.Close
    set rsNew = nothing
    rsClone.close
    set rsClone = nothing

    Docmd.Close 'Close your form if that is what you want

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

Similar Threads

  1. Populate Junction Table related to 3 Tables
    By Daoud1987 in forum Access
    Replies: 5
    Last Post: 12-11-2013, 12:13 PM
  2. Replies: 25
    Last Post: 01-25-2013, 10:26 AM
  3. Replies: 7
    Last Post: 11-28-2012, 01:41 PM
  4. Replies: 2
    Last Post: 12-07-2010, 12:32 PM
  5. Replies: 3
    Last Post: 10-05-2009, 07:22 AM

Tags for this Thread

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