Validation code

[Guest]

I am trying to verify an odometer entry. If this entry is less than a
previous, I want to be able to accept or redo the entry. I am trying to
combine two sets of code to accomplish this but I am having problems getting
it to work. The problem is in the strMessage. Here is my code:
Private Sub ValidateOdometer()
'verify that odometer entry is correct
Dim varPrevOdometer As Variant
Dim varPrevDate As Variant

varPrevOdometer = DLookup("MaxOfOdometer", "qryLastService")
varPrevDate = DLookup("ServiceDate", "qryLastService")

If Me.Odometer < varPrevOdometer Then
Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "The last service for this unit was on" & vbCrLf & _
varPrevDate & " at " & varPrevOdometer.vbCrLf & _
"Are you entering this out of order?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
Response = acDataErrAdded

Else
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

Thanks for your help!

 

[BruceM]

Why not a number (Double, maybe) for PrevOdometer and a date for PrevDate?
Also, I expect you want DMax rather than DLookup. In any case, without a
criteria DLookup is unlikely to return a useful result.
Your strMessage contains: varPrevOdometer.vbCrLf
This should be:
varPrevOdometer & vbCrLf
Where do you call this sub? The After Update event of various controls?
From what I can tell, you are undoing the control if Me.Odometer >
varPrevOdometer, as well as if the user clicks No at the message box. That
means the only value you will accept is one that is less than the previous
one. Is that your intention?

 

[Guest]

varPrevOdometer as variant came from the code in another form. I added
varPrevDate for this instance.

DLookup gets the MaxOfOdometer and ServiceDate as defined by qryLastService.

I call this from the Before Update event. If the entry is correct I want to
accept it and move to the next control. If it is incorrect I want to erase
the entry and focus remain in the control.

Thanks again!

 

[BruceM]

What is MaxOfOdometer? Where did it come from? Same for ServiceDate? Are
they guaranteed to come from the same record?
That you copied code that includes variant data types does not mean they are
the best choice in another situation.

If the odometer reading is to include decimals, try:
Dim dblPrevOdometer as Double

If no decimals, you could use:
Dim lngPrevOdometer as Long

In any case, use:
Dim datPrevDate as Date

Add the rest of the Dim statements:
Dim ctrl As Control
Dim strMessage As String

This brings me back to the question about the MaxOfOdometer and ServiceDate
fields.
The comment in my previous posting about the syntax of strMessage is still
relevant.

See comments in the code below. This is the code you provided, without
changes.

If Me.Odometer < varPrevOdometer Then
Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "The last service for this unit was on" & vbCrLf &
_
varPrevDate & " at " & varPrevOdometer.vbCrLf & _
"Are you entering this out of order?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
Response = acDataErrAdded
Else
ctrl.Undo
End If
' I think you could simplify this to:
If MsgBox(strMessage, vbYesNo + vbQuestion) = vbNo Then
ctrl.Undo
End If

Else
' This "Else" section comes into effect if Me.Odometer >=
varPrevOdometer
' (which I assume is what you would ordinarily expect).
' In all such cases, you undo the control.
' Entering a reading greater than the maximum previous is therefore
not allowed.
Response = acDataErrContinue
ctrl.Undo
End If

If you would rather accept a reading in which Me.Odometer >=
varPrevOdometer, just leave out the Else section.

 

[Guest]

I created a series of queries to find the max odometer and the date for the
truck contained in the truckID control of the current form. If I open the
query and supply a truck number it returns the appropriate record.
Here is the SQL:
qryLastService1;
SELECT tblServiceDetails.TruckID, Max(tblServiceDetails.Odometer) AS
MaxOfOdometer
FROM tblServiceDetails
GROUP BY tblServiceDetails.TruckID
HAVING (((tblServiceDetails.TruckID)=[forms]![frmServices]![TruckID]));

qryLastService2;
SELECT qryLastService1.TruckID, qryLastService1.MaxOfOdometer,
tblServiceDetails.ServiceDate
FROM qryLastService1 INNER JOIN tblServiceDetails ON
(qryLastService1.MaxOfOdometer = tblServiceDetails.Odometer) AND
(qryLastService1.TruckID = tblServiceDetails.TruckID);

As I stated, if I open the 2nd query, it returns the correct results.
However, when I check the variables in the code, they are empty. I have
redefined the variables as you suggested.
Thanks!