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

Proper use of IsNull (A2K3)

P: n/a
PW
Hi All,

I go into debug mode with the code below and varReturnVal is Null, but
the code still is run.

Any idea why?

Dim varReturnVal As Variant
Dim intDogID As Integer

intDogID = Me.txtDogID

varReturnVal = DLookup("[tDogPicLoc]", "dogs", "[iDog_ID]= " &
intDogID)

If IsNull(varReturnVal) Then
Me.imgDogPic.Picture = "" ' <-- This code runs although
varReturnVal in debug is Null
Else
Me.imgDogPic.Picture = varReturnVal
End If

End Sub
Oct 23 '08 #1
Share this Question
Share on Google+
22 Replies


P: n/a
A number of points.

I would expect your iDog_ID should be an AutoNumber (Long)
So defining intDogID as an integer is a conflict
I supect that the line intDogID = Me.txtDogID should be LngDogID =
CLng(txtDogID)
Have a look at Allen Browne's website http://allenbrowne.com/ser-42.html
This is a much faster version of DLookup and although it appears a bit
complicated just use it inslead of DLookup
so your line should read

varReturnVal = ELookup("tDogPicLoc", "dogs", "iDog_ID = " & LngDogID)
Assuming you are loading the picture into an Image Control, if there is no
image the picture is (none) so that the if clause should be

If IsNull(varReturnVal) Then
Me.imgDogPic.Picture = "(none)" ' <-- This code runs although
varReturnVal in debug is Null
Else
Me.imgDogPic.Picture = varReturnVal
End If

Phil

"PW" <pa**********************@removehotmail.comwrote in message
news:nq********************************@4ax.com...
Hi All,

I go into debug mode with the code below and varReturnVal is Null, but
the code still is run.

Any idea why?

Dim varReturnVal As Variant
Dim intDogID As Integer

intDogID = Me.txtDogID

varReturnVal = DLookup("[tDogPicLoc]", "dogs", "[iDog_ID]= " &
intDogID)

If IsNull(varReturnVal) Then
Me.imgDogPic.Picture = "" ' <-- This code runs although
varReturnVal in debug is Null
Else
Me.imgDogPic.Picture = varReturnVal
End If

End Sub

Oct 23 '08 #2

P: n/a
PW
On Thu, 23 Oct 2008 09:09:39 +0100, "Phil Stanton"
<ph**@myfamilyname.co.ukwrote:
>A number of points.

I would expect your iDog_ID should be an AutoNumber (Long)
If I knew what I was doing way back then, it would have been an
AutoNumber, but I made it an integer and update it manually in code
:-)

Thanks for the tip though!

I have no idea why the return value from the DLookup = Null but the
IsNull code still runs!

Any ideas anyone?

-paul

>So defining intDogID as an integer is a conflict
I supect that the line intDogID = Me.txtDogID should be LngDogID =
CLng(txtDogID)
Have a look at Allen Browne's website http://allenbrowne.com/ser-42.html
This is a much faster version of DLookup and although it appears a bit
complicated just use it inslead of DLookup
so your line should read

varReturnVal = ELookup("tDogPicLoc", "dogs", "iDog_ID = " & LngDogID)
Assuming you are loading the picture into an Image Control, if there is no
image the picture is (none) so that the if clause should be

If IsNull(varReturnVal) Then
Me.imgDogPic.Picture = "(none)" ' <-- This code runs although
varReturnVal in debug is Null
Else
Me.imgDogPic.Picture = varReturnVal
End If

Phil

"PW" <pa**********************@removehotmail.comwrote in message
news:nq********************************@4ax.com.. .
>Hi All,

I go into debug mode with the code below and varReturnVal is Null, but
the code still is run.

Any idea why?

Dim varReturnVal As Variant
Dim intDogID As Integer

intDogID = Me.txtDogID

varReturnVal = DLookup("[tDogPicLoc]", "dogs", "[iDog_ID]= " &
intDogID)

If IsNull(varReturnVal) Then
Me.imgDogPic.Picture = "" ' <-- This code runs although
varReturnVal in debug is Null
Else
Me.imgDogPic.Picture = varReturnVal
End If

End Sub
Oct 23 '08 #3

P: n/a
On Oct 23, 7:56*pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
On Thu, 23 Oct 2008 09:09:39 +0100, "Phil Stanton"

<p...@myfamilyname.co.ukwrote:
A number of points.
I would expect your iDog_ID should be an AutoNumber (Long)

If I knew what I was doing way back then, it would have been an
AutoNumber, but I made it an integer and update it manually in code
:-)

Thanks for the tip though!

I have no idea why the return value from the DLookup = Null but the
IsNull code still runs!

Any ideas anyone?

-paul
So defining intDogID as an integer is a conflict
I supect that the line intDogID = Me.txtDogID should be LngDogID =
CLng(txtDogID)
Have a look at Allen Browne's websitehttp://allenbrowne.com/ser-42.html
This is a much faster version of DLookup and although it appears a bit
complicated just use it inslead of DLookup
so your line should read
varReturnVal = ELookup("tDogPicLoc", "dogs", "iDog_ID = " & LngDogID)
Assuming you are loading the picture into an Image Control, if there isno
image the picture is (none) so that the if clause should be
If IsNull(varReturnVal) Then
* * * *Me.imgDogPic.Picture = "(none)" * * ' <-- This *code runs *although
varReturnVal in debug is Null
Else
* *Me.imgDogPic.Picture = varReturnVal
End If
Phil
"PW" <paulremove_williamson...@removehotmail.comwrote in message
news:nq********************************@4ax.com...
Hi All,
I go into debug mode with the code below and varReturnVal is Null, but
the code still is run.
Any idea why?
Dim varReturnVal As Variant
Dim intDogID As Integer
intDogID = Me.txtDogID
varReturnVal = DLookup("[tDogPicLoc]", "dogs", "[iDog_ID]= " &
intDogID)
If IsNull(varReturnVal) Then
* *Me.imgDogPic.Picture = "" * * ' <-- This *code runs *although
varReturnVal in debug is Null
Else
* *Me.imgDogPic.Picture = varReturnVal
End If
End Sub- Hide quoted text -

- Show quoted text -

It's happening because the dlookup is returning a null. Remember, a
variant will accept a null as a value, whereas a typed variable won't.
If the types of iDog_ID and intDogID don't match the dlookup could
well return a null regardless, so check that you've also typed the
iDogID field as an integer, to match intDogID.

I wouldn't worry too much about the speed of dlookup unless you have
an old, slow machine - it's perfectly ok in most cases, although
Allen's elookup does improve one or two other things as well as speed.
If dlookup works ok without the frilly bits then don't go to the
complication of using elookup.

One of the main deficiencies in your code is that it is always best to
use typed variables where possible and wrap dlookup with nz, as in -

dim varReturnVal as string
varReturnVal =nz(dLookup("tDogPicLoc", "dogs", "iDog_ID =
" & LngDogID))

You can then test with

If len(varReturnVal) =0 Then
Me.imgDogPic.Picture = "(none)"
Else
Me.imgDogPic.Picture = varReturnVal
End If

Finally, and a long shot, if you've by some chance left the iDog_ID
field typed as text (the default) in the table you'll always get a
null returned. And yes, it would be better even at this stage to
convert iDog_ID into an autonumber field.

Dave
Oct 23 '08 #4

P: n/a
PW
On Thu, 23 Oct 2008 15:22:21 -0700 (PDT), daved
<da*******@googlemail.comwrote:
>On Oct 23, 7:56*pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
>On Thu, 23 Oct 2008 09:09:39 +0100, "Phil Stanton"

<p...@myfamilyname.co.ukwrote:
>A number of points.
>I would expect your iDog_ID should be an AutoNumber (Long)

If I knew what I was doing way back then, it would have been an
AutoNumber, but I made it an integer and update it manually in code
:-)

Thanks for the tip though!

I have no idea why the return value from the DLookup = Null but the
IsNull code still runs!

Any ideas anyone?

-paul
>So defining intDogID as an integer is a conflict
I supect that the line intDogID = Me.txtDogID should be LngDogID =
CLng(txtDogID)
Have a look at Allen Browne's websitehttp://allenbrowne.com/ser-42.html
This is a much faster version of DLookup and although it appears a bit
complicated just use it inslead of DLookup
so your line should read
>varReturnVal = ELookup("tDogPicLoc", "dogs", "iDog_ID = " & LngDogID)
Assuming you are loading the picture into an Image Control, if there is no
image the picture is (none) so that the if clause should be
If IsNull(varReturnVal) Then
* * * *Me.imgDogPic.Picture = "(none)" * * ' <-- This *code runs *although
varReturnVal in debug is Null
Else
* *Me.imgDogPic.Picture = varReturnVal
End If
>Phil
>"PW" <paulremove_williamson...@removehotmail.comwrote in message
news:nq********************************@4ax.com.. .
Hi All,
>I go into debug mode with the code below and varReturnVal is Null, but
the code still is run.
>Any idea why?
>Dim varReturnVal As Variant
Dim intDogID As Integer
>intDogID = Me.txtDogID
>varReturnVal = DLookup("[tDogPicLoc]", "dogs", "[iDog_ID]= " &
intDogID)
>If IsNull(varReturnVal) Then
* *Me.imgDogPic.Picture = "" * * ' <-- This *code runs *although
varReturnVal in debug is Null
Else
* *Me.imgDogPic.Picture = varReturnVal
End If
>End Sub- Hide quoted text -

- Show quoted text -


It's happening because the dlookup is returning a null. Remember, a
variant will accept a null as a value, whereas a typed variable won't.
If the types of iDog_ID and intDogID don't match the dlookup could
well return a null regardless, so check that you've also typed the
iDogID field as an integer, to match intDogID.

I wouldn't worry too much about the speed of dlookup unless you have
an old, slow machine - it's perfectly ok in most cases, although
Allen's elookup does improve one or two other things as well as speed.
If dlookup works ok without the frilly bits then don't go to the
complication of using elookup.

One of the main deficiencies in your code is that it is always best to
use typed variables where possible and wrap dlookup with nz, as in -

dim varReturnVal as string
varReturnVal =nz(dLookup("tDogPicLoc", "dogs", "iDog_ID =
" & LngDogID))

You can then test with

If len(varReturnVal) =0 Then
Me.imgDogPic.Picture = "(none)"
Else
Me.imgDogPic.Picture = varReturnVal
End If

Finally, and a long shot, if you've by some chance left the iDog_ID
field typed as text (the default) in the table you'll always get a
null returned. And yes, it would be better even at this stage to
convert iDog_ID into an autonumber field.

Dave
Picture keeps appearing.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Dim lngDogID As Long

Dim varReturnVal As String

lngDogID = Me.txtDogID

varReturnVal = Nz(DLookup("tDogPicLoc", "dogs", "iDog_ID = " &
lngDogID))

Me.imgDogPic.Picture = "(none)"

If Len(varReturnVal) = 0 Then
Me.imgDogPic.Picture = "(none)"
Else
Me.imgDogPic.Picture = varReturnVal
End If

End Sub

The idogid field in the table is an integer, as so as the textbox on
the form is in debug mode

1st time, len(varReturnVal) = 33 and the record is located and the dog
picture appears.

2nd dlookup, the idogid is not found and len(varreturnval) = 0 but yet
the preceeding dog picture is still loaded. I have tried "" and
"(none)".

So, it looks like your code is working! Yeay! Just need to know how
to properly wipe out the picture property. Funny, when I type
Me.imgDogPic Intellisense does not come up with a .Picture property!

Any ideas?

-paul
Oct 24 '08 #5

P: n/a
PW
On Thu, 23 Oct 2008 15:22:21 -0700 (PDT), daved
<da*******@googlemail.comwrote:
>On Oct 23, 7:56*pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
>On Thu, 23 Oct 2008 09:09:39 +0100, "Phil Stanton"

<p...@myfamilyname.co.ukwrote:
>A number of points.
>I would expect your iDog_ID should be an AutoNumber (Long)

If I knew what I was doing way back then, it would have been an
AutoNumber, but I made it an integer and update it manually in code
:-)

Thanks for the tip though!

I have no idea why the return value from the DLookup = Null but the
IsNull code still runs!

Any ideas anyone?

-paul
>So defining intDogID as an integer is a conflict
I supect that the line intDogID = Me.txtDogID should be LngDogID =
CLng(txtDogID)
Have a look at Allen Browne's websitehttp://allenbrowne.com/ser-42.html
This is a much faster version of DLookup and although it appears a bit
complicated just use it inslead of DLookup
so your line should read
>varReturnVal = ELookup("tDogPicLoc", "dogs", "iDog_ID = " & LngDogID)
Assuming you are loading the picture into an Image Control, if there is no
image the picture is (none) so that the if clause should be
If IsNull(varReturnVal) Then
* * * *Me.imgDogPic.Picture = "(none)" * * ' <-- This *code runs *although
varReturnVal in debug is Null
Else
* *Me.imgDogPic.Picture = varReturnVal
End If
>Phil
>"PW" <paulremove_williamson...@removehotmail.comwrote in message
news:nq********************************@4ax.com.. .
Hi All,
>I go into debug mode with the code below and varReturnVal is Null, but
the code still is run.
>Any idea why?
>Dim varReturnVal As Variant
Dim intDogID As Integer
>intDogID = Me.txtDogID
>varReturnVal = DLookup("[tDogPicLoc]", "dogs", "[iDog_ID]= " &
intDogID)
>If IsNull(varReturnVal) Then
* *Me.imgDogPic.Picture = "" * * ' <-- This *code runs *although
varReturnVal in debug is Null
Else
* *Me.imgDogPic.Picture = varReturnVal
End If
>End Sub- Hide quoted text -

- Show quoted text -


It's happening because the dlookup is returning a null. Remember, a
variant will accept a null as a value, whereas a typed variable won't.
If the types of iDog_ID and intDogID don't match the dlookup could
well return a null regardless, so check that you've also typed the
iDogID field as an integer, to match intDogID.

I wouldn't worry too much about the speed of dlookup unless you have
an old, slow machine - it's perfectly ok in most cases, although
Allen's elookup does improve one or two other things as well as speed.
If dlookup works ok without the frilly bits then don't go to the
complication of using elookup.

One of the main deficiencies in your code is that it is always best to
use typed variables where possible and wrap dlookup with nz, as in -

dim varReturnVal as string
varReturnVal =nz(dLookup("tDogPicLoc", "dogs", "iDog_ID =
" & LngDogID))

You can then test with

If len(varReturnVal) =0 Then
Me.imgDogPic.Picture = "(none)"
Else
Me.imgDogPic.Picture = varReturnVal
End If

Finally, and a long shot, if you've by some chance left the iDog_ID
field typed as text (the default) in the table you'll always get a
null returned. And yes, it would be better even at this stage to
convert iDog_ID into an autonumber field.

Dave
It works but once a picture is located, all the other records/dogs
will end up with the same picture. Gotta look at all the code I
guess!

-pw
Oct 24 '08 #6

P: n/a
If you're talking about continuous forms, all the pictures will be of the
dog on your current record. The image is not bound and I don't think there
is a way round this problem.
It is very similar to having a label on a continuous form which appears
identically on each repetition of the form.

Phil

"PW" <pa**********************@removehotmail.comwrote in message
news:qq********************************@4ax.com...
On Thu, 23 Oct 2008 15:22:21 -0700 (PDT), daved
<da*******@googlemail.comwrote:
>>On Oct 23, 7:56 pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
>>On Thu, 23 Oct 2008 09:09:39 +0100, "Phil Stanton"

<p...@myfamilyname.co.ukwrote:
A number of points.

I would expect your iDog_ID should be an AutoNumber (Long)

If I knew what I was doing way back then, it would have been an
AutoNumber, but I made it an integer and update it manually in code
:-)

Thanks for the tip though!

I have no idea why the return value from the DLookup = Null but the
IsNull code still runs!

Any ideas anyone?

-paul

So defining intDogID as an integer is a conflict
I supect that the line intDogID = Me.txtDogID should be LngDogID =
CLng(txtDogID)
Have a look at Allen Browne's websitehttp://allenbrowne.com/ser-42.html
This is a much faster version of DLookup and although it appears a bit
complicated just use it inslead of DLookup
so your line should read

varReturnVal = ELookup("tDogPicLoc", "dogs", "iDog_ID = " & LngDogID)
Assuming you are loading the picture into an Image Control, if there
is no
image the picture is (none) so that the if clause should be

If IsNull(varReturnVal) Then
Me.imgDogPic.Picture = "(none)" ' <-- This code runs although
varReturnVal in debug is Null
Else
Me.imgDogPic.Picture = varReturnVal
End If

Phil

"PW" <paulremove_williamson...@removehotmail.comwrote in message
news:nq********************************@4ax.com. ..
Hi All,

I go into debug mode with the code below and varReturnVal is Null,
but
the code still is run.

Any idea why?

Dim varReturnVal As Variant
Dim intDogID As Integer

intDogID = Me.txtDogID

varReturnVal = DLookup("[tDogPicLoc]", "dogs", "[iDog_ID]= " &
intDogID)

If IsNull(varReturnVal) Then
Me.imgDogPic.Picture = "" ' <-- This code runs although
varReturnVal in debug is Null
Else
Me.imgDogPic.Picture = varReturnVal
End If

End Sub- Hide quoted text -

- Show quoted text -


It's happening because the dlookup is returning a null. Remember, a
variant will accept a null as a value, whereas a typed variable won't.
If the types of iDog_ID and intDogID don't match the dlookup could
well return a null regardless, so check that you've also typed the
iDogID field as an integer, to match intDogID.

I wouldn't worry too much about the speed of dlookup unless you have
an old, slow machine - it's perfectly ok in most cases, although
Allen's elookup does improve one or two other things as well as speed.
If dlookup works ok without the frilly bits then don't go to the
complication of using elookup.

One of the main deficiencies in your code is that it is always best to
use typed variables where possible and wrap dlookup with nz, as in -

dim varReturnVal as string
varReturnVal =nz(dLookup("tDogPicLoc", "dogs", "iDog_ID =
" & LngDogID))

You can then test with

If len(varReturnVal) =0 Then
Me.imgDogPic.Picture = "(none)"
Else
Me.imgDogPic.Picture = varReturnVal
End If

Finally, and a long shot, if you've by some chance left the iDog_ID
field typed as text (the default) in the table you'll always get a
null returned. And yes, it would be better even at this stage to
convert iDog_ID into an autonumber field.

Dave

It works but once a picture is located, all the other records/dogs
will end up with the same picture. Gotta look at all the code I
guess!

-pw

Oct 24 '08 #7

P: n/a
On Oct 22, 11:48*pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
If IsNull(varReturnVal) Then
* * Me.imgDogPic.Picture = "" * * ' <-- This *code runs *although
varReturnVal in debug is Null
I'm very old so perhaps my brain has fused but:
Isn't that what it should do?
Oct 24 '08 #8

P: n/a
rkc
On Oct 24, 8:06*am, lyle fairfield <lyle.fairfi...@gmail.comwrote:
On Oct 22, 11:48*pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
If IsNull(varReturnVal) Then
* * Me.imgDogPic.Picture = "" * * ' <-- This *code runs *although
varReturnVal in debug is Null

I'm very old so perhaps my brain has fused but:
Isn't that what it should do?
Apparently it's just what it's being told to do, not what it should do.
Oct 24 '08 #9

P: n/a
PW
On Fri, 24 Oct 2008 10:13:34 +0100, "Phil Stanton"
<ph**@myfamilyname.co.ukwrote:
>If you're talking about continuous forms, all the pictures will be of the
dog on your current record. The image is not bound and I don't think there
is a way round this problem.
It is very similar to having a label on a continuous form which appears
identically on each repetition of the form.

Phil
Thanks Phil. I think that is the case. If I print the report for one
dog, then the proper picture comes up. For all dogs, then the same
picture gets loaded for all.

I will stop working on this then. Thanks again.

-paul
>
"PW" <pa**********************@removehotmail.comwrote in message
news:qq********************************@4ax.com.. .
>On Thu, 23 Oct 2008 15:22:21 -0700 (PDT), daved
<da*******@googlemail.comwrote:
>>>On Oct 23, 7:56 pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
On Thu, 23 Oct 2008 09:09:39 +0100, "Phil Stanton"

<p...@myfamilyname.co.ukwrote:
A number of points.

I would expect your iDog_ID should be an AutoNumber (Long)

If I knew what I was doing way back then, it would have been an
AutoNumber, but I made it an integer and update it manually in code
:-)

Thanks for the tip though!

I have no idea why the return value from the DLookup = Null but the
IsNull code still runs!

Any ideas anyone?

-paul

So defining intDogID as an integer is a conflict
I supect that the line intDogID = Me.txtDogID should be LngDogID =
CLng(txtDogID)
Have a look at Allen Browne's websitehttp://allenbrowne.com/ser-42.html
This is a much faster version of DLookup and although it appears a bit
complicated just use it inslead of DLookup
so your line should read

varReturnVal = ELookup("tDogPicLoc", "dogs", "iDog_ID = " & LngDogID)
Assuming you are loading the picture into an Image Control, if there
is no
image the picture is (none) so that the if clause should be

If IsNull(varReturnVal) Then
Me.imgDogPic.Picture = "(none)" ' <-- This code runs although
varReturnVal in debug is Null
Else
Me.imgDogPic.Picture = varReturnVal
End If

Phil

"PW" <paulremove_williamson...@removehotmail.comwrote in message
news:nq********************************@4ax.com ...
Hi All,

I go into debug mode with the code below and varReturnVal is Null,
but
the code still is run.

Any idea why?

Dim varReturnVal As Variant
Dim intDogID As Integer

intDogID = Me.txtDogID

varReturnVal = DLookup("[tDogPicLoc]", "dogs", "[iDog_ID]= " &
intDogID)

If IsNull(varReturnVal) Then
Me.imgDogPic.Picture = "" ' <-- This code runs although
varReturnVal in debug is Null
Else
Me.imgDogPic.Picture = varReturnVal
End If

End Sub- Hide quoted text -

- Show quoted text -
It's happening because the dlookup is returning a null. Remember, a
variant will accept a null as a value, whereas a typed variable won't.
If the types of iDog_ID and intDogID don't match the dlookup could
well return a null regardless, so check that you've also typed the
iDogID field as an integer, to match intDogID.

I wouldn't worry too much about the speed of dlookup unless you have
an old, slow machine - it's perfectly ok in most cases, although
Allen's elookup does improve one or two other things as well as speed.
If dlookup works ok without the frilly bits then don't go to the
complication of using elookup.

One of the main deficiencies in your code is that it is always best to
use typed variables where possible and wrap dlookup with nz, as in -

dim varReturnVal as string
varReturnVal =nz(dLookup("tDogPicLoc", "dogs", "iDog_ID =
" & LngDogID))

You can then test with

If len(varReturnVal) =0 Then
Me.imgDogPic.Picture = "(none)"
Else
Me.imgDogPic.Picture = varReturnVal
End If

Finally, and a long shot, if you've by some chance left the iDog_ID
field typed as text (the default) in the table you'll always get a
null returned. And yes, it would be better even at this stage to
convert iDog_ID into an autonumber field.

Dave

It works but once a picture is located, all the other records/dogs
will end up with the same picture. Gotta look at all the code I
guess!

-pw
Oct 24 '08 #10

P: n/a
PW
On Fri, 24 Oct 2008 05:06:31 -0700 (PDT), lyle fairfield
<ly************@gmail.comwrote:
>On Oct 22, 11:48*pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
>If IsNull(varReturnVal) Then
* * Me.imgDogPic.Picture = "" * * ' <-- This *code runs *although
varReturnVal in debug is Null

I'm very old so perhaps my brain has fused but:
Isn't that what it should do?
LOL! I didn't type it correctly. Yes, that code is doing what it is
supposed to do :-)

-paulw
Oct 24 '08 #11

P: n/a
Maybe ... (Air! Code)

imgDogPic.Picture = Nz(DLookup("[tDogPicLoc]", "dogs", "[iDog_ID]= "
&
txtDogID), "")

.... unless you're bucking for promotion to MVP in which case you'll
want to add to the two redundant variables and the ten unnecessary
lines of code ... and add a totally superfluous error handling
routine.

On Oct 24, 12:15*pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
On Fri, 24 Oct 2008 05:06:31 -0700 (PDT), lyle fairfield

<lyle.fairfi...@gmail.comwrote:
On Oct 22, 11:48*pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
If IsNull(varReturnVal) Then
* * Me.imgDogPic.Picture = "" * * ' <-- This *code runs *although
varReturnVal in debug is Null
I'm very old so perhaps my brain has fused but:
Isn't that what it should do?

LOL! *I didn't type it correctly. *Yes, that code is doing what it is
supposed to do :-)

-paulw
Oct 24 '08 #12

P: n/a
PW
On Fri, 24 Oct 2008 09:58:09 -0700 (PDT), lyle fairfield
<ly************@gmail.comwrote:
>Maybe ... (Air! Code)
Airhead I am sometimes! :-)
>
imgDogPic.Picture = Nz(DLookup("[tDogPicLoc]", "dogs", "[iDog_ID]= "
&
txtDogID), "")
Brevity is the sole of wit. :-) I like it! One line of code.

I don't think I've ever used the Nz function. I am studying the help
file now!

Thanks,

-paulw

>... unless you're bucking for promotion to MVP in which case you'll
want to add to the two redundant variables and the ten unnecessary
lines of code ... and add a totally superfluous error handling
routine.

On Oct 24, 12:15*pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
>On Fri, 24 Oct 2008 05:06:31 -0700 (PDT), lyle fairfield

<lyle.fairfi...@gmail.comwrote:
>On Oct 22, 11:48*pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
If IsNull(varReturnVal) Then
* * Me.imgDogPic.Picture = "" * * ' <-- This *code runs *although
varReturnVal in debug is Null
>I'm very old so perhaps my brain has fused but:
Isn't that what it should do?

LOL! *I didn't type it correctly. *Yes, that code is doing what it is
supposed to do :-)

-paulw
Oct 24 '08 #13

P: n/a
PW
Too bad I can't use it to print test results for all dogs (only
specific dogies) because when Access finds the first picture, all dogs
after it contain the same picture. Oh well, at least I learned a few
things in the process!

-paulw
On Fri, 24 Oct 2008 09:58:09 -0700 (PDT), lyle fairfield
<ly************@gmail.comwrote:
>Maybe ... (Air! Code)

imgDogPic.Picture = Nz(DLookup("[tDogPicLoc]", "dogs", "[iDog_ID]= "
&
txtDogID), "")

... unless you're bucking for promotion to MVP in which case you'll
want to add to the two redundant variables and the ten unnecessary
lines of code ... and add a totally superfluous error handling
routine.

On Oct 24, 12:15*pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
>On Fri, 24 Oct 2008 05:06:31 -0700 (PDT), lyle fairfield

<lyle.fairfi...@gmail.comwrote:
>On Oct 22, 11:48*pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
If IsNull(varReturnVal) Then
* * Me.imgDogPic.Picture = "" * * ' <-- This *code runs *although
varReturnVal in debug is Null
>I'm very old so perhaps my brain has fused but:
Isn't that what it should do?

LOL! *I didn't type it correctly. *Yes, that code is doing what it is
supposed to do :-)

-paulw
Oct 24 '08 #14

P: n/a
rkc
On Oct 24, 2:46*pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
Too bad I can't use it to print test results for all dogs (only
specific dogies) because when Access finds the first picture, all dogs
after it contain the same picture. *Oh well, at least I learned a few
things in the process!
Why don't you create a report to print the test results instead of
trying to print
a continuous form?
Oct 25 '08 #15

P: n/a
PW
On Sat, 25 Oct 2008 04:50:35 -0700 (PDT), rkc <rk*@rkcny.comwrote:
>On Oct 24, 2:46*pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
>Too bad I can't use it to print test results for all dogs (only
specific dogies) because when Access finds the first picture, all dogs
after it contain the same picture. *Oh well, at least I learned a few
things in the process!

Why don't you create a report to print the test results instead of
trying to print
a continuous form?
It is a report.

-pw

Oct 25 '08 #16

P: n/a
There is no problem having different furry friends on a continous report;
just difficult (or impossible) with continuous forms.

On the OnFormat of the report detail you need to use the routine to load
your picture and set that as the picture property of your image.

Phil

"PW" <pa**********************@removehotmail.comwrote in message
news:l4********************************@4ax.com...
On Sat, 25 Oct 2008 04:50:35 -0700 (PDT), rkc <rk*@rkcny.comwrote:
>>On Oct 24, 2:46 pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
>>Too bad I can't use it to print test results for all dogs (only
specific dogies) because when Access finds the first picture, all dogs
after it contain the same picture. Oh well, at least I learned a few
things in the process!

Why don't you create a report to print the test results instead of
trying to print
a continuous form?

It is a report.

-pw

Oct 26 '08 #17

P: n/a
PW
On Sun, 26 Oct 2008 08:36:06 -0000, "Phil Stanton"
<ph**@myfamilyname.co.ukwrote:
>There is no problem having different furry friends on a continous report;
just difficult (or impossible) with continuous forms.

On the OnFormat of the report detail you need to use the routine to load
your picture and set that as the picture property of your image.

Phil
All dogs have the same picture with the following code (in the
OnFormat routine of the detail section), even though strReturnVal =
"". Also, putting the code there instead of the Print section slows
down the report. A window pops up to load the image and it takes a
while to do o. Report also flickers.
Dim strReturnVal As String

strReturnVal = Nz(DLookup("[tDogPicLoc]", "dogs", "[iDog_ID]= " &
txtDogID), "")

If strReturnVal = "" Then

Me.imgDogPic.Picture = ""

Else

Me.imgDogPic.Picture = strReturnVal

End If

Thanks,

-paul

>
"PW" <pa**********************@removehotmail.comwrote in message
news:l4********************************@4ax.com.. .
>On Sat, 25 Oct 2008 04:50:35 -0700 (PDT), rkc <rk*@rkcny.comwrote:
>>>On Oct 24, 2:46 pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
Too bad I can't use it to print test results for all dogs (only
specific dogies) because when Access finds the first picture, all dogs
after it contain the same picture. Oh well, at least I learned a few
things in the process!

Why don't you create a report to print the test results instead of
trying to print
a continuous form?

It is a report.

-pw
Oct 26 '08 #18

P: n/a
Hi Paul

The following works for me in my Trohy database (Sailing not shooting), and
it is the OnFormat, not OnPrint

for some reason setting the picture to (none) is not working, don't know
why, but making the picture invisible works just as well.

MOST IMPORTANT. When you create the image on the report, you always have to
add a valid image. After you have done so you must change the image picture
to (none). Access then asks if you want to remove the picture to which you
reply yes.

Assuming you are using JPEGs of the dogs, you may need to patch the
registry. On your own head be it, but the note
'HKEY_LOCAL_MACHINE\Software\Microsoft\ Shared Tools\Graphics
Filters\Import\JPEG\Options ShowProgressDialog
may well say Yes, which shows a sort of progress bar as the image is loaded.
I think there are similar references to other sorts of Graphic Filters.
Anyway you need to change that registry entry to No
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

'HKEY_LOCAL_MACHINE\Software\Microsoft\ Shared Tools\Graphics
Filters\Import\JPEG\Options ShowProgressDialog No

Dim StrReturnVal As String

'StrReturnVal = Nz(DLookup("PhotoName", "Trophies", "TrophyID = " &
TrophyID))
StrReturnVal = Nz(DLookup("[tDogPicLoc]", "dogs", "[iDog_ID]= " &
txtDogID), "")

If StrReturnVal = "" Then
imgDogPic.Visible = False
'TrophyPic.Visible = False
Else
Me.imgDogPic.Picture = StrReturnVal
imgDogPic.Visible = True
'TrophyPic.Visible = True
'TrophyPic.Picture = StrReturnVal
End If

End Sub

Phil

"PW" <pa**********************@removehotmail.comwrote in message
news:oq********************************@4ax.com...
On Sun, 26 Oct 2008 08:36:06 -0000, "Phil Stanton"
<ph**@myfamilyname.co.ukwrote:
>>There is no problem having different furry friends on a continous report;
just difficult (or impossible) with continuous forms.

On the OnFormat of the report detail you need to use the routine to load
your picture and set that as the picture property of your image.

Phil

All dogs have the same picture with the following code (in the
OnFormat routine of the detail section), even though strReturnVal =
"". Also, putting the code there instead of the Print section slows
down the report. A window pops up to load the image and it takes a
while to do o. Report also flickers.
Dim strReturnVal As String

strReturnVal = Nz(DLookup("[tDogPicLoc]", "dogs", "[iDog_ID]= " &
txtDogID), "")

If strReturnVal = "" Then

Me.imgDogPic.Picture = ""

Else

Me.imgDogPic.Picture = strReturnVal

End If

Thanks,

-paul

>>
"PW" <pa**********************@removehotmail.comwrote in message
news:l4********************************@4ax.com. ..
>>On Sat, 25 Oct 2008 04:50:35 -0700 (PDT), rkc <rk*@rkcny.comwrote:

On Oct 24, 2:46 pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
Too bad I can't use it to print test results for all dogs (only
specific dogies) because when Access finds the first picture, all dogs
after it contain the same picture. Oh well, at least I learned a few
things in the process!

Why don't you create a report to print the test results instead of
trying to print
a continuous form?
It is a report.

-pw

Oct 26 '08 #19

P: n/a
I ment to mention

If the picture is not found at the location specified by your DLookup
statement, you will get an error. On way out of this is to create a picture
(use Paint) which says "Picture Missing" say MissingPic.Jpg
Your error routine would load this picture instead

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

'HKEY_LOCAL_MACHINE\Software\Microsoft\ Shared Tools\Graphics
Filters\Import\JPEG\Options ShowProgressDialog No

Dim StrReturnVal As String
Dim Path As String

Path = "C:\Documents and Settings\Phil\My Documents\Access\MDB\WFYC
Trophies\Trophy Photos\" ' Your path where the photos are stored

On Error GoTo Detail_Format_Err

StrReturnVal = Nz(DLookup("[tDogPicLoc]", "dogs", "[iDog_ID]= " &
txtDogID), "")

If StrReturnVal = "" Then
imgDogPic.Visible = False
Else
imgDogPic.Picture = StrReturnVal
imgDogPic.Visible = True
End If

Exit Sub

Detail_Format_Err:

If Err = 2220 Then
imgDogPic.Picture = Path & "MissingPic.Jpg"
Resume Next
Else
MsgBox Err.Description
End If

End Sub
Phil
"Phil Stanton" <ph**@myfamilyname.co.ukwrote in message
news:Lv******************************@posted.plusn et...
Hi Paul

The following works for me in my Trohy database (Sailing not shooting),
and it is the OnFormat, not OnPrint

for some reason setting the picture to (none) is not working, don't know
why, but making the picture invisible works just as well.

MOST IMPORTANT. When you create the image on the report, you always have
to add a valid image. After you have done so you must change the image
picture to (none). Access then asks if you want to remove the picture to
which you reply yes.

Assuming you are using JPEGs of the dogs, you may need to patch the
registry. On your own head be it, but the note
'HKEY_LOCAL_MACHINE\Software\Microsoft\ Shared Tools\Graphics
Filters\Import\JPEG\Options ShowProgressDialog
may well say Yes, which shows a sort of progress bar as the image is
loaded. I think there are similar references to other sorts of Graphic
Filters. Anyway you need to change that registry entry to No
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

'HKEY_LOCAL_MACHINE\Software\Microsoft\ Shared Tools\Graphics
Filters\Import\JPEG\Options ShowProgressDialog No

Dim StrReturnVal As String

'StrReturnVal = Nz(DLookup("PhotoName", "Trophies", "TrophyID = " &
TrophyID))
StrReturnVal = Nz(DLookup("[tDogPicLoc]", "dogs", "[iDog_ID]= " &
txtDogID), "")

If StrReturnVal = "" Then
imgDogPic.Visible = False
'TrophyPic.Visible = False
Else
Me.imgDogPic.Picture = StrReturnVal
imgDogPic.Visible = True
'TrophyPic.Visible = True
'TrophyPic.Picture = StrReturnVal
End If

End Sub

Phil

"PW" <pa**********************@removehotmail.comwrote in message
news:oq********************************@4ax.com...
>On Sun, 26 Oct 2008 08:36:06 -0000, "Phil Stanton"
<ph**@myfamilyname.co.ukwrote:
>>>There is no problem having different furry friends on a continous report;
just difficult (or impossible) with continuous forms.

On the OnFormat of the report detail you need to use the routine to load
your picture and set that as the picture property of your image.

Phil

All dogs have the same picture with the following code (in the
OnFormat routine of the detail section), even though strReturnVal =
"". Also, putting the code there instead of the Print section slows
down the report. A window pops up to load the image and it takes a
while to do o. Report also flickers.
Dim strReturnVal As String

strReturnVal = Nz(DLookup("[tDogPicLoc]", "dogs", "[iDog_ID]= " &
txtDogID), "")

If strReturnVal = "" Then

Me.imgDogPic.Picture = ""

Else

Me.imgDogPic.Picture = strReturnVal

End If

Thanks,

-paul

>>>
"PW" <pa**********************@removehotmail.comwrote in message
news:l4********************************@4ax.com ...
On Sat, 25 Oct 2008 04:50:35 -0700 (PDT), rkc <rk*@rkcny.comwrote:

>On Oct 24, 2:46 pm, PW <paulremove_williamson...@removehotmail.com>
>wrote:
>Too bad I can't use it to print test results for all dogs (only
>specific dogies) because when Access finds the first picture, all
>dogs
>after it contain the same picture. Oh well, at least I learned a few
>things in the process!
>
>Why don't you create a report to print the test results instead of
>trying to print
>a continuous form?
>

It is a report.

-pw



Oct 27 '08 #20

P: n/a
rkc
On Oct 25, 1:00*pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
On Sat, 25 Oct 2008 04:50:35 -0700 (PDT), rkc <r...@rkcny.comwrote:
On Oct 24, 2:46*pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
Too bad I can't use it to print test results for all dogs (only
specific dogies) because when Access finds the first picture, all dogs
after it contain the same picture. *Oh well, at least I learned a few
things in the process!
Why don't you create a report to print the test results instead of
trying to print
a continuous form?

It is a report.

-pw
Great. Then if all else fails create a default image file for dogs
that have no
image file and set the Picture value to the default image file instead
of an
empty string.
Oct 27 '08 #21

P: n/a
Hi Paul

There are 2 possible error conditions.

One is there is no photo of a particular dog in which case the tDogPicLoc should be either "" or null

The other is that tDogPicLoc has a value in it like "MyDoggy.Jpg" or even "My Documents\Dog Pics\MyDoggy.Jpg" but the picture is not there.

SoI was suggesting 2 dummy pictures, 1 where there is no photo and the other where the location of a photo is defined, but is not where it should be

Phil
"rkc" <rk*@rkcny.comwrote in message news:d5**********************************@75g2000h so.googlegroups.com...
On Oct 25, 1:00 pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
On Sat, 25 Oct 2008 04:50:35 -0700 (PDT), rkc <r...@rkcny.comwrote:
On Oct 24, 2:46 pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
Too bad I can't use it to print test results for all dogs (only
specific dogies) because when Access finds the first picture, all dogs
after it contain the same picture. Oh well, at least I learned a few
things in the process!
Why don't you create a report to print the test results instead of
trying to print
a continuous form?

It is a report.

-pw
Great. Then if all else fails create a default image file for dogs
that have no
image file and set the Picture value to the default image file instead
of an
empty string.
Oct 27 '08 #22

P: n/a
rkc
On Oct 26, 4:36*am, "Phil Stanton" <p...@myfamilyname.co.ukwrote:
There is no problem having different furry friends on a continous report;
just difficult (or impossible) with continuous forms.

On the OnFormat of the report detail you need to use the routine to load
your picture and set that as the picture property of your image.

Phil

"PW" <paulremove_williamson...@removehotmail.comwrote in message

news:l4********************************@4ax.com...
On Sat, 25 Oct 2008 04:50:35 -0700 (PDT), rkc <r...@rkcny.comwrote:
>On Oct 24, 2:46 pm, PW <paulremove_williamson...@removehotmail.com>
wrote:
Too bad I can't use it to print test results for all dogs (only
specific dogies) because when Access finds the first picture, all dogs
after it contain the same picture. Oh well, at least I learned a few
things in the process!
>Why don't you create a report to print the test results instead of
trying to print
a continuous form?
It is a report.
-pw
Just to add to that, if trying to set the Picture property to a null
string is causing
the previous image to repeat then just create a default image to load
when there
is no associated image.
Oct 28 '08 #23

This discussion thread is closed

Replies have been disabled for this discussion.