Using "Before Update" to check for Nulls/Message Box Question (1 Viewer)

[KAllen31]

I have been working on this for the past couple of days and seem to have hit a stone wall.

I have a form that is used to add a new customer/relationship. The four fields on this form are RelID, RelName,CostCenter & RelationshipManager. All of these are required and cannot be null.

I have code on each of these text boxes in the before update section that looks like this.

If IsNull (Me![FieldName]) Then
MsgBox "Please remember to enter blah blah blah"
DoCmd.Cancel Event
End If
End Sub.

When I use this it does not work!
After reading some of the posts in this forum I realized that I could do this in the before update section of the form itself.

My question is, do I have to do a different If IsNull event for each required field or can I include all of my required items on one If IsNull statement?

Also, when I did a test with the "before update" section of the form (with one field), it was able to capture the fact that the field had a null value, but immediately following my message box, came the standardized access message that says the primary key cannot contain a null value. Is there any way to fix this?

I hope I explained myself well, code is new to me and don't have my terminology down straight yet.

Thanks

 

[D-Fresh]

The quickest way would probably be to set the tag property of each of your required fields to some common value, say Required. Then loop through each control in the form in the before update event of the form and if the tag matches, cancel the update... Here's an example...

dim ctl as control

for each ctl in controls
if ctl.tag = "Required" then
if isnull(ctl.value) then
msgbox ctl.name & " is a required field."
cancel = true
exit for
end if
end if
next

I hope this helps and gets you started.

Doug

 

[KAllen31]

Wow!

I thought it would look a lot more complicated than that.

I will give it a go.

Thanks

 

[KAllen31]

Well I gave this a go and while it worked MUCH better than my sorry structure previously, I still have some quirks.

Quirk One.

When using the following:
MsgBox ctr.name & " is a required field."
I get a message like this:
RelName is a required field.
What it is doing is taking the name of the field from my table, which is RelName. I changed the name of this field on my form (to make it user friendly) to Relationship Name. Is there anyway I can fix this to have it appear as the form format?

Lastly, After getting the right message box reminding the user that they need to enter the correct information, I get a 2nd message box (OK Only) That tells the user that the last operation was cancelled.

I am not sure how to go about fixing that so that the second message does not appear.

As ususal, any advice will be greatly appreciated.

 

[Randomblink]

Ok, maybe I'm way out there on this, but...
Couldn't you just go into the underlying table and set the fields to [Required: Yes] and then it wouldn't let you make a record without adding those fields?

I have tried to do the same thing you are doing, but only when a user is in a specific form that does a specific function. And then I TRY to add a msgbox that lets the user know they need to make an entry into that field.

I used to use IsNull or IsEmpty (I think) in Access97, but in Access2k I hit the same stone wall...but...
I usually find a work around that covers it...

Hope that helps...

 

[KAllen31]

Thanks for responding.

In the table design I do have all fields tagged as required.

What I will do is add my message to validation text, within the table itself and see if that works. I am so anxious to start learning code, that I think I may be ignoring the obvious and easiest way of doing things.

Thanks



[This message has been edited by KAllen31 (edited 08-22-2001).]

 

[Randomblink]

I'm still learning code...
It is very nice to learn this way too...
It's kinda funny that you said that because that is my problem too...
Im not a guru yet on VB...heck, I still don't understand basic principles and fundamentals of VB but I feel strong enough to do basic coding...now I am anxious to start picking up SQL coding, and I do the same thing you mentioned...overlook the obvious trying to go advanced too soon...
Nice to know I'm not the only one...lol