Passing data between forms, and using it in the receiving form

[jcaswell]

I am managing a many-to-many relationship using the below form. This is called by a previous form, and the Element name 'Deskware Hardware' is passed in. The form only shows the Components that are linked to Desktop Hardware (there is a many-to-many relationship between Element and Component that is resolved throough a link table). In this case, I want to add more Components to the given Element, but this table will allow the user to pick any Element/Component combination to add, but I want to constrain it to this Element.



So - what I would like to do is to stop update of the Element column, but whenever they add a new Component using the Combo box, the Element name and ID (the second column) are automatically added. I know I can set the Component field up so when it changes, I can take an action, and in this case I would like to automatically add the Element Name and ID.

How can I do this?

[June7]

The Element and Component are in the same table? You could have code that will set the DefaultValue of ElementID field. http://access.mvps.org/access/forms/frm0012.htm

Why are you saving the Element name and Component description?

[jcaswell]

Element and Component information are in two different tables, and each one has an ID. There is a many-to-many relationship between them, so I have a link table that contains the IDs of the linked pairs. The form contains the results of a join of the two main tables and the link table, using the Element ID to initially populate it, showing initially only the links already established for that Element. It allows me to delete links that i no longer need by pressing the Delete buton (it only deletes the link), and also allows me to establish new links using the down arrows to select new link pairs. Thus although I am showing the descriptions, I only do that to allow easy selection. The thing that is saved is the pair of Element ID and Component ID in the Link table.

In this case, a previous form was allowing the user to update a particular Element (Desktop Hardware), and they had a button to allow then to establish the links to Components. That form called the one being shown, passing the Desktop Hardware ID. this form was initially populated with the existing links for Desktop Hardware, and allows the user to add more, or delete existing ones.

So the user action in that case is to use the drop-down arrows in the left column to select Desktop Hardware, and those in the right column to select the Component they want to link to. So, the selection of Desktop Hardware is un-necessary in this case as all pairs will be linked to Desktop Hardware, it is the right column (Component) that needs the selection.

So, I want to disable the left hand column, and automatically enter Desktop Hardware and its ID (columns 1 and 2) when an entry is made in column 4. The trigger can be set using a function such as 'Got Focus' on the component field when the user makes an entry, but I do need to understand how to get hold of the variables

Does that help?

[June7]

Have you considered a form/subform arrangement?

Want to provide db for analysis? Follow instructions at bottom of my post.