Solved

MS Access Form Control Background Color Change Depending On How Long The String Text Length Is

Posted on 2016-10-13
18
70 Views
Last Modified: 2016-10-13
Ok this might be a interesting one! I would like to add the ability of changing the background color of a specific text box control on my form depending on the length of text that is in it.

Example:
Normal basic color is white.    White = Good!
Text string count reaches 76 character length then the background goes Yellow.    Yellow = Warning!
Text string count reaches 150 character length then the background goes Red.    Red = Maximum!

We like to keep it under 76 length but in some cases it can be longer!

My specific text box control name is "SkuNm".

How can I do this??

THANK YOU ALL!
0
Comment
Question by:Dustin Stanley
  • 12
  • 5
18 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41842829
change the backcolor property of your field (in the keyup event) based on LEN of your field
0
 

Author Comment

by:Dustin Stanley
ID: 41842844
Thanks. Can you explain a little more about the LEN Please?
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41842845
len is a built in vba function e.g. LEN("abc") = 3
place your textbox name in the braces
0
 

Author Comment

by:Dustin Stanley
ID: 41842846
Also on the KeyUp event would that count the key strokes for the information? Thanks for the help!
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41842848
this is based on my field txtRevs

Private Sub txtRevs_KeyUp(KeyCode As Integer, Shift As Integer)
  If Len(txtRevs.Text) > 3 Then
   txtRevs.BackColor = vbRed  
  End If
End Sub
0
 

Author Comment

by:Dustin Stanley
ID: 41842854
Ok that makes sense greater then 3 the message box appears. How can I integrate this with the background color scheme?
0
 

Author Comment

by:Dustin Stanley
ID: 41842860
Ok So far I have

Private Sub SkuNm_KeyUp(KeyCode As Integer, Shift As Integer)
 If Len(SkuNm.Text) > 75 Then
 SkuNm.BackColor = vbRed
 End If
 End Sub

Open in new window

0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41842866
not sure what you want?
0
 

Author Comment

by:Dustin Stanley
ID: 41842867
Ok that is not working TOTALLY! If I was to delete some of the string say back down to 74 it never changes back to white from red?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:Dustin Stanley
ID: 41842869
Thanks for your help. I am looking for : Text field greater then 75 background goes red. Delete some characters and background goes back to white.
0
 

Author Comment

by:Dustin Stanley
ID: 41842871
Hey what happened to the Message box Code LOL. Did you edit your comment? I was going to try that out also.
0
 
LVL 7

Accepted Solution

by:
COACHMAN99 earned 250 total points
ID: 41842873
Private Sub txtRevs_KeyUp(KeyCode As Integer, Shift As Integer)
  If Len(txtRevs.Text) > 75 Then
   txtRevs.BackColor = vbRed
else
   txtRevs.BackColor = vbWhite
  End If
End Sub
0
 

Author Comment

by:Dustin Stanley
ID: 41842876
BRILLIANT! JUST BRILLIANT! Works like a charm! Just one last thing. What if i wanted to do a 3 color scheme. Like good, warning, maximum. Would I just use ELSE?
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 41842877
How about setting up a Conditional format for the control.  Set it to Expression, the use something like:

Switch(Len([FieldName])<76, 1, Len([FieldName]) < 150, 2, True, 3) = 2

This would be the first critieria in the controls conditional formatting, and you would simply set the background color to Yellow based on that  Then set the second criteria to:

Switch(Len([FieldName])<76, 1, Len([FieldName]) < 150, 2, True, 3) = 3

But I don't think that is going to work while you are actually editing the control.  If you need it to work while editing the control, then you might need to do something in the Controls Change event and evaluate the Length of the controls "Text" property.  This property is only available while the control has the focus, so you would not be able to use this property in the conditional formatting.


HTH
Dale
1
 

Author Comment

by:Dustin Stanley
ID: 41842880
I do need it while editing the controls only. After it is saved there is no need for it. It will just set a warning for the user to know while editing. Hopefully to keep character count down.
0
 

Author Comment

by:Dustin Stanley
ID: 41842896
Would there be a way to only see the yellow when the control has the focus and to revert back to White after the control loses focus?
0
 

Author Comment

by:Dustin Stanley
ID: 41842899
Nevermind I got it.

Control Name is SkuNm

Yellow if greater then 75 length with On change Event:
Private Sub SkuNm_Change()
      If Len(SkuNm.Text) > 75 Then
   SkuNm.BackColor = vbYellow
Else
   SkuNm.BackColor = vbWhite
 End If
End Sub

Open in new window


Then to revert back to white after with Lost focus Event:
Private Sub SkuNm_LostFocus()
SkuNm.BackColor = vbWhite
End Sub

Open in new window

0
 

Author Closing Comment

by:Dustin Stanley
ID: 41842900
THANK YOU!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article discusses four methods for overlaying images in a container on a web page
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

929 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now