Can this be done: A Form to Copy-and-Paste (and Revise) Hundreds of Many-to-Many Relationships?

[KrisAK]

I know Excel/VBA pretty well, but haven't yet jumped to Access beyond playing around a bit, and I'm wondering if it can pull-off a particular task.

In short, I'm hoping to provide a tool that generates an overview document that maps Projects to Locations: a matrix, really, like one of those product-feature comparison grids you see on web pages. At its most basic, this suggests three tables: 1) a Project Table, 2) a Location table, and 3) a 'junction' or 'associate' table to manage the many-to-many relationship. (Stop me if I'm way off track.)

Here's the trick: what I need is the ability to rapidly bulk-assign many Locations to a single Project. As in, once a new Project is defined, I'd like to be able to pull up an 'Assigned To' form into which I can paste hundreds of Location Numbers, possibly via cut-and-past from a column in existing Excel documents. (Location Numbers are used internally, and I'd be using them as keys in Access.)



So, could a form be designed that would allow me to select a Project, probably via a drop-down, and then bulk-paste Location Numbers? Maybe allowing me to first delete the existing assignment records in the junction table?

I've found examples of many-to-many designs, but none that handle the sort of bulk assignment as I've tried to describe it here.
Any pointers would be appreciated.

[June7]

Entire columns can be copy/pasted from Excel into an Access table. I've never tried in a form (in datasheet view).

However, what you might really want is an INSERT SELECT action SQL. Something like:

CurrentDb.Execute "INSERT INTO [junction table](ProjectID, LocationID) SELECT " & Me.ProjectID & ", LocationID FROM Locations"

This action would create a record in the junction table for each and every Location and associate with specified Project. If you want to be restrictive on the Locations, that will be more complicated and can be accomplished several ways.

Why would you need to delete records from the junction table?

[KrisAK]

I think you might be on to something. (I only just discovered the concept of datasheet views and sub-forms...like I said, I'm new.)

While I'd hoped to avoid coding, a hybrid data-entry form with a few scripted buttons ("Clear All Current Entries", etc.), and the ability to past columns into a datasheet subform (which I've yet to try) might be one approach.

And to answer your question, "why would they need to delete records," sometimes projects are reassigned, and for consistencies sake it would be easier to zap all current records, and paste in the updated list.

Thanks!

[June7]

Probably easier to run an UPDATE sql action to change the assignment (of what - manager?).