Validation Dilemma

[LQ]

I have a field on a form that I don't want users to leave blank. So in the On Click property of the exit button on that form, I put in

If IsNull(Me![Combo20]) Or Len(Combo20) = 0 Then
MsgBox "You must enter a payor!"
Else: DoCmd.Close
End If

This works fine under most circumstances. But if the user somehow hits the start new record button (or if they get to the record after end of the recordset) and then they try to exit, they are unable to do so (because the entire form is blank).

Is there some way to solve this? Or is it an insurmountable design problem? Any help would be appreciated...just keep in mind that my coding skills are at the beginner level (or below!)

Thanks!

 

[AlanS]

In the underlying table, set the field's Required property to Yes, and the AllowZeroLength property (if applicable) to No.

 

[LQ]

I may be doing something wrong, but I took out that bit of code and did as you suggested, AlanS, and the form lets me exit without telling me that I have to enter data into that field. I don't think that the data is being saved into the table, which is good, but I would like to let users know that they have to enter data into that field and give them the opportunity to do so. Otherwise, everything they just entered in (it's quite a lengthy form) will be lost.

Any further suggestions would be appreciated!

 

[jimbrooking]

You could also put something like the code you listed in the field's BeforeUpdate event handler, and set Cancel=-1 if there's an error in the field.

Try (In the Combo20 BeforeUpdate event):

If Nz(Me.[Combo20],"") = "" Then
Msgbox "You must enter a payor"
DoCmd.GotoControl "Combo20"
Cancel = -1
Exit Sub
End If

(And take the code out of the "start new record" button.)

Alan's solution is effective, but the user will get a rather less specific error message if s/he leaves out the payor number.

Jim

 

[charityg]

If you have an autonumber field, you can check that to make sure you aren't on a new record that entry hasn't been started on.

Just a little modification to your original setup.

If (IsNull(Me![Combo20]) Or Len(Combo20) = 0) and not isnull(me!autofield) Then
MsgBox "You must enter a payor!"
Else: DoCmd.Close
End If

 

[LQ]

Thank you everyone for your suggestions, but I am still having problems. I am still not getting any error message when I do not fill in the payor field. I tried Jim's suggestion for the Before Update event, but still no error message and I just exit (close) the form without the record being saved.

This may be a dumb question, but if I put the code in the fields BeforeUpdate event and the user never tabs into that field, would I still get the error msg?

Any further suggestions would be appreciated...this is an annoying little problem!

 

[Fornatian]

Shouldn't that be:

If IsNull(Me![Combo20]) Or Len(Combo20)= 0 And Not IsNull(Me!Question) Then
MsgBox "You must enter a payor!"
Else: DoCmd.Close
End If

You've missed =0 on the end of Len(Combo20)

Ian

 

[LQ]

Thanks, Ian! I had just figured it out when I read your post. There's a high "duh" factor on my part! Sometimes it's the little things that trip one up. I guess it's because I'm so new to writing code that I am convinced there must be something majorly wrong with it rather than a parenthesis or an = 0.

Many thanks to Charityg as well....it was your suggestion that helped me get it to work!

 

[dhoffman]

I have a question. If used the code from the original post, but changed the if statement to this:
If (IsNull(Me![Combo20]) Or Len(Combo20)=0) AND (not Me.NewRecord) THen

would that work?

 

[LQ]

It sounded good to me, but when I tried it out, it didn't work.

Thanks for the suggestion, though.

 

[LQ]

I just tried to enter the same code into a different form, and I cannot get it to work. Again, I am trying to force a user to fill in a certain field, namely "by". Here is the code I used in the exit button's On Click event:

If (IsNull(Me![By]) Or Len(By) = 0) And Not IsNull(Me.Action) Then
MsgBox "You must enter a name!"
Else: DoCmd.Close
End If

But this time, a msg box pops up to tell me I must enter a name, but then it goes ahead and closes the form anyway. This seems to defy logic and it is VERY aggravating. IF anyone can tell me what I am doing wrong, I would greatly appreciate it!

 

[Pat Hartman]

The code needs to be in the FORM's BeforeUpdate event to be certain that it will ALWAYS be executed before the record is saved and NEVER executed if the record was not updated. The exit button's click event isn't used if the user just scrolls to the next record. The BeforeUpdate is ONLY fired if the record is dirty - meaning that something was changed so it won't execute if the record was viewed but not updated.

Putting the edit in the field's events will not work reliably since it won't execute if the field is never entered.

 

[LQ]

I tried putting the code in the form's before update event as you suggested, and it did give me the msgbox, but what I would like to do is stop the user from going on to the next record or exiting the form if they haven't filled in the proper fields. I tried to set the focus on the field that is missing data, but was unable to do so. The form advanced to the next record, but I presume that the record with the missing data was not saved (?). Do you have any suggestions as to how I can "force" people to enter data into certain fields if other fields are filled in?

Thanks in advance!

 

[LQ]

An update on what I did (since I hate threads that leave you hanging!):

Although I understand Pat's point, I could not get the code to work properly when I placed it in the form's BeforeUpdate event. So I ended up putting the code in the exit button (I will also put it in the start new record button). It may be a lousy way to do it, but it was the only way I could get it to do what I wanted.

If (IsNull(Me![By]) Or Len(By) = 0) And Not IsNull(Me.Action) Then
MsgBox "You must enter a name", vbOKOnly
DoCmd.GoToControl "[By]"
Else: DoCmd.Close
DoCmd.OpenForm "Switchboard", acNormal, "", "", , acNormal
End If

If anyone has a better suggestion, then feel free to share...I only did what I did because I couldn't get it to work any other way!

Thanks!

 

[Rich]

It wouldn't work anywhere else because you haven't got Cancel=true.
If (IsNull(Me.By) Or Len(By) = 0) And Not IsNull(Me.Action) Then
Cancel=True
MsgBox "You must enter a name", vbOKOnly
By.SetFocus
End If

 

[Pat Hartman]

I don't know why you think this code is working in the exit event. Just because the form reopens doesn't mean that the table was not updated incorrectly. And that is what this edit is about after all.

To prove to yourself that your code is currently not working, add a messagebox to the Form's BeforeUpdate event and add one to the Form's Exit event. You will see that the BeforeUpdate event will execute BEFORE the Exit event. Also note that the table is updated by Access immediately after the BeforeUpdate event and before anything else happens. Therefore, no matter what your code does in the exit event, THE RECORD HAS ALREADY BEEN SAVED. You can liken this to closing the barn door after the horses get loose.

You'll probably end up needing code in both events. The code in the BeforeUpdate event to prevent bad data from being saved and code in the Exit event to prevent the form from being closed.

 

[LQ]

All right, Pat, you've convinced me...I don't want any horses escaping the barn. I do understand that the update happens before the exit event. I guess I was counting on the user entering in the missing data (because they would not be able to exit the form) and then re-updating the record. Now I am going to try putting code in *both* places. I was going to just put something like this in the form's Before Update event (note: the code relates to my original form, from the beginning of this post)

If IsNull(Me![Combo20]) Or Len(Combo20) = 0 And Not IsNull(Me!Question) Then
Cancel = True
End If

This *seems* to work (the record is not saved). Appearances can be deceiving, though, so if Pat or anyone else can suggest something further, please feel free to do so.

Thanks for making me think, Pat, although it's a bit difficult to do so on a Friday afternoon!

 

[LQ]

Will this issue never be resolved?!!

I put the following code in my form's before update event

If IsNull(Me![By]) Or Len(By) = 0 And Not IsNull(Me.Action) Then
Cancel = True
ElseIf IsNull(Me![By2]) Or Len(By2) = 0 And Not IsNull(Me.Action2) Then
Cancel = True
End If

This works fine only if *both* Action and Action2 are filled in. What I really want to do is to only require By2 *if* Action2 is filled in. What is happening is that nothing is getting updated unless both action fields are filled in, and this won't work since many times only the first of the action fields will be filled in.

How can I modify my code to check for the appropriate fields?

This is making me crazy, so any suggestions will be greatly appreciated.
Thanks!