I never use validatioin rule whether it be in a control or in the database.
I feel I have a lot more control using events. Someone else may have a
different opinion who has used them before. One of the main reasons I don't
is that almost every app I have done, the situation is that there are
business rules that have to be applied to the data in a record and two
conditions always arise.
1. If I choose 'Oranges' for [Fruit] then [Container] can only be 'Box', but
If I choose 'Apples' [Container] can be 'Box' or 'Bag' and if I choose
'Watermelon' then I can't put anything in [Container]. As you can see, there
is no way to do this with validation rules (that I know of). It all has to
be done with events. Sometimes it can be done in the control events and
sometimes it has to be at the form leve.
2. "Well," says the goofy user, "I know we have to have all these rules in
place, but sometimes I don't know all the answers at the time we do the entry
and I want to put in what I know now and complete the rest when I know it."
If you haven't heard this one, you will. In this case, If the data fails
validation, I have a yes/no field called [Pending]. I give them the option
of canceling (not entering the record) or saving it as "pending". I set the
pending flag to True. I then have to put logic elsewhere in the system to
deal with incomplete data. I also use it to create a report showing which
records are pending and what needs to be done to complete them.
BruceM said:
Thanks, I didn't know that. I take it that what you are saying would be the
case whether control validation is done through an event (such as a text
box's Before Update event) or through the control's properties (Validation
Rule). It doesn't really apply in this case, I don't think, since validation
is all at the form level. The main reason validation is needed is because
people have a way of not using their names or the date. There are also a few
other items people tend to overlook, such as summary (for the log). I'm not
all that picky about what they enter, as long as they enter something. Name
is from a limit to list combo box, and date must be in date format, so
there's not a lot of room for improvisation there, but the text (the main box
in each section) can be anything at all.
:
Okay, I see what you are doing, however, be aware that control events fire
before the form events fire. That is, when you start to move to another
record, if you have changed any data on the form (the form is dirty) the
order is:
Control Before Update
Control AfterUpdate
Form Before Update
Form After Update
So what you are doing is okay, but you can put validation code in a control
and it will be executed regardless of whether you have put anything in the
control or not.
:
Thank you so much. I think I see how that works, and at last I have a real
sense of what Cancel = True accomplishes. The only thing is that I would
need to check from the form's Before Update event rather than from the text
box Before Update event, because if the user never clicks into the text box
that event will never run. I think I will be able to figure that out
eventually, once I can get my brain wrapped around the concept. It sometimes
takes a while for me to assimilate new methods.
:
Cancel = true is the same as setting focus to yourself, which you cannot do
via other code. For example, if you are in Me.txtBox1, you cannot do
Me.txtBox1.Setfocus. However, tne Cancel = True leaves you in the state you
were in before you tried to move away from the control.
So, If you want to write a generic function that can be used by multiple
controls, instead of doing the actions in the code you sent, do them in the
event, but you can do the rest of the checking in the function:
Private Sub txtName_BeforeUpdate(Cancel As Integer)
Dim IntRetVal as Integer
IintRetVal = CheckTextValues(Me.txtName)
If intRetVal = vbCancel then
Me.Undo
Else If intRetVal = vkOk Then
Cancel = True
End If
End Sub
Function CheckTextValues( varValue as Variant) as Integer
If IsNull(varValue) or varValue = "" Then
CheckTextValue = MsgBox(strMsg, vbOKCancel, strTitle)
Else
CheckTextValue = 0
End If
End Function
Hope this will help
:
Thank you for the prompt reply. It's not that I was attempting to do
something outside the event that should be done from within the event. I
know it needs to be done inside the event. I'm trying to figure out the best
way. If a field that needs to be filled in is not filled in, the message box
appears, offering a choice between returning to the form to complete filling
it in and exiting without saving. I will use Me.Undo to exit without saving,
therefore it needs to be in the Before Update event.
Even as I was writing this I thought of something, and went back to change
the code. I may have solved most of the problem, but I don't quite
understand how.
Here is some of the (new) validation code in the Before Update event:
If IsNull(Me.txtName) Then
If MsgBox(strMsg, vbOKCancel, strTitle) = vbCancel Then
Me.Undo
Else
Me.txtName.SetFocus
Cancel = True
End If
ElseIf IsNull(Me.txtStartDate) Then
If MsgBox(strMsg, vbOKCancel, strTitle) = vbCancel Then
Me.Undo
Else
Me.txtStartDate.SetFocus
Cancel = True
End If
End If
strMsg and strTitle were defined earlier in the code, but the content is not
relevant to my question. There are four text boxes, but the code is similar
for the next two, so I left them out of this posting.
The idea with this code is that it check the text boxes, all of which need
to contain something in order for the record to be saved. If the user
selects Cancel the record is wiped clean. If they click OK they return to
the text box. The record is still not saved, which I have verified by
clicking a Me.Undo command button I placed on the form for test purposes.
It is not a big deal in this situation, but I wonder if I can avoid
repeating the same lines of code over and over? In other situations it could
become truly unwieldy.
Also, can you tell me what Cancel = True actually means? I sort of
understand when to use it, but what is the English language description of
the statement? The code I have posted here is somewhat different from the
way it was when I first posted in that I moved Cancel = True to the Else part
of the statement, and now it works as intended, but I don't quite know why.
:
Without seeing all your code, it is difficult to know. It is possible to
call out from an event. It is no different than any other sub. My guess is
that you are trying to do something outside that should be done inside.
If you are going out to validate data, then I would suggest that your call
be to a function that will return a value to tell you what is wrong and
evaluate the returned value in your event to take the necessary acitons. No
reason the message box can't be in the called function, but any actions like
the set focus or the undo should be in the event.
:
I asked a similar question in an earlier thread, but I think I wasn't as
specific as I should have been. I am using a form's BeforeUpdate event to
perform data validation. When something is found to be amiss, a message box
advises the user that they need to go back and fix something
(Me.ControlName.SetFocus), or that they can exit without saving (Me.Undo).
The same error message may be produced at different points in the Before
Update event. I cannot call an external procedure and then Undo from there
(can I?). I need to either pass the choice back to the Before Update event
(if that is possible) or else keep the message box within Before Update. Is
there any way to, in effect, call within the Before Update procedure so that
I don't need to enter the same code over and over? Or is there another
approach I haven't mentioned?
Regarding the code, I have used something like this for a message box with
choices:
If MsgBox ("Prompt",vbOKCancel,"Title") = vbOK Then
'Do something
Else: 'Do something else
End If
Is that the best way to handle that situation?
