If . . . Then On Current Event

G

Guest

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?
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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.
 
G

Guest

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

Close Message box 1
Trouble with Focus-Subform to MainForm 2
remove duplicate email addresses on bulk email 1
Report preview behind database view 3
Renaming Reports For Emailing 6
Startup and forms caption 2
Email from Access 2003 1
VBA problem (I think) to check for data entry 9

Top