Best way to create mandatory fields in a database?

[JungleJme]

Hi guys & gals,

This is yet another stop on my Access Education (i'm building my first database) and i was wondering what people's thoughts are!

I have a table, which when i first set it up i decided that a couple of the fields had to be mandatory.

So, i set the "Required" property of the field to "Yes" (at table level - which is probably poor practice???)

I now have a form that allows a user at the front end to enter items in the table.

If they complete all the fields on the form i have used macro builder to save the record, present a nice message and move to a New Record. (3 elements in all)

However, if they try to save the record having not completed the "Required" fields then they get an error message instructing them the field is required, but then the macro crashes.

So what's the best way to approach Mandatory fields? Is it best to leave the "Required" property at table level set to No and then have something at form level which checks they have entered a value in the field? (i'm guessing this is probably the correct approach)

I tried removing the table level condition from the [Field] and adding this as the first argument of the macro builder which saves my record:

IF [Field] = "" Then 
MessageBox "You Must Complete Mandatory Fields"
Stop Macro
End If

But my macro still completes and saves the record...

Any thoughts on what i should be doing?

Thanks!

J

 

[bob fitz]

I would do the validation in the forms Before Update event. See: http://www.baldyweb.com/BeforeUpdate.htm

 

[JungleJme]

Thanks very much, i'll try it at form level as you suggested and see how i get on.

 

[Pat Hartman]

You also need to distinguish between a Zero Length String ("") and Null. When you compare a control to "", you are only checking to see if it contains a ZLS. You are not checking to see if it is null. Use the following to check for both.

If Me.YourField & "" = "" Then 'the field is ZLS or null

 

[JungleJme]

Thanks to you both for responding, sorry i've been slow to reply - i'm attempting this project in my spare time!

I've now got my validation up and running thanks so much to you both!

 

[tucker61]

I had a similar issue and used a similar piece of code on my controls.

Problem is that the form contains approx 30 controls.

Is it possible to loop the validation or do I have to write it out for each control?

 

[Pat Hartman]

If all the controls of a certain type have the same validation rule, you can use the Fields collection and loop through it looking for "textboxes" for example. Another possibility is to hard-code something in the Tag property of a control. Then your code can look for controls with a certain Tag property value.