Is there a GUI control I can use in VBA to map elements of one list to elements of another list?

I'm writing an application to import a file into MS Access.  The application will have a target table already defined, and needs a way to map the columns of spreadsheet/CSV to the columns in the table.  I want to do this using a GUI control which shows the table column names listed on the left, the names of the fields from the source file, and some means of matching them up ... ideally a line drawn between elements on the left to elements on the right.

If drawing lines is asking too much, then the control for Map Custom Fields in Outlook for Contact Import would be a usable alternative.

Where might I find such a control?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
None that I'm aware of.  What your asking for is not built-in (access doesn't even have a 'mover' control - a pair of list boxes). And Acess does not support many 3rd party controls as it doesn't fully implement the IDispatch interface.

Because of that,  if you do find a control you might want to use,  make sure it specifies that it will work with  Access.


instead of using a special GUI element which may or may not have problems with Access - why not simply use comboboxes? You can create a continous form which shows the available/mappable columns in a textbox in one column and an empty combobox with all the identified columns of the file. The user can now select which internal column should mapped to which external column, of course he could also use the same external column for x internal columns on the same way. That's much easier to input (not all users are mouse acrobats...) and simple to solve with Access controls.


Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Yes, if your bent on using Access, then I would stick with native controls.   Typically this type of interface is done with two list box controls (which is why I mentioned the 'mover' style of control), source on the left, destination on the right.

You click on the left, which is not a multi-select, and then on the right, everything it is mapped to is selected (box is a multi-select).


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
I generally use three temporary tables
1. The first contains the field names and data types of the destination table.
2.  The second does the same for the source table.  I usually link the Excel spreadsheet to the database.

I generally display this data in subforms or a datasheet so i can include a checkbox to indicate that a field is used

3.  Then the third is the mapping table, where i select a field from each of the other tables and click a command button to do the mapping.  

I generally include a check in that process to determine whether i need to convert the incoming field using one of the data conversion functions (maybe convert a number to text or similar).

I also include a button associated with each of the first two datasheets so that the user can actually view the data in the selected column in a popup form.  If you get ambitious, you can also deteine whether the destination field is required and check for missing values inthe incoming data.
I've used side-by-side listbox controls and two sets of button controls to reposition a selected item in the list (top, up, down, bottom).  I usually include a Delete button as well as an add expression feature that will allow input columns to be manipulated as they are being mapped.  For instance, you might have last name and first name input fields and need to concatenate them to be mapped into a whole name field.  So, the expression might be:
[last name] & ", " & [first name]

Open in new window

You should preprocess the two sets of fields and automatically 'map' ones with the same names.
Dale FyeCommented:
If you are going to have to do this on a regular basis, I would recommend storing these mappings in your own Import Specifications set of tables, so you can select the import specification and it would identify the destination table and field mappings.  Then you could check to see whether the source fields exist in the spreasheet you have elected to import and if not, use conditional formatting in the 3rd table to indicate so.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You could use a Subform bound to a temporary "Mapping" table. The table would contain two rows: SourceColumn and DestinationColumn.

SourceColumn = The Excel column name/number
DestinationColumn = The name of the Column in the temp table

You could then create a Subform based on that table. If you did this in Datasheet or Continuous view, you'd have:

A control bound to DestinationColumn, while could possibly be prefilled, since you presumably know the names of all your fields in the Access table where your Excel data will eventually land. If you prefill, be sure to disable this control so users cannot inadvertently modify the data.

A Combo bound to SourceColumn could contain all the incoming column names. If those are always known, then just prefill the combo with those values. If not, you could perhaps use Automation to get the names of the columns, and use that to fill the combo.

Once that's done, your user would simply setup the mapping by choosing the appropriate column in the dropdown, for the DestinationColumn. You could include logic to disallow a selection in the combo if it's already present in the table, if that fits your needs.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<off topic> Sorry about my duplication of your suggestion, guys. My internet is very slooooow today, and this post (which I sent off about a hour ago) just now showed up!

I think the EE servers are slow today.  Responses from some of my clicks take a long time, as do page loads.
Dale FyeCommented:

I thought the problem with latency was that I'm using LogMeIn to log onto my home computer, and then accessing the internet from home.  

Now that I've switched over to accessing directly from my laptop via wifi, it is still slow.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Curious why you awarded a B grade without even responding to any of the Expert comments.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.