By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

Altering fore colour properties only in selected field

P: 22
Hi, I have a form in MS Access 2003 where the user types a file extension into one text box (e.g. mp3), a folder path into another text box (e.g. D:\Backup) and a song name into a third text box (e.g. Shallow). After the song name text box has been updated a procedure is run which attempts to generate a file path for the given song name and then output it to a fourth text box. In the above examples the result outputted to the file path text box would be D:\Backup\Shallow.mp3.

A second procedure then runs an existence check on the file path, if it is valid the text in the file path text box turns green and if it isn't the text turns red. The file extension text box and the folder path text box are contained in the form header. The song name text box and file path text box are contained in the detail and make up a record.

The above two procedures work fine, however if i create 3 records with valid file paths and then a fourth with an invalid file path, all four record's file path text box text turns red. Is there an option in the form properties or vba code that results in only the current record's file path text box fore colour being edited instead of all the records on the form?

Thanks,

The code for the song name text box after update is listed below:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub TrackTitle_AfterUpdate()
  3.  
  4.     AlbumSave.Enabled = True
  5.  
  6.     Dim folder As String
  7.     Dim number As String
  8.     Dim prefix As String
  9.     Dim name As String
  10.     Dim ext As String
  11.     Dim autonumbered As String
  12.     Dim Filepath As String
  13.  
  14.     folder = URLSource
  15.     number = TrackNumber
  16.  
  17.     If number < 10 Then
  18.         prefix = "0"
  19.     Else
  20.         prefix = ""
  21.     End If
  22.  
  23.     name = TrackTitle
  24.     ext = ExtensionSource
  25.     autonumbered = Numbered
  26.  
  27.     If autonumbered = "Yes" Then
  28.         Filepath = folder + "\" + prefix + number + " " + name + "." + ext
  29.     Else
  30.         Filepath = folder + "\" + name + "." + ext
  31.     End If
  32.  
  33.     URL = Filepath
  34.  
  35.     Dim fso
  36.     Dim File As String
  37.  
  38.     Dim red As Long
  39.     Dim green As Long
  40.  
  41.     red = RGB(255, 0, 0)
  42.     green = RGB(0, 128, 0)
  43.  
  44.     File = Filepath
  45.     Set fso = CreateObject("Scripting.FileSystemObject")
  46.  
  47.     If Not fso.FileExists(File) Then
  48.         URL.ForeColor = red
  49.     Else
  50.         URL.ForeColor = green
  51.     End If
  52.  
  53.     If URL.ForeColor = green Then
  54.         Play.Enabled = True
  55.     Else
  56.         Play.Enabled = False
  57.     End If
  58.  
  59.     If URL.ForeColor = red Then
  60.         Browsefile.SetFocus
  61.     Else
  62.         Play.SetFocus
  63.     End If
  64.  
  65. End Sub
  66.  
  67.  
Jan 6 '08 #1
Share this Question
Share on Google+
4 Replies


Megalog
Expert 100+
P: 378
Is this a single form? If so, then the problem is that your code is only triggering after an update to the data, so when you cycle through records the same color is still being applied since there's no OnCurrent event.


Is this a continuous form? If so, I dont think you can independantly color the records based on individual criteria using VBA, since all the objects are basically multiple instances of the same objects.

Now, I'm not using 2003 anymore, so cant remember if this works there, but in 2007 you can use conditional formatting in a continuous form. If it works for you, you can enable/disable playing with your filecheck routine (instead of setting the color, and using the color to determine .enabled = true/false) and then apply a conditional format however you like.
Jan 6 '08 #2

missinglinq
Expert 2.5K+
P: 3,532
From the behavior you describe you're not speaking of a Single View form, and so, as Megalog said, Conditional Formatting is the route you need to take.

What I would do is add a Yes/No field to the underlying table. If your form is based on a query, be sure to go into Design View for the query and add the new field to the query so it's available to the form.

Instead of trying to set your fore colors in code, based on the validity of the path/filename, you'll need to modify your code so that if the path is valid, you set the value of the checkbox (letís call it ValidPath) to -1.
Note that you won't actually have to place the Yes/No field on the form to reference or set it.

Now replace your code Lines 46 thru 64 with this code

Expand|Select|Wrap|Line Numbers
  1. If Not fso.FileExists(File) Then
  2.    Me.ValidPath = 0
  3.  Else
  4.    Me.ValidPath = -1
  5. End If
  6.  
  7. If Me.ValidPath = -1   Then
  8. Play.Enabled = True
  9. Else
  10. Play.Enabled = False
  11. End If
  12.  
  13. If Me.ValidPath = 0 Then
  14. Browsefile.SetFocus
  15. Else
  16. Play.SetFocus
  17. End If
  18.  
  19. End Sub
  20.  
Now select the URL textbox

Goto Format - Conditional Format
Under Condition1 select Expression Is
In the blank box enter [ValidPath] = -1
Set the fore color to Green

Hit Add, then

Under Condition2 select Expression Is
In the blank box enter [ValidPath] = 0
Set the fore color to Red

I think I've included everything, but I've had some distractions here! Post back if you have any problems.

Welcome to TheScripts!

Linq ;0)>
Jan 6 '08 #3

P: 22
Thank you very much Megalog and missinglinq, It now works fine. The only alteration I had to make to the solution you suggested was to add the Yes/No field to the form as the code wouldn't work without it being there. This was not a problem, I just made the field invisible and unenabled. Thanks again!

From the behavior you describe you're not speaking of a Single View form, and so, as Megalog said, Conditional Formatting is the route you need to take.

What I would do is add a Yes/No field to the underlying table. If your form is based on a query, be sure to go into Design View for the query and add the new field to the query so it's available to the form.

Instead of trying to set your fore colors in code, based on the validity of the path/filename, you'll need to modify your code so that if the path is valid, you set the value of the checkbox (letís call it ValidPath) to -1.
Note that you won't actually have to place the Yes/No field on the form to reference or set it.

Now replace your code Lines 46 thru 64 with this code

Expand|Select|Wrap|Line Numbers
  1. If Not fso.FileExists(File) Then
  2.    Me.ValidPath = 0
  3.  Else
  4.    Me.ValidPath = -1
  5. End If
  6.  
  7. If Me.ValidPath = -1   Then
  8. Play.Enabled = True
  9. Else
  10. Play.Enabled = False
  11. End If
  12.  
  13. If Me.ValidPath = 0 Then
  14. Browsefile.SetFocus
  15. Else
  16. Play.SetFocus
  17. End If
  18.  
  19. End Sub
  20.  
Now select the URL textbox

Goto Format - Conditional Format
Under Condition1 select Expression Is
In the blank box enter [ValidPath] = -1
Set the fore color to Green

Hit Add, then

Under Condition2 select Expression Is
In the blank box enter [ValidPath] = 0
Set the fore color to Red

I think I've included everything, but I've had some distractions here! Post back if you have any problems.

Welcome to TheScripts!

Linq ;0)>
Jan 7 '08 #4

missinglinq
Expert 2.5K+
P: 3,532
Glad we could be of help!

Linq ;0)>
Jan 7 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.