How to make a field mandatory

[hilian]

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]

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.

 

[Deleted member 147267]

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]

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)?

 

[Deleted member 147267]

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]

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.

 

[Deleted member 147267]

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]

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]

req,Your momma wears army boots
[/COLOR

Showing your age there Micron!

 

[Deleted member 147267]

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]

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]

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]

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]

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

 

[Gasman]

Could you use this approach?

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

 

[hilian]

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]

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]

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]

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]

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.