VBA Access - How do I get a loop in VBA that allows me to iterate over control names

Question

I have about 10 text fields in a form that are actually used to display without writing. They are called txt_001_Name , txt_002_Title , etc. What type of loop is used for this.

Which VBA should be used to actually scroll through the text field names? Therefore, if I were debug.print, it would look like this:

 txt_001_Title txt_002_Title txt_003_Title 

This is probably pretty simple - especially since I have to learn!

EDIT: Sorry, I should have described it in more detail.

Due to the above naming convention, I am looking for iteration through these text fields so that I can do something with each. What each of these 10 text fields represents is numeric values, each of which has an SQL statement behind it in the form of onload . I also have another set of ten that contain numerical values ​​that are much more static, and finally ten more that use an expression to simply divide each of the first ten against the relative “second” ten, and the value ends in relative 3. So basically it looks like a dashboard table.

 'first ten' 'second ten' 'resulting ten' --------------------------------------------------- txt_001_value txt_001_calc txt_001_result txt_002_value txt_002_calc txt_002_result 

and etc.

So I really want to use this for the "result" text fields. I want to go through the top ten and do this simple calculation:

  me.txt_001_result = me.txt_001_value / me.txt_001_calc 

All naming conventions “match”, so I can manually print 10 lines above for this, but I'm sure there is a better way (going around this loop), and I should probably study it.

Answer 1

You can specify the names of the text field controls using a simple procedure, for example:

 Public Sub TextBoxNames(ByRef pfrm As Form) Dim ctl As Control For Each ctl In pfrm.Controls If ctl.ControlType = acTextBox Then Debug.Print ctl.Name End If Next ctl Set ctl = Nothing End Sub 

You can call it from the Load event form:

 Private Sub Form_Load() TextBoxNames Me End Sub 

However, I do not understand what you are trying to accomplish. I understand that you want to do something with ctl.Name, except Debug.Print , but I do not know what it is.

Instead of calculating the result for me.txt_001_result and then assigning this value to the text field, consider setting the control source for txt_001_result to txt_001_value / txt_001_calc and let Access enter the correct value in txt_001_result for you.

In response to your comments, I suggest this procedure as a starting point for you:

 Public Sub MyTextBoxValues() Const cintLastTextBoxNum As Integer = 10 Dim i As Integer Dim strValueControl As String Dim strCalcControl As String Dim strResultControl As String Dim strPrefix As String For i = 1 To cintLastTextBoxNum strPrefix = "txt_" & Format(i, "000") 'txt_001_value txt_001_calc txt_001_result ' strValueControl = strPrefix & "_value" strCalcControl = strPrefix & "_calc" strResultControl = strPrefix & "_result" 'me.txt_001_result = me.txt_001_value / me.txt_001_calc ' 'Debug.Print strResultControl, strValueControl, strCalcControl ' Me.Controls(strResultControl) = Me.Controls(strValueControl) / _ Me.Controls(strCalcControl) Next i End Sub 

Answer 2

I prefer to use FOR EACH to iterate through a collection of controls that contain text fields (either the form itself or the control panel)

 dim myBox as Textbox For each myBox in myForm myBox.Text = "hello" Next 

Also means that you can create custom groups (by putting them all in the same container). Please note: if you have other controls, you may need typecheck ( IF TYPEOF(myBox) = "TextBox" THEN ... )

You can also do this:

 dim i as integer For i = 1 to 10 myForm.Controls("txt_00" & i & "_Title").Text = "hello" Next i 

I definitely prefer "For Everyone."

Answer 3

I cannot fully understand why you need to do what you are doing, but I had such forms when I had an unrelated form that I wanted to display on an arbitrary number of fields, so I can see it, If you are going to collect elements control only in the form of OnOpen, this is great. But if you do this in an OnCurrent related form or several times in an unrelated form, you can consider a long post on using custom collections to manage groups of controls .