if then statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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]
 
If Forms![frm_report].[snumber] = Forms![frm_verify].[check] Then
Forms![frm_report].[snumber] = Forms![frm_report].[snumber] & " (R)"
Else
DoCmd.CancelEvent
End If
 
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
__________________________________________

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

John Vinson said:
Care to post your code?


John W. Vinson[MVP]
 
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
__________________________________________

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

John Vinson said:
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]
 
Back
Top