Data Validation on an Attachment Field

-Polak
-Polak used Ask the Experts™
on
Would like to know if there is a way to require that a document be attached. Currently, I have the conditional formatting shade red and bold the box when another field is equal to true, but would prefer it to be part of data validation....?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Clay FoxDirector of Business Integration

Commented:
Just put a rule on submit that checks if the field is blank or not.

Author

Commented:
I would like an asterisk or a red outline appear around the box so that the forms user knows that they need to include this attachment when another given field is checked. This is more effective than an error on submit that they won't read until they expand the error in details.

Author

Commented:
possible?
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

MIS Liason
Most Valuable Expert 2012
Commented:
-Polak,

"This is more effective than an error on submit"
1. Not really IMHO, remember that the value in a control is not really stable until After the record is updated, so checking for a value in a field before the record is updated (while you are still editing the record) is problematic. (partially because of reason 32 below)
2. Also the issue of users filling out the fields in a different order than you specify can also cause issues.
3. I know of no direct way to validate if an attachment filed has a value or not, so the best you can do is make the border around the Attachment Red if your True/False Field is True

So you can use code like this on your form:

Private Sub chkNeedAttachment_AfterUpdate()
    Call SetAttatchmentBorderColor
End Sub

Private Sub Form_Current()
    Call SetAttatchmentBorderColor
End Sub
Private Sub SetAttatchmentBorderColor()
    If Me.chkYourCheckBox = True Then
        Me.attYourAttachment.BorderColor = vbRed
    Else
        Me.attYourAttachment.BorderColor = vbBlack
    End If
End Sub

JeffCoachman

Author

Commented:
I think I understand what your saying about why you think its better to just put a rule on it; but, doesn't conditionally hiding the attachment control until my ture/false field is checked true eliminate your reason 2 and any other reservations you have?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<I think I understand what your saying about why you think its better to just put a rule on it;>
I'm confused, ... I never said anything about a rule...?

<doesn't conditionally hiding the attachment control...>
... I never said this either...?

My point was that this is a common question here  (validate a control, while still editing the record).
Despite how "logical" this approach seems, it is wrought with pitfalls.

The code I posted is what I tested, and it works.
Did you try it?
What was your determination?

As I also said, I could find no direct way to validate if an attachment field had a value in it, so part of your reason for wanting to do this is what makes this difficult.

JeffCoachman

Author

Commented:
I won't have a chance to test it until tomorrow.

When I said "This is more effective than an error on submit", you said you disagreed and provided reasons that mainly touched on the issue of users may not fill out the form in the correct order. What I meant by conditionally hiding the attachment field is it gets rid of the issues you raised on incorrect order.

You're point is well taken; but since the audience of this form is fairly non-technical, I still think this work around is more "effective" than an error on submit.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Again, hiding the control may help, but that is not what you originally asked for.

But remember we have a controlling issue here.
...As I stated, there is no direct way (that I am aware of) to validate if an attachment field had a value in it.
So are you saying that you still want to continue with this "validation" if there is no way to detect if the attachment control has a value or not?
Yes or No?

Besides you still have not stated if you tried my code of not and what the result was...?
Please let me know.

JeffCoachman

Author

Commented:
I will try your code, it will undoubtedly work, and I think it in combination with a rule is the best solution for the question I've asked.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
OK, keep me posted

Author

Commented:
Worked!
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Great!

;-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial