Altering fore colour properties only in selected field

[shredder249]

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:

 
Private Sub TrackTitle_AfterUpdate()
 
    AlbumSave.Enabled = True
 
    Dim folder As String
    Dim number As String
    Dim prefix As String
    Dim name As String
    Dim ext As String
    Dim autonumbered As String
    Dim Filepath As String
 
    folder = URLSource
    number = TrackNumber
 
    If number < 10 Then
        prefix = "0"
    Else
        prefix = ""
    End If
 
    name = TrackTitle
    ext = ExtensionSource
    autonumbered = Numbered
 
    If autonumbered = "Yes" Then
        Filepath = folder + "\" + prefix + number + " " + name + "." + ext
    Else
        Filepath = folder + "\" + name + "." + ext
    End If
 
    URL = Filepath
 
    Dim fso
    Dim File As String
 
    Dim red As Long
    Dim green As Long
 
    red = RGB(255, 0, 0)
    green = RGB(0, 128, 0)
 
    File = Filepath
    Set fso = CreateObject("Scripting.FileSystemObject")
 
    If Not fso.FileExists(File) Then
        URL.ForeColor = red
    Else
        URL.ForeColor = green
    End If
 
    If URL.ForeColor = green Then
        Play.Enabled = True
    Else
        Play.Enabled = False
    End If
 
    If URL.ForeColor = red Then
        Browsefile.SetFocus
    Else
        Play.SetFocus
    End If
 
End Sub
 
 

[Megalog]

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.

[missinglinq]

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

If Not fso.FileExists(File) Then
   Me.ValidPath = 0
 Else
   Me.ValidPath = -1
End If
 
If Me.ValidPath = -1   Then
Play.Enabled = True
Else
Play.Enabled = False
End If
 
If Me.ValidPath = 0 Then
Browsefile.SetFocus
Else
Play.SetFocus
End If
 
End Sub
 

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)>

[shredder249]

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

If Not fso.FileExists(File) Then
   Me.ValidPath = 0
 Else
   Me.ValidPath = -1
End If
 
If Me.ValidPath = -1   Then
Play.Enabled = True
Else
Play.Enabled = False
End If
 
If Me.ValidPath = 0 Then
Browsefile.SetFocus
Else
Play.SetFocus
End If
 
End Sub
 

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)>

[missinglinq]

Glad we could be of help!

Linq ;0)>