Iterate through form controls

[nickvans]

Hello Access Gods...

I am writing a bit of VBA code with the aim of allowing a user to easily add fields to a search form. I have a table with Category / Control_Name columns, which the VBA goes through item by item and creates a control for each one. I am able to create the controls just fine, but would like to re-name them to something more user-friendly than Combo0, Combo2, Combo4 etc.

Is there an "object name" property that can be set when creating a control? (As in, when I call the CreateControl function?)

If not, can someone recommend a way to reference the equivalent of:

[Forms]![Form1]!["Combo" & variable_name].Name = "strNewName " ?

This would be enough for me because when it creates the controls it will always be an even number starting with 0, so I can simply iterate by 2 within a loop to re-name each control.

Thanks in advance!

[jaxjagfan]

Hello Access Gods...

I am writing a bit of VBA code with the aim of allowing a user to easily add fields to a search form. I have a table with Category / Control_Name columns, which the VBA goes through item by item and creates a control for each one. I am able to create the controls just fine, but would like to re-name them to something more user-friendly than Combo0, Combo2, Combo4 etc.

Is there an "object name" property that can be set when creating a control? (As in, when I call the CreateControl function?)

If not, can someone recommend a way to reference the equivalent of:

[Forms]![Form1]!["Combo" & variable_name].Name = "strNewName " ?

This would be enough for me because when it creates the controls it will always be an even number starting with 0, so I can simply iterate by 2 within a loop to re-name each control.

Thanks in advance!

Do you want to Create Multiple controls or just chnage the function of a couple on the fly. I have an option block where users pick the type of search (I.E. State, AccountNumber, PhoneNumber, etc. The form has the options, 1 textbox, 1 search button and a reset button. It displays all records by default.
The textbox allows for partial entries.

Since the data is the same I just change the Where clause depending on which field is being searched. Each time criteria is entered and search is clicked I go thru something like this:

strSQL = "Select tblAccounts.* from tblAccounts"
Select Case Me.opt1
Case 1 'All Records
strCrit = ";"
Case 2 'State
strCrit = " WHERE State Like '*" & Me.txtSearch & "*';" 
Case 3
Case 4
End Select
Me.RecordSource = strSQL & strCrit

[nickvans]

Do you want to Create Multiple controls or just chnage the function of a couple on the fly. I have an option block where users pick the type of search (I.E. State, AccountNumber, PhoneNumber, etc. The form has the options, 1 textbox, 1 search button and a reset button. It displays all records by default.
The textbox allows for partial entries.

Since the data is the same I just change the Where clause depending on which field is being searched. Each time criteria is entered and search is clicked I go thru something like this:

strSQL = "Select tblAccounts.* from tblAccounts"
Select Case Me.opt1
Case 1 'All Records
strCrit = ";"
Case 2 'State
strCrit = " WHERE State Like '*" & Me.txtSearch & "*';" 
Case 3
Case 4
End Select
Me.RecordSource = strSQL & strCrit

Hey Jax, thanks for getting back to me.

Well, I'm doing something like that as well, but for my purposes its not exactly what I'm looking for. I roughly 30 combo boxes that are created from my table, each of which I would like to rename.

Below is the code I used to create the combo boxes in the form.

Function CreateComboBoxes()

Dim dbs As DAO.Database
Dim rstBaseNumber As DAO.Recordset
Dim rstSearchField As DAO.Recordset
Dim rstSubTable As DAO.Recordset
Dim strBaseNumber As String
Dim strSearchField As String
Dim strSearchFieldType As String
Dim strSubTable As String
Dim strTableName As String

    Dim intDataX As Integer, intDataY As Integer
    Dim intLabelX As Integer, intLabelY As Integer
    intLabelX = 100
    intLabelY = 100
    intDataX = 2000
    intDataY = 100

Dim intItemNumber As Integer
intItemNumber = 0

Set dbs = CurrentDb
Set frm = CreateForm

strTableName = "SELECT tblFormBuild_Source.BaseNumber FROM tblFormBuild_Source " & _
    "GROUP BY tblFormBuild_Source.BaseNumber;"
Set rstBaseNumber = dbs.OpenRecordset(strTableName)

Do Until rstBaseNumber.EOF
    strBaseNumber = rstBaseNumber![BaseNumber]
    'MsgBox ("The base number is " & strBaseNumber)

    DoCmd.SetWarnings False
    DoCmd.RunSQL "SELECT tblFormBuild_Source.BaseNumber, tblFormBuild_Source.SearchField, tblFormBuild_Source.SearchFieldType, tblFormBuild_Source.SubTable " & _
        "INTO tblTemp FROM tblFormBuild_Source " & _
        "WHERE((" & _
        "(tblFormBuild_Source.BaseNumber)=" & """" & strBaseNumber & """" & "));"
    DoCmd.SetWarnings True
    
    Set rstSearchField = dbs.OpenRecordset("tblTemp")
    Do Until rstSearchField.EOF
        'create a string for each column of the record
        strSearchField = rstSearchField![SearchField]
        strSearchFieldType = rstSearchField![SearchFieldType]
        
        If Not (IsNull(rstSearchField![SubTable])) Then
            strSubTable = rstSearchField![SubTable]
            'MsgBox ("the subtable for this record is " & strSubTable)
        End If
        
        
'INSERT SEARCH CRITERIA COMBO BOXES
        Set ctlText = CreateControl(frm.Name, acComboBox, , "", "", _
            intDataX, intDataY)
        Set ctlLabel = CreateControl(frm.Name, acLabel, , _
            ctlText.Name, strBaseNumber & " " & strSearchField, intLabelX, intLabelY)
 
        intLabelY = intLabelY + 300
        intDataY = intDataY + 300
        intNumber = intNumber + 2
'END BUILD FORM CODE

        rstSearchField.MoveNext
    Loop
    Set rstSearchField = Nothing
    
    rstBaseNumber.MoveNext
Loop
Set rstBaseNumber = Nothing
DoCmd.Restore


End Function

This code assumes the user has a table "tblFormBuild_S ource" with columns: BaseNumber / SearchField / SearchFieldType / SubTable

BaseNumber will be my categories
SearchField will be the name of each field
SearchFieldType I intended to use for re-naming controls
SubTable will be used to link 1-many tables to the primary tables

When the code runs it creates "Form1" with all the combo boxes I want with the labels I want.

My problem is the form ends up with a bunch of combo boxes named "Combo0";"Combo 2";"Combo4". .. and it would be easier to make the associated queries if I could use something a little more creative (something really clever like "SearchFieldTyp e" & "BaseNumber " & "SearchFiel d")
I would like to put a bit of code (either as part of this block, or as a separate function) that goes through and renames each "Combo" & "#" with a string of my choosing.

What'da think? Should I just suck it up and deal with them as Combo0 Combo2 etc?

[nickvans]

Update:

Well, after scouring the internet and the Access help files, I was able to circumvent my problem by using this line

[Forms]![Form1].Controls(intNu mber).Name = strBaseNumber & strSearchField

and iterating intNumber by 2 every time the loop came around.

[NeoPa]

A good solution Nick (I was going to suggest it) but now I think about it, it may be safer to use a variation of the first method as this one (your latest) relies on the order the controls were added to the form :

[Forms]![Form1].Controls("Combo" & intNumber).Name = strBaseNumber & strSearchField