How to make a field mandatory (1 Viewer)

hilian · Jul 15, 2019

I tried Micron’s code, but I’m stuck on the Tag property. Do I enter it in the property sheet for each control I need to be filled in? I’ve never used the Tag property before, so I looked it up, and all the examples I found showed setting it with code. However, none showed where the code goes, e.g. the before update event on the form or an event on the actual control.

Also I’m unclear about,
“reqd,message 4 and more text Note that it is reqd and then a comma and
then text with no spaces until the message.”
I get “reqd,” but is the word “message” followed by my actual message or does something go before the message? “4 and more text, and.” text with no spaces until the message suggests that there is, but what comes before “Please enter a sample number,” etc.

I also tried missinglinq’s suggestion of adding Exit Sub inside of each If statement. When I tested it by first removing the values in the table, saving the table and then going back to the form and trying to save the record or go to the next record, nothing happened. It did work if I first put the cursor in the text box or the combo box, but the problem is that users are apt to skip the control and never put the cursor there.

isladogs · Jul 15, 2019

I’m stuck on the Tag property. Do I enter it in the property sheet for each control I need to be filled in?

Yes. Its at the bottom of the Other tab on the property sheet

I really do recommend you look at my example database to understand how the Tag property can be used for various purposes.
The link is in post #3 or you can find it here: https://www.access-programmers.co.uk/forums/showthread.php?t=293439

I'll leave the authors of the other items to answer the rest of your questions.

Micron · Jul 16, 2019

I get “reqd,” but is the word “message” followed by my actual message or does something go before the message? “4 and more text, and.” text with no spaces until the message suggests that there is, but what comes before “Please enter a sample number,” etc.

Then as appropriate for each control (no quotes)

reqd,Please enter a sample number

reqd,Did this case originate from ECS?

req,Your momma wears army boots

etc. -
Not reqd,Please enter a sample number, or else
See the big red comma in the message? If you do that, the procedure will split it off.

OR
If you'd like to be able to use commas, then the separator needs to be a different character. I probably should have used the redirect pipe or tilde as nobody would want to put a | or a ~ in a sentence, right?

hilian · Jul 16, 2019

I copied in the code as a before update event on the form, and I put in reqd,Please enter a sample number in the tag property of txtSampleNo and reqd,Did this case originate with ECS? in the tag property of cboECS.

I tested the form with the same records with missing data I used the last time, and, unfortunately, got nothing. Where should I look for the mistake(s)?

Micron · Jul 16, 2019

I tested the form with the same records with missing data I used the last time, and, unfortunately, got nothing.

I have no idea what that means. I think it would be best if you posted a db copy. Strip out everything that's private if it's not relevant. If you need to include data that is private, consider this code for obscuring it
https://www.accessforums.net/showthread.php?t=77482

hilian · Jul 16, 2019

Thank you very much. I'm attaching the db.

I'll be for a week away starting tomorrow. I'll check in again next Wednesday, 7/24.

Micron · Jul 17, 2019

works for me, but I did make a few changes - one of which was to remove the save button embedded macro. You have a mix of code and such macros - better to have one or the other, I'd say. Under certain conditions, it wasn't going to try to save the form, so the form Before Update event would not run. Since you are off-line until Wednesday, I'll play around some more. Code has tons of compile errors.

hilian · Jul 24, 2019

Hi Micron,

I’m anxious to see the changes.

We put the save buttons in to reassure the users that their work was being saved. I can live without the Save buttons, but what would suggest putting in their place.

I’m surprised about the compile errors. It isn’t something I’ve experienced on this project.

Henry

NauticalGent · Jul 24, 2019

Micron said:
req,Your momma wears army boots
[/COLOR

Showing your age there Micron!

Micron · Jul 24, 2019

NauticalGent said:
Showing your age there Micron!

It's probably meaningless beyond North America (unless you're an ex-pat)? I thought about other "yo' mama" jokes but they seemed somewhat politically incorrect.

Pat Hartman · Jul 25, 2019

Once you get this sorted out, I would make the required fields required in the table. Before you do that, you will need to populate all the "empty" fields. For the text fields, you might use the expression -- "actual value unknown", for numeric values, pick something that would never be valid such as -999. If you can identify who added these records, you could create a report for each user asking them to clean up their own mess.

hilian · Jul 25, 2019

Pat,
I'm unclear about how making the fields required in the table or populating them would help. That's where this thread started. I made fields required in the table, but the messages aren't helpful to users. I changed the required property back in the table and pursued making the fields required in the form.

Pat Hartman · Jul 25, 2019

As I said, once you fix the existing problem, preventing it from occurring again is the best option and I would never leave it to code behind a form when the database engine is far better equipped to enforce this type of rule. Wanting a better error message is a fine goal and that requires code behind the forms but stopping there is a mistake. You can always put code in your forms but that won't help at all if you are importing data from some other place or running update or append queries. Setting the fields to required at the table level is not mutually exclusive from adding edits in forms to provide more user friendly error messages.

GinaWhipp · Jul 25, 2019

Hmm, perhaps this will help...
https://www.access-diva.com/vba13.html

Gasman · Jul 25, 2019

Could you use this approach?

http://www.databasedev.co.uk/custom-error-message.html

hilian · Jul 25, 2019

I see the principle that it's safest to enforce data integrity at the table level. In this case, the application is only for data entry. We won't be importing data or running update or append queries--we download the data into an application we created in Excel for analysis, which also has data integrity checks.

My greatest concern is data-entry errors. We're using data validation rules for all the text boxes, and all the combo boxes are limited to their respective lists. Going over our data, the problem we discovered most recently was missing entries, which is the problem I'm trying to fix now.

hilian · Jul 25, 2019

Gina,

Thanks for the link.

At first I thought that I couldn't make Micron's code work, but I realized that I had to modify a record for it to kick in. I see how your code would do the same kind of thing: scroll through all of the controls, and for the ones with required, or reqd in the Tag property give an error message. I have a question, though: Where does the second piece of code, the one that begins, "If frmValidateData Then," go? Do I put it as an event for each control that I want to require? If so, which event?

Many thanks,

Henry

hilian · Jul 25, 2019

Gasman,

Thank you for the link.

I see where the code goes, but should the field be required in the table or the form?

GinaWhipp · Jul 25, 2019

hilian said:
Gina,

Thanks for the link.

At first I thought that I couldn't make Micron's code work, but I realized that I had to modify a record for it to kick in. I see how your code would do the same kind of thing: scroll through all of the controls, and for the ones with required, or reqd in the Tag property give an error message. I have a question, though: Where does the second piece of code, the one that begins, "If frmValidateData Then," go? Do I put it as an event for each control that I want to require? If so, which event?

No, not on each control but *around* the code that takes you to a new record and\or closes the Form.

hilian · Jul 25, 2019

I have buttons for Exit, Previous Record, Next Record and Save. Does the code go around the code for each of these buttons? The code contains the line, "DoCmd.RunCommand acCmdSaveRecord." Does that invoke the Save button code? If so, I'm still unclear on where "If frmValidateData Then," etc. goes and how to wrap it around the code that takes you to a new record and\or closes the Form.

How to make a field mandatory (1 Viewer)

Episodic User

MVP / VIP

AWF VIP

Episodic User

AWF VIP

Episodic User

Attachments

AWF VIP

Episodic User

Ignore List Poster Boy

AWF VIP

Super Moderator

Episodic User

Super Moderator

AWF VIP

Enthusiastic Amateur

Episodic User

Episodic User

Episodic User

AWF VIP

Episodic User

Similar threads

Users who are viewing this thread