Adding Bound Pictures to an Access Database

[CBinMI]

I have a file folder on my server of close to 3000 people in it. Each picture
is named as the persons ID number. Is there a way in mass to get these
pictures into an Access Table so I can have them as a bound object in my form?

 

[Jack Leach]

You could fairly easily write a function to do this, but I would advise
against it. In general you usually don't want to store the images in access
itself (its slow and rapidly brings you closer to the 2gb max).

I usually handle this by storing all of the images in their own folder,
named as the record ID (sounds like you've got this so far), and then create
an unbound image control on form you want to use. On the OnCurrent event of
the form, reset the picture path, so each time you change a record it
re-links to the appropriate image without actually needing to store them in
access.

If you try this, make sure the image is Linked and not Embeded, and also
make sure that the images are in a controlled folder so noone can screw with
them.

Something along the lines of this:



Private Sub Form_Current

Private Const IMGPATH as string = "C:\YourFolder\"

Me.ctlImageControl.Picture = _
IMGPTH & Trim(Str(Me![ID])) & ".jpg"

End Sub


hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[CBinMI]

Thank you Jack for all your help.

I used your idea this morning and when I click the compile button I am
getting a Compile Error: Invalid Attribute in Sub or Function. This is what
I have so far, can you help me?

Private Sub Form_Current()
Private Const IMGPATH As String = "S:\Administration\Technology
Services\Pictures 2008-09\AE 08-09"

Me.ctlImageControl.Picture = _
IMGPTH & Trim(Str(Me![Student #])) & ".jpg"
End Sub

You could fairly easily write a function to do this, but I would advise
against it. In general you usually don't want to store the images in access
itself (its slow and rapidly brings you closer to the 2gb max).

I usually handle this by storing all of the images in their own folder,
named as the record ID (sounds like you've got this so far), and then create
an unbound image control on form you want to use. On the OnCurrent event of
the form, reset the picture path, so each time you change a record it
re-links to the appropriate image without actually needing to store them in
access.

If you try this, make sure the image is Linked and not Embeded, and also
make sure that the images are in a controlled folder so noone can screw with
them.

Something along the lines of this:



Private Sub Form_Current

Private Const IMGPATH as string = "C:\YourFolder\"

Me.ctlImageControl.Picture = _
IMGPTH & Trim(Str(Me![ID])) & ".jpg"

End Sub


hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I have a file folder on my server of close to 3000 people in it. Each picture
is named as the persons ID number. Is there a way in mass to get these
pictures into an Access Table so I can have them as a bound object in my form?

 

[Jack Leach]

Private Const IMGPATH As String = "S:\Administration\Technology

Services\Pictures 2008-09\AE 08-09"

You'll want to inlude the last "\" at the end of this line (the way you have
this will read as "S:\...\...\AE 08-09STUDENTID.jpg")

You need "S:\..\...\AE 0809\STUDENTID.jpg"

Me![Student #]

Also, I would get rid of the pound sign here. Special characters in field
names tend to make things a mess, and may be the source of the error.
StudentID would be much more stable (you'll have to change the corresponding
field value as well).


A couple of further notes...

You probably want to confirm that the file extensions are the same for each
image if you haven't done so yet. If most of your files are .jpg but you
have one or two .jpeg files, this won't work without some further processing.

As Jim mentioned, you may want to think about setting the base path to
something other than a constant if you plan on distributing this. There's no
reason it won't work the way it is now but if the path ever changes it
becomes very difficult to remember everywhere it's been hardcoded.

Let me know how those two changes work out.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Thank you Jack for all your help.

I used your idea this morning and when I click the compile button I am
getting a Compile Error: Invalid Attribute in Sub or Function. This is what
I have so far, can you help me?

Private Sub Form_Current()
Private Const IMGPATH As String = "S:\Administration\Technology
Services\Pictures 2008-09\AE 08-09"

Me.ctlImageControl.Picture = _
IMGPTH & Trim(Str(Me![Student #])) & ".jpg"
End Sub

 

[CBinMI]

I made the changes and it still does not like the "Private Const" part, it
gives me a Invalid Attribute in Sub or Function error, when I click ok it
highlights the "Private Const" section.

This is what I changed it to:
Private Sub Form_Current()
Private Const IMGPATH As String = "S:\Administration\Technology
Services\Pictures 2008-09\AE 08-09\STUDENTID.JPG"


Me.ctlImageControl.Picture = _
IMGPTH & Trim(Str(Me![StudentID])) & ".jpg"
End Sub

Thanks for your help.

Private Const IMGPATH As String = "S:\Administration\Technology
Services\Pictures 2008-09\AE 08-09"

You'll want to inlude the last "\" at the end of this line (the way you have
this will read as "S:\...\...\AE 08-09STUDENTID.jpg")

You need "S:\..\...\AE 0809\STUDENTID.jpg"

Me![Student #]

Also, I would get rid of the pound sign here. Special characters in field
names tend to make things a mess, and may be the source of the error.
StudentID would be much more stable (you'll have to change the corresponding
field value as well).


A couple of further notes...

You probably want to confirm that the file extensions are the same for each
image if you haven't done so yet. If most of your files are .jpg but you
have one or two .jpeg files, this won't work without some further processing.

As Jim mentioned, you may want to think about setting the base path to
something other than a constant if you plan on distributing this. There's no
reason it won't work the way it is now but if the path ever changes it
becomes very difficult to remember everywhere it's been hardcoded.

Let me know how those two changes work out.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Thank you Jack for all your help.

I used your idea this morning and when I click the compile button I am
getting a Compile Error: Invalid Attribute in Sub or Function. This is what
I have so far, can you help me?

Private Sub Form_Current()
Private Const IMGPATH As String = "S:\Administration\Technology
Services\Pictures 2008-09\AE 08-09"

Me.ctlImageControl.Picture = _
IMGPTH & Trim(Str(Me![Student #])) & ".jpg"
End Sub

 

[Jack Leach]

Private Const IMGPATH As String = "S:\Administration\Technology

Services\Pictures 2008-09\AE 08-09\STUDENTID.JPG"

Lets get rid of the const and put it in a variable instead. Also, STUDENTID
is a placefiller I used as an example, you don't actually want that in the
code. Try this:



Dim strPath As String
strPath = "S:\Administration\Technology\Services\Pictures 2008-09\AE 08-09\"

Me.ctlImageControl.Picture = strPath & Trim(Str(Me![StudentID])) & ".jpg"



try that, lemme know

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I made the changes and it still does not like the "Private Const" part, it
gives me a Invalid Attribute in Sub or Function error, when I click ok it
highlights the "Private Const" section.

This is what I changed it to:
Private Sub Form_Current()
Private Const IMGPATH As String = "S:\Administration\Technology
Services\Pictures 2008-09\AE 08-09\STUDENTID.JPG"


Me.ctlImageControl.Picture = _
IMGPTH & Trim(Str(Me![StudentID])) & ".jpg"
End Sub

Thanks for your help.

 

[CBinMI]

Ok that worked, I got off the top row, now it is giving me an error on the
ctlImageControl it is saying Compile Error: Method or data member not found.

I REALLY appreciate your help and hope I am not becoming a pain.

Private Const IMGPATH As String = "S:\Administration\Technology
Services\Pictures 2008-09\AE 08-09\STUDENTID.JPG"

Lets get rid of the const and put it in a variable instead. Also, STUDENTID
is a placefiller I used as an example, you don't actually want that in the
code. Try this:



Dim strPath As String
strPath = "S:\Administration\Technology\Services\Pictures 2008-09\AE 08-09\"

Me.ctlImageControl.Picture = strPath & Trim(Str(Me![StudentID])) & ".jpg"



try that, lemme know

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I made the changes and it still does not like the "Private Const" part, it
gives me a Invalid Attribute in Sub or Function error, when I click ok it
highlights the "Private Const" section.

This is what I changed it to:
Private Sub Form_Current()
Private Const IMGPATH As String = "S:\Administration\Technology
Services\Pictures 2008-09\AE 08-09\STUDENTID.JPG"


Me.ctlImageControl.Picture = _
IMGPTH & Trim(Str(Me![StudentID])) & ".jpg"
End Sub

Thanks for your help.

 

[Jack Leach]

No problem. I thought this might come up, should be an easy fix.


ctlImageControl is a generic control name that I threw in there, you will
need to change that to whatever your actual control name is.

In design view on the form, select the image control and open the properies
box (Alt + Enter). Click the Other tab, and the first options should be
Name. You'll give the control it's Name here, and reference that name in the
VBA code.

So, if you name this control "ctlPersonPicture", the start of your line will
read:

Me.ctlPersonPicture.Picture = ....

When you hit the period key after typeing Me you should see a drop down list
with a lot of different items in it. One of those should be the control Name
that you are trying to reference.


--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Ok that worked, I got off the top row, now it is giving me an error on the
ctlImageControl it is saying Compile Error: Method or data member not found.

I REALLY appreciate your help and hope I am not becoming a pain.

Private Const IMGPATH As String = "S:\Administration\Technology
Services\Pictures 2008-09\AE 08-09\STUDENTID.JPG"

Lets get rid of the const and put it in a variable instead. Also, STUDENTID
is a placefiller I used as an example, you don't actually want that in the
code. Try this:



Dim strPath As String
strPath = "S:\Administration\Technology\Services\Pictures 2008-09\AE 08-09\"

Me.ctlImageControl.Picture = strPath & Trim(Str(Me![StudentID])) & ".jpg"



try that, lemme know

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I made the changes and it still does not like the "Private Const" part, it
gives me a Invalid Attribute in Sub or Function error, when I click ok it
highlights the "Private Const" section.

This is what I changed it to:
Private Sub Form_Current()
Private Const IMGPATH As String = "S:\Administration\Technology
Services\Pictures 2008-09\AE 08-09\STUDENTID.JPG"


Me.ctlImageControl.Picture = _
IMGPTH & Trim(Str(Me![StudentID])) & ".jpg"
End Sub

Thanks for your help.

 

[Jack Leach]

The Null issue is common and should be easy enough to get by... I'll get back
to that in a few minutes.

As far as the object not supporting the property or method, I think you may
have the wrong control on your form. I'm giving descriptions based from off
the UI in acess 2003 (which should be the same for 2002 or 2000), so if
you're using 97 or 2007 it might be different, but here's where to look.

On the Toolbox in design view, there's three controls in a row right next to
each other, Image, Unbound Object, and Bound Object. You want to make sure
you have the Image control, not either of the Object controls.

If you right click on the control in design view and pull up the properties,
under the Format tab the first item in the list should be a Picture property
(or in the All tab it would be the second one). If you're not using the same
UI I'm not sure where you'll find it, but the Picture property should be
there if you have the right control (when you drag/drop this control to the
form, it will probably ask you for a picture, you can pick any one out, it
will be overidden later).


So back to that error with the Null. What's happening here is that any time
you are navigating to a record (opening the form) that doesn't have an ID
field, it's throwing the error on you. This will always happen when going to
a new record (until we work around it), but may also happen if the
Me![StudentID] statement is not correct. We'll need to add a few lines to
account for Null values so we don't keep getting errors. That said, I'm
going to change things around a little bit even more than that so we can more
easily track what's going on (and more easily catch the next problem...

Don't forget to copy/paste the pfValidFile function at the end into your
form's module.




'------- CODE START
Private Sub Form_Current()

Dim strPath As String 'This holds our folder
Dim strFile As String 'This will hold each complete filepath

strPath = "S:\Administration\Technology\Services\Pictures 2008-09\AE 08-09\"


'Use the Debug.Print <expression> statement to print
'the current value of something to the immediate window
'Press ctl+G to view the immediate window, or select it
'from the View menu in the VBA IDE Window.
'
'Set a Breakpoint on the Private Sub Form_Current Line
'so we can step through the code line by line and make
'sure everything is going as planned. To set a breakpoint,
'click just to the left of the statement (in the grey bar on
'the side), and you should see the line now highlighted red.
'
'Press F8 to execute one line of code at a time.


'If the field isn't null, carry on, otherwise skip all this
If Not IsNull(Me![StudentID]) Then

'Check the value of the StudentID field
Debug.Print Me![StudentID])

'Build the string for the filename
strFile = strPath & Trim(Str(Me![StudentID])) & ".jpg"

Debug.Print strFile

'The above debug.print should be a complete valid
'filepath to the image you want. We'll verify this exists
'below...

If pfValidFile(strFile) = False Then
MsgBox "File Not Valid!!!"
Goto Exit_Proc
End If

'and finally, set the Picture property of the control

Me.ctlStudentImage.Picture = strFile

End If

Exit_Proc:
Exit Sub
End Sub




Private Function pfValidFile(aFile As String) As Boolean
On Error Resume Next
Dim var As Variant
var = GetAttr(aFile)
pfValidFile = Iif(Err.Number <> 0, False, True)
Err.Clear
End Function
'------- CODE END


Its a bit more code but this should cover just about everything that might
pop up from here on out, and make it nice and easy to debug (now and
later...).

The key here is to verify the type of control you have and the field
[StudentID] are correct. ( [StudentID] needs to be the exact name of the
field in the underlying table/query...)


Almost there... the only other thing you may possible run into is a variance
in file extensions, which can be taken care of if need be.


--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I think we are almost there...

I am getting a runtime error now. It says Run-Time error '438': Object
doesn't support this property or method. So I click debug and it highlights
my Me statement. Here is what I have:

Private Sub Form_Current()
Dim strPath As String
strPath = "S:\Administration\Technology\Services\Pictures 2008-09\AE 08-09\"

Me.ctlStudentPicture.Picture = strPath & Trim(Str(Me![StudentID])) & ".jpg"
End Sub

When I hit the . after ctlStudentPicture. I get a dropdown but Picture is
not in there. It also will give me an erro of improper use of "Null".

 

[Dale Fye]

Another point you might want to consider if this is a multi-user application.
Not everyone will have shared network drives mapped to the same drive
letter. So, while it may be S:\Administration\ on your machine, it could be
P:\Administration on someone elses. In this case, you might want to use the
UNC path to the server.

 

[CBinMI]

Jack,

Your last change to the form worked and my pictures are now showing up,
except in the case where I don't have a picture for a student. Then it will
not show the form at all and gives me a run-time error 2220. Says it can't
open the file, because it's not there.

I have updated the script like you wanted, I think, you kinda lost me at the
Debug.Print spot. This is what I have so far:

Private Sub Form_Current()
Dim strPath As String
Dim strFile As String

strPath = "S:\Administration\Technology Services\Pictures 2008-09\AE 08-09\"

Me.ctlPersonPicture.Picture = strPath & Trim(Str(Me![StudentID])) & ".jpg"
Debug.Print strFile
End Sub

The Null issue is common and should be easy enough to get by... I'll get back
to that in a few minutes.

As far as the object not supporting the property or method, I think you may
have the wrong control on your form. I'm giving descriptions based from off
the UI in acess 2003 (which should be the same for 2002 or 2000), so if
you're using 97 or 2007 it might be different, but here's where to look.

On the Toolbox in design view, there's three controls in a row right next to
each other, Image, Unbound Object, and Bound Object. You want to make sure
you have the Image control, not either of the Object controls.

If you right click on the control in design view and pull up the properties,
under the Format tab the first item in the list should be a Picture property
(or in the All tab it would be the second one). If you're not using the same
UI I'm not sure where you'll find it, but the Picture property should be
there if you have the right control (when you drag/drop this control to the
form, it will probably ask you for a picture, you can pick any one out, it
will be overidden later).


So back to that error with the Null. What's happening here is that any time
you are navigating to a record (opening the form) that doesn't have an ID
field, it's throwing the error on you. This will always happen when going to
a new record (until we work around it), but may also happen if the
Me![StudentID] statement is not correct. We'll need to add a few lines to
account for Null values so we don't keep getting errors. That said, I'm
going to change things around a little bit even more than that so we can more
easily track what's going on (and more easily catch the next problem...

Don't forget to copy/paste the pfValidFile function at the end into your
form's module.




'------- CODE START
Private Sub Form_Current()

Dim strPath As String 'This holds our folder
Dim strFile As String 'This will hold each complete filepath

strPath = "S:\Administration\Technology\Services\Pictures 2008-09\AE 08-09\"


'Use the Debug.Print <expression> statement to print
'the current value of something to the immediate window
'Press ctl+G to view the immediate window, or select it
'from the View menu in the VBA IDE Window.
'
'Set a Breakpoint on the Private Sub Form_Current Line
'so we can step through the code line by line and make
'sure everything is going as planned. To set a breakpoint,
'click just to the left of the statement (in the grey bar on
'the side), and you should see the line now highlighted red.
'
'Press F8 to execute one line of code at a time.


'If the field isn't null, carry on, otherwise skip all this
If Not IsNull(Me![StudentID]) Then

'Check the value of the StudentID field
Debug.Print Me![StudentID])

'Build the string for the filename
strFile = strPath & Trim(Str(Me![StudentID])) & ".jpg"

Debug.Print strFile

'The above debug.print should be a complete valid
'filepath to the image you want. We'll verify this exists
'below...

If pfValidFile(strFile) = False Then
MsgBox "File Not Valid!!!"
Goto Exit_Proc
End If

'and finally, set the Picture property of the control

Me.ctlStudentImage.Picture = strFile

End If

Exit_Proc:
Exit Sub
End Sub




Private Function pfValidFile(aFile As String) As Boolean
On Error Resume Next
Dim var As Variant
var = GetAttr(aFile)
pfValidFile = Iif(Err.Number <> 0, False, True)
Err.Clear
End Function
'------- CODE END


Its a bit more code but this should cover just about everything that might
pop up from here on out, and make it nice and easy to debug (now and
later...).

The key here is to verify the type of control you have and the field
[StudentID] are correct. ( [StudentID] needs to be the exact name of the
field in the underlying table/query...)


Almost there... the only other thing you may possible run into is a variance
in file extensions, which can be taken care of if need be.


--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I think we are almost there...

I am getting a runtime error now. It says Run-Time error '438': Object
doesn't support this property or method. So I click debug and it highlights
my Me statement. Here is what I have:

Private Sub Form_Current()
Dim strPath As String
strPath = "S:\Administration\Technology\Services\Pictures 2008-09\AE 08-09\"

Me.ctlStudentPicture.Picture = strPath & Trim(Str(Me![StudentID])) & ".jpg"
End Sub

When I hit the . after ctlStudentPicture. I get a dropdown but Picture is
not in there. It also will give me an erro of improper use of "Null".

 

[Dale Fye]

Modify your current event code to check for the files existence before
setting the picture. Something like:

strPath = "S:\.."
strFileName = strPath & TRIM(str(me.StudentID)) & ".jpg"

if len(dir(strFileName)) = 0 then
me.ctlPersonPicture.Picture = ""
else
me.ctlPersonPicture.Picture = strFileName
endif


--
----
HTH
Dale

Jack,

Your last change to the form worked and my pictures are now showing up,
except in the case where I don't have a picture for a student. Then it will
not show the form at all and gives me a run-time error 2220. Says it can't
open the file, because it's not there.

I have updated the script like you wanted, I think, you kinda lost me at the
Debug.Print spot. This is what I have so far:

Private Sub Form_Current()
Dim strPath As String
Dim strFile As String

strPath = "S:\Administration\Technology Services\Pictures 2008-09\AE 08-09\"

Me.ctlPersonPicture.Picture = strPath & Trim(Str(Me![StudentID])) & ".jpg"
Debug.Print strFile
End Sub

The Null issue is common and should be easy enough to get by... I'll get back
to that in a few minutes.

As far as the object not supporting the property or method, I think you may
have the wrong control on your form. I'm giving descriptions based from off
the UI in acess 2003 (which should be the same for 2002 or 2000), so if
you're using 97 or 2007 it might be different, but here's where to look.

On the Toolbox in design view, there's three controls in a row right next to
each other, Image, Unbound Object, and Bound Object. You want to make sure
you have the Image control, not either of the Object controls.

If you right click on the control in design view and pull up the properties,
under the Format tab the first item in the list should be a Picture property
(or in the All tab it would be the second one). If you're not using the same
UI I'm not sure where you'll find it, but the Picture property should be
there if you have the right control (when you drag/drop this control to the
form, it will probably ask you for a picture, you can pick any one out, it
will be overidden later).


So back to that error with the Null. What's happening here is that any time
you are navigating to a record (opening the form) that doesn't have an ID
field, it's throwing the error on you. This will always happen when going to
a new record (until we work around it), but may also happen if the
Me![StudentID] statement is not correct. We'll need to add a few lines to
account for Null values so we don't keep getting errors. That said, I'm
going to change things around a little bit even more than that so we can more
easily track what's going on (and more easily catch the next problem...

Don't forget to copy/paste the pfValidFile function at the end into your
form's module.




'------- CODE START
Private Sub Form_Current()

Dim strPath As String 'This holds our folder
Dim strFile As String 'This will hold each complete filepath

strPath = "S:\Administration\Technology\Services\Pictures 2008-09\AE 08-09\"


'Use the Debug.Print <expression> statement to print
'the current value of something to the immediate window
'Press ctl+G to view the immediate window, or select it
'from the View menu in the VBA IDE Window.
'
'Set a Breakpoint on the Private Sub Form_Current Line
'so we can step through the code line by line and make
'sure everything is going as planned. To set a breakpoint,
'click just to the left of the statement (in the grey bar on
'the side), and you should see the line now highlighted red.
'
'Press F8 to execute one line of code at a time.


'If the field isn't null, carry on, otherwise skip all this
If Not IsNull(Me![StudentID]) Then

'Check the value of the StudentID field
Debug.Print Me![StudentID])

'Build the string for the filename
strFile = strPath & Trim(Str(Me![StudentID])) & ".jpg"

Debug.Print strFile

'The above debug.print should be a complete valid
'filepath to the image you want. We'll verify this exists
'below...

If pfValidFile(strFile) = False Then
MsgBox "File Not Valid!!!"
Goto Exit_Proc
End If

'and finally, set the Picture property of the control

Me.ctlStudentImage.Picture = strFile

End If

Exit_Proc:
Exit Sub
End Sub




Private Function pfValidFile(aFile As String) As Boolean
On Error Resume Next
Dim var As Variant
var = GetAttr(aFile)
pfValidFile = Iif(Err.Number <> 0, False, True)
Err.Clear
End Function
'------- CODE END


Its a bit more code but this should cover just about everything that might
pop up from here on out, and make it nice and easy to debug (now and
later...).

The key here is to verify the type of control you have and the field
[StudentID] are correct. ( [StudentID] needs to be the exact name of the
field in the underlying table/query...)


Almost there... the only other thing you may possible run into is a variance
in file extensions, which can be taken care of if need be.


--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I think we are almost there...

I am getting a runtime error now. It says Run-Time error '438': Object
doesn't support this property or method. So I click debug and it highlights
my Me statement. Here is what I have:

Private Sub Form_Current()
Dim strPath As String
strPath = "S:\Administration\Technology\Services\Pictures 2008-09\AE 08-09\"

Me.ctlStudentPicture.Picture = strPath & Trim(Str(Me![StudentID])) & ".jpg"
End Sub

When I hit the . after ctlStudentPicture. I get a dropdown but Picture is
not in there. It also will give me an erro of improper use of "Null".

 

[Jack Leach]

We should verify that the file exists before we try and set it to the
picture. I'm also going to throw in an If/Then in case of a null in a new
record. Try this:


Private Sub Form_Current
Dim strPath As String

If Not Isnull(Me![StudentID]) Then

strPath = "S:\Administration\Technology Services" _
& "\Pictures 2008-09\AE 08-09\" _
& Trim(Str(Me![StudentID])) & ".jpg"

If pfValidFile(strPath) = True Then
Me.ctlPersonPicture.Picture = strPath
End If

End If
End Sub

Private Function pfValidFile(aFile As String) As Boolean
On Error Resume Next
Dim var As Variant
var = GetAttr(aFile)
pfValidFile = Iif(Err.Number <> 0, False, True)
Err.Clear
End Function



This will:
- Do nothing if there's no current StudentID
- Do nothing if it can't find the file


Let me know how that works. Make sure the pfValidFile() code is in your
form module or you'll get a Sub or Function Not Defined error

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Jack,

Your last change to the form worked and my pictures are now showing up,
except in the case where I don't have a picture for a student. Then it will
not show the form at all and gives me a run-time error 2220. Says it can't
open the file, because it's not there.

I have updated the script like you wanted, I think, you kinda lost me at the
Debug.Print spot. This is what I have so far:

Private Sub Form_Current()
Dim strPath As String
Dim strFile As String

strPath = "S:\Administration\Technology Services\Pictures 2008-09\AE 08-09\"

Me.ctlPersonPicture.Picture = strPath & Trim(Str(Me![StudentID])) & ".jpg"
Debug.Print strFile
End Sub

The Null issue is common and should be easy enough to get by... I'll get back
to that in a few minutes.

As far as the object not supporting the property or method, I think you may
have the wrong control on your form. I'm giving descriptions based from off
the UI in acess 2003 (which should be the same for 2002 or 2000), so if
you're using 97 or 2007 it might be different, but here's where to look.

On the Toolbox in design view, there's three controls in a row right next to
each other, Image, Unbound Object, and Bound Object. You want to make sure
you have the Image control, not either of the Object controls.

If you right click on the control in design view and pull up the properties,
under the Format tab the first item in the list should be a Picture property
(or in the All tab it would be the second one). If you're not using the same
UI I'm not sure where you'll find it, but the Picture property should be
there if you have the right control (when you drag/drop this control to the
form, it will probably ask you for a picture, you can pick any one out, it
will be overidden later).


So back to that error with the Null. What's happening here is that any time
you are navigating to a record (opening the form) that doesn't have an ID
field, it's throwing the error on you. This will always happen when going to
a new record (until we work around it), but may also happen if the
Me![StudentID] statement is not correct. We'll need to add a few lines to
account for Null values so we don't keep getting errors. That said, I'm
going to change things around a little bit even more than that so we can more
easily track what's going on (and more easily catch the next problem...

Don't forget to copy/paste the pfValidFile function at the end into your
form's module.




'------- CODE START
Private Sub Form_Current()

Dim strPath As String 'This holds our folder
Dim strFile As String 'This will hold each complete filepath

strPath = "S:\Administration\Technology\Services\Pictures 2008-09\AE 08-09\"


'Use the Debug.Print <expression> statement to print
'the current value of something to the immediate window
'Press ctl+G to view the immediate window, or select it
'from the View menu in the VBA IDE Window.
'
'Set a Breakpoint on the Private Sub Form_Current Line
'so we can step through the code line by line and make
'sure everything is going as planned. To set a breakpoint,
'click just to the left of the statement (in the grey bar on
'the side), and you should see the line now highlighted red.
'
'Press F8 to execute one line of code at a time.


'If the field isn't null, carry on, otherwise skip all this
If Not IsNull(Me![StudentID]) Then

'Check the value of the StudentID field
Debug.Print Me![StudentID])

'Build the string for the filename
strFile = strPath & Trim(Str(Me![StudentID])) & ".jpg"

Debug.Print strFile

'The above debug.print should be a complete valid
'filepath to the image you want. We'll verify this exists
'below...

If pfValidFile(strFile) = False Then
MsgBox "File Not Valid!!!"
Goto Exit_Proc
End If

'and finally, set the Picture property of the control

Me.ctlStudentImage.Picture = strFile

End If

Exit_Proc:
Exit Sub
End Sub




Private Function pfValidFile(aFile As String) As Boolean
On Error Resume Next
Dim var As Variant
var = GetAttr(aFile)
pfValidFile = Iif(Err.Number <> 0, False, True)
Err.Clear
End Function
'------- CODE END


Its a bit more code but this should cover just about everything that might
pop up from here on out, and make it nice and easy to debug (now and
later...).

The key here is to verify the type of control you have and the field
[StudentID] are correct. ( [StudentID] needs to be the exact name of the
field in the underlying table/query...)


Almost there... the only other thing you may possible run into is a variance
in file extensions, which can be taken care of if need be.


--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I think we are almost there...

I am getting a runtime error now. It says Run-Time error '438': Object
doesn't support this property or method. So I click debug and it highlights
my Me statement. Here is what I have:

Private Sub Form_Current()
Dim strPath As String
strPath = "S:\Administration\Technology\Services\Pictures 2008-09\AE 08-09\"

Me.ctlStudentPicture.Picture = strPath & Trim(Str(Me![StudentID])) & ".jpg"
End Sub

When I hit the . after ctlStudentPicture. I get a dropdown but Picture is
not in there. It also will give me an erro of improper use of "Null".

 

[Dale Fye]

Another option would be to create a "NoPhoto.jpg" file that shows shadowy
figure. You could open any photo in a photo editor, then color their photo
in in grey, and save the image as NoPhoto.jpg

Then, when you use dir( ) to check to see whether the file exists, instead
of setting .picture = "", you could set it to strPath & "NoPhoto.jpg"

----
HTH
Dale

Jack,

Your last change to the form worked and my pictures are now showing up,
except in the case where I don't have a picture for a student. Then it will
not show the form at all and gives me a run-time error 2220. Says it can't
open the file, because it's not there.

I have updated the script like you wanted, I think, you kinda lost me at the
Debug.Print spot. This is what I have so far:

Private Sub Form_Current()
Dim strPath As String
Dim strFile As String

strPath = "S:\Administration\Technology Services\Pictures 2008-09\AE 08-09\"

Me.ctlPersonPicture.Picture = strPath & Trim(Str(Me![StudentID])) & ".jpg"
Debug.Print strFile
End Sub

The Null issue is common and should be easy enough to get by... I'll get back
to that in a few minutes.

As far as the object not supporting the property or method, I think you may
have the wrong control on your form. I'm giving descriptions based from off
the UI in acess 2003 (which should be the same for 2002 or 2000), so if
you're using 97 or 2007 it might be different, but here's where to look.

On the Toolbox in design view, there's three controls in a row right next to
each other, Image, Unbound Object, and Bound Object. You want to make sure
you have the Image control, not either of the Object controls.

If you right click on the control in design view and pull up the properties,
under the Format tab the first item in the list should be a Picture property
(or in the All tab it would be the second one). If you're not using the same
UI I'm not sure where you'll find it, but the Picture property should be
there if you have the right control (when you drag/drop this control to the
form, it will probably ask you for a picture, you can pick any one out, it
will be overidden later).


So back to that error with the Null. What's happening here is that any time
you are navigating to a record (opening the form) that doesn't have an ID
field, it's throwing the error on you. This will always happen when going to
a new record (until we work around it), but may also happen if the
Me![StudentID] statement is not correct. We'll need to add a few lines to
account for Null values so we don't keep getting errors. That said, I'm
going to change things around a little bit even more than that so we can more
easily track what's going on (and more easily catch the next problem...

Don't forget to copy/paste the pfValidFile function at the end into your
form's module.




'------- CODE START
Private Sub Form_Current()

Dim strPath As String 'This holds our folder
Dim strFile As String 'This will hold each complete filepath

strPath = "S:\Administration\Technology\Services\Pictures 2008-09\AE 08-09\"


'Use the Debug.Print <expression> statement to print
'the current value of something to the immediate window
'Press ctl+G to view the immediate window, or select it
'from the View menu in the VBA IDE Window.
'
'Set a Breakpoint on the Private Sub Form_Current Line
'so we can step through the code line by line and make
'sure everything is going as planned. To set a breakpoint,
'click just to the left of the statement (in the grey bar on
'the side), and you should see the line now highlighted red.
'
'Press F8 to execute one line of code at a time.


'If the field isn't null, carry on, otherwise skip all this
If Not IsNull(Me![StudentID]) Then

'Check the value of the StudentID field
Debug.Print Me![StudentID])

'Build the string for the filename
strFile = strPath & Trim(Str(Me![StudentID])) & ".jpg"

Debug.Print strFile

'The above debug.print should be a complete valid
'filepath to the image you want. We'll verify this exists
'below...

If pfValidFile(strFile) = False Then
MsgBox "File Not Valid!!!"
Goto Exit_Proc
End If

'and finally, set the Picture property of the control

Me.ctlStudentImage.Picture = strFile

End If

Exit_Proc:
Exit Sub
End Sub




Private Function pfValidFile(aFile As String) As Boolean
On Error Resume Next
Dim var As Variant
var = GetAttr(aFile)
pfValidFile = Iif(Err.Number <> 0, False, True)
Err.Clear
End Function
'------- CODE END


Its a bit more code but this should cover just about everything that might
pop up from here on out, and make it nice and easy to debug (now and
later...).

The key here is to verify the type of control you have and the field
[StudentID] are correct. ( [StudentID] needs to be the exact name of the
field in the underlying table/query...)


Almost there... the only other thing you may possible run into is a variance
in file extensions, which can be taken care of if need be.


--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I think we are almost there...

I am getting a runtime error now. It says Run-Time error '438': Object
doesn't support this property or method. So I click debug and it highlights
my Me statement. Here is what I have:

Private Sub Form_Current()
Dim strPath As String
strPath = "S:\Administration\Technology\Services\Pictures 2008-09\AE 08-09\"

Me.ctlStudentPicture.Picture = strPath & Trim(Str(Me![StudentID])) & ".jpg"
End Sub

When I hit the . after ctlStudentPicture. I get a dropdown but Picture is
not in there. It also will give me an erro of improper use of "Null".

 

[CBinMI]

It is me again... This is the first chance today I have had to work on this.
What you gave me yesterday worked great it is not giving me "NULL" error
anymore. However, when the student's picture is missing it is leaving the
previous records picture up for the next record. Any ideas? This is what I
have:

Private Sub ctlPersonPicture_Click()

End Sub

Private Sub Form_Current()
Dim strPath As String

If Not IsNull(Me![StudentID]) Then

strPath = "S:\Administration\Technology Services" _
& "\Pictures 2008-09\AE 08-09\" _
& Trim(Str(Me![StudentID])) & ".jpg"

If pfValidFile(strPath) = True Then
Me.ctlPersonPicture.Picture = strPath
End If

End If
End Sub

Private Function pfValidFile(aFile As String) As Boolean
On Error Resume Next
Dim var As Variant
var = GetAttr(aFile)
pfValidFile = IIf(Err.Number <> 0, False, True)
Err.Clear
End Function

Thanks

We should verify that the file exists before we try and set it to the
picture. I'm also going to throw in an If/Then in case of a null in a new
record. Try this:


Private Sub Form_Current
Dim strPath As String

If Not Isnull(Me![StudentID]) Then

strPath = "S:\Administration\Technology Services" _
& "\Pictures 2008-09\AE 08-09\" _
& Trim(Str(Me![StudentID])) & ".jpg"

If pfValidFile(strPath) = True Then
Me.ctlPersonPicture.Picture = strPath
End If

End If
End Sub

Private Function pfValidFile(aFile As String) As Boolean
On Error Resume Next
Dim var As Variant
var = GetAttr(aFile)
pfValidFile = Iif(Err.Number <> 0, False, True)
Err.Clear
End Function



This will:
- Do nothing if there's no current StudentID
- Do nothing if it can't find the file


Let me know how that works. Make sure the pfValidFile() code is in your
form module or you'll get a Sub or Function Not Defined error

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Jack,

Your last change to the form worked and my pictures are now showing up,
except in the case where I don't have a picture for a student. Then it will
not show the form at all and gives me a run-time error 2220. Says it can't
open the file, because it's not there.

I have updated the script like you wanted, I think, you kinda lost me at the
Debug.Print spot. This is what I have so far:

Private Sub Form_Current()
Dim strPath As String
Dim strFile As String

strPath = "S:\Administration\Technology Services\Pictures 2008-09\AE 08-09\"

Me.ctlPersonPicture.Picture = strPath & Trim(Str(Me![StudentID])) & ".jpg"
Debug.Print strFile
End Sub

The Null issue is common and should be easy enough to get by... I'll get back
to that in a few minutes.

As far as the object not supporting the property or method, I think you may
have the wrong control on your form. I'm giving descriptions based from off
the UI in acess 2003 (which should be the same for 2002 or 2000), so if
you're using 97 or 2007 it might be different, but here's where to look.

On the Toolbox in design view, there's three controls in a row right next to
each other, Image, Unbound Object, and Bound Object. You want to make sure
you have the Image control, not either of the Object controls.

If you right click on the control in design view and pull up the properties,
under the Format tab the first item in the list should be a Picture property
(or in the All tab it would be the second one). If you're not using the same
UI I'm not sure where you'll find it, but the Picture property should be
there if you have the right control (when you drag/drop this control to the
form, it will probably ask you for a picture, you can pick any one out, it
will be overidden later).


So back to that error with the Null. What's happening here is that any time
you are navigating to a record (opening the form) that doesn't have an ID
field, it's throwing the error on you. This will always happen when going to
a new record (until we work around it), but may also happen if the
Me![StudentID] statement is not correct. We'll need to add a few lines to
account for Null values so we don't keep getting errors. That said, I'm
going to change things around a little bit even more than that so we can more
easily track what's going on (and more easily catch the next problem...

Don't forget to copy/paste the pfValidFile function at the end into your
form's module.




'------- CODE START
Private Sub Form_Current()

Dim strPath As String 'This holds our folder
Dim strFile As String 'This will hold each complete filepath

strPath = "S:\Administration\Technology\Services\Pictures 2008-09\AE 08-09\"


'Use the Debug.Print <expression> statement to print
'the current value of something to the immediate window
'Press ctl+G to view the immediate window, or select it
'from the View menu in the VBA IDE Window.
'
'Set a Breakpoint on the Private Sub Form_Current Line
'so we can step through the code line by line and make
'sure everything is going as planned. To set a breakpoint,
'click just to the left of the statement (in the grey bar on
'the side), and you should see the line now highlighted red.
'
'Press F8 to execute one line of code at a time.


'If the field isn't null, carry on, otherwise skip all this
If Not IsNull(Me![StudentID]) Then

'Check the value of the StudentID field
Debug.Print Me![StudentID])

'Build the string for the filename
strFile = strPath & Trim(Str(Me![StudentID])) & ".jpg"

Debug.Print strFile

'The above debug.print should be a complete valid
'filepath to the image you want. We'll verify this exists
'below...

If pfValidFile(strFile) = False Then
MsgBox "File Not Valid!!!"
Goto Exit_Proc
End If

'and finally, set the Picture property of the control

Me.ctlStudentImage.Picture = strFile

End If

Exit_Proc:
Exit Sub
End Sub




Private Function pfValidFile(aFile As String) As Boolean
On Error Resume Next
Dim var As Variant
var = GetAttr(aFile)
pfValidFile = Iif(Err.Number <> 0, False, True)
Err.Clear
End Function
'------- CODE END


Its a bit more code but this should cover just about everything that might
pop up from here on out, and make it nice and easy to debug (now and
later...).

The key here is to verify the type of control you have and the field
[StudentID] are correct. ( [StudentID] needs to be the exact name of the
field in the underlying table/query...)


Almost there... the only other thing you may possible run into is a variance
in file extensions, which can be taken care of if need be.


--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



:

I think we are almost there...

I am getting a runtime error now. It says Run-Time error '438': Object
doesn't support this property or method. So I click debug and it highlights
my Me statement. Here is what I have:

Private Sub Form_Current()
Dim strPath As String
strPath = "S:\Administration\Technology\Services\Pictures 2008-09\AE 08-09\"

Me.ctlStudentPicture.Picture = strPath & Trim(Str(Me![StudentID])) & ".jpg"
End Sub

When I hit the . after ctlStudentPicture. I get a dropdown but Picture is
not in there. It also will give me an erro of improper use of "Null".

 

[Jack Leach]

Just add an Else to the If statement that checks for a picture:

If pfValidFile(strPath) = True Then
Me.ctlPersonPicture.Picture = strPath

Else
Me.ctlPersonPicture.Picture = ""

End If

Or, as Dale suggested, you can use a default "no pic" picture

If pfValidFile(strPath) = True Then
Me.ctlPersonPicture.Picture = strPath

Else
Me.ctlPersonPicture.Picture = "yourNoPicPicturePath.jpg"

End If

Also, per Dale's suggestion, you can replace the entire pfValidFile function
with a Len(Dir(strPath)) = 0 expression, which gets rid of the need for a
separate function to check it and cleans the code up a little. That would
turn your code into:


Private Sub Form_Current()
Dim strPath As String

If Not IsNull(Me![StudentID]) Then

strPath = "S:\Administration\Technology Services" _
& "\Pictures 2008-09\AE 08-09\" _
& Trim(Str(Me![StudentID])) & ".jpg"

If Len(Dir(strPath)) <> 0 Then
Me.ctlPersonPicture.Picture = strPath
Else
Me.ctlPersonPicture.Picture = ""
End If

End If
End Sub




To explain that line a bit:

If Len(Dir(strPath)) <> 0 Then

The Len() function returns the number of characters in a string.
The Dir() function returns the file(s) for the specified folder or file

So, if the file exists, its Dir() Length is > 0, otherwise the code sets the
picture to "".

This is probably a much better way to do it. It keeps the code cleaner and
is probably a bit more stable besides.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

It is me again... This is the first chance today I have had to work on this.
What you gave me yesterday worked great it is not giving me "NULL" error
anymore. However, when the student's picture is missing it is leaving the
previous records picture up for the next record. Any ideas? This is what I
have:

Private Sub ctlPersonPicture_Click()

End Sub

Private Sub Form_Current()
Dim strPath As String

If Not IsNull(Me![StudentID]) Then

strPath = "S:\Administration\Technology Services" _
& "\Pictures 2008-09\AE 08-09\" _
& Trim(Str(Me![StudentID])) & ".jpg"

If pfValidFile(strPath) = True Then
Me.ctlPersonPicture.Picture = strPath
End If

End If
End Sub

Private Function pfValidFile(aFile As String) As Boolean
On Error Resume Next
Dim var As Variant
var = GetAttr(aFile)
pfValidFile = IIf(Err.Number <> 0, False, True)
Err.Clear
End Function

Thanks

We should verify that the file exists before we try and set it to the
picture. I'm also going to throw in an If/Then in case of a null in a new
record. Try this:


Private Sub Form_Current
Dim strPath As String

If Not Isnull(Me![StudentID]) Then

strPath = "S:\Administration\Technology Services" _
& "\Pictures 2008-09\AE 08-09\" _
& Trim(Str(Me![StudentID])) & ".jpg"

If pfValidFile(strPath) = True Then
Me.ctlPersonPicture.Picture = strPath
End If

End If
End Sub

Private Function pfValidFile(aFile As String) As Boolean
On Error Resume Next
Dim var As Variant
var = GetAttr(aFile)
pfValidFile = Iif(Err.Number <> 0, False, True)
Err.Clear
End Function



This will:
- Do nothing if there's no current StudentID
- Do nothing if it can't find the file


Let me know how that works. Make sure the pfValidFile() code is in your
form module or you'll get a Sub or Function Not Defined error

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Jack,

Your last change to the form worked and my pictures are now showing up,
except in the case where I don't have a picture for a student. Then it will
not show the form at all and gives me a run-time error 2220. Says it can't
open the file, because it's not there.

I have updated the script like you wanted, I think, you kinda lost me at the
Debug.Print spot. This is what I have so far:

Private Sub Form_Current()
Dim strPath As String
Dim strFile As String

strPath = "S:\Administration\Technology Services\Pictures 2008-09\AE 08-09\"

Me.ctlPersonPicture.Picture = strPath & Trim(Str(Me![StudentID])) & ".jpg"
Debug.Print strFile
End Sub


:

The Null issue is common and should be easy enough to get by... I'll get back
to that in a few minutes.

As far as the object not supporting the property or method, I think you may
have the wrong control on your form. I'm giving descriptions based from off
the UI in acess 2003 (which should be the same for 2002 or 2000), so if
you're using 97 or 2007 it might be different, but here's where to look.

On the Toolbox in design view, there's three controls in a row right next to
each other, Image, Unbound Object, and Bound Object. You want to make sure
you have the Image control, not either of the Object controls.

If you right click on the control in design view and pull up the properties,
under the Format tab the first item in the list should be a Picture property
(or in the All tab it would be the second one). If you're not using the same
UI I'm not sure where you'll find it, but the Picture property should be
there if you have the right control (when you drag/drop this control to the
form, it will probably ask you for a picture, you can pick any one out, it
will be overidden later).


So back to that error with the Null. What's happening here is that any time
you are navigating to a record (opening the form) that doesn't have an ID
field, it's throwing the error on you. This will always happen when going to
a new record (until we work around it), but may also happen if the
Me![StudentID] statement is not correct. We'll need to add a few lines to
account for Null values so we don't keep getting errors. That said, I'm
going to change things around a little bit even more than that so we can more
easily track what's going on (and more easily catch the next problem...

Don't forget to copy/paste the pfValidFile function at the end into your
form's module.




'------- CODE START
Private Sub Form_Current()

Dim strPath As String 'This holds our folder
Dim strFile As String 'This will hold each complete filepath

strPath = "S:\Administration\Technology\Services\Pictures 2008-09\AE 08-09\"


'Use the Debug.Print <expression> statement to print
'the current value of something to the immediate window
'Press ctl+G to view the immediate window, or select it
'from the View menu in the VBA IDE Window.
'
'Set a Breakpoint on the Private Sub Form_Current Line
'so we can step through the code line by line and make
'sure everything is going as planned. To set a breakpoint,
'click just to the left of the statement (in the grey bar on
'the side), and you should see the line now highlighted red.
'
'Press F8 to execute one line of code at a time.


'If the field isn't null, carry on, otherwise skip all this
If Not IsNull(Me![StudentID]) Then

'Check the value of the StudentID field
Debug.Print Me![StudentID])

'Build the string for the filename
strFile = strPath & Trim(Str(Me![StudentID])) & ".jpg"

Debug.Print strFile

'The above debug.print should be a complete valid
'filepath to the image you want. We'll verify this exists
'below...

If pfValidFile(strFile) = False Then
MsgBox "File Not Valid!!!"
Goto Exit_Proc
End If

'and finally, set the Picture property of the control

Me.ctlStudentImage.Picture = strFile

End If

Exit_Proc:
Exit Sub
End Sub




Private Function pfValidFile(aFile As String) As Boolean
On Error Resume Next
Dim var As Variant
var = GetAttr(aFile)
pfValidFile = Iif(Err.Number <> 0, False, True)
Err.Clear
End Function
'------- CODE END


Its a bit more code but this should cover just about everything that might
pop up from here on out, and make it nice and easy to debug (now and
later...).

The key here is to verify the type of control you have and the field
[StudentID] are correct. ( [StudentID] needs to be the exact name of the
field in the underlying table/query...)


Almost there... the only other thing you may possible run into is a variance
in file extensions, which can be taken care of if need be.


--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



:

I think we are almost there...

I am getting a runtime error now. It says Run-Time error '438': Object
doesn't support this property or method. So I click debug and it highlights
my Me statement. Here is what I have:

Private Sub Form_Current()
Dim strPath As String
strPath = "S:\Administration\Technology\Services\Pictures 2008-09\AE 08-09\"

Me.ctlStudentPicture.Picture = strPath & Trim(Str(Me![StudentID])) & ".jpg"
End Sub

When I hit the . after ctlStudentPicture. I get a dropdown but Picture is
not in there. It also will give me an erro of improper use of "Null".

 

[CBinMI]

That worked PERFECTLY!!! You two have been a HUGE help to me.

I tried following the same procedure to get the pictures to work when I
print my Report out, but it stays on only one picture.

Is there something different I need to do for that?

Thanks

Just add an Else to the If statement that checks for a picture:

If pfValidFile(strPath) = True Then
Me.ctlPersonPicture.Picture = strPath

Else
Me.ctlPersonPicture.Picture = ""

End If

Or, as Dale suggested, you can use a default "no pic" picture

If pfValidFile(strPath) = True Then
Me.ctlPersonPicture.Picture = strPath

Else
Me.ctlPersonPicture.Picture = "yourNoPicPicturePath.jpg"

End If

Also, per Dale's suggestion, you can replace the entire pfValidFile function
with a Len(Dir(strPath)) = 0 expression, which gets rid of the need for a
separate function to check it and cleans the code up a little. That would
turn your code into:


Private Sub Form_Current()
Dim strPath As String

If Not IsNull(Me![StudentID]) Then

strPath = "S:\Administration\Technology Services" _
& "\Pictures 2008-09\AE 08-09\" _
& Trim(Str(Me![StudentID])) & ".jpg"

If Len(Dir(strPath)) <> 0 Then
Me.ctlPersonPicture.Picture = strPath
Else
Me.ctlPersonPicture.Picture = ""
End If

End If
End Sub




To explain that line a bit:

If Len(Dir(strPath)) <> 0 Then

The Len() function returns the number of characters in a string.
The Dir() function returns the file(s) for the specified folder or file

So, if the file exists, its Dir() Length is > 0, otherwise the code sets the
picture to "".

This is probably a much better way to do it. It keeps the code cleaner and
is probably a bit more stable besides.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

It is me again... This is the first chance today I have had to work on this.
What you gave me yesterday worked great it is not giving me "NULL" error
anymore. However, when the student's picture is missing it is leaving the
previous records picture up for the next record. Any ideas? This is what I
have:

Private Sub ctlPersonPicture_Click()

End Sub

Private Sub Form_Current()
Dim strPath As String

If Not IsNull(Me![StudentID]) Then

strPath = "S:\Administration\Technology Services" _
& "\Pictures 2008-09\AE 08-09\" _
& Trim(Str(Me![StudentID])) & ".jpg"

If pfValidFile(strPath) = True Then
Me.ctlPersonPicture.Picture = strPath
End If

End If
End Sub

Private Function pfValidFile(aFile As String) As Boolean
On Error Resume Next
Dim var As Variant
var = GetAttr(aFile)
pfValidFile = IIf(Err.Number <> 0, False, True)
Err.Clear
End Function

Thanks

We should verify that the file exists before we try and set it to the
picture. I'm also going to throw in an If/Then in case of a null in a new
record. Try this:


Private Sub Form_Current
Dim strPath As String

If Not Isnull(Me![StudentID]) Then

strPath = "S:\Administration\Technology Services" _
& "\Pictures 2008-09\AE 08-09\" _
& Trim(Str(Me![StudentID])) & ".jpg"

If pfValidFile(strPath) = True Then
Me.ctlPersonPicture.Picture = strPath
End If

End If
End Sub

Private Function pfValidFile(aFile As String) As Boolean
On Error Resume Next
Dim var As Variant
var = GetAttr(aFile)
pfValidFile = Iif(Err.Number <> 0, False, True)
Err.Clear
End Function



This will:
- Do nothing if there's no current StudentID
- Do nothing if it can't find the file


Let me know how that works. Make sure the pfValidFile() code is in your
form module or you'll get a Sub or Function Not Defined error

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



:

Jack,

Your last change to the form worked and my pictures are now showing up,
except in the case where I don't have a picture for a student. Then it will
not show the form at all and gives me a run-time error 2220. Says it can't
open the file, because it's not there.

I have updated the script like you wanted, I think, you kinda lost me at the
Debug.Print spot. This is what I have so far:

Private Sub Form_Current()
Dim strPath As String
Dim strFile As String

strPath = "S:\Administration\Technology Services\Pictures 2008-09\AE 08-09\"

Me.ctlPersonPicture.Picture = strPath & Trim(Str(Me![StudentID])) & ".jpg"
Debug.Print strFile
End Sub


:

The Null issue is common and should be easy enough to get by... I'll get back
to that in a few minutes.

As far as the object not supporting the property or method, I think you may
have the wrong control on your form. I'm giving descriptions based from off
the UI in acess 2003 (which should be the same for 2002 or 2000), so if
you're using 97 or 2007 it might be different, but here's where to look.

On the Toolbox in design view, there's three controls in a row right next to
each other, Image, Unbound Object, and Bound Object. You want to make sure
you have the Image control, not either of the Object controls.

If you right click on the control in design view and pull up the properties,
under the Format tab the first item in the list should be a Picture property
(or in the All tab it would be the second one). If you're not using the same
UI I'm not sure where you'll find it, but the Picture property should be
there if you have the right control (when you drag/drop this control to the
form, it will probably ask you for a picture, you can pick any one out, it
will be overidden later).


So back to that error with the Null. What's happening here is that any time
you are navigating to a record (opening the form) that doesn't have an ID
field, it's throwing the error on you. This will always happen when going to
a new record (until we work around it), but may also happen if the
Me![StudentID] statement is not correct. We'll need to add a few lines to
account for Null values so we don't keep getting errors. That said, I'm
going to change things around a little bit even more than that so we can more
easily track what's going on (and more easily catch the next problem...

Don't forget to copy/paste the pfValidFile function at the end into your
form's module.




'------- CODE START
Private Sub Form_Current()

Dim strPath As String 'This holds our folder
Dim strFile As String 'This will hold each complete filepath

strPath = "S:\Administration\Technology\Services\Pictures 2008-09\AE 08-09\"


'Use the Debug.Print <expression> statement to print
'the current value of something to the immediate window
'Press ctl+G to view the immediate window, or select it
'from the View menu in the VBA IDE Window.
'
'Set a Breakpoint on the Private Sub Form_Current Line
'so we can step through the code line by line and make
'sure everything is going as planned. To set a breakpoint,
'click just to the left of the statement (in the grey bar on
'the side), and you should see the line now highlighted red.
'
'Press F8 to execute one line of code at a time.


'If the field isn't null, carry on, otherwise skip all this
If Not IsNull(Me![StudentID]) Then

'Check the value of the StudentID field
Debug.Print Me![StudentID])

'Build the string for the filename
strFile = strPath & Trim(Str(Me![StudentID])) & ".jpg"

Debug.Print strFile

'The above debug.print should be a complete valid
'filepath to the image you want. We'll verify this exists
'below...

If pfValidFile(strFile) = False Then
MsgBox "File Not Valid!!!"
Goto Exit_Proc
End If

'and finally, set the Picture property of the control

Me.ctlStudentImage.Picture = strFile

End If

Exit_Proc:
Exit Sub
End Sub




Private Function pfValidFile(aFile As String) As Boolean
On Error Resume Next
Dim var As Variant
var = GetAttr(aFile)
pfValidFile = Iif(Err.Number <> 0, False, True)
Err.Clear
End Function
'------- CODE END


Its a bit more code but this should cover just about everything that might
pop up from here on out, and make it nice and easy to debug (now and
later...).

The key here is to verify the type of control you have and the field
[StudentID] are correct. ( [StudentID] needs to be the exact name of the
field in the underlying table/query...)


Almost there... the only other thing you may possible run into is a variance
in file extensions, which can be taken care of if need be.