Avatar of mrdbmagic
mrdbmagic
Flag for United States of America asked on

Require field to be updated

I have a form called frmMealDailyEntry. I have a public function myjobfun that records the job function of the individual logging into the db. If an RN logs into the db and opens the form frmMealDailyEntry I want them to be required to enter the MealsDeliv control to be updated to a numeric value. The form should not close withoout this number being entered. The default value is currently set to "***" I would think on close event should be something like if myjobfun="RN" and me.MealsDeliv="***" then docmd. msgbox("MealsDeliv must be entered") then docmd.goto control("MealsDeliv") cancel close. I am not sure how to cancel the close. Thanks for the help.
Microsoft Access

Avatar of undefined
Last Comment
mbizup

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
mbizup

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
mrdbmagic

ASKER
Thanks for the reply but The form still closes.
mrdbmagic

ASKER
hold on now I get it to come with the msgbox but then it says you can't save the record at this time and do you want to close anyway. I don't want that to come up. Any suggestions?
mbizup

To prevent the form from closing even if the user has made no edits, you may need to disable the close (x) button in the form's property sheet and create a custom close button with the following code:

Private Sub CloseButton_Click()
 if myjobfun="RN" and me.MealsDeliv="***" then 
            msgbox  "MealsDeliv must be entered"
            Exit Sub
 end if
docmd.close acform, Me.name
end sub

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
mbizup

<< . I don't want that to come up. Any suggestions?>>

Do you have table level validation in your table's design?

In general, you should use either code validation or table-level validation - but not both.  So take out any table-level validation for this field to test the code...
mrdbmagic

ASKER
no table level validation. Any chance I could put the close button statements in my before update?
mbizup

The issue that you may be running into with the before update event is that it only fires if the form has been edited.  If there have been no edits at all, the Before Update event is skipped.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
mbizup

You could try my initial (Before Update)  suggestion another try, adding the following line to the form's Current Event:

Me.Dirty = true


That will make the form appear edited, and should make the Before Update event run regardless of user edits.
mrdbmagic

ASKER
Thanks for the solution. I really appreciate it.
mbizup

Glad to help out :)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck