If . . . Then On Current Event

J

Jim Ory

Using WinXP, Access2002

On a main form, using an unbound text box [status], using the On Current
event for the form I have the following:

Private Sub Form_Current()
On Error GoTo ProcError
'Moves Focus to Ham Call on record change
Me.strHamCall.SetFocus

If Not IsNull(Me.dteExpiration) Then
If Date >= Me.dteExpiration Then
Me.Status = "Expired"
Else: Me.Status = "Valid"
End If
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc

End Sub

If I statement out the first "If not . . ." and last "End If" lines, the
code works fine, except I get a "Valid" return for status with a null value
in [dteExpiration]. If I leave the lines intact, I get an error message, ". .
.. cannot find field, etc. ...."

How should I write this?
 
A

Aaron G

Jim,

Try:

If Isnull(Me.dteExpiration) = False Then
etc.

HTH

Aaron G
Philadelphia, PA
 
J

Jim Ory

Perfect. Thank you so much for your quick response and excellent advice.
--
Jim Ory


Aaron G said:
Jim,

Try:

If Isnull(Me.dteExpiration) = False Then
etc.

HTH

Aaron G
Philadelphia, PA

Jim Ory said:
Using WinXP, Access2002

On a main form, using an unbound text box [status], using the On Current
event for the form I have the following:

Private Sub Form_Current()
On Error GoTo ProcError
'Moves Focus to Ham Call on record change
Me.strHamCall.SetFocus

If Not IsNull(Me.dteExpiration) Then
If Date >= Me.dteExpiration Then
Me.Status = "Expired"
Else: Me.Status = "Valid"
End If
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc

End Sub

If I statement out the first "If not . . ." and last "End If" lines, the
code works fine, except I get a "Valid" return for status with a null value
in [dteExpiration]. If I leave the lines intact, I get an error message, ". .
. cannot find field, etc. ...."

How should I write this?
Click to expand...
Click to expand...
 
J

Jim Ory

Aaron,
Your advice removed the error message, but I still get "Valid" returned to
[status] if [dteExpiration] is null.

Am I missing a line to change [status] back to null if [dteExpiration] goes
back to null?
--
Jim Ory


Aaron G said:
Jim,

Try:

If Isnull(Me.dteExpiration) = False Then
etc.

HTH

Aaron G
Philadelphia, PA

Jim Ory said:
Using WinXP, Access2002

On a main form, using an unbound text box [status], using the On Current
event for the form I have the following:

Private Sub Form_Current()
On Error GoTo ProcError
'Moves Focus to Ham Call on record change
Me.strHamCall.SetFocus

If Not IsNull(Me.dteExpiration) Then
If Date >= Me.dteExpiration Then
Me.Status = "Expired"
Else: Me.Status = "Valid"
End If
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc

End Sub

If I statement out the first "If not . . ." and last "End If" lines, the
code works fine, except I get a "Valid" return for status with a null value
in [dteExpiration]. If I leave the lines intact, I get an error message, ". .
. cannot find field, etc. ...."

How should I write this?
Click to expand...
Click to expand...
 
A

Aaron G

Jim,

You are indeed missing a line that would void out your field. Here's what
you need:

If IsNull(Me.dteExpiration) = False Then
If Date >= Me.dteExpiration Then
Me.Status = "Expired"
Else: Me.Status = "Valid"
End If
Else 'this is what you were missing
Me.Status = Null 'you can replace 'Null' with '0' or double quotes if
that fits better
End If

Glad I could help, and I hope this solves it!

Aaron G
Philadelphia, PA

Jim Ory said:
Aaron,
Your advice removed the error message, but I still get "Valid" returned to
[status] if [dteExpiration] is null.

Am I missing a line to change [status] back to null if [dteExpiration] goes
back to null?
--
Jim Ory


Aaron G said:
Jim,

Try:

If Isnull(Me.dteExpiration) = False Then
etc.

HTH

Aaron G
Philadelphia, PA

Jim Ory said:
Using WinXP, Access2002

On a main form, using an unbound text box [status], using the On Current
event for the form I have the following:

Private Sub Form_Current()
On Error GoTo ProcError
'Moves Focus to Ham Call on record change
Me.strHamCall.SetFocus

If Not IsNull(Me.dteExpiration) Then
If Date >= Me.dteExpiration Then
Me.Status = "Expired"
Else: Me.Status = "Valid"
End If
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc

End Sub

If I statement out the first "If not . . ." and last "End If" lines, the
code works fine, except I get a "Valid" return for status with a null value
in [dteExpiration]. If I leave the lines intact, I get an error message, ". .
. cannot find field, etc. ...."

How should I write this?
Click to expand...
Click to expand...
Click to expand...
 
J

Jim Ory

Aaron,
That was it. Except it would not accept the values: Null or "", but does
accept " " or any combination between quotes.
Thanks again.
--
Jim Ory


Aaron G said:
Jim,

You are indeed missing a line that would void out your field. Here's what
you need:

If IsNull(Me.dteExpiration) = False Then
If Date >= Me.dteExpiration Then
Me.Status = "Expired"
Else: Me.Status = "Valid"
End If
Else 'this is what you were missing
Me.Status = Null 'you can replace 'Null' with '0' or double quotes if
that fits better
End If

Glad I could help, and I hope this solves it!

Aaron G
Philadelphia, PA

Jim Ory said:
Aaron,
Your advice removed the error message, but I still get "Valid" returned to
[status] if [dteExpiration] is null.

Am I missing a line to change [status] back to null if [dteExpiration] goes
back to null?
--
Jim Ory


Aaron G said:
Jim,

Try:

If Isnull(Me.dteExpiration) = False Then
etc.

HTH

Aaron G
Philadelphia, PA

:

Using WinXP, Access2002

On a main form, using an unbound text box [status], using the On Current
event for the form I have the following:

Private Sub Form_Current()
On Error GoTo ProcError
'Moves Focus to Ham Call on record change
Me.strHamCall.SetFocus

If Not IsNull(Me.dteExpiration) Then
If Date >= Me.dteExpiration Then
Me.Status = "Expired"
Else: Me.Status = "Valid"
End If
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc

End Sub

If I statement out the first "If not . . ." and last "End If" lines, the
code works fine, except I get a "Valid" return for status with a null value
in [dteExpiration]. If I leave the lines intact, I get an error message, ". .
. cannot find field, etc. ...."

How should I write this?
Click to expand...
Click to expand...
Click to expand...
 
R

Ron2005

sounds like "Status" has a DB definition constraint that says it
cannot be 0 length.

Change that and the Null will work.
 
J

Jim Ory

Ron,
You're right. [status] does not have a definition constraint and I tried
using 'null' again and that works also.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trying to remove error from conflicting code 0
Working outlook via vba code with xlsx - error 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Close Message box 1
AddPicturesToShapes 0
Dynamic Naming of Form Button 3
Making a macro only work on certain files 2
remove duplicate email addresses on bulk email 1

Top