Yes its another Many-to-Many question :)

[vapid2323]

Hey guys I have done Many-to-Many forms a few times with a previous database so I understand the basic concepts but I wanted to see how you might tackle this one.

I have a standerd setup:

tblCQA
Field1
Field2
Field3

tblJoin
fk_CQAID
fk_COIID

tblCOI
Field1
Field2
Field3


I would like my users to add new records to the CQA Form and also create new COI records and link them in one smooth proccess.

I have always created a subform where users can select pre-created records like Staff names, but in this case I would like to create and link in the same form/subform.

Thanks for the help!

 

[boblarson]

What you could do is to have a main form which is not bound and then have all three of tables on that form as subforms. The junction table wouldn't necessarily need to be there and then you could select one record from the CQA table and one from COI and click a button called LINK. And then do an append query to append the two ID's. And it could have a DCount to check first to see if the link already exists and, if so, it could return a message.

 

[vapid2323]

Humm, an idea I was thinking of was to have the main form bound to my CQA table and then just have a area on the form with unbound controls that match the COI table.

A user would be able to select all the options from the unbound fields then click a add button that will create the new COI record (with the proper checks for current records etc)

I might then have a listbox that shows the current COI/CQA joined records where a user can remove edit or change that info by loading the unbound fields with the selected record and having an update button.

I think our ideas are close but I am trying to figure if one is a better design.

 

[vapid2323]

What you could do is to have a main form which is not bound and then have all three of tables on that form as subforms. The junction table wouldn't necessarily need to be there and then you could select one record from the CQA table and one from COI and click a button called LINK. And then do an append query to append the two ID's. And it could have a DCount to check first to see if the link already exists and, if so, it could return a message.

I think it might help to add that my users will always be starting from the CQA side. They will never start from the COI end.

This might make things more simple so we can focus on just one side of the join.

 

[boblarson]

Then why not just have the CQA as the main form's record source and then have the junction table as the subform and the subform will automatically set the CQA_ID and the user just needs to select the COI's that belong to that CQA?

 

[vapid2323]

Then why not just have the CQA as the main form's record source and then have the junction table as the subform and the subform will automatically set the CQA_ID and the user just needs to select the COI's that belong to that CQA?

Will the users also be able to add in the new COI records with that? I thought it would cause issues to have the subform be able to create new COI records and also link them.

Granted I have been known to over think things

What I really am trying to do is not create two steps, this is very much a data entry thing. I dont want my users to open a new form, add in the COI records then come back to the CQA form and link them.

I want that to all be on the single CQA form, If you think the normal many to many setup (Mainform/subform) will work that way then I simply overthought this whole thing >.<

 

[boblarson]

Well, the COI form can be on the main form as a subform but not linked. Then they can add them and in that form's After Update event just requery the combo on the subform for the junction table so that new records will become available.

 

[vapid2323]

Well, the COI form can be on the main form as a subform but not linked. Then they can add them and in that form's After Update event just requery the combo on the subform for the junction table so that new records will become available.

I love it! I think thats a great solution!