Syntax Problem?

[TheSearcher]

'***This works
Me.Controls("Date1").BackColor = "8454143"

'*** When I replace the hard coded field name with a variable -
FieldName = "Date1"
Me.Controls(FieldName).BackColor = "8454143"

'*** I get an "Object doesn't support this property or method" error.
Does anyone know why?

Thanks in advance,
TS

 

[Simon_MT]

The Colour is an integer and doesn't need the quotes.

eg. .[Orig Sell Net].BackColor = 8388608

Simon

 

[TheSearcher]

I get the same error if I remove the quotes.

 

[Simon_MT]

try:

With CodeContextObject
       .[Date1].BackColor = 8454143
End With

Simon

 

[EternalMyrtle]

In VBA you can simply use Me.ControlName for most things. I guess you can use Me.Controls("ControlName") but why?

If you wanted to declare a variable you would do so as you would with regular vb using a Dim declaration. As far as I know, you cannot declare a field name in VBA the way you are trying to do it. I have not seen that done.
That is why it is not working.

 

[TheSearcher]

Simon - As with my code, your code works until I replace the hard coded field name (Date1) with a variable (FieldName). But with your code my error is slightly different. Now the error is: "Microsoft Acces can't find the field '|1' referred to in your expression.

 

[TheSearcher]

EternalMyrtle - I did declare my variable with a dim statement.

 

[bob fitz]

EternalMyrtle - I did declare my variable with a dim statement.

What did you declare it as.

 

[TheSearcher]

Hi Bob - I declared it as a string.

 

[EternalMyrtle]

EternalMyrtle - I did declare my variable with a dim statement.

Ok, well you didn't show it. You showed: "FieldName = "Date1""

Honestly, I don't know how or why you would declare a control name in code anyway. You make the control on the form and call it something and then use code to do things with it. I have never heard of anyone setting a control name this way.

And setting a field name this way would be even odder since fields are found in tables and you don't normally apply code to tables.

 

[Simon_MT]

Please show us exactly what you are doing otherwise we go around in circles. My code works but we need to apply it to your instance.

Simon

 

[jdraw]

I've seen controls use a variable name when set up in advance then done like this

Dim Ctrl As Control
Dim CtrlName As String
Dim Counter As Long

For Counter = 1 To 10
    CtrlName = "P" & Counter
    Set Ctrl = Me.Controls(CtrlName)

 

[EternalMyrtle]

Unless perhaps you were executing a query to make a new table or add data into new fields in an existing table. That would be one reason you might do this.

But I cannot see doing this just to change the backcolor on a control...

 

[EternalMyrtle]

I've seen controls use a variable name when set up in advance then done like this

Yes, I agree that there are valid reasons but not to change the backcolor!!

 

[bob fitz]

Hi Bob - I declared it as a string.

Perhaps it should be declared as a control

 

[TheSearcher]

Simon - Thanks for taking time on this.
I have a form with 10 text boxes which hold dates. These dates can be anything based on a date range that the user chooses If the date is a non-workday (Saturday or Sunday or Holiday) I want to change the backcolor of the text box. So, I want to put some code in the on_open event to handle this. I have done this many times in the past but never did it using a variable for the name of the control.
If:
Me.Controls("Date1").BackColor = "8454143" works
then:
dim FieldName as string
FieldName = "Date1"
Me.Controls(FieldName).BackColor = "8454143" should work also, right?

 

[TheSearcher]

Bob - If I declared it as a control I would get a type mismatch. Remember the control already exists. I am not creating a new one. I'm only changing its name by using a variable.

 

[TheSearcher]

EternalMyrtle - To respond to your inquiry of 3:19:

I cannot refer to the control the way you suggest because coding:
Me.FieldName.BackColor = 8454143
would result in a "Method or data member not found" error since FieldName is a variable.

 

[EternalMyrtle]

Ok, did you try:

Me.ControlName.Name=

with whatever you declared the name to be after the = sign. Then try referring to that name and see if that works.

Still not sure why you are doing this though

 

[TheSearcher]

EternalMyrtle - How would Access know to which control I am assigning a name?