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

Color coding lists on sub forms

P: n/a
Hello there,

I have a subform that has anywhere from 20-500 ID numbers (unique)
listed on it. Each ID is going to be linked to a picture. First off I
set up a check for existance of the file;

Public Function fileexists(Name As String) As Boolean
On Error Resume Next
Dim temp As String
temp = Dir(Name, vbHidden Or vbSystem Or vbArchive Or vbReadOnly)
fileexists = ((Len(temp) > 0) And (Err.number = 0))
End Function

Now what I want to do is have the form look at the textbox (ID) and
then determine if the ID has a picture. If it does change the text
color of the ID text box to red other wise leave it black. Here is
what I have and it is not working correctly.

Private Sub Form_Load()
Dim path As String

If Me.sfmblue.Form.Controls("ID") < 10000 Then
path = CurrentProject.path & "\oapic0_9K\" &
Me.sfmblue.Form.Controls("ID") & ".jpg"
ElseIf Me.sfmblue.Form.Controls("ID") > 9999 And
Me.sfmblue.Form.Controls("ID") < 20000 Then
path = CurrentProject.path & "\oapic10_19K\" &
Me.sfmblue.Form.Controls("ID") & ".jpg"
End If

If fileexists(path) = True Then
Me.sfmblue.Form.Controls("ID").ForeColor = 255
Else
Me.sfmblue.Form.Controls("ID").ForeColor = -2147483640
End If
End Sub

Any help would be greatly appreciated.

worldbadger

Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
The problem with a continuous form view is that there is really only one
copy of unbound controls on the form at a time, so what you do to the one in
the current record/row will happen to all of them. There is an exception,
using Conditional Formatting will allow for each row to be different (Access
2000 and newer).

The Load event code you are using will run on the current record, the first
one in the subform at the time of the load unless you have more code above
this that moves to another record. What you could do though is use
Conditional Formatting on the ID textbox. In form design view, right click
the ID textbox and choose Conditional Formatting. Set the Default Formatting
to be one of the fore colors, then for Condition 1 choose Expression Is in
the combo box. For the expression, use a "user defined function" to
determine if the picture exists. You will need to pass this function the
value of the ID textbox and have the function return True or False. Set the
fore color to the other color.

Example expression:
FileExists([txtID]) = True

Example function (using your FileExists code):
Public Function FileExists(lngID As Long) As Boolean
On Error Resume Next
Dim temp As String, Path As String
Select Case lngID
Case Is <10000
path = CurrentProject.path & "\oapic0_9K\" & lngID & ".jpg"
Case 10000 To 20000
path = CurrentProject.path & "\oapic10_19K\" & lngID & ".jpg"
Case Else
End Select
temp = Dir(Path, vbHidden Or vbSystem Or vbArchive Or vbReadOnly)
FileExists = ((Len(temp) > 0) And (Err.number = 0))
End Function

I assumed the ID number to be a number (Long), if it is text, change the
function to show lngID to be a String. Also, VB will probably make the
conversion for you, but if there is a problem in concatenating the path and
lngID is a number, wrap it in CStr(lngID).

--
Wayne Morgan
MS Access MVP
"worldbadger" <wo*********@yahoo.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
Hello there,

I have a subform that has anywhere from 20-500 ID numbers (unique)
listed on it. Each ID is going to be linked to a picture. First off I
set up a check for existance of the file;

Public Function fileexists(Name As String) As Boolean
On Error Resume Next
Dim temp As String
temp = Dir(Name, vbHidden Or vbSystem Or vbArchive Or vbReadOnly)
fileexists = ((Len(temp) > 0) And (Err.number = 0))
End Function

Now what I want to do is have the form look at the textbox (ID) and
then determine if the ID has a picture. If it does change the text
color of the ID text box to red other wise leave it black. Here is
what I have and it is not working correctly.

Private Sub Form_Load()
Dim path As String

If Me.sfmblue.Form.Controls("ID") < 10000 Then
path = CurrentProject.path & "\oapic0_9K\" &
Me.sfmblue.Form.Controls("ID") & ".jpg"
ElseIf Me.sfmblue.Form.Controls("ID") > 9999 And
Me.sfmblue.Form.Controls("ID") < 20000 Then
path = CurrentProject.path & "\oapic10_19K\" &
Me.sfmblue.Form.Controls("ID") & ".jpg"
End If

If fileexists(path) = True Then
Me.sfmblue.Form.Controls("ID").ForeColor = 255
Else
Me.sfmblue.Form.Controls("ID").ForeColor = -2147483640
End If
End Sub

Any help would be greatly appreciated.

worldbadger

Nov 13 '05 #2

P: n/a
Wayne,

Thanks for the information. When I tried it there was no CONDITIONAL
FORMATTING to choose from when I right clicked the ID Text box.
Perhaps I have an older version of Access 2000 or maybe it is only on
Access 2003

As always, any help is always welcome.

worldbadger

Nov 13 '05 #3

P: n/a
It may be that your shortcut menus (the right click menus) have been changed
to other than default. The option should be available with the form or
report open in design view. If you don't see it on the right click menu,
select the textbox then go to the Format menu on the main menu bar and
select Conditional Formatting from there.

http://support.microsoft.com/default...b;en-us;208773

--
Wayne Morgan
MS Access MVP
"worldbadger" <wo*********@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Wayne,

Thanks for the information. When I tried it there was no CONDITIONAL
FORMATTING to choose from when I right clicked the ID Text box.
Perhaps I have an older version of Access 2000 or maybe it is only on
Access 2003

As always, any help is always welcome.

worldbadger

Nov 13 '05 #4

P: n/a
Hello,

Thank-you for assisting me with this. I did find the conditional
format, set up the module program and put in the test for expression.
Unfortunately, it still does not work. I am wondering if it is the fact
the lists of ID's are on a subform?

Also the ID text box is an AutoNumber format in the table if that has
any bearing. I am not sure how to check what the format is in the
formbox. When I look on the properties menu the format box is blank. I
was afraid to change it in case something else did not work then.

Thanks for your assistance.

worldbadger

Nov 13 '05 #5

P: n/a
Did you adjust the names of controls and fields that I used to match your
names? Have you set a break point in the function to see what is happening?
Is the function being called and if so, what values does it show and return?

--
Wayne Morgan
MS Access MVP
"worldbadger" <wo*********@yahoo.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...
Hello,

Thank-you for assisting me with this. I did find the conditional
format, set up the module program and put in the test for expression.
Unfortunately, it still does not work. I am wondering if it is the fact
the lists of ID's are on a subform?

Also the ID text box is an AutoNumber format in the table if that has
any bearing. I am not sure how to check what the format is in the
formbox. When I look on the properties menu the format box is blank. I
was afraid to change it in case something else did not work then.

Thanks for your assistance.

worldbadger

Nov 13 '05 #6

P: n/a
Ahhhhhh HA!!! You are right. When I set a breakpoint and run the form,
nothing happens (the form runs ok, just not the conditional
formatting). I assume that means that the function is not being
called. I did double check the controls and fields and I changed them
all to my names (although there was not much changing needed since the
the ID text box is called ID and the function is called FileExists).

Any thoughts?

worldbadger

Nov 13 '05 #7

P: n/a
If the function isn't being called, then I would suspect a typing error
(something misspelled) or a duplicate name. The function can't have the name
of anything else, including the name of the module it is in.

--
Wayne Morgan
MS Access MVP
"worldbadger" <wo*********@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Ahhhhhh HA!!! You are right. When I set a breakpoint and run the form,
nothing happens (the form runs ok, just not the conditional
formatting). I assume that means that the function is not being
called. I did double check the controls and fields and I changed them
all to my names (although there was not much changing needed since the
the ID text box is called ID and the function is called FileExists).

Any thoughts?

worldbadger

Nov 13 '05 #8

P: n/a
Wayne -- you are a true genius. When I changed the name of the module
and the name of the fuction (since I use it else where in the same
database) it all worked like a charm.

Thank-you for all you do. I am learning a lot from this and other
threads and it is because of people like you, willing to share their
expertise, that we are able to learn so much.

worldbadger

Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.