Required fields based on info entered in another field

  • Thread starter Thread starter Lori Mihalko
  • Start date Start date
L

Lori Mihalko

I'm not sure whether this will be a forms issue or a
table issue, but I'll start here with forms...

I need to make a field required based on the information
entered in another field.

example A: if field10 = Yes, field11 is required.

example B: either field11 or field 12 is required.

Any help is appreciated,
Thanks,
Lori Mihalko
 
I'm not sure whether this will be a forms issue or a
table issue, but I'll start here with forms...
Click to expand...

It's a form issue, primarily; though you could construct a table
validation rule to enforce these constraints. Check the properties of
the table itself to do so.
I need to make a field required based on the information
entered in another field.

example A: if field10 = Yes, field11 is required.

example B: either field11 or field 12 is required.
Click to expand...

Probably the simplest way to do this is to put VBA code in the
BeforeUpdate event of a form, such as (for example A):

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me![Field10] = True Then ' assuming it's a Yes/No field
If IsNull(Me![Field11]) Then
MsgBox "If Field10 is true you must fill in Field11", vbOKOnly
Cancel = True
End If
End If
End Sub

Example B is left as an exercise... <g>
 
Thanks John,

I used this code and a message did pop up as the command
states, saying the field is required.

However, is there a way to go a step further to change
the field properties so that the field cannot be null?

Thanks,
Lori

-----Original Message-----
I'm not sure whether this will be a forms issue or a
table issue, but I'll start here with forms...
Click to expand...

It's a form issue, primarily; though you could construct a table
validation rule to enforce these constraints. Check the properties of
the table itself to do so.
I need to make a field required based on the information
entered in another field.

example A: if field10 = Yes, field11 is required.

example B: either field11 or field 12 is required.
Click to expand...

Probably the simplest way to do this is to put VBA code in the
BeforeUpdate event of a form, such as (for example A):

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me![Field10] = True Then ' assuming it's a Yes/No field
If IsNull(Me![Field11]) Then
MsgBox "If Field10 is true you must fill in Field11", vbOKOnly
Cancel = True
End If
End If
End Sub

Example B is left as an exercise... <g>


.
Click to expand...
 
However, is there a way to go a step further to change
the field properties so that the field cannot be null?
Click to expand...

Certainly; simply set the Required property of the field to True in
table design view.

You can get a friendlier message by checking that the field is
non-NULL in the Form's BeforeUpdate event.
 
John,

Is there a way to change field2's properties
programmatically instead of or in addition to the message
if field1 = True?

Field2 need to be kept as not required in the table
design because it is not required if Field1 is False.

Feel free to email me directly,
Lori
 
John,

Is there a way to change field2's properties
programmatically instead of or in addition to the message
if field1 = True?
Click to expand...

The code I posted previously will do exactly this:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me![Field10] = True Then ' assuming it's a Yes/No field
If IsNull(Me![Field11]) Then
MsgBox "If Field10 is true you must fill in Field11", vbOKOnly
Cancel = True
End If
End If
End Sub

It will issue an error message only if Field10 is TRUE and field11 is
NULL. Is it not working as you expect? What property of Field2 do you
want to change? It's either required or it's not; that's not a dynamic
field property.
Field2 need to be kept as not required in the table
design because it is not required if Field1 is False.

Feel free to email me directly,
Click to expand...

Certainly, provided you're willing to open a consulting contract at my
usual rates, which I'll provide upon request.
 
John,

Thanks for all your help. The code does generate a
message, but I wanted the code to not allow the record to
be saved if Field1 is = True and Field2 is null. I
understand that cannot be done now, as the required
property is not dynamic.

Thanks again,
Lori
-----Original Message-----
John,

Is there a way to change field2's properties
programmatically instead of or in addition to the message
if field1 = True?
Click to expand...

The code I posted previously will do exactly this:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me![Field10] = True Then ' assuming it's a Yes/No field
If IsNull(Me![Field11]) Then
MsgBox "If Field10 is true you must fill in Field11", vbOKOnly
Cancel = True
End If
End If
End Sub

It will issue an error message only if Field10 is TRUE and field11 is
NULL. Is it not working as you expect? What property of Field2 do you
want to change? It's either required or it's not; that's not a dynamic
field property.
Field2 need to be kept as not required in the table
design because it is not required if Field1 is False.

Feel free to email me directly,
Click to expand...

Certainly, provided you're willing to open a consulting contract at my
usual rates, which I'll provide upon request.


.
Click to expand...
 
John,

Thanks for all your help. The code does generate a
message, but I wanted the code to not allow the record to
be saved if Field1 is = True and Field2 is null. I
understand that cannot be done now, as the required
property is not dynamic.
Click to expand...

Lori,

Did you try the code?

As written (unless I'm losing it completely!) the code will do EXACTLY
what you request: it will not allow the record to be saved (that is,
it will set the Cancel argument of BeforeUpdate to TRUE and cancel the
addition of the record) if Field1 is TRUE and Field2 is NULL.

That's what you asked for. That's what the code does.

If it's not working, please post the *actual code* that you're using,
with the actual control names of the two controls, and describe what's
happening.
 
Back
Top