Required Field based on another Field

[jlo]

I have a field "Issue Resolved" that is a checkbox. Is there a way to set
this field when it is checked to go to the field "Date Resolved" and make
that field required so the user has to type a date in the field?

I can only make this field required if the issue was resolved.

Thanks for all your help in advance!!

 

[Linq Adams via AccessMonster.com]

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Issue_Resolved And IsNull(Me.Date_Resolved) Then
Cancel = True
MsgBox "A Resolution Date Must Be Entered!"
Me.Date_Resolved.SetFocus
End If
End Sub


Note the control name Date_Resolved, with the underline character. This is
the way Access VBA refers to a control name when you've left a space in it.
You really should refrain from having spaces in object names. DateRersolved
would be much better, or as Access does it, Date_Resolved.

Also not that when dealing with a checkbox,

If Me.Issue_Resolved

is the same as saying

If Me.Issue_Resolved =-1

or

If Me.Issue_Resolved = True

In other words, if Me.Issue_Resolved is checked.

 

[Ryan]

Try this.

Private Sub Issue_Resolved_AfterUpdate()
If Me.Issue_Resolved = True Then
DoCmd.GoToControl "Date Resolved"
Me.Date_Resolved.Required = True
End If
Exit Sub

 

[Allen Browne]

Remove the [Issue Resolved] field from your table.

Create a query using this table, and type this into the field row:
IssueResolved: ([Date Resolved] Is Not Null)
Access will automatically populate this field for you when the date is
entered. It solves all the validation issues, and the data can never be
wrong.

Just use the query anywhere where you want this field (e.g. as the source of
a form, a report, ...)

 

[jlo]

Thank you for the suggestion. When I create fields, I do not put spaces in
the field names. Unfortunately this person doesn't know Access to well and
put spaces in the field names. I will see if it works. Thanks.

 

[jlo]

Thank you too. This does work as the last post does. I also like Allen's
suggestion and will see if the customer wishes to try that.

 

[jlo]

I'm loving this idea and will present to the user. Thank you to all of you!

Remove the [Issue Resolved] field from your table.

Create a query using this table, and type this into the field row:
IssueResolved: ([Date Resolved] Is Not Null)
Access will automatically populate this field for you when the date is
entered. It solves all the validation issues, and the data can never be
wrong.

Just use the query anywhere where you want this field (e.g. as the source of
a form, a report, ...)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a field "Issue Resolved" that is a checkbox. Is there a way to set
this field when it is checked to go to the field "Date Resolved" and make
that field required so the user has to type a date in the field?

I can only make this field required if the issue was resolved.

Thanks for all your help in advance!!