creating a variable for a control and setting a property

Posted on 2013-11-05
Medium Priority
Last Modified: 2013-11-06
I can do this from a module:
Forms![Sales_Contract_Review]![01-TextBox].BackColor = 967423

i am trying to create a loop where i change the 01 above based on my textbox names.

sTextBox = "Forms![Sales_Contract_Review]![" & Mid(Ctl.Name, 1, 2) & "-TextBox]"

Forms![Sales_Contract_Review].Controls("sTextBox").BackColor = 967423

when it hits my code I get "Sales Contract Review can't find the field 'sTextBox' referred to in your expression.

if i display sTextbox it is: "Forms![Sales_Contract_Review]![01-TextBox]."

i assume i am not doing something correct with my .controls

thank you in advance
Question by:BFanguy
  • 2
LVL 61

Accepted Solution

mbizup earned 1000 total points
ID: 39625805
Try this...

sTexbox =  Mid(Ctl.Name, 1, 2) & "-TextBox"
' And drop the quotes around sTextbox in this:
Forms![Sales_Contract_Review].Controls(sTextBox).BackColor = 967423

Open in new window

LVL 61

Expert Comment

ID: 39625812
My above post assumes that somewhere in your unposted code you are looping through form controls... but In a simpler loop, 1 through 10 your code would be something like this:

Dim I as Integer
For I = 1 to 10
   sTexbox =  Format(I, "00") &  "-TextBox"
   Forms![Sales_Contract_Review].Controls(sTextBox).BackColor = 967423
Next I 

Open in new window

LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 1000 total points
ID: 39625825

Dim I as integer
Dim iControlCount as Integer

 iControlCount = 10 ' set tot eh number of textboxes

For I = 1 to  iControlCount 

   sTextBox = Format(I ,"00") & "-TextBox"
   Forms![Sales_Contract_Review].Controls(sTextBox).BackColor = 967423


Open in new window

EDIT: mbizup types faster. Need more caffeine!

Or you could use the Tag property for the controls. It will make it easy to add control and not depend on the name.


    For Each cCnt In Me.Controls
            If Left(cCnt.Tag, 3) = "req" Then
                cCnt.BackColor = 967423
            End If

        End If
    Next cCnt

Open in new window

Change this part Left(cCnt.Tag, 3) = "req" to match whatever value you place in the tag property

Author Closing Comment

ID: 39627273
thanks guys, this is what I needed!

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question