Form field required 'if'

  • Thread starter Thread starter Carrol
  • Start date Start date
C

Carrol

Hi,
I have a form that has a field for 'job description' which
is a combo box and the tasks listed begin with a 3 digit
code. Also a memo field that is for explanation of the
task if needed. I've been asked to make this memo field a
required field if the task selected begins with 161 or 260
otherwise it is not required. I'm not even sure if this
is possible, but I would greatly appreciate any ideas.
Thanks from Iowa.
 
One possible solution is as follows:
Place the text box on the form, linked to the memo field.
(Do not set it this field to required in the table)
Set the visible property of the text box to False.
In the combo box change event, use something like this:

If Value=161 or Value=260 then
Textboxname.visible=true
Else
Textboxname.Visible=False
End If

In the Beforeupdate event of the form, check if the
textbox is visible. If it is, check the length of the text
in the texbox. If it is greater than 0 all is OK, if it is
0, display msgbox stating that an explanation is required,
set cancel to true and exit the sub.

Dave
 
This can be done on a form. In the BeforeUpdate event of the form, check the
value of the task fields left 3 characters and if they meet the
specification, check for a value in the memo field. If the memo field is
blank, Cancel the update and alert the user.

Example:
If Left(Me.txtTask, 3) = "161" Or Left(Me.txtTask, 3) = "260" Then
If Nz(Me.txtMemo, "") = "" Then
Cancel = True
Msgbox "The memo is required!", vbOkOnly + vbInformation
Me.txtMemo.SetFocus
End If
End If
 
Wayne,
Thank you for your idea. I think this would work if only
my text field name didn't have a space in the name...."Job
Name". I continue to receive an error message
"Compile error:
Expected list separator or )"
I believe this error is because of the space in the field
name. Is there a solution for this?
Thanks so much.
 
Your correct, spaces will cause a problem. I was referring to the name of
the control (textbox) not the field, but you should be able to make it work
either way. For names with spaces, put brackets around them.

Example:
Me.[My Textbox]
or
[Table Name].[Field Name]
 
The brackets worked but still nothing happens when I test
the form. When I put the task beginning with 260 and tab
through the memo field....no message pops up and can still
continue to next record.
This is what I have entered.....

Private Sub Misc_BeforeUpdate(Cancel As Integer)
If Left(Me.[Job Name], 3) = "161" Or Left(Me.[Job Name],
3) = "260" Then
If Nz(Me.Misc, "") = "" Then
Cancel = True
MsgBox "Miscellaneous explanation is required!",
vbOKOnly + vbInformation
Me.txtMisc.SetFocus
End If
End If
End Sub

What have I done wrong?
Sorry to be so dense.
-----Original Message-----
Your correct, spaces will cause a problem. I was referring to the name of
the control (textbox) not the field, but you should be able to make it work
either way. For names with spaces, put brackets around them.

Example:
Me.[My Textbox]
or
[Table Name].[Field Name]

--
Wayne Morgan
Microsoft Access MVP


Wayne,
Thank you for your idea. I think this would work if only
my text field name didn't have a space in the name...."Job
Name". I continue to receive an error message
"Compile error:
Expected list separator or )"
I believe this error is because of the space in the field
name. Is there a solution for this?
Thanks so much. of
the form, check the or
260
Click to expand...


.
Click to expand...
 
Private Sub Misc_BeforeUpdate(Cancel As Integer)
If Nz(Me.Misc, "") = "" Then
Click to expand...

Is Misc the name of the form or the memo field control? It appears you have
placed the code in the BeforeUpdate event of the memo field control, it
should be in the form's BeforeUpdate event.

--
Wayne Morgan
Microsoft Access MVP


The brackets worked but still nothing happens when I test
the form. When I put the task beginning with 260 and tab
through the memo field....no message pops up and can still
continue to next record.
This is what I have entered.....

Private Sub Misc_BeforeUpdate(Cancel As Integer)
If Left(Me.[Job Name], 3) = "161" Or Left(Me.[Job Name],
3) = "260" Then
If Nz(Me.Misc, "") = "" Then
Cancel = True
MsgBox "Miscellaneous explanation is required!",
vbOKOnly + vbInformation
Me.txtMisc.SetFocus
End If
End If
End Sub

What have I done wrong?
Sorry to be so dense.
-----Original Message-----
Your correct, spaces will cause a problem. I was referring to the name of
the control (textbox) not the field, but you should be able to make it work
either way. For names with spaces, put brackets around them.

Example:
Me.[My Textbox]
or
[Table Name].[Field Name]

--
Wayne Morgan
Microsoft Access MVP


Wayne,
Thank you for your idea. I think this would work if only
my text field name didn't have a space in the name...."Job
Name". I continue to receive an error message
"Compile error:
Expected list separator or )"
I believe this error is because of the space in the field
name. Is there a solution for this?
Thanks so much.
-----Original Message-----
This can be done on a form. In the BeforeUpdate event of
the form, check the
value of the task fields left 3 characters and if they
meet the
specification, check for a value in the memo field. If
the memo field is
blank, Cancel the update and alert the user.

Example:
If Left(Me.txtTask, 3) = "161" Or Left(Me.txtTask, 3)
= "260" Then
If Nz(Me.txtMemo, "") = "" Then
Cancel = True
Msgbox "The memo is required!", vbOkOnly +
vbInformation
Me.txtMemo.SetFocus
End If
End If

--
Wayne Morgan
Microsoft Access MVP


message
Hi,
I have a form that has a field for 'job description'
which
is a combo box and the tasks listed begin with a 3 digit
code. Also a memo field that is for explanation of the
task if needed. I've been asked to make this memo
field a
required field if the task selected begins with 161 or
260
otherwise it is not required. I'm not even sure if this
is possible, but I would greatly appreciate any ideas.
Thanks from Iowa.


.
Click to expand...


.
Click to expand...
Click to expand...
 
IT WORKS! Thank you so much Wayne....you saved the day.
-----Original Message-----
Private Sub Misc_BeforeUpdate(Cancel As Integer)
If Nz(Me.Misc, "") = "" Then
Click to expand...

Is Misc the name of the form or the memo field control? It appears you have
placed the code in the BeforeUpdate event of the memo field control, it
should be in the form's BeforeUpdate event.

--
Wayne Morgan
Microsoft Access MVP


The brackets worked but still nothing happens when I test
the form. When I put the task beginning with 260 and tab
through the memo field....no message pops up and can still
continue to next record.
This is what I have entered.....

Private Sub Misc_BeforeUpdate(Cancel As Integer)
If Left(Me.[Job Name], 3) = "161" Or Left(Me.[Job Name],
3) = "260" Then
If Nz(Me.Misc, "") = "" Then
Cancel = True
MsgBox "Miscellaneous explanation is required!",
vbOKOnly + vbInformation
Me.txtMisc.SetFocus
End If
End If
End Sub

What have I done wrong?
Sorry to be so dense.
-----Original Message-----
Your correct, spaces will cause a problem. I was referring to the name of
the control (textbox) not the field, but you should be able to make it work
either way. For names with spaces, put brackets around them.

Example:
Me.[My Textbox]
or
[Table Name].[Field Name]

--
Wayne Morgan
Microsoft Access MVP


Wayne,
Thank you for your idea. I think this would work if only
my text field name didn't have a space in the name...."Job
Name". I continue to receive an error message
"Compile error:
Expected list separator or )"
I believe this error is because of the space in the field
name. Is there a solution for this?
Thanks so much.
-----Original Message-----
This can be done on a form. In the BeforeUpdate
Click to expand...
event
of
the form, check the
value of the task fields left 3 characters and if they
meet the
specification, check for a value in the memo field. If
the memo field is
blank, Cancel the update and alert the user.

Example:
If Left(Me.txtTask, 3) = "161" Or Left(Me.txtTask, 3)
= "260" Then
If Nz(Me.txtMemo, "") = "" Then
Cancel = True
Msgbox "The memo is required!", vbOkOnly +
vbInformation
Me.txtMemo.SetFocus
End If
End If

--
Wayne Morgan
Microsoft Access MVP


message
Hi,
I have a form that has a field for 'job description'
which
is a combo box and the tasks listed begin with a 3 digit
code. Also a memo field that is for explanation
Click to expand...
of
the
task if needed. I've been asked to make this memo
field a
required field if the task selected begins with
Click to expand...
161
or
260
otherwise it is not required. I'm not even sure
Click to expand...
if
this
is possible, but I would greatly appreciate any ideas.
Thanks from Iowa.


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top