"For Each Control" statement skipping controls

[Radioactiveduck]

Alright, so I have two forms in my access database, WIP and Exports.

The WIP form has a button cmd_ExcelExport and a textbox txt_ColNum, which is validated to be a number greater than zero. When the user pushes the cmd_ExcelExport button, I intend for Access to open the Exports form and a number of combo boxes to it equal to the number in txt_ColNum. It's also supposed to delete any combo boxes already in the Exports form before adding the new ones. This is the code for the button in the WIP form:

Private Sub cmd_Export_Click()

'--------------------------------------------------------------
'This code opens the Exports form and adds a new combo box
'to be used for selecting a column of data from the customers
'database to be used for selecting what data to use in a client
'report.
'--------------------------------------------------------------


'--------------------------------------------------------------
'Declaring variables
'Declare and assign the database to be used
    Dim db As DAO.Database
    Set db = CurrentDb
'
'Declare integer and string variables
    Dim ColNum As Integer, i As Integer, xLeft As Integer
    Dim strFieldName As String, strVarString As String
'
'Declare and set other variables
    Dim ctrl As Control
    Dim xField As DAO.Field
    Dim xTable As DAO.TableDef
    Set xTable = db.TableDefs("Customers")
'--------------------------------------------------------------


'--------------------------------------------------------------
'Open the Exports form, Delete all combobox controls already in
'the Exports Form and add a combobox named "cmbCol1" to the form
    DoCmd.OpenForm "Exports", acDesign, , , acFormEdit, acWindowNormal
'
    For Each ctrl In Forms![Exports]
        strVarString = ctrl.Properties("Name")
        strFieldName = "Deleting " & strVarString
        MsgBox strFieldName
        If Left(strVarString, 6) = "cmbCol" Then DeleteControl "Exports", strVarString
    Next ctrl
'
    For i = 1 To txt_ColNum
        strVarString = "cmbCol" & i
        xLeft = (150 * (i + 1)) + (1000 * (i - 1))
        Set ctrl = CreateControl("Exports", acComboBox, acDetail, , "", xLeft, 700, 1000, 300)
        ctrl.Properties("Name") = strVarString
    Next i
'
'Switch the Exports form to Normal view so items can be added to
'the combo box
    DoCmd.OpenForm "Exports", acNormal, , , acFormEdit, acWindowNormal
'--------------------------------------------------------------


'--------------------------------------------------------------
'Set the Row Source Type property of each combo box
    For i = 1 To txt_ColNum
        strVarString = "cmbCol" & i
        Forms![Exports].Controls(strVarString).RowSourceType = "Value List"
'
'For every column in the charter base (also known as a "Field")
'this code adds the name of that column to each combo box being
'added to the Exports Form
        For Each xField In xTable.Fields
            strFieldName = xField.Name
            With Forms![Exports].Controls(strVarString)
                .AddItem strFieldName
            End With
        Next xField
    Next i
'--------------------------------------------------------------


End Sub

I placed the message box on line 37 to diagnose the problem I'm having. For some reason, whenever this code runs, if there are already combo boxes in the Exports form, it skips some of them when running through the For Each statement. For instance, if there are five combo boxes in the Exports sheet, it will always skip cmbCol2 and cmbCol4. This causes it to throw an error whenever it's trying to create new combo boxes that are supposed to use the same name.

Thoughts?

 

[pbaldy]

You can keep the formatting of your code by using the code tags (the "#" icon in the advanced reply screen).

Avoiding for a moment your actual question, what is the overall goal of this process? It is very unusual to be creating and deleting controls all the time. For one thing, you run the risk of running into the limit on the number of controls (deleted controls still count). I've seen solutions where you put the maximum number of combos on the form, and make them visible/not visible as appropriate. Depending on what you're doing, perhaps a multiselect listbox to allow users to select fields?

 

[Radioactiveduck]

Ah, I wasn't aware that deleting controls wouldn't avoid a control limit. I may have to find another way to write this then.

But essentially, the goal of this code is to allow the user to conveniently export any variable combination of fields from their "Customers" table to either Microsoft Excel or Word. So they need to be able to quickly select the fields they want in any combination. Furthermore, the user I'm writing this for wants the code to export to either Word or Excel in their custom format, so once the export code finishes in Access, I'm going to have it format and manipulate the data in Excel or Word to match their format.

EDIT: Also, I've decided to have the sub close the "Exports" form without saving once the export to a new document is complete. That should prevent it from reaching any control limit, correct? Since I know the number of fields in the Customers table, and I know roughly the maximum number of fields the user will likely use, I'm certain they won't be able to overwhelm any control limit in a single instance of using this code.

 

[pbaldy]

Have you considered a multiselect listbox with all the fields? That would enable the user to choose whatever fields they wanted. To me that would be cleaner and easier for the user anyway.

 

[Radioactiveduck]

I have, and I may use it if the combo boxes prove to be too difficult to work with, but there are two virtues of the combo boxes that I'd prefer to keep:

1: The user can view all of the columns they've already selected without having to scroll through the list to see what they've highlighted.

2: The order in which the columns will appear in excel can be determined by the order in which the user selects the columns in the combo boxes, whereas with a multiselect list box, there isn't a convenient way to do this.

There are a few other subtleties between the two methods I'd prefer not to change, but the bottom line is that for the time being, I'd like to stick with the combo boxes. That said, I doubt there is a correlation between using the combo boxes and my VBA skipping over some of the controls and not others. Any thoughts on that?

 

[SOS]

Pardon the intrusion, but I just wanted to clarify why you (Radioactiveduck) were having issues with the For Each ctl code.

When you are deleting you have to step backwards because if you don't, you delete anything and let's say you delete control #3 then Control #4 then becomes control #3 and since you've already done #3 it just keeps going and then #5 gets deleted and then #6 becomes #5 and so the code is already past 5, etc.

So you have to use the control count and then step backwards (can't remember the exact syntax at the moment). But that is why you were having issues.

 

[pbaldy]

As SOS said, your problem dealt with the control index, and the solution is to go backwards. That said, I still wouldn't use that method. If you want to stay with the combos, I'd go the route of having the maximum quantity hidden.

 

[c_smithwick]

Forget the deleting and re-adding. You're making life too hard. Find the maximum number of combos you will concievably need, name them "cmbCol1", "cmbCol2", etc... then just reiterate through the control array each time, hiding/showing the number needed and programatically changing the list items.

Dim ctl as Control
Dim i as Integer
 
For each ctl in Forms("Exports").Controls
    if Mid(ctl.name,1, 6) = "cmbCol" then
        ctl.Visible = False
        For i = ctl.ListCount -1 to 0 Step -1
            ctl.RemoveItem i
        Next
    end if
Next
For i = 0 to xTable.Fields.Count -1    
    Set ctl = Forms("Exports").Controls("cmbCol" & i + 1)
    For each xFields i xTable.Fields
        ctl.AddItem xFields.Name
    Next
    ctl.Visible = True
    Set ctl = Nothing
Next

 

[Radioactiveduck]

Ah, I see now. That is, in regard to the control index. I've changed the form to use a set number of combo boxes, but I'm sure it'll come in handy in the future anyway. Thanks.