many to many relationship automatic set up - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

many to many relationship automatic set up

Cristiano Covre

2 years ago

Hi, could you please help me? I have a database for an organization. The database is centered about clients and their support. I have some junction tables (for example, a table ClientT with primary key ClientID, a junction table xClientGoalT with primary key xClientGoalID and foreign keys ClientID and GoalID, a table GoalT with primary key GoalID). I would like to be able to have automatically assigned to a Client the list of all GoalIDs, so I can enter info specific to that Client/Goal for each Goal from a form, forcing the user to follow the order of GoalID (i.e., a continuous subform, NOT a combo box where user can select GoalID 1 or 3 and mess up the order). Is there a simple way without using update/append queries? I hope I made sense... (also, please forgive me English)

	John Jira @Reply 2 years ago
Review the "Employee Training Series" because that sound similar to what you need. Unfortunately there no way to get around queries because that the power of Access. Good Luck.

Kevin Robertson @Reply

2 years ago

I did something similar. I used a Recordset to add 9 records to the Table.
This is a snippet from that database which should hopefully help.

Details

    Dim db As Database

    Dim rs As Recordset

    Dim X As Long

    

    X = 0

    

    Set db = CurrentDb

    Set rs = db.OpenRecordset("PageSynopsisT", dbOpenDynaset)

    

    While X < 9

        rs.AddNew

            X = X + 1

            rs!ScreenplayID = ScreenplayID

            rs!PageID = X

        rs.Update

    Wend



    rs.Close

    db.Close

    

    Set rs = Nothing

    Set db = Nothing



    Forms!ScreenplayF!PageSynopsisSubF.Form.Requery

	Cristiano Covre OP @Reply 2 years ago
Thanks John and Kevin!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View