Copy sub form records

[ScottV]

Hi All

I've attached screenshots of the table relationships and some nested forms that I need to discuss in my database.

If you look at the forms screenshot you'll see I have a main form "business/cmc issues" that uses a combo box to select a business name; nested into that I have a second form "policy issues log" that holds details of policy issues about that business; then inside that I have a sub form "issue updates" that records brief details about the actions carried out in trying to resolve each policy issue.

The same policy issue can affect more than one business (because of a relationship between the two companies etc) but still needs to be viewed separately. So for example in the business selector combo box I might have business "ABC". In the policy issue it might say "doesn't pay on time". The "doesn't pay on time" issue might also apply to business "123" and so if I picked that business from the combo box you'd see the same policy issue.

Because it's the same issue for two separate businesses, the actions carried out will be the same, so what I want to do is, after a new action is carried out (where relevant) to be able to click a button that would run some code that copies the actions entered in the sub form for business ABC and pastes them into the sub form for business 123 where the product area and policy issue are equal. This is to avoid having to enter the same data twice.

I hope that all makes sense, but please let me know if you need more information.

Thanks very much.

 

[llkhoutx]

Use DAO or ADO or an update query to copy the subform's data source records to the other table, making appropriate modifications.

 

[spikepl]

The same policy issue can affect more than one business (because of a relationship between the two companies etc)

That relationship is not illustrated in you diagram, right? Further, there seems to be no indication on your form that some actions apply to multiple businesses. Even more, actions presumably get added further down the road, yet there still seems to be no connection between the businesses.

 

[ScottV]

I know. It's a bit of a pain. I probably didn't do the best job of normalising the tables. I thought of starting again but I leave my job in a couple of weeks and needed to get this in place before I went.

I thought of starting again and getting a junction table in to handle the many to many relationship, but because the policy issues are quite generic, it will make finding the right policy issue for the right businesses a bit long winded.

I was thinking to create a form with a policy issue combo box to find the right issue, then two sub forms - one to show the businesses affected, and one to show the actions carried out. The problem is that using the policy issue on the main form means the user would probably need to scroll through a few records before they found the right one for the businesses involved.

Do you have any suggestions?

Thanks a lot.