if then statement

[Guest]

i have an If Then statement on the close event of a form, which is based on a
query.
if the form/query doesnt find anything i get an error "you entered an
expression that has no value".
what code would a i use to keep from getting this error?
thanks in advance.

 

[Guest]

Hi Lou,

You can likely use the Nz function, but it's kind of difficult to say with
any certainty. Please post the code so that we can see exactly what is going
on.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[John Vinson]

i have an If Then statement on the close event of a form, which is based on a
query.
if the form/query doesnt find anything i get an error "you entered an
expression that has no value".
what code would a i use to keep from getting this error?
thanks in advance.

Care to post your code?


John W. Vinson[MVP]

 

[Guest]

If Forms![frm_report].[snumber] = Forms![frm_verify].[check] Then
Forms![frm_report].[snumber] = Forms![frm_report].[snumber] & " (R)"
Else
DoCmd.CancelEvent
End If

 

[Guest]

Hi Lou,

I think you have a few possibilities. I'm assuming that "check" is the name
of a checkbox control. Is this correct?

The first possibility would be to use the On Click event procedure for the
checkbox to run code that validates that a value has been entered into the
snumber field, if this checkbox is true (or not false, ie <>0). That should
prevent the error from occuring in the code that runs in Form_Close.

The other possibility might be to use the Nz function, as I first alluded to:

If Forms![frm_report].[snumber] = Forms![frm_verify].[check] Then
Forms![frm_report].[snumber] = Nz(Forms![frm_report].[snumber], "") & "
(R)"
Else
DoCmd.CancelEvent
End If

I don't know if you would consider the second possibility an acceptable
alternative, since, in the example above, you would be concatenating R to a
zero-length string. You can use the Nz function to convert nulls to other
values, such as 0 (zero), or "Unknown" or....just about anything you want.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

If Forms![frm_report].[snumber] = Forms![frm_verify].[check] Then
Forms![frm_report].[snumber] = Forms![frm_report].[snumber] & " (R)"
Else
DoCmd.CancelEvent
End If

Care to post your code?


John W. Vinson[MVP]

 

[Guest]

Thanks for replying Tom. Turns out it wasn't my If..Then statement that was
the problem. It was the way I was opening the second form (frm_verify) that
was producing the error. I had a command button to run a macro to open and
close the form. Instead I used the DoCmd.OpenForm and DoCmd.Close to open
and close the form on the click event. And also, just so you know I wasn't
concatenating
a zero length string, I was adding it to the end of the value that was there
already.
Sorry if I didn't make that clear.
Thanks again, I appreciate your response.

Hi Lou,

I think you have a few possibilities. I'm assuming that "check" is the name
of a checkbox control. Is this correct?

The first possibility would be to use the On Click event procedure for the
checkbox to run code that validates that a value has been entered into the
snumber field, if this checkbox is true (or not false, ie <>0). That should
prevent the error from occuring in the code that runs in Form_Close.

The other possibility might be to use the Nz function, as I first alluded to:

If Forms![frm_report].[snumber] = Forms![frm_verify].[check] Then
Forms![frm_report].[snumber] = Nz(Forms![frm_report].[snumber], "") & "
(R)"
Else
DoCmd.CancelEvent
End If

I don't know if you would consider the second possibility an acceptable
alternative, since, in the example above, you would be concatenating R to a
zero-length string. You can use the Nz function to convert nulls to other
values, such as 0 (zero), or "Unknown" or....just about anything you want.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

If Forms![frm_report].[snumber] = Forms![frm_verify].[check] Then
Forms![frm_report].[snumber] = Forms![frm_report].[snumber] & " (R)"
Else
DoCmd.CancelEvent
End If

On Mon, 8 May 2006 18:02:01 -0700, LouD

i have an If Then statement on the close event of a form, which is based on a
query.
if the form/query doesnt find anything i get an error "you entered an
expression that has no value".
what code would a i use to keep from getting this error?
thanks in advance.

Care to post your code?


John W. Vinson[MVP]