Solved Creating combo boxes on the fly

[DOSPLUS]

Hi,

Been trying to find some code that allows for the creation of a combo box for each value that an SQL tables fields.

Lets say 10 fields name in the table would yield 10 combo boxes setup one above each other. It is for mapping fields for import.
I have the code to extract the fields names. I can import and all using my current script. But I'd like to be able to remove the hard coded field name and matching.

Any ideas...? would help.

 

[June7]

Most anything can be done with enough code.

Will the number of comboboxes vary?

Controls can be created "on the fly".

Can certainly use VBA to set combobox properties. Code could be executed every time form opens. If you wanted changes to be permanent, form would have to be opened in design view then saved.

 

[DOSPLUS]

Well, I need to create a combo box from vba, not preset. The problem i have is that combo boxes amount will be based on the number of table fields.

So, if you were to open the form in design view, you'd see just the header, because combos are not yet created for that one shot time field mapping.

 

[June7]

Search topic "Access VBA create controls runtime". Start with https://stackoverflow.com/questions/31301070/how-to-create-controls-at-run-time-access-vb

If form is never saved with these created controls, might not hit the 754 control over lifetime limit. Maybe even go as far as having form created from scratch.

 

[MajP]

DO NOT CREATE DYNAMIC CONTROLS!
Access is not designed to do this and you will run into a lot of problems. Eventually this form will lock up after several uses. Instead make invisible some number of comboboxes and then unhide them.

 

[theDBguy]

I am wondering if the Field List Row Source Type is enough to accomplish what you're after.

BTW. Welcome to AWF!

 

[MajP]

can you demo an example of what this would look like?

 

[theDBguy]

can you demo an example of what this would look like?

@MajP Were you asking me? I'm not sure what the OP had in mind, but I was just thinking two combos. The first one has the field list, and the second one gets updated from the first combo to show the records from the selected field.

 

[MajP]

No I was asking the OP, since I do not really get what you are selecting and what you are mapping.

 

[Edgar_]

This is a possibility

Option Compare Database
Option Explicit

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
  
    'give it some name for show
    newForm.Caption = "MyForm"
    newForm.HasModule = True
    newForm.OnClose = "[Event Procedure]"
    newForm.Module.AddFromString _
                                "Private Sub Form_Close()" & vbCrLf & _
                                "    MsgBox ""Handle this somehow""" & vbCrLf & _
                                "End Sub"
  
    'open form
    DoCmd.OpenForm newForm.Name
  
    'you don't have to save it
End Sub

Now, of course, you'd have to adapt it to your particular scenario. I don't recommend that you save the newly created form, instead, it should be created on the fly and discarded the same way to avoid hitting Access' limits.

Is this the best approach? We are not even sure what you want to do. I have other methods for dynamic control creation using the almighty web browser control, but this should suffice... maybe

EDITED to add code to form created

 

[Uncle Gizmo]

As others have said, your requirements are obscured by lack of information.

As others have said, creating objects on the fly is the wrong approach.

I think a multi select listbox that shows the table fields is a better option, as it is unlimited.

In this video at time index 3:30, I demonstrate how to transpose table field names from a horizontal layout into a vertical list, using VBA and a multi select listbox.

The purpose? To transpose data from a spreadsheet structure, to a new table, a table with the data structured to take advantage of MS Access features.

The transpose tool which provides a good demo of using a list box to capture a tables field names and use them in VBA code is available for free. If you want a copy, send me a private message (PM) using this Forums messaging system.

 

[Pat Hartman]

I will remind you of what @MajP was alluding to and that is the HARD limit of controls on a form. Access is very helpful and keeps track of all controls you add to a form whether they are removed or not. When you reach that limit, the form stops working.

You might want to rethink your schema design as well as your application design and then think about your development platform. Access is inappropriate if you actually need to create control objects on the fly.

 

[DOSPLUS]

WOW! Thx to you all for pitching in.

I have to admit that I did not mention lots of info because I was looking at ideas and some brainstorming. What i want to do is as follow:

0 - Combo list for selection of which table to update (around 10 table possibilities)

But, lets pick one...

PART 1
1 - Naviguate to an excel file and load it into a temp table called Tbl_sow_import (Preparation for update or append)
2 - Output a list of columns names for Tbl_sow_import into a form txt field (For user conf.)
3 - Output a list of columns names for Tbl_sow into a form txt field (For user conf.)
4 - Compare both list to each other to validate the qty and column name, and populate a txt.box with MATCH or NO MATCH
5 - Select if you only want to update the table, or update an append.
6 - If there is a match unhide the import button since both are the same
7 - Delete all temp tables once done

OR, on another form

PART 2
1 - Naviguate to an excel file and load it into a temp table called Tbl_sow_import (Preparation for update or append)
2 - Output a list of columns names for Tbl_sow into a form txt field (For user conf.)
3 - Side-by-side, create one combo box per header column found in the excel Tbl_sow column field.
4 - Select if you only want to update the table, or update an append.
5 - Match individually each of the column field from Tbl_sow to the choices taken fromtheExcel headers.
6 - Name each boxes with strCBox1, strCBox2, strCBox3 ... as they are created on the fly. Positionned properly
7 - Use those values to build the SQL query and update the appropriate table, in this case Tbl_sow
8 - Once all fields are matched, unhide the import button.
9 - Delete all temp tables once done

Most of it works so far for part 1 and 2, I am at creating the SQL command. But do not understand why MSAccess would keep any data once done and everything is trashed.

a. Creating a form on the fly without saving is somewhat easy and could work in conjunction with ComboBox temporary creation since creating the combobox is also easy.

Hide and unhide boxes is not interesting to me as the amount of headings will change from different type of updates. I want on the fly by using the count function for the amount of boxes needed.

Thank you so much guys for the input, so far the import in part 1 works as intended, but still need work on part 2.

I placed a representation of the results as attachments

 

[June7]

Could you use the Field List option for Row Source Type?

 

[DOSPLUS]

I do not think so. Once I've parsed the incoming file the value list is the same for all boxes. I create the string for that when grabing initial amount of headings from the excel.

I also have to open the form in design view, minimize it, add the boxes, switch to NormalView and then maximize.

I read somewhere that boxes cannot be added in NormalView so far the test box created fine... I need to work the SQL that will update depending of the boxes value and qty.

 

[MajP]

STOP!. Just hide a set amount of combboxes and if necessary textboxes and be done with it.
One more time. YOU DO NOT WANT TO CREATE NEW CONTROLS.
I 100% can ensure you anything that you want to do and waste your time with dynamic controls can be better faked with hidden controls. At run time you can unhide, move, resize, and reformat.
To show this at the absolute extreme. Here is a form where you can drag and drop 150 different images onto a floor (deck plan). You can right click and drop an image anywhere. It will appear to you and everyone else that those image controls are being created out of thin air, but they are not. There are 150 hidden and stacked controls that are unhidden and positioned at runtime.

Solved - User made command button that can be moved with mouse?

haha! We got plenty of liferafts! Thanks MajP, is working good. Attached is idea of the jobs pane. I think when a new dot is moved to the plan/deck then it would ideally open up a new record/job for this new dot that has been added, this would highlight as a new job in the jobs detail pane...

www.access-programmers.co.uk

 

[DOSPLUS]

This is a possibility

Option Compare Database
Option Explicit

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
 
    'give it some name for show
    newForm.Caption = "MyForm"
    newForm.HasModule = True
    newForm.OnClose = "[Event Procedure]"
    newForm.Module.AddFromString _
                                "Private Sub Form_Close()" & vbCrLf & _
                                "    MsgBox ""Handle this somehow""" & vbCrLf & _
                                "End Sub"
 
    'open form
    DoCmd.OpenForm newForm.Name
 
    'you don't have to save it
End Sub

Now, of course, you'd have to adapt it to your particular scenario. I don't recommend that you save the newly created form, instead, it should be created on the fly and discarded the same way to avoid hitting Access' limits.

Is this the best approach? We are not even sure what you want to do. I have other methods for dynamic control creation using the almighty web browser control, but this should suffice... maybe

EDITED to add code to form created

Thx for taking the time, I will be investiguating the approach.

 

[MajP]

I would think this could be done in a simple continuous subform.
Have a temp table
tblTemp
--fieldToMap
--MapField

when your form opens load tblTemp with the fields to map.
the subform has uneditable fields to map and then a combobox with choices for the map field. In the continuous form you can do an "not in / not exist" query so you can only select values that have not been previously mapped.

 

[DOSPLUS]

I would think this could be done in a simple continuous subform.
Have a temp table
tblTemp
--fieldToMap
--MapField

when your form opens load tblTemp with the fields to map.
the subform has uneditable fields to map and then a combobox with choices for the map field. In the continuous form you can do an "not in / not exist" query so you can only select values that have not been previously mapped.

Yeah, I wanted to play with that too. That could be a good approach too. I am getting in deep of access by investiguating what is actually logged into the app and why , that way I can evaluate the limit problem a bit better.

 

[DOSPLUS]

I am wondering if the Field List Row Source Type is enough to accomplish what you're after.

BTW. Welcome to AWF!

Thx for the warm welcome!