Error checking code

G

GLT

Hi,

I have the following code which checks for nulls and if the field(s) are
null, then it will turn the boarder of the box (which all the fields are
listed in) and the field label red.

When the field has been updated with a value, then the colours will change
from red back to green.

This works fine, except it will not change the last "red" field back to green.

For example, when I test it, if there are three fields that turn red, then
after I update the three fields one by one, they all turn green except the
last field.

If anyone could advise what is wrong with this logic I would be most greatful:

Dim errTag As Variant

errTag = 0

If IsNull(Me![cmb_BoxStat]) Or IsNull(Me![cmb_RecBy]) Or IsNull(Me![Quantum
No]) Or (IsNull(Me![cmb_RecBy])) Or IsNull(Me![Recall Date]) Or
IsNull(Me![cmb_Urg]) Then

If IsNull(Me![cmb_BoxStat]) Then
Me![lbl_BoxStat].ForeColor = 255
Me![Box23].SpecialEffect = 0
Me![Box23].BorderColor = 255
errTag = 1

Else

Me![lbl_BoxStat].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![cmb_RecBy]) Then
Me![lbl_RecBy].ForeColor = 255
Me![Box23].SpecialEffect = 0
Me![Box23].BorderColor = 255
errTag = 2

Else

Me![lbl_RecBy].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![Quantum No]) Then
Me![lbl_QtnNo].ForeColor = 255
Me![Box23].SpecialEffect = 0
errTag = 3

Else

Me![lbl_QtnNo].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![cmb_RecBy]) Then
Me![lbl_RecBy].ForeColor = 255
Me![Box23].SpecialEffect = 0
errTag = 4

Else

Me![lbl_RecBy].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![Recall Date]) Then
Me![lbl_RecDte].ForeColor = 255
Me![Box23].SpecialEffect = 0
errTag = 5

Else

Me![lbl_RecDte].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![cmb_Urg]) Then
Me![lbl_Urg].ForeColor = 255
Me![Box23].SpecialEffect = 0
errTag = 6

Else

Me![lbl_Urg].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

End If

If (Me![cmb_BoxStat] = "WAITING - ON ORDER") And (errTag = 0) Then

MsgBox "WOULD YOU LIKE TO SEND AN EMAIL?", vbOKCancel, "EMAIL NOTIFICATION"

SendMessage True

End If

End Sub
 
J

JamesDeckert

Where is this code placed?
I think you'd need to call this routine from the On_Update event of every
field.
Does it work the same way if you fill the fields in starting with the last
field and progressing up? In other words, is it the last field in time which
isn't working or the last field on the form? If the last field in time, you
have a timing problem (which event is calling the code). If the last field on
the form, step through the code in debug to watch it execute.

James

GLT said:
Hi,

I have the following code which checks for nulls and if the field(s) are
null, then it will turn the boarder of the box (which all the fields are
listed in) and the field label red.

When the field has been updated with a value, then the colours will change
from red back to green.

This works fine, except it will not change the last "red" field back to green.

For example, when I test it, if there are three fields that turn red, then
after I update the three fields one by one, they all turn green except the
last field.

If anyone could advise what is wrong with this logic I would be most greatful:

Dim errTag As Variant

errTag = 0

If IsNull(Me![cmb_BoxStat]) Or IsNull(Me![cmb_RecBy]) Or IsNull(Me![Quantum
No]) Or (IsNull(Me![cmb_RecBy])) Or IsNull(Me![Recall Date]) Or
IsNull(Me![cmb_Urg]) Then

If IsNull(Me![cmb_BoxStat]) Then
Me![lbl_BoxStat].ForeColor = 255
Me![Box23].SpecialEffect = 0
Me![Box23].BorderColor = 255
errTag = 1

Else

Me![lbl_BoxStat].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![cmb_RecBy]) Then
Me![lbl_RecBy].ForeColor = 255
Me![Box23].SpecialEffect = 0
Me![Box23].BorderColor = 255
errTag = 2

Else

Me![lbl_RecBy].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![Quantum No]) Then
Me![lbl_QtnNo].ForeColor = 255
Me![Box23].SpecialEffect = 0
errTag = 3

Else

Me![lbl_QtnNo].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![cmb_RecBy]) Then
Me![lbl_RecBy].ForeColor = 255
Me![Box23].SpecialEffect = 0
errTag = 4

Else

Me![lbl_RecBy].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![Recall Date]) Then
Me![lbl_RecDte].ForeColor = 255
Me![Box23].SpecialEffect = 0
errTag = 5

Else

Me![lbl_RecDte].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![cmb_Urg]) Then
Me![lbl_Urg].ForeColor = 255
Me![Box23].SpecialEffect = 0
errTag = 6

Else

Me![lbl_Urg].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

End If

If (Me![cmb_BoxStat] = "WAITING - ON ORDER") And (errTag = 0) Then

MsgBox "WOULD YOU LIKE TO SEND AN EMAIL?", vbOKCancel, "EMAIL NOTIFICATION"

SendMessage True

End If

End Sub
Click to expand...
 
G

GLT

Hi James,

I have an 'Update Details' button that this code is attached to.

So everytime I press the update details button, it does this checking to
ensure fields are not null before sending an email (which uses those fields).

I just tested it again and it does not matter which order the field values
are entered - it is whatever (the last field value entered which had turned
red) will not turn green.

It like its exiting the code too early ...






JamesDeckert said:
Where is this code placed?
I think you'd need to call this routine from the On_Update event of every
field.
Does it work the same way if you fill the fields in starting with the last
field and progressing up? In other words, is it the last field in time which
isn't working or the last field on the form? If the last field in time, you
have a timing problem (which event is calling the code). If the last field on
the form, step through the code in debug to watch it execute.

James

GLT said:
Hi,

I have the following code which checks for nulls and if the field(s) are
null, then it will turn the boarder of the box (which all the fields are
listed in) and the field label red.

When the field has been updated with a value, then the colours will change
from red back to green.

This works fine, except it will not change the last "red" field back to green.

For example, when I test it, if there are three fields that turn red, then
after I update the three fields one by one, they all turn green except the
last field.

If anyone could advise what is wrong with this logic I would be most greatful:

Dim errTag As Variant

errTag = 0

If IsNull(Me![cmb_BoxStat]) Or IsNull(Me![cmb_RecBy]) Or IsNull(Me![Quantum
No]) Or (IsNull(Me![cmb_RecBy])) Or IsNull(Me![Recall Date]) Or
IsNull(Me![cmb_Urg]) Then

If IsNull(Me![cmb_BoxStat]) Then
Me![lbl_BoxStat].ForeColor = 255
Me![Box23].SpecialEffect = 0
Me![Box23].BorderColor = 255
errTag = 1

Else

Me![lbl_BoxStat].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![cmb_RecBy]) Then
Me![lbl_RecBy].ForeColor = 255
Me![Box23].SpecialEffect = 0
Me![Box23].BorderColor = 255
errTag = 2

Else

Me![lbl_RecBy].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![Quantum No]) Then
Me![lbl_QtnNo].ForeColor = 255
Me![Box23].SpecialEffect = 0
errTag = 3

Else

Me![lbl_QtnNo].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![cmb_RecBy]) Then
Me![lbl_RecBy].ForeColor = 255
Me![Box23].SpecialEffect = 0
errTag = 4

Else

Me![lbl_RecBy].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![Recall Date]) Then
Me![lbl_RecDte].ForeColor = 255
Me![Box23].SpecialEffect = 0
errTag = 5

Else

Me![lbl_RecDte].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![cmb_Urg]) Then
Me![lbl_Urg].ForeColor = 255
Me![Box23].SpecialEffect = 0
errTag = 6

Else

Me![lbl_Urg].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

End If

If (Me![cmb_BoxStat] = "WAITING - ON ORDER") And (errTag = 0) Then

MsgBox "WOULD YOU LIKE TO SEND AN EMAIL?", vbOKCancel, "EMAIL NOTIFICATION"

SendMessage True

End If

End Sub
Click to expand...
Click to expand...
 
G

GLT

Ok I fixed it now - there was nothing at the end of the code to reset all
colors if the all tests succeeded. I put the following code between the else
and endif statements and it works fine:

Else

Me![lbl_BoxStat].ForeColor = 8421376
Me![lbl_QtnNo].ForeColor = 8421376
Me![lbl_RecBy].ForeColor = 8421376
Me![lbl_RecDte].ForeColor = 8421376
Me![lbl_Urg].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

EndIf

GLT said:
Hi James,

I have an 'Update Details' button that this code is attached to.

So everytime I press the update details button, it does this checking to
ensure fields are not null before sending an email (which uses those fields).

I just tested it again and it does not matter which order the field values
are entered - it is whatever (the last field value entered which had turned
red) will not turn green.

It like its exiting the code too early ...






JamesDeckert said:
Where is this code placed?
I think you'd need to call this routine from the On_Update event of every
field.
Does it work the same way if you fill the fields in starting with the last
field and progressing up? In other words, is it the last field in time which
isn't working or the last field on the form? If the last field in time, you
have a timing problem (which event is calling the code). If the last field on
the form, step through the code in debug to watch it execute.

James

GLT said:
Hi,

I have the following code which checks for nulls and if the field(s) are
null, then it will turn the boarder of the box (which all the fields are
listed in) and the field label red.

When the field has been updated with a value, then the colours will change
from red back to green.

This works fine, except it will not change the last "red" field back to green.

For example, when I test it, if there are three fields that turn red, then
after I update the three fields one by one, they all turn green except the
last field.

If anyone could advise what is wrong with this logic I would be most greatful:

Dim errTag As Variant

errTag = 0

If IsNull(Me![cmb_BoxStat]) Or IsNull(Me![cmb_RecBy]) Or IsNull(Me![Quantum
No]) Or (IsNull(Me![cmb_RecBy])) Or IsNull(Me![Recall Date]) Or
IsNull(Me![cmb_Urg]) Then

If IsNull(Me![cmb_BoxStat]) Then
Me![lbl_BoxStat].ForeColor = 255
Me![Box23].SpecialEffect = 0
Me![Box23].BorderColor = 255
errTag = 1

Else

Me![lbl_BoxStat].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![cmb_RecBy]) Then
Me![lbl_RecBy].ForeColor = 255
Me![Box23].SpecialEffect = 0
Me![Box23].BorderColor = 255
errTag = 2

Else

Me![lbl_RecBy].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![Quantum No]) Then
Me![lbl_QtnNo].ForeColor = 255
Me![Box23].SpecialEffect = 0
errTag = 3

Else

Me![lbl_QtnNo].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![cmb_RecBy]) Then
Me![lbl_RecBy].ForeColor = 255
Me![Box23].SpecialEffect = 0
errTag = 4

Else

Me![lbl_RecBy].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![Recall Date]) Then
Me![lbl_RecDte].ForeColor = 255
Me![Box23].SpecialEffect = 0
errTag = 5

Else

Me![lbl_RecDte].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![cmb_Urg]) Then
Me![lbl_Urg].ForeColor = 255
Me![Box23].SpecialEffect = 0
errTag = 6

Else

Me![lbl_Urg].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

End If

If (Me![cmb_BoxStat] = "WAITING - ON ORDER") And (errTag = 0) Then

MsgBox "WOULD YOU LIKE TO SEND AN EMAIL?", vbOKCancel, "EMAIL NOTIFICATION"

SendMessage True

End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 

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

Changing Combobox to text box 4
For each loop 3
Forecolor Conditional Formating 5
Working outlook via vba code with xlsx - error 0
ForeColor 3
Change color of duplicate fields in a form 5
checking for records need a better way 5
Access 2007 Continuous form vba code 1

Top