Coding Issue

[Pierre]

Morning and Happy New Year all.
I am having an issue with a code I'm trying to use to change the back color
of some text boxes I'm using on my form. The actual issue is not that the
code is not working. It’s just only working with one text box. I would like
to know how I would fix this code to change a group of numbers and not a
series of numbers.
i.e. "220, 222, 224, 226" - AND NOT - "220 To 226"
Now, when I try and enter it in example A it does not allow the comma.
Example B gives me an error because there are no odd numbers. I have tried
changing the numbers to odd numbers with no avail.
Any and All help in this matter is greatly appreciated.
Complete code below:

Private Sub Form_Current ()

Dim IngColor As Long
Dim IntVal As Integer

If InStr (Me.[Overall Classification], “SAMâ€) = 1 Then
IngColor = 255
ElseIf InStr(Me.[Overall Classification], “TSâ€) = 1 Then
IngColor = 65535
Else
IngColor = 0
End If

For IntVal = 220 To 226

Me.Controls(“Text†& IntVal).BackColor = IngColor
Me.Controls(“Text’ & IntVal).Value = Me.[Overall Classification]
Next
End Sub

 

[George Nicholson]

Not 100% sure of what you are asking, but:

For intVal = 220 To 226
Select Case intVal
Case 220, 222, 224, 226
Me.Controls("Text" & IntVal).BackColor = IngColor
Me.Controls("Text" & IntVal).Value = Me.[Overall
Classification]
Case Else
'Do Nothing? Do Something else?
End Select
Next intVal

 

[Baz]

For IntVal = 220 To 226 Step 2

 

[Pierre]

Baz that was very helpful and worked like a cherm.

I have another code that was working at one point. But stop working after I
was trying to fix the one I just spoke about. Is it possible to view this
and inform me to what I did (changed) wrong. It is suppose to change as text
is entered or based on the text in the box. Similar to the others, excite
this is the box where the initial Classification is being entered. Hope that
made sense. Here is the Code:

Private Sub Overall_Classification_AfterUpdate()

If InStr(Me.[Overall Classification], “SECRETâ€) = 1 Then
IngColor = 255
ElseIf InStr (Me.[Overall Classification], “TS†= 1 Then
IngColor = 65535
Else
IngColor = 0
End If

For InVal = 220 To 240 Step 2
Me.Controls(“Text†& IntVal).BackColor = IngColor
Me.Controls(“Text†& IntVal).Value = Me.[Overall Classification]
Next

End Sub

 

[John Smith]

The easy answer would be to rename your text boxes so that they have
consecutive numbers but if you want to keep them as even numbers for some
other reason then:

For IntVal = 220 To 226 Step 2
Me.Controls(“Text†& IntVal).BackColor = IngColor
Me.Controls(“Text’ & IntVal).Value = Me.[Overall Classification]
Next IntVal

Should do what you require.

John
##################################
Don't Print - Save trees

 

[Baz]

If I understand you correctly you want the code to be executed while someone
is still typing in the box, is that right?

As you have discovered, the AfterUpdate event only executes after the typing
is finished. To run the code as each letter is typed, the Change event or
the KeyDown event of [Overall Classification] is probably the best place to
put it. Make sure you refer explicity to the Text property, thus:

If InStr(Me.[Overall Classification].Text, "SECRET") = 1 Then

 

[Pierre]

It does not have to change while the information is being type. But what I
was trying to say is that its not changing at all. It remains with a white
back color regardless of what I put in. All others are changing based on the
set WORDS entered in the [Overall Classification] field. But the [Overall
Classification] entry field is not changing as it was when I first started.
I made some change to try to get the other fields to work. And really can't
figure out what I did to make it stop working. Hope that cleared my intent
up better.
--
Work is sometimes hard....but someone has to do it.

If I understand you correctly you want the code to be executed while someone
is still typing in the box, is that right?

As you have discovered, the AfterUpdate event only executes after the typing
is finished. To run the code as each letter is typed, the Change event or
the KeyDown event of [Overall Classification] is probably the best place to
put it. Make sure you refer explicity to the Text property, thus:

If InStr(Me.[Overall Classification].Text, "SECRET") = 1 Then

Baz that was very helpful and worked like a cherm.

I have another code that was working at one point. But stop working after
I
was trying to fix the one I just spoke about. Is it possible to view this
and inform me to what I did (changed) wrong. It is suppose to change as
text
is entered or based on the text in the box. Similar to the others, excite
this is the box where the initial Classification is being entered. Hope
that
made sense. Here is the Code:

Private Sub Overall_Classification_AfterUpdate()

If InStr(Me.[Overall Classification], "SECRET") = 1 Then
IngColor = 255
ElseIf InStr (Me.[Overall Classification], "TS" = 1 Then
IngColor = 65535
Else
IngColor = 0
End If

For InVal = 220 To 240 Step 2
Me.Controls("Text" & IntVal).BackColor = IngColor
Me.Controls("Text" & IntVal).Value = Me.[Overall Classification]
Next

End Sub

 

[Baz]

Yep, gotcha.

The problem is that there is nothing in the code to change the background
colour of [Overall Classification]. You must have accidentally deleted it
or overtyped it. Try this:

Private Sub Overall_Classification_AfterUpdate()

If InStr(Me.[Overall Classification], "SECRET") = 1 Then
IngColor = 255
ElseIf InStr (Me.[Overall Classification], "TS" = 1 Then
IngColor = 65535
Else
IngColor = 0
End If

For InVal = 220 To 240 Step 2
Me.Controls("Text" & IntVal).BackColor = IngColor
Me.Controls("Text" & IntVal).Value = Me.[Overall Classification]
Next

Me.[Overall Classification].BackColor = IngColor

End Sub

It does not have to change while the information is being type. But what
I
was trying to say is that its not changing at all. It remains with a
white
back color regardless of what I put in. All others are changing based on
the
set WORDS entered in the [Overall Classification] field. But the [Overall
Classification] entry field is not changing as it was when I first
started.
I made some change to try to get the other fields to work. And really
can't
figure out what I did to make it stop working. Hope that cleared my
intent
up better.
--
Work is sometimes hard....but someone has to do it.

If I understand you correctly you want the code to be executed while
someone
is still typing in the box, is that right?

As you have discovered, the AfterUpdate event only executes after the
typing
is finished. To run the code as each letter is typed, the Change event
or
the KeyDown event of [Overall Classification] is probably the best place
to
put it. Make sure you refer explicity to the Text property, thus:

If InStr(Me.[Overall Classification].Text, "SECRET") = 1 Then

Baz that was very helpful and worked like a cherm.

I have another code that was working at one point. But stop working
after
I
was trying to fix the one I just spoke about. Is it possible to view
this
and inform me to what I did (changed) wrong. It is suppose to change
as
text
is entered or based on the text in the box. Similar to the others,
excite
this is the box where the initial Classification is being entered.
Hope
that
made sense. Here is the Code:

Private Sub Overall_Classification_AfterUpdate()

If InStr(Me.[Overall Classification], "SECRET") = 1 Then
IngColor = 255
ElseIf InStr (Me.[Overall Classification], "TS" = 1 Then
IngColor = 65535
Else
IngColor = 0
End If

For InVal = 220 To 240 Step 2
Me.Controls("Text" & IntVal).BackColor = IngColor
Me.Controls("Text" & IntVal).Value = Me.[Overall Classification]
Next

End Sub

 

[Pierre]

Ty, that was it and for some reason I didn't see it. I remember why I ended
up trying to change it. Once I change to a new record, that field does not
update because the code is entered in the AFTERUPDATE property. So if I
enter Secret it will remain RED for any record I go to until I retype the
classification. (only the Overall Classification field box has this issue)
Then its a big circle if you see where I'm going with this now. So my
question now is where should I input or modify this code so that it updates
the back color based on the text displayed or entered.
--
Work is sometimes hard....but someone has to do it.

Yep, gotcha.

The problem is that there is nothing in the code to change the background
colour of [Overall Classification]. You must have accidentally deleted it
or overtyped it. Try this:

Private Sub Overall_Classification_AfterUpdate()

If InStr(Me.[Overall Classification], "SECRET") = 1 Then
IngColor = 255
ElseIf InStr (Me.[Overall Classification], "TS" = 1 Then
IngColor = 65535
Else
IngColor = 0
End If

For InVal = 220 To 240 Step 2
Me.Controls("Text" & IntVal).BackColor = IngColor
Me.Controls("Text" & IntVal).Value = Me.[Overall Classification]
Next

Me.[Overall Classification].BackColor = IngColor

End Sub

It does not have to change while the information is being type. But what
I
was trying to say is that its not changing at all. It remains with a
white
back color regardless of what I put in. All others are changing based on
the
set WORDS entered in the [Overall Classification] field. But the [Overall
Classification] entry field is not changing as it was when I first
started.
I made some change to try to get the other fields to work. And really
can't
figure out what I did to make it stop working. Hope that cleared my
intent
up better.
--
Work is sometimes hard....but someone has to do it.

If I understand you correctly you want the code to be executed while
someone
is still typing in the box, is that right?

As you have discovered, the AfterUpdate event only executes after the
typing
is finished. To run the code as each letter is typed, the Change event
or
the KeyDown event of [Overall Classification] is probably the best place
to
put it. Make sure you refer explicity to the Text property, thus:

If InStr(Me.[Overall Classification].Text, "SECRET") = 1 Then

Baz that was very helpful and worked like a cherm.

I have another code that was working at one point. But stop working
after
I
was trying to fix the one I just spoke about. Is it possible to view
this
and inform me to what I did (changed) wrong. It is suppose to change
as
text
is entered or based on the text in the box. Similar to the others,
excite
this is the box where the initial Classification is being entered.
Hope
that
made sense. Here is the Code:

Private Sub Overall_Classification_AfterUpdate()

If InStr(Me.[Overall Classification], "SECRET") = 1 Then
IngColor = 255
ElseIf InStr (Me.[Overall Classification], "TS" = 1 Then
IngColor = 65535
Else
IngColor = 0
End If

For InVal = 220 To 240 Step 2
Me.Controls("Text" & IntVal).BackColor = IngColor
Me.Controls("Text" & IntVal).Value = Me.[Overall Classification]
Next

End Sub

 

[Baz]

To do it based on the value already in the record you need to code the
form's Current event.

Ty, that was it and for some reason I didn't see it. I remember why I
ended
up trying to change it. Once I change to a new record, that field does
not
update because the code is entered in the AFTERUPDATE property. So if I
enter Secret it will remain RED for any record I go to until I retype the
classification. (only the Overall Classification field box has this issue)
Then its a big circle if you see where I'm going with this now. So my
question now is where should I input or modify this code so that it
updates
the back color based on the text displayed or entered.
--
Work is sometimes hard....but someone has to do it.

Yep, gotcha.

The problem is that there is nothing in the code to change the background
colour of [Overall Classification]. You must have accidentally deleted
it
or overtyped it. Try this:

Private Sub Overall_Classification_AfterUpdate()

If InStr(Me.[Overall Classification], "SECRET") = 1 Then
IngColor = 255
ElseIf InStr (Me.[Overall Classification], "TS" = 1 Then
IngColor = 65535
Else
IngColor = 0
End If

For InVal = 220 To 240 Step 2
Me.Controls("Text" & IntVal).BackColor = IngColor
Me.Controls("Text" & IntVal).Value = Me.[Overall Classification]
Next

Me.[Overall Classification].BackColor = IngColor

End Sub

It does not have to change while the information is being type. But
what
I
was trying to say is that its not changing at all. It remains with a
white
back color regardless of what I put in. All others are changing based
on
the
set WORDS entered in the [Overall Classification] field. But the
[Overall
Classification] entry field is not changing as it was when I first
started.
I made some change to try to get the other fields to work. And really
can't
figure out what I did to make it stop working. Hope that cleared my
intent
up better.
--
Work is sometimes hard....but someone has to do it.


:

If I understand you correctly you want the code to be executed while
someone
is still typing in the box, is that right?

As you have discovered, the AfterUpdate event only executes after the
typing
is finished. To run the code as each letter is typed, the Change
event
or
the KeyDown event of [Overall Classification] is probably the best
place
to
put it. Make sure you refer explicity to the Text property, thus:

If InStr(Me.[Overall Classification].Text, "SECRET") = 1 Then

Baz that was very helpful and worked like a cherm.

I have another code that was working at one point. But stop working
after
I
was trying to fix the one I just spoke about. Is it possible to
view
this
and inform me to what I did (changed) wrong. It is suppose to
change
as
text
is entered or based on the text in the box. Similar to the others,
excite
this is the box where the initial Classification is being entered.
Hope
that
made sense. Here is the Code:

Private Sub Overall_Classification_AfterUpdate()

If InStr(Me.[Overall Classification], "SECRET") = 1 Then
IngColor = 255
ElseIf InStr (Me.[Overall Classification], "TS" = 1 Then
IngColor = 65535
Else
IngColor = 0
End If

For InVal = 220 To 240 Step 2
Me.Controls("Text" & IntVal).BackColor = IngColor
Me.Controls("Text" & IntVal).Value = Me.[Overall Classification]
Next

End Sub

 

[Pierre]

Baz I completely understand, so please don't give up on my yet. I feel as if
I'm running you in circles or something. I know it has to be in the
Form_Current, I just don't know coding well enough to add it to the current
code in the Form_Current.
Is it possible to just add it with the numbers on LINE: For IntVal = 220 to
240 Step 2.
If so how, b/c I have already tried:
= [Overall Classification],220 to 240
= Overall Classification,220 to 240
= Me.[Overall Classification],220 to 240
= [Overall Classification];220 to 240
= [Overall Classification] AND 220 to 240
but nothing is working

'Code being used in Form Current (works and updates with no issue right now)
Private Sub Form_Current ()

Dim IngColor As Long
Dim IntVal As Integer

If InStr (Me.[Overall Classification], "SAM") = 1 Then
IngColor = 255
ElseIf InStr(Me.[Overall Classification], "TS") = 1 Then
IngColor = 65535
Else
IngColor = 0
End If

For IntVal = 220 To 240 Step 2

Me.Controls("Text" & IntVal).BackColor = IngColor
Me.Controls("Text' & IntVal).Value = Me.[Overall Classification]
Next
End Sub

--

 

[Pierre]

i tried changing them and it still wouldn't pick the new numbers up. But the
step 2 worked.

Thanks for your help
--
Work is sometimes hard....but someone has to do it.

The easy answer would be to rename your text boxes so that they have
consecutive numbers but if you want to keep them as even numbers for some
other reason then:

For IntVal = 220 To 226 Step 2
Me.Controls(“Text†& IntVal).BackColor = IngColor
Me.Controls(“Text’ & IntVal).Value = Me.[Overall Classification]
Next IntVal

Should do what you require.

John
##################################
Don't Print - Save trees

Morning and Happy New Year all.
I am having an issue with a code I'm trying to use to change the back color
of some text boxes I'm using on my form. The actual issue is not that the
code is not working. It’s just only working with one text box. I would like
to know how I would fix this code to change a group of numbers and not a
series of numbers.
i.e. "220, 222, 224, 226" - AND NOT - "220 To 226"
Now, when I try and enter it in example A it does not allow the comma.
Example B gives me an error because there are no odd numbers. I have tried
changing the numbers to odd numbers with no avail.
Any and All help in this matter is greatly appreciated.
Complete code below:
Private Sub Form_Current ()
Dim IngColor As Long
Dim IntVal As Integer
If InStr (Me.[Overall Classification], “SAMâ€) = 1 Then
IngColor = 255
ElseIf InStr(Me.[Overall Classification], “TSâ€) = 1 Then
IngColor = 65535
Else
IngColor = 0
End If
For IntVal = 220 To 226
Me.Controls(“Text†& IntVal).BackColor = IngColor
Me.Controls(“Text’ & IntVal).Value = Me.[Overall Classification]
Next
End Sub

 

[Baz]

Hi Pierre,

Just copy the extra line from the other procedure and paste it into the
Current event procedure i.e. this:

Me.[Overall Classification].BackColor = IngColor

Do it after the Next and before the End Sub, just like it is in the other
procedure.

HTH

Baz

 

[Pierre]

If only I was as good. But thank you for all of your help. Maybe I'll be of
some assistance one day.

--
Work is sometimes hard....but someone has to do it.

Hi Pierre,

Just copy the extra line from the other procedure and paste it into the
Current event procedure i.e. this:

Me.[Overall Classification].BackColor = IngColor

Do it after the Next and before the End Sub, just like it is in the other
procedure.

HTH

Baz

Baz I completely understand, so please don't give up on my yet. I feel as
if
I'm running you in circles or something. I know it has to be in the
Form_Current, I just don't know coding well enough to add it to the
current
code in the Form_Current.
Is it possible to just add it with the numbers on LINE: For IntVal = 220
to
240 Step 2.
If so how, b/c I have already tried:
= [Overall Classification],220 to 240
= Overall Classification,220 to 240
= Me.[Overall Classification],220 to 240
= [Overall Classification];220 to 240
= [Overall Classification] AND 220 to 240
but nothing is working

'Code being used in Form Current (works and updates with no issue right
now)
Private Sub Form_Current ()

Dim IngColor As Long
Dim IntVal As Integer

If InStr (Me.[Overall Classification], "SAM") = 1 Then
IngColor = 255
ElseIf InStr(Me.[Overall Classification], "TS") = 1 Then
IngColor = 65535
Else
IngColor = 0
End If

For IntVal = 220 To 240 Step 2

Me.Controls("Text" & IntVal).BackColor = IngColor
Me.Controls("Text' & IntVal).Value = Me.[Overall Classification]
Next
End Sub

 

[Baz]

You are most welcome Pierre.

If only I was as good. But thank you for all of your help. Maybe I'll be
of
some assistance one day.

--
Work is sometimes hard....but someone has to do it.

Hi Pierre,

Just copy the extra line from the other procedure and paste it into the
Current event procedure i.e. this:

Me.[Overall Classification].BackColor = IngColor

Do it after the Next and before the End Sub, just like it is in the other
procedure.

HTH

Baz

Baz I completely understand, so please don't give up on my yet. I feel
as
if
I'm running you in circles or something. I know it has to be in the
Form_Current, I just don't know coding well enough to add it to the
current
code in the Form_Current.
Is it possible to just add it with the numbers on LINE: For IntVal =
220
to
240 Step 2.
If so how, b/c I have already tried:
= [Overall Classification],220 to 240
= Overall Classification,220 to 240
= Me.[Overall Classification],220 to 240
= [Overall Classification];220 to 240
= [Overall Classification] AND 220 to 240
but nothing is working

'Code being used in Form Current (works and updates with no issue right
now)
Private Sub Form_Current ()

Dim IngColor As Long
Dim IntVal As Integer

If InStr (Me.[Overall Classification], "SAM") = 1 Then
IngColor = 255
ElseIf InStr(Me.[Overall Classification], "TS") = 1 Then
IngColor = 65535
Else
IngColor = 0
End If

For IntVal = 220 To 240 Step 2

Me.Controls("Text" & IntVal).BackColor = IngColor
Me.Controls("Text' & IntVal).Value = Me.[Overall Classification]
Next
End Sub