We help IT Professionals succeed at work.

Mandatory fields in forms Access

daanhofman
daanhofman asked
on
960 Views
Last Modified: 2013-11-28
I have a form in Access and I want to assign mandatory fields.
So the user can not exit the record without populating this field (these fields).

Thanks
Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
In your table, set the Required property for those fields to Yes.

mx

Author

Commented:
That would indeed be the solution. However, to create a new record, the situation is that I have an append query to automatically add a record to the table. (This is necessary because some special reasons). If I set the required property to Yes, then I will not be able to add the record anymore.

So I need to be able to add a record to the table using a query (while the field is empty), but in the form this field need to be mandatory.

Thanks
On the BeforeUpdate event of the form you can check each field that should be required with the IsNull() function and if any required field is empty, you can use the DoCmd.CancelEvent method to prevent the user from saving the record.
' in the BeforeUpdate event of the Form
If IsNull(Me.RequiredField1) Then
    MsgBox "Field 'RequiredField1' is a required field!", vbExclamation
    DoCmd.CancelEvent
    Exit Sub
End If
' repeat for any other required fields

Open in new window

MIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I tried your solution in the beforeUpdate event. But it did not work.
I also added this code in the close event, but it did not work.

However, what I did is following: I removed the close button from the form and I added a button on the form and I added your code to it.
So this solved my problem.

Thanks

Daan
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"In your table, set the Required property for those fields to Yes."

I believe a point split was in order here ....

mx
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
daanhofman,

If you used mystiquevc's code, then they deserve the points.

(My post just suggested adding the missing data to the append query.)

Contact the support staff to reopen this Q, so that you can accept mystiquevc's post, instead of mine.

JeffCoachman
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
mystiquevc,

Please follow-up on this, as I feel you should be awarded the points.
:)

JeffCoachman
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
DatabaseMX ,

OOOOOOO....Sorry buddy!
:)

All I meant was that "I" did not deserve any points.
;)

Jeff
Hey Jeff,

No worries - it's a community thing - I don't really care about the points per se. :)  But thanks for the acknowledgement, anyway! :)
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
mystiquevc,

Well, you know sometimes things can be taken the wrong way.

I just want others to know that I will actualy request that my name be removed, if it is obvious that a mistake was made. (and not just "let it slide")

;)

JeffCoachman
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
np.mx

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.