MsgBox problem

[Walter]

Hi Dirk,
Sorry for taking so long to get back but I've been trucking. Gotta pay the
bills. I am trying to validate the trip date with this code. Chances of an
error are slim but people do make mistakes and I'm trying to cover all
possibilities. At present the date is entered first although not an absolute
necessity but it seems to me to be the most logical order. The TruckID is a
combo box with Limit to List set so it would be very unlikely to have a
mistake here. I had originally put the code in the AfterUpdate event but was
advised that it would be better it the BeforeUpdate event. My idea is you
know what truck you are working with but it is theoretically possible that
either a driver put a wrong date on a trip sheet or the date was entered
incorrectly. Generating this error will cause the user to be sure that the
data is correct. It seems to me that it would be better to validate now
rather than completing the whole form first in case there was a problem with
the trip sheet and the record could not be entered at this time. I don't
like the wasted effort of entering data that may be undone. My goal is to
make this as user friendly as I possibly can such as choosing from lists
whenever possible and also validating the other data to assure correctness.
Thanks again for your help and advice.
Walter

 

[Dirk Goldgar]

Hi Dirk,
Sorry for taking so long to get back but I've been trucking. Gotta
pay the bills.

I am trying to validate the trip date with this code.
Chances of an error are slim but people do make mistakes and I'm
trying to cover all possibilities. At present the date is entered
first although not an absolute necessity but it seems to me to be the
most logical order. The TruckID is a combo box with Limit to List
set so it would be very unlikely to have a mistake here.

But there's nothing, as far as I'm aware, to stop someone from selecting
or entering a TruckID that is valid (in the list), but not the correct
one.

I had
originally put the code in the AfterUpdate event but was advised that
it would be better it the BeforeUpdate event.

If you want to correct the user's entry in *TruckID*, that would be
true. But you tell me that you would rather assume that TruckID is
correct, and TripDate is wrong. In that case, the AfterUpdate event of
TruckID is more appropriate.

My idea is you know
what truck you are working with but it is theoretically possible that
either a driver put a wrong date on a trip sheet or the date was
entered incorrectly. Generating this error will cause the user to be
sure that the data is correct. It seems to me that it would be
better to validate now rather than completing the whole form first in
case there was a problem with the trip sheet and the record could not
be entered at this time. I don't like the wasted effort of entering
data that may be undone. My goal is to make this as user friendly as
I possibly can such as choosing from lists whenever possible and also
validating the other data to assure correctness.

It seems to me that the safest thing would be check for validity in both
the AfterUpdate event of TruckID and the BeforeUpdate event of the form
itself. The efficient way to do that would be to move the validation
code out into a separate function, and call that function from both
events. Something like this:

'----- start of code for form's module -----
Private Function ValidateTripDate() As Boolean

' Returns True if TripDate is valid for this TruckID,
' False if not.

Dim varLastTrip As Variant

varLastTrip = DMax("TripDate", "tblTrips", "TruckID='" & Me.TruckID
& "'")

If IsNull(varLastTrip) Then
ValidateTripDate = True
Else
If Me.TripDate >= varLastTrip Then
ValidateTripDate = True
Else

ValidateTripDate = False

MsgBox _
"The last trip entered for this truck was on " & _
varLastTrip & vbCrLf & _
"Please enter a date greater than or equal " & _
"to this date.", , _
"Invalid Date Entry"

Me.TripDate.SetFocus

End If
End If

End Function


Private Sub TruckID_AfterUpdate()

ValidateTripDate

End Sub



Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = ValidateTripDate()

End Sub
'----- end of code for form's module -----

Of course, the form's module may have other code in it, too; the above
would just be the routines that are relevant to this purpose.

 

[Walter]

Thanks. That seems to be working fine. I don't know how you're able to
spend the time you do helping people like me but I sure appreciate it.
Walter