By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,478 Members | 1,665 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,478 IT Pros & Developers. It's quick & easy.

Iterate through form controls

P: 62
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!
Jan 28 '08 #1
Share this Question
Share on Google+
4 Replies


jaxjagfan
Expert 100+
P: 254
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:

Expand|Select|Wrap|Line Numbers
  1. strSQL = "Select tblAccounts.* from tblAccounts"
  2. Select Case Me.opt1
  3. Case 1 'All Records
  4. strCrit = ";"
  5. Case 2 'State
  6. strCrit = " WHERE State Like '*" & Me.txtSearch & "*';" 
  7. Case 3
  8. Case 4
  9. End Select
  10. Me.RecordSource = strSQL & strCrit 
  11.  
Jan 28 '08 #2

P: 62
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:

Expand|Select|Wrap|Line Numbers
  1. strSQL = "Select tblAccounts.* from tblAccounts"
  2. Select Case Me.opt1
  3. Case 1 'All Records
  4. strCrit = ";"
  5. Case 2 'State
  6. strCrit = " WHERE State Like '*" & Me.txtSearch & "*';" 
  7. Case 3
  8. Case 4
  9. End Select
  10. Me.RecordSource = strSQL & strCrit 
  11.  
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.

Expand|Select|Wrap|Line Numbers
  1. Function CreateComboBoxes()
  2.  
  3. Dim dbs As DAO.Database
  4. Dim rstBaseNumber As DAO.Recordset
  5. Dim rstSearchField As DAO.Recordset
  6. Dim rstSubTable As DAO.Recordset
  7. Dim strBaseNumber As String
  8. Dim strSearchField As String
  9. Dim strSearchFieldType As String
  10. Dim strSubTable As String
  11. Dim strTableName As String
  12.  
  13.     Dim intDataX As Integer, intDataY As Integer
  14.     Dim intLabelX As Integer, intLabelY As Integer
  15.     intLabelX = 100
  16.     intLabelY = 100
  17.     intDataX = 2000
  18.     intDataY = 100
  19.  
  20. Dim intItemNumber As Integer
  21. intItemNumber = 0
  22.  
  23. Set dbs = CurrentDb
  24. Set frm = CreateForm
  25.  
  26. strTableName = "SELECT tblFormBuild_Source.BaseNumber FROM tblFormBuild_Source " & _
  27.     "GROUP BY tblFormBuild_Source.BaseNumber;"
  28. Set rstBaseNumber = dbs.OpenRecordset(strTableName)
  29.  
  30. Do Until rstBaseNumber.EOF
  31.     strBaseNumber = rstBaseNumber![BaseNumber]
  32.     'MsgBox ("The base number is " & strBaseNumber)
  33.  
  34.     DoCmd.SetWarnings False
  35.     DoCmd.RunSQL "SELECT tblFormBuild_Source.BaseNumber, tblFormBuild_Source.SearchField, tblFormBuild_Source.SearchFieldType, tblFormBuild_Source.SubTable " & _
  36.         "INTO tblTemp FROM tblFormBuild_Source " & _
  37.         "WHERE((" & _
  38.         "(tblFormBuild_Source.BaseNumber)=" & """" & strBaseNumber & """" & "));"
  39.     DoCmd.SetWarnings True
  40.  
  41.     Set rstSearchField = dbs.OpenRecordset("tblTemp")
  42.     Do Until rstSearchField.EOF
  43.         'create a string for each column of the record
  44.         strSearchField = rstSearchField![SearchField]
  45.         strSearchFieldType = rstSearchField![SearchFieldType]
  46.  
  47.         If Not (IsNull(rstSearchField![SubTable])) Then
  48.             strSubTable = rstSearchField![SubTable]
  49.             'MsgBox ("the subtable for this record is " & strSubTable)
  50.         End If
  51.  
  52.  
  53. 'INSERT SEARCH CRITERIA COMBO BOXES
  54.         Set ctlText = CreateControl(frm.Name, acComboBox, , "", "", _
  55.             intDataX, intDataY)
  56.         Set ctlLabel = CreateControl(frm.Name, acLabel, , _
  57.             ctlText.Name, strBaseNumber & " " & strSearchField, intLabelX, intLabelY)
  58.  
  59.         intLabelY = intLabelY + 300
  60.         intDataY = intDataY + 300
  61.         intNumber = intNumber + 2
  62. 'END BUILD FORM CODE
  63.  
  64.         rstSearchField.MoveNext
  65.     Loop
  66.     Set rstSearchField = Nothing
  67.  
  68.     rstBaseNumber.MoveNext
  69. Loop
  70. Set rstBaseNumber = Nothing
  71. DoCmd.Restore
  72.  
  73.  
  74. End Function
  75.  
This code assumes the user has a table "tblFormBuild_Source" 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";"Combo2";"Combo4"... and it would be easier to make the associated queries if I could use something a little more creative (something really clever like "SearchFieldType" & "BaseNumber" & "SearchField")
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?
Jan 28 '08 #3

P: 62
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(intNumber).Name = strBaseNumber & strSearchField

and iterating intNumber by 2 every time the loop came around.
Jan 29 '08 #4

NeoPa
Expert Mod 15k+
P: 31,709
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 :
Expand|Select|Wrap|Line Numbers
  1. [Forms]![Form1].Controls("Combo" & intNumber).Name = strBaseNumber & strSearchField
Feb 3 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.