Solved Creating combo boxes on the fly

[MajP]

This may do something similar. Uses a temp table to allow mapping of fields between two tables. I defaults the selections if the names are close.

 

[DOSPLUS]

This may do something similar. Uses a temp table to allow mapping of fields between two tables. I defaults the selections if the names are close.

Thx, i'll give it a go.

 

[Edgar_]

Do you think that the method would create a problem with the loggin of info in Access. Cannot find anything that says Access saves the info if the form is deleted. ???

When you delete a form, rest assured that no data is lost. All your information is safely retained in the tables of your database. The tables don't care if you make or remove forms that work with them, so there's no issue with the method. Just double check that the info you put in the form is saved in the table before you delete the form and you'll be fine.

You can disable the close button of the form and make a button that takes care of the closing, like:

Private Sub Comando0_Click()
    'make form
    Dim newForm As Form
    Set newForm = Application.CreateForm
  
    'define some utilitary variables
    Dim i As Long, j As Long, fieldCount As Long
    Dim someValues As String
  
    'set an imaginary number of textboxes
    'could be your field count
    fieldCount = 10
  
    'define placeholder variable for combos
    Dim currCombo As ComboBox
  
    'loop field count
    For i = 1 To fieldCount
        'make combos
        Set currCombo = Application.CreateControl(newForm.Name, acComboBox)
        'give them some props
        currCombo.RowSourceType = "Value List"
        currCombo.Top = i * 500
        currCombo.Left = 600
        currCombo.Width = 5000
        currCombo.Height = 300
      
        'add some data for the combo
        'guess it'll be dynamic
        For j = 1 To 3
            someValues = someValues & " something in combo " & i & " (" & j & ")" & ","
        Next j
        currCombo.RowSource = someValues
      
        'restart strings for next combo
        someValues = ""
    Next i
  
    Dim closeButton As CommandButton
    Set closeButton = Application.CreateControl(newForm.Name, acCommandButton)
    closeButton.Caption = "Close form"
    closeButton.OnClick = "=CloseWithoutSave(""" & newForm.Name & """)"
  
    'give it some name for show
    newForm.Caption = "MyForm"
  
    'add code
    newForm.HasModule = True
  
    'open form
    DoCmd.OpenForm newForm.Name
  
    'you don't have to save it
End Sub

And in a module:

Public Function CloseWithoutSave(frmName As String)
    DoCmd.Close acForm, frmName, acSaveNo
End Function

That way you discard the pop up asking you to save changes. The form needs to be discarded, if you save it, it will eventually become unusable.

I have not analyzed your actual needs, but I'm sure that you and everyone reading the thread will benefit from a sample of the data in its initial state and its target state, so we can all come up with a reasonable workflow for you.

 

[DOSPLUS]

When you delete a form, rest assured that no data is lost. All your information is safely retained in the tables of your database. The tables don't care if you make or remove forms that work with them, so there's no issue with the method. Just double check that the info you put in the form is saved in the table before you delete the form and you'll be fine.

You can disable the close button of the form and make a button that takes care of the closing, like:

Private Sub Comando0_Click()
    'make form
    Dim newForm As Form
    Set newForm = Application.CreateForm

    'define some utilitary variables
    Dim i As Long, j As Long, fieldCount As Long
    Dim someValues As String

    'set an imaginary number of textboxes
    'could be your field count
    fieldCount = 10

    'define placeholder variable for combos
    Dim currCombo As ComboBox

    'loop field count
    For i = 1 To fieldCount
        'make combos
        Set currCombo = Application.CreateControl(newForm.Name, acComboBox)
        'give them some props
        currCombo.RowSourceType = "Value List"
        currCombo.Top = i * 500
        currCombo.Left = 600
        currCombo.Width = 5000
        currCombo.Height = 300
  
        'add some data for the combo
        'guess it'll be dynamic
        For j = 1 To 3
            someValues = someValues & " something in combo " & i & " (" & j & ")" & ","
        Next j
        currCombo.RowSource = someValues
  
        'restart strings for next combo
        someValues = ""
    Next i

    Dim closeButton As CommandButton
    Set closeButton = Application.CreateControl(newForm.Name, acCommandButton)
    closeButton.Caption = "Close form"
    closeButton.OnClick = "=CloseWithoutSave(""" & newForm.Name & """)"

    'give it some name for show
    newForm.Caption = "MyForm"

    'add code
    newForm.HasModule = True

    'open form
    DoCmd.OpenForm newForm.Name

    'you don't have to save it
End Sub

And in a module:

Public Function CloseWithoutSave(frmName As String)
    DoCmd.Close acForm, frmName, acSaveNo
End Function

That way you discard the pop up asking you to save changes. The form needs to be discarded, if you save it, it will eventually become unusable.

I have not analyzed your actual needs, but I'm sure that you and everyone reading the thread will benefit from a sample of the data in its initial state and its target state, so we can all come up with a reasonable workflow for you.

Sorry, i must of not be clear. And BTW, thank you for taking the time to discuss with me, that goes for all of the users that commented as well!!!

I understand that if there is no change to tables and no save, everything should stay intact. Anyways, the form created would not be bound to any tables, thus cannot change values in any tables. My intent is to pull what I need using VBA and action it also through VBA.

What I was referring to is the comment of MajP about the form maxing out eventually. I am trying to read more about it but not much is available on what access actually keeps track of. It seems, fromprevious comments made, that MSAccess keeps logging data, even if you discard the form, which I do not understand. If the form and all associated controls are never even saved, where is this data logging residing? And why would MSAccess keep info on a deleted form that was never ever saved? Seems odd to me.

Attached is a sample of the Tbl_sow table in a pre and post status from an update. Note that the post result would be an excel pulled in and the update would update the table from that excel. All good when fields headers match. What i would like is to pull in any excel file, show the table and the file headers, and then allow the user to choose what field of the Excel is to be matched with the table field. Instead of hard coding things. I will lots of files coming in to update various tables and i do not want to have to alter the file to match headers.

Regards

 

[DOSPLUS]

Microsoft Access 2016 Maximum Limits and Specifications

Microsoft Access 2016: maximum limits and specifications ... Microsoft Access training in Toronto, Ontario, Canada. Our instructor-led courses are offered in downtown Toronto.

www.avantixlearning.ca

View attachment 111210

Hi Pat

Thx for looking this up! So from what you circled I can understand that if you save and re-use the form, it will actually keep in mind what was added and remove fromit.

So, the debate is if the form is never saved, there would be no lifetime to start with, hence the data would be discarded, right?

So, no maxing out, for forms that will never be saved?

Cheers

 

[DOSPLUS]

OK, I will see and play a little bit with it. All tables are linked on a network drive, never to be accessed from a connected network.

 

[DOSPLUS]

If you want to update multiple different file types, why involve a middle-man? Just open the file and update it. Adding Access to the middle adds complexity but doesn't otherwise appear to add value.

I don't know what this means. If you are not connected to the network, how are you accessing the files?

The Excel files are given to me via USB stick to be imported into access. The Excel given to me are the main files used to update the tables and get a snapshot of the connected network. There is no middle-man here, simply an app in access that shows the data we need (intranet) to see from the connected network (Internet).

 

[Edgar_]

Attached is a sample of the Tbl_sow table in a pre and post status from an update. Note that the post result would be an excel pulled in and the update would update the table from that excel. All good when fields headers match. What i would like is to pull in any excel file, show the table and the file headers, and then allow the user to choose what field of the Excel is to be matched with the table field. Instead of hard coding things. I will lots of files coming in to update various tables and i do not want to have to alter the file to match headers.

I checked your files. I think I understand what you need. I don't have time left today, but I can give it a try tomorrow. Nothing will be stored in Access, right? you just want to manage your files. Is that correct?

In the example you provided, the two sheets have matching headers, can you describe 3 scenarios of what you need? Like... first I import a source workbook, the form parses its headers, then I import a target workbook and the form parses its headers too... then I choose what ranges from the source workbook go into the target workbook... etc... something like that?

 

[DOSPLUS]

I checked your files. I think I understand what you need. I don't have time left today, but I can give it a try tomorrow. Nothing will be stored in Access, right? you just want to manage your files. Is that correct?

In the example you provided, the two sheets have matching headers, can you describe 3 scenarios of what you need? Like... first I import a source workbook, the form parses its headers, then I import a target workbook and the form parses its headers too... then I choose what ranges from the source workbook go into the target workbook... etc... something like that?

Hi,

Thank for answering.

So, the task at in itself is simple.

I need to import data into an Access table using an Excel file.

The scope is two fold:

1 - Using an Excel file with matching headers (verified header match prior to insert) This part I got it workng
2 - Using an Excel without matching headers. At that point, I need the user to pick what Excel header column goes with the Access table

That's the task. In addition, once it works, I will prompt the user to select what table is to be update with the selected file.

Setup in access is linked tables hosted on a shared drive. I have some problem posting some stuff here as it gives me errors when changing location.

Regards,

 

[DOSPLUS]

That's a different take on the problem and would not require building forms and controls on the fly as you envisioned.

However, I question this as a design option. Importing data is not as simple as matching columns. Data imported this way should be validated as it is imported. Some columns will be required, there may be instances where you need to prevent a file from being imported multiple times, some columns might need specific types of validation. You can, without too much difficulty Pick a spreadsheet and then using a combo, pick a table. Then you have a subform bound to a cross reference table. The first column has a combo that lists column names from the spreadsheet. Then you have a combo that is dependent on the table combo in the parent form that lists the columns of the selected table. It is the subform that lets you match which columns from the spreadsheet go to which columns in the selected table. You can even run a query after the table is selected that populates the subform with all columns that match by name so the user only has to pick the odd columns.

But, what if you don't have a required field, what if the required field is empty in some rows? Where are you really going with this? You need to put this into context so we have a sense of how best to suggest making this mapping table. Once you have a mapping table, then it is pretty simple to build a query. You can even store the query and store its name in the header table of the cross reference. That way, the user only needs to do the mapping once.

Hi,

Lots to take into consideration. But for the data quality, I am not too worried as the excel are produce by a system and not a human (IBM DOORS). The validation should of been done there. But I would verify some column. As for the overwrite and multiple upload, it is not too important because there can only be one row ID that exist in the table, thus the line is overwritten with the excel line if it exists.

You gave me a good idea though, I could populate a table and then simply present them in a continuous subform using the header count.

 

[DOSPLUS]

OK,

I think I got the PRESET (Excel matching table headers) working, attached are two picture, one when matching, or when not.

 

[Edgar_]

OK,

I think I got the PRESET (Excel matching table headers) working, attached are two picture, one when matching, or when not.

Cool, so what is next?

 

[DOSPLUS]

Cool, so what is next?

Well,

The exact thing but using a field mapping technique. You had me think yesterday and i think I will avoid object creator and opt for the setup of a temp table containing some of the information. Then I will pull it in a continuous subform for mapping.

 

[mdlueck]

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.

 

[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"??

 

[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

 

[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.

 

[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.

 

[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.