Catching nulls or empty strings part II

[Talismanic]

I am trying to catch empty fields in my form with this:

Private Sub PayDate_BeforeUpdate(Cancel As Integer)

Dim intQuest As Integer
If IsNull(Me.PayDate) Or Me.PayDate = "" Then

Me.PayDate.BackColor = vbRed
Me.PayDate.ForeColor = vbYellow
intQuest = MsgBox("You must enter a value for this field", _
vbOKOnly, "Entry Error")
Cancel = True

Else

Me.PayDate.BackColor = 65535
Me.PayDate.BackStyle = 0
Me.PayDate.ForeColor = vbBlack

End If
End Sub

However if the user tabs into the field and then out of it without typing anything Access is allowing the entry to continue. What is the problem? Shouldn't it be stopping the action until there is a value in that field.

By the way it does stop action if something it written and then removed, which to me seems like a problem with the Events timing. I would understand it if I was using the After Update but shouldn't the Before Updatate catch it?

 

[Fornatian]

Could you not fix this by putting a call:
PayDate_BeforeUpdate False
in the OnExit of the textbox?

 

[Talismanic]

I had problems with the On_Exit because I want to stop the user from going to the next text box. Once the cursor leaves the text box you lose control and can not set it back to the first text box without the control.

I did find this post from Pat Hartman answering some one else who asked a similar question that provides some insite and a possible solution for me. I'll let you know.

This quote was taken from Pat Hartman -
Since there is no way to force the user to enter a particular field, you really can't use the exit event or any field event for that matter, to trap for missing data. You need to put any edits of this type in the BeforeUpdate event of the FORM. In this event you would do what ever edits are necessary. Then if anything is missing or invalid, you would cancel the update event and display a message explaining the problem.

Private Sub Form_YourFormName_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Company_Name) or Len(Company_Name) = 0 Then
MsgBox "You must enter your company name"
Cancel = True
Me.[Company_Name].SetFocus = True
End If
End Sub

[This message has been edited by Talismanic (edited 07-26-2001).]

 

[Talismanic]

These don't work either:

If IsNull(Me.PayDate) Or Len(PayDate) = "" Then
If IsNull(Me.PayDate) Or Len(PayDate) = 0 Then

I don't understand what the deal is here?

 

[Talismanic]

Help me please, this is holding up the entire project.

Anybody?

 

[KeaganQuilty]

The problem you're having is not with your code per se, but with your placement of it.

You have this code in the BeforeUpdate event of your text box. It should be in the BeforeUpdate event of your FORM. Just like it says in Pat Hartman's quote.

Keagan Quilty
Kilbride,Newfoundland

 

[Talismanic]

I wondered about that for a brief second and then blew it off. I guess that is probably why he capitalized Form.

I have 10 fields to catch I am going to assume that this method will not work for multiple fields. What is the right way to run a check on multiple fields?

 

[Rich]

Didn't the answer I gave you offer a solution or are you looking to more than just force a required entry?

 

[Talismanic]

Rich, I assume you are referring to this thread, Topic: Validating fields before record update. My project manager wants the fields checked instantly and a message sent to the user instead of doing it at the end of the record entry.

It would seen that there would be an easy way to catch a null this way, but if there is, I havent found it yet. I know I could Require the field but I would lose the custom message and the visual que.

 

[Talismanic]

There are edits you can do in the BeforeUpdate event of a field if you want to notify the user immediatedly BUT, you still need to repeat those same edits in the BeforeUpdate event of the form for the reasons stated in my earlier post.

So I have to write code for both events, the forms catches entry problems and the fields takes care of messages and formatting.

If a field is null, that means that nothing was entered. If nothing was entered, the cursor was probably never placed in the field. Therefore you can't use any event associated with a control to check that control for nulls.

I don't understand this at all because the cursor is placed in the textbox, nothing is typed but there is a tab in and then a tab out. Isn't that considered focus?

PS, just because I only showed one edit in the example that you quoted doesn't mean that you can ONLY do one edit. You can put as many edits as you need to into that event. The point of using the BeforeUpdate event is that it is the LAST event executed before the table is actually updated. "The buck stops here.", so to speak. This event is even executed if the user trys to close Access entirely provided the current record is "dirty". This event is your last chance to cancel the update to prevent bad data from entering the table.

If the user enters data into a single field on your form and then clicks the Access close button. That partially completed record will be added to the table UNLESS your edits are ALSO placed in the FORM's BeforeUpdate event and the update is canceled if an error is encountered.

Have I said this enough times in enough different ways. Please your manager if you want to keep your job and put the edits in each control to make sure that the previous control is not null. BUT ALSO PUT THEM IN THE BEFOREUPDATE EVENT OF THE FORM!

If I have to do all that it may be better to go back to my original method, the one I got from Rich. Thanks for taking the time to try and get this through my thick skull

.



[This message has been edited by Talismanic (edited 07-30-2001).]

 

[Talismanic]

Thanks Pat. I think I finaly have it now.