Solved Creating combo boxes on the fly

[DOSPLUS]

Hi you all. Thanks again for giving me pointers as I am trying to figure this out.

The setup is coming along. But I am hitting a problem. I am creating a table from vba with the required fields and it works fine, but I am unable to create a field in a combo list style to put in my heading values list for the mapping.

    ' Create the mapping table
    Set strCreateTable = CurrentDb.CreateTableDef(strMappingTable)
   
    ' Adding table fields to request
    strCreateTable.Fields.Append strCreateTable.CreateField("AutoID", dbInteger)
    strCreateTable.Fields.Append strCreateTable.CreateField("TargetTbl", dbText)
    strCreateTable.Fields.Append strCreateTable.CreateField("SourceTbl", dbText)
   
    ' Process the request
    CurrentDb.TableDefs.Append strCreateTable
    Application.RefreshDatabaseWindow

How can I identify the field created in my table as combo list and place the value list in it??

Regards,

 

[MajP]

On a similar topic warning things never to attempt in Access / VBA....

Several years ago I was involved in a discussion in this form about the possibility of getting an Access Form bound to an ADO.RecordSet rather than a DAO.RecordSet object. I still have the prototype form we arrived at.... as the best we could get it working.... so I was hoping the filename would assist in relocating the thread. Alas, no...

Oh!!! Here it is.... I changed search keywords, and located it....

"Fabricated ADO recordset - adding records"

Fabricated ADO recordset - adding records

Lagbolt has kindly provided an example of a fabricated ADO recordset bound to an ADO recordset here: http://www.access-programmers.co.uk/forums/showthread.php?t=258210 and it is reattached here. I have been unable to discover how to add records using the form. So, what needs to be changed, so...

www.access-programmers.co.uk

My example code is attached to the thread.

I have never used Access Forms bound to an ADO.RecordSet out side this thread / prototype. It is just not safe / reliable.

There is no issue with binding an ADO recordset to a form and be updateable. Never heard any issues with reliability. "Just not safe"??

 

[Pat Hartman]

How can I identify the field created in my table as combo list and place the value list in it??

Bind the combo RowSource to a query or table.

 

[DOSPLUS]

OK, I am adding the works that I did so far. I am at the stage where you can verify the file loaded in a temp table, I am trying to dynamically create the SQL to update or update+append to any table. The functions have a debug popup. You can turn them off by putting False in any function last param.

Any ideas about how to produce the SQL depandant of the field in the target table and the mapped match would be nice. Otherwise, I am continuing the work and will post it here once done.

Note: I also put a test file in excel to play with. I also put a table where you can add target tables in.

Open database / Launch with : Frm_import

Thx

 

[Pat Hartman]

1. If you want to reuse mappings, the mapping table needs to be permanent. Loading the listboxes into memory is OK.
2. If all the arguments you need are on the form, you do not need to pass them around as arguments.
3. You don't have Option Explicit turned on.
4. Importing the spreadsheet data creates bloat. Link is better
5. Your code doesn't actually look for matching column names, it assumes that they match????? For every row in rsTarget.Fields, you add a row to the mapping table and repeat the field name as both the target and the source. This code is wrong.
6. I couldn't figure out how Preset was different from mapped. I would expect preset to pick a row from tbl_Import_target to get an existing mapping to run and mapped to go through the steps you have sort of working.


You must fix 5 before moving on. To start the mapping table, you need to search one listbox based on the other. It doesn't much matter which way you go but you need to loop through all the entries of lstboxA and then see if any entries in listboxB match. If they do, add a row to the mapping table.

Now you need a form based on the mapping table so that the user can pick fldA from comboA and pick fldXYZ from comboB to make a match.

You can leave the source combo's RowSource unfiltered but it it best to restrict the target combo's RowSource to only columns which don't already exist in the mapping table. This allows you to map fldB to the target's fld12 and fldABC. Rare but OK when you are converting data. But there is no point to updating the same column from multiple sources and that would generate an error in the Insert query anyway.

Once the mapping is complete, it is a simple matter to make the Insert query by looping through the mapping table and building the two clauses independently. Then you put the whole SQL string together at the end and run it.

 

[DOSPLUS]

Hi Pat, thx for details.

The point of using preset is that it is assumed that headings are matching. All the time. It also checks that they are
Hence why I used the same column headers on each side.

As for mapping, I am not trying to match field names, I am telling access to use what ever I am telling it. The whole ideas is the ability to import column that are not designated.

Contrary to PRESET where If you load an excel that has more headers (Column) or that the header's name do not match. It will tell you that that your preset is wrong and you should not see the import button.

If you use the same file in mapping, you have the ability to identify the mapping using any field you wish. Even if they do not have the same headers. I would suggest making another excel with twice as much field to understand the logic.

The main premise of the script is to not match headings, but decide what is to be inserted, the user decides the heading to put in the target table. Aside from preset that does not allow that to occur.

If you look at the picture attached, you'll see that the whole list of fields is in fact in the combo box value list. In this case the user could choose any of the 55 headings available. It is understood that the user will have to verify if both columns are indeed of the same type.

 

[Pat Hartman]

I had a totally different view of what you are trying to do based on your original idea that this should be done with controls that you create on the fly.

Your distinction between preset and mapped doesn't make any sense. If the mapping is always ONLY on columns with identical names, who cares what other columns exist? UNLESS the target table has more columns than the source and so some data would be missing. So, ignore #1 and #6 but pay attention to the other comments.

 

[DOSPLUS]

Pat, they are not the same. Because data is coming multiple software scattered in different divisions. But the field format are generally the same. As for the create on the fly, I would of done it, but the consensus seemed to be that you do not want fields generation as the form might lockup.

Anyways, not particularly a bad thing. But it differs from my original idea. Maybe I should of stick with it.

 

[Pat Hartman]

I would of done it, but the consensus seemed to be that you do not want fields generation as the form might lockup.

Your solution of creating controls on the fly to implement this mapping was bizarre, not the concept of mapping itself. Finding the names of all the columns in the spreadsheet and mapping them to the columns of the target table makes sense if the spreadsheets might be inconsistent in their formatting. Once you decide you need to do this to avoid errors, why would you not do it on every import? Why does the user have to tell you - I opened this spreadsheet and checked it myself so I know that all the fields map and there are no extraneous fields?

I didn't see any code that did what I would call mapping. The code assumed that the columns matched if the column count was identical.

 

[DOSPLUS]

I understand but the source file i provided are no where near what is going to be imported. It is imp,ortant for the user to override fields names. Most times the field name willnot match and searching for a match will not yield anything, but the informationm is there under another column name. I will check this out, but I thibk that i might not have much choices.

 

[Pat Hartman]

It is imp,ortant for the user to override fields names.

That was my assumption but earlier you said no. Perhaps we have a language problem. The 6 items in my earlier list relate to what is missing from what I saw that I think is required to actual mapping of non-identical names.

Anyway, I looked closely at what you posted and made my suggestions. Good luck with it.

 

[DOSPLUS]

It is probably a language thing. I am struggling to explain it as the concept in itself a bit bizarre, but I cannot share any data as it is protected and confidential. I made test ty[pe spreadsheet just to see what is resulting when importing.

So, to kinda go around the problem I have, I will make it so that users identify what they want the script to load in which column. I understand that my problem will be the chnace of data type missmatch or an error of importing the wrong column into the table. But generally speaking I can reload the last update.

I am sure its clear as mud!

 

[DOSPLUS]

Hi all,

I am done with the excel import file. I consider the thing done.

I am adding the file here for others to use as an example.

BIG Thanks to all that helped.