Expression to Require Data Entry

[Sharon W]

Hello,
Here is what I am trying to do...I would like to require data entry for a
control on a form but only if it meets the condition where:
If Reason for Call = 2, 3, 4, or 5 then Warrant Type cannot be null.

Does this make sense?
Please help.

 

[Ken Sheridan]

in the form's BeforeInsert event procedure put:

Const conMESSAGE = "Warrent type must be entered."

If Me.[Reason for Call] >= 2 _
And [Reason for Call] <= 5 Then

Cancel = IsNull(Me.[Warrent Type])
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Me.[Warrent Type].SetFocus

End If

Ken Sheridan
Stafford, England

 

[Sharon W]

Hi Ken,
I did try this and it isn't working. Any other suggestions?

in the form's BeforeInsert event procedure put:

Const conMESSAGE = "Warrent type must be entered."

If Me.[Reason for Call] >= 2 _
And [Reason for Call] <= 5 Then

Cancel = IsNull(Me.[Warrent Type])
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Me.[Warrent Type].SetFocus

End If

Ken Sheridan
Stafford, England

Hello,
Here is what I am trying to do...I would like to require data entry for a
control on a form but only if it meets the condition where:
If Reason for Call = 2, 3, 4, or 5 then Warrant Type cannot be null.

Does this make sense?
Please help.

 

[Ken Sheridan]

In what way isn't it working? You are going to have to give us more to go on
than that.

Possibilities are:

1. Warrant Type, if a text data type column, contains a zero-length string
rather than being Null.

2. If it is a numeric foreign key column referencing a numeric, e.g. an
autonumber, primary key of another table, then it may well be zero rather
than Null by virtue of having zero as its DefaultValue property. If when
designing the form's underlying table you've used the look-up wizard this
won't be readily apparent as the real value of the column will be disguised
even in raw datasheet view (one of the many reasons why this feature should
not be used).

But without more information we can only second guess.

Ken Sheridan
Stafford, England

Hi Ken,
I did try this and it isn't working. Any other suggestions?

in the form's BeforeInsert event procedure put:

Const conMESSAGE = "Warrent type must be entered."

If Me.[Reason for Call] >= 2 _
And [Reason for Call] <= 5 Then

Cancel = IsNull(Me.[Warrent Type])
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Me.[Warrent Type].SetFocus

End If

Ken Sheridan
Stafford, England

Hello,
Here is what I am trying to do...I would like to require data entry for a
control on a form but only if it meets the condition where:
If Reason for Call = 2, 3, 4, or 5 then Warrant Type cannot be null.

Does this make sense?
Please help.

 

[Sharon W]

Hi Ken,
thank you for your response.
Warrant Type is in fact a Text Data Type and therefore contains a zero
length string.
Given this, do you have any suggested next step?

In what way isn't it working? You are going to have to give us more to go on
than that.

Possibilities are:

1. Warrant Type, if a text data type column, contains a zero-length string
rather than being Null.

2. If it is a numeric foreign key column referencing a numeric, e.g. an
autonumber, primary key of another table, then it may well be zero rather
than Null by virtue of having zero as its DefaultValue property. If when
designing the form's underlying table you've used the look-up wizard this
won't be readily apparent as the real value of the column will be disguised
even in raw datasheet view (one of the many reasons why this feature should
not be used).

But without more information we can only second guess.

Ken Sheridan
Stafford, England

Hi Ken,
I did try this and it isn't working. Any other suggestions?

in the form's BeforeInsert event procedure put:

Const conMESSAGE = "Warrent type must be entered."

If Me.[Reason for Call] >= 2 _
And [Reason for Call] <= 5 Then

Cancel = IsNull(Me.[Warrent Type])
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Me.[Warrent Type].SetFocus

End If

Ken Sheridan
Stafford, England

:

Hello,
Here is what I am trying to do...I would like to require data entry for a
control on a form but only if it meets the condition where:
If Reason for Call = 2, 3, 4, or 5 then Warrant Type cannot be null.

Does this make sense?
Please help.

 

[Ken Sheridan]

I may have been leading you up the garden path. I've just noticed that I
specified the wrong event procedure; the code should go in the form's
BeforeUpdate event procedure, NOT its BeforeInsert event procedure.
Apologies for the confusion. You may well find it will work there, but its
still worth covering a few other bases.

It doesn't necessarily follow that because the column is of text data type
it will contain any zero-length strings rather than Nulls, but you can cater
for both possibilities with:

Cancel = (Len(Nz(Me.[Warrant Type],"")) = 0)

But unless you have a real reason for having zero-length strings in the
column its better to avoid them altogether:

1. In table design view set the Required property of the Warrant Type
column to False (No). This allows Nulls.

2. Execute the following update query to make any column positions with
zero-length strings Null:

UPDATE [YourtableNameGoeshere]
SET [Warrant Type] = NULL
WHERE LEN([Warrant Type]) = 0;

3. In table design view set the AllowZeroLenth property of the Warrant Type
column to False (No). This prohibits zero-length strings.

The original code should then work (once you've corrected my spelling of
Warrant that is!)

Ken Sheridan
Stafford, England

Hi Ken,
thank you for your response.
Warrant Type is in fact a Text Data Type and therefore contains a zero
length string.
Given this, do you have any suggested next step?

In what way isn't it working? You are going to have to give us more to go on
than that.

Possibilities are:

1. Warrant Type, if a text data type column, contains a zero-length string
rather than being Null.

2. If it is a numeric foreign key column referencing a numeric, e.g. an
autonumber, primary key of another table, then it may well be zero rather
than Null by virtue of having zero as its DefaultValue property. If when
designing the form's underlying table you've used the look-up wizard this
won't be readily apparent as the real value of the column will be disguised
even in raw datasheet view (one of the many reasons why this feature should
not be used).

But without more information we can only second guess.

Ken Sheridan
Stafford, England

Hi Ken,
I did try this and it isn't working. Any other suggestions?

:

in the form's BeforeInsert event procedure put:

Const conMESSAGE = "Warrant type must be entered."

If Me.[Reason for Call] >= 2 _
And [Reason for Call] <= 5 Then

Cancel = IsNull(Me.[Warrant Type])
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Me.[Warrant Type].SetFocus

End If

Ken Sheridan
Stafford, England

:

Hello,
Here is what I am trying to do...I would like to require data entry for a
control on a form but only if it meets the condition where:
If Reason for Call = 2, 3, 4, or 5 then Warrant Type cannot be null.

Does this make sense?
Please help.

 

[Sharon W]

Hi Ken,
thank you. What I ultimately did was put the code along side the Save Macro
which the user does before proceeding to the next part.
However, even after I enter the warrant type the message continues to appear
and does not let the user move forward. Can you provide any insight?
My code follows below:
Private Sub Save_Record_Click()
Const conMessage = "Warrant Type Must Be Entered."

If Me.[Offense_s_] >= 2 And [Offense_s_] <= 5 Then

Cancel = (Len(Nz(Me.[Warrant_Type], "")) = 0)
MsgBox conMessage, vbExclamation, "Invalid Operation"
Me.[Warrant_Type].SetFocus


End If

I may have been leading you up the garden path. I've just noticed that I
specified the wrong event procedure; the code should go in the form's
BeforeUpdate event procedure, NOT its BeforeInsert event procedure.
Apologies for the confusion. You may well find it will work there, but its
still worth covering a few other bases.

It doesn't necessarily follow that because the column is of text data type
it will contain any zero-length strings rather than Nulls, but you can cater
for both possibilities with:

Cancel = (Len(Nz(Me.[Warrant Type],"")) = 0)

But unless you have a real reason for having zero-length strings in the
column its better to avoid them altogether:

1. In table design view set the Required property of the Warrant Type
column to False (No). This allows Nulls.

2. Execute the following update query to make any column positions with
zero-length strings Null:

UPDATE [YourtableNameGoeshere]
SET [Warrant Type] = NULL
WHERE LEN([Warrant Type]) = 0;

3. In table design view set the AllowZeroLenth property of the Warrant Type
column to False (No). This prohibits zero-length strings.

The original code should then work (once you've corrected my spelling of
Warrant that is!)

Ken Sheridan
Stafford, England

Hi Ken,
thank you for your response.
Warrant Type is in fact a Text Data Type and therefore contains a zero
length string.
Given this, do you have any suggested next step?

In what way isn't it working? You are going to have to give us more to go on
than that.

Possibilities are:

1. Warrant Type, if a text data type column, contains a zero-length string
rather than being Null.

2. If it is a numeric foreign key column referencing a numeric, e.g. an
autonumber, primary key of another table, then it may well be zero rather
than Null by virtue of having zero as its DefaultValue property. If when
designing the form's underlying table you've used the look-up wizard this
won't be readily apparent as the real value of the column will be disguised
even in raw datasheet view (one of the many reasons why this feature should
not be used).

But without more information we can only second guess.

Ken Sheridan
Stafford, England

:

Hi Ken,
I did try this and it isn't working. Any other suggestions?

:

in the form's BeforeInsert event procedure put:

Const conMESSAGE = "Warrant type must be entered."

If Me.[Reason for Call] >= 2 _
And [Reason for Call] <= 5 Then

Cancel = IsNull(Me.[Warrant Type])
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Me.[Warrant Type].SetFocus

End If

Ken Sheridan
Stafford, England

:

Hello,
Here is what I am trying to do...I would like to require data entry for a
control on a form but only if it meets the condition where:
If Reason for Call = 2, 3, 4, or 5 then Warrant Type cannot be null.

Does this make sense?
Please help.

 

[Ken Sheridan]

You can't include the code in the button's Click event procedure (its not a
macro BTW) in the way you have done as the event does not include a Cancel
argument. You'd have to do it another way:

Private Sub Save_Record_Click()

Const conMessage = "Warrant Type Must Be Entered."

If Me.[Offense_s_] >= 2 And Me.[Offense_s_] <= 5 And _
Len(Nz(Me.[Warrant_Type], "")) = 0 Then

MsgBox conMessage, vbExclamation, "Invalid Operation"
Me.[Warrant_Type].SetFocus
Else
RunCommand acCmdSaveRecord
End If

End Sub

However this is not a good way of doing it as it relies on the user clicking
the button to save the record. If they do so by moving to another record or
closing the form then the validation won't be undertaken. If you use the
form's BeforeUpdate event procedure, however, which includes a cancel
argument, it will be executed regardless of how the record is being saved.

Ken Sheridan
Stafford, England

Hi Ken,
thank you. What I ultimately did was put the code along side the Save Macro
which the user does before proceeding to the next part.
However, even after I enter the warrant type the message continues to appear
and does not let the user move forward. Can you provide any insight?
My code follows below:
Private Sub Save_Record_Click()
Const conMessage = "Warrant Type Must Be Entered."

If Me.[Offense_s_] >= 2 And [Offense_s_] <= 5 Then

Cancel = (Len(Nz(Me.[Warrant_Type], "")) = 0)
MsgBox conMessage, vbExclamation, "Invalid Operation"
Me.[Warrant_Type].SetFocus


End If

I may have been leading you up the garden path. I've just noticed that I
specified the wrong event procedure; the code should go in the form's
BeforeUpdate event procedure, NOT its BeforeInsert event procedure.
Apologies for the confusion. You may well find it will work there, but its
still worth covering a few other bases.

It doesn't necessarily follow that because the column is of text data type
it will contain any zero-length strings rather than Nulls, but you can cater
for both possibilities with:

Cancel = (Len(Nz(Me.[Warrant Type],"")) = 0)

But unless you have a real reason for having zero-length strings in the
column its better to avoid them altogether:

1. In table design view set the Required property of the Warrant Type
column to False (No). This allows Nulls.

2. Execute the following update query to make any column positions with
zero-length strings Null:

UPDATE [YourtableNameGoeshere]
SET [Warrant Type] = NULL
WHERE LEN([Warrant Type]) = 0;

3. In table design view set the AllowZeroLenth property of the Warrant Type
column to False (No). This prohibits zero-length strings.

The original code should then work (once you've corrected my spelling of
Warrant that is!)

Ken Sheridan
Stafford, England

Hi Ken,
thank you for your response.
Warrant Type is in fact a Text Data Type and therefore contains a zero
length string.
Given this, do you have any suggested next step?

:

In what way isn't it working? You are going to have to give us more to go on
than that.

Possibilities are:

1. Warrant Type, if a text data type column, contains a zero-length string
rather than being Null.

2. If it is a numeric foreign key column referencing a numeric, e.g. an
autonumber, primary key of another table, then it may well be zero rather
than Null by virtue of having zero as its DefaultValue property. If when
designing the form's underlying table you've used the look-up wizard this
won't be readily apparent as the real value of the column will be disguised
even in raw datasheet view (one of the many reasons why this feature should
not be used).

But without more information we can only second guess.

Ken Sheridan
Stafford, England

:

Hi Ken,
I did try this and it isn't working. Any other suggestions?

:

in the form's BeforeInsert event procedure put:

Const conMESSAGE = "Warrant type must be entered."

If Me.[Reason for Call] >= 2 _
And [Reason for Call] <= 5 Then

Cancel = IsNull(Me.[Warrant Type])
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Me.[Warrant Type].SetFocus

End If

Ken Sheridan
Stafford, England

:

Hello,
Here is what I am trying to do...I would like to require data entry for a
control on a form but only if it meets the condition where:
If Reason for Call = 2, 3, 4, or 5 then Warrant Type cannot be null.

Does this make sense?
Please help.

 

[Sharon W]

This worked perfectly....
THANK YOU VERY MUCH!

HAPPY NEW YEAR!

You can't include the code in the button's Click event procedure (its not a
macro BTW) in the way you have done as the event does not include a Cancel
argument. You'd have to do it another way:

Private Sub Save_Record_Click()

Const conMessage = "Warrant Type Must Be Entered."

If Me.[Offense_s_] >= 2 And Me.[Offense_s_] <= 5 And _
Len(Nz(Me.[Warrant_Type], "")) = 0 Then

MsgBox conMessage, vbExclamation, "Invalid Operation"
Me.[Warrant_Type].SetFocus
Else
RunCommand acCmdSaveRecord
End If

End Sub

However this is not a good way of doing it as it relies on the user clicking
the button to save the record. If they do so by moving to another record or
closing the form then the validation won't be undertaken. If you use the
form's BeforeUpdate event procedure, however, which includes a cancel
argument, it will be executed regardless of how the record is being saved.

Ken Sheridan
Stafford, England

Hi Ken,
thank you. What I ultimately did was put the code along side the Save Macro
which the user does before proceeding to the next part.
However, even after I enter the warrant type the message continues to appear
and does not let the user move forward. Can you provide any insight?
My code follows below:
Private Sub Save_Record_Click()
Const conMessage = "Warrant Type Must Be Entered."

If Me.[Offense_s_] >= 2 And [Offense_s_] <= 5 Then

Cancel = (Len(Nz(Me.[Warrant_Type], "")) = 0)
MsgBox conMessage, vbExclamation, "Invalid Operation"
Me.[Warrant_Type].SetFocus


End If

I may have been leading you up the garden path. I've just noticed that I
specified the wrong event procedure; the code should go in the form's
BeforeUpdate event procedure, NOT its BeforeInsert event procedure.
Apologies for the confusion. You may well find it will work there, but its
still worth covering a few other bases.

It doesn't necessarily follow that because the column is of text data type
it will contain any zero-length strings rather than Nulls, but you can cater
for both possibilities with:

Cancel = (Len(Nz(Me.[Warrant Type],"")) = 0)

But unless you have a real reason for having zero-length strings in the
column its better to avoid them altogether:

1. In table design view set the Required property of the Warrant Type
column to False (No). This allows Nulls.

2. Execute the following update query to make any column positions with
zero-length strings Null:

UPDATE [YourtableNameGoeshere]
SET [Warrant Type] = NULL
WHERE LEN([Warrant Type]) = 0;

3. In table design view set the AllowZeroLenth property of the Warrant Type
column to False (No). This prohibits zero-length strings.

The original code should then work (once you've corrected my spelling of
Warrant that is!)

Ken Sheridan
Stafford, England

:

Hi Ken,
thank you for your response.
Warrant Type is in fact a Text Data Type and therefore contains a zero
length string.
Given this, do you have any suggested next step?

:

In what way isn't it working? You are going to have to give us more to go on
than that.

Possibilities are:

1. Warrant Type, if a text data type column, contains a zero-length string
rather than being Null.

2. If it is a numeric foreign key column referencing a numeric, e.g. an
autonumber, primary key of another table, then it may well be zero rather
than Null by virtue of having zero as its DefaultValue property. If when
designing the form's underlying table you've used the look-up wizard this
won't be readily apparent as the real value of the column will be disguised
even in raw datasheet view (one of the many reasons why this feature should
not be used).

But without more information we can only second guess.

Ken Sheridan
Stafford, England

:

Hi Ken,
I did try this and it isn't working. Any other suggestions?

:

in the form's BeforeInsert event procedure put:

Const conMESSAGE = "Warrant type must be entered."

If Me.[Reason for Call] >= 2 _
And [Reason for Call] <= 5 Then

Cancel = IsNull(Me.[Warrant Type])
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Me.[Warrant Type].SetFocus

End If

Ken Sheridan
Stafford, England

:

Hello,
Here is what I am trying to do...I would like to require data entry for a
control on a form but only if it meets the condition where:
If Reason for Call = 2, 3, 4, or 5 then Warrant Type cannot be null.

Does this make sense?
Please help.