Compare mileage in same field

[dmoran]

I can't seem to find an answer in the forum yet - I have a form where a vehicle number is selected from a combo-box and then a current mileage is added in the next field. Is there a simple way to compare the mileage added to the previous mileage entered to make sure the new number makes logical sense to the previous. In other words, a way to prevent the user from entering a smaller number or a significantly larger number that is not realistic to the prvious entry.
Thanks for any help -
David

 

[randy700]

How about this...
Include a hidden text box that also holds the current mileage. When the user changes the mileage in the visible form, compare the new number to the value in the hidden form. If it is smaller, don't allow the change and put up a message box explaining the error.

Randy

 

[kjv1611]

Could you not use the Dlookup function for pulling the stored miledge, and then compare it to the miledge entered? Sort of like this:

If DLookup("Milege", "tblYourTableName", "AutoID = cmbAutoID") > txtMilege + 5000 or Dlookup(same stuff) < txtMilege - 10000 Then

  ~Code you want run here

Else
   ~code you want run here
End If

Of course, the "Milege" would be the milege in your table, "tblYourTableName" would be the name of your table, "AutoID" would be the field in your table identifying each auto, txtMilege would be the control on your form where the user inputs the milege to compare against, I guess the new milege. cmbAutoID would be the control that contains the auto id for the automobile having the new data entered/compared against..

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[willir]

dmoran

Calculating differences such as milage is not easily done. (In fact, I joined Tek-Tips when I posted a similar problem - meter readings, instead of milage)

My solution was to...
- End user enters meter reading

When I need to perform an anlysis...
- Use an array to reference previous and current readings. This is typical programming instead of relational stuff.
- Write to a TEMP table for anlysis.

I will post some code tonight when I get home.

Richard

 

[willir]

dmoran

Public Sub load_array(strSQL1 As String, strTemp As String)

' NEED to pass SQL, TEMP file name

'Determine SQL to run
'- for now, restrict to each meter, for days running back
'- if meter or date select, reset
'Find size of array needed
'Define array
'Set cursor and load array
'Delete temp file
'Create temp file
'Prime temp file with data from array

Dim dbs As DAO.Database, rstR As DAO.Recordset, rstd As DAO.Recordset, rstt As DAO.Recordset
Dim intR As Long, intI As Long
Dim strSQL As String
Dim intMeter As Integer, intMid As Integer, intRid As Integer
Dim intRead As Integer, intPrev As Integer, intConsump As Integer, intDaysBN As Integer

Dim arMeter() As Long
Dim arDate() As Date
Dim arStatus() As String
Dim arAvgConsump() As Single
Dim arComments() As Variant

intMid = 1
intRid = 2
intRead = 3
intPrev = 4
intConsump = 5
intDaysBN = 6


Set dbs = CurrentDb()

Set rstR = dbs.OpenRecordset(strSQL1)

intR = rstR.RecordCount

If intR > 0 Then

    rstR.MoveLast
    intR = rstR.RecordCount
    
    rstR.MoveFirst
    
    ReDim arMeter(1 To 6, 1 To intR)
    ReDim arDate(1 To intR)
    ReDim arStatus(1 To intR)
    ReDim arComments(1 To intR)
    ReDim arAvgConsump(1 To intR)
    
    'Make table from call routine
    'Have to delete the table before here b/c it may be in use
    'This is now the responsibility of the calling module
    'MakeTempTable False, strTemp
    
    MakeTempTable True, strTemp
    
    Set rstt = dbs.OpenRecordset(strTemp)
    
    intMeter = 0
[COLOR=blue]    
    For intI = 1 To intR
        
        arMeter(intMid, intI) = rstR!meter_id
        arMeter(intRid, intI) = rstR!read_id
        arMeter(intRead, intI) = rstR!read
        arDate(intI) = rstR!read_date
        arStatus(intI) = rstR!get_var_name
        arComments(intI) = rstR!comments
        
        If intMeter <> arMeter(intMid, intI) Then
            intMeter = arMeter(intMid, intI)
            arMeter(intPrev, intI) = 0
            arMeter(intConsump, intI) = arMeter(intRead, intI) - arMeter(intPrev, intI)
            arMeter(intDaysBN, intI) = 0
            arAvgConsump(intI) = 0
        Else
            arMeter(intPrev, intI) = arMeter(intRead, intI - 1)
            arMeter(intConsump, intI) = arMeter(intRead, intI) - arMeter(intPrev, intI)
            arMeter(intDaysBN, intI) = DateDiff("d", arDate(intI - 1), arDate(intI))
            arAvgConsump(intI) = Format(arMeter(intConsump, intI) / arMeter(intDaysBN, intI))
        End If
[/color]        
[COLOR=red]        
        With rstt
            .AddNew
                !read_id = arMeter(intRid, intI)
                !meter_id = arMeter(intMid, intI)
                !read_date = arDate(intI)
                !status = arStatus(intI)
                !comments = arComments(intI)
                !read = arMeter(intRead, intI)
                !lngPrev = arMeter(intPrev, intI)
                !lngConsump = arMeter(intConsump, intI)
                !lngDaysBN = arMeter(intDaysBN, intI)
                !lngAvgConsump = arAvgConsump(intI)
            .Update
        End With
        
        rstR.MoveNext
[/color]    
    Next intI
    
    rstt.Close

End If

rstR.Close

End Sub

The array is loaded where text is blue. The TEMP table is updated where the text is red.

There are some serious coders who could make mince meat of the aforementioned but it got the job done for me.

Richard

 

[randy700]

If....
..your form is bound to the table/query.
..it includes a text box that identifies the vehicle.
..it includes a text box that displays current mileage.

Create a hidden text box that displays stored mileage (txtStoredMiles). Change the control source of the visible text box (txtCurrentMiles) to nothing. Add code to the OnCurrent event to make this value equal to txtStoredMiles. So far, the user sees no changes.

Now, when the user enters a new value, you can compare it to the value in txtStoredMiles. Something like this...

Select Case Me.txtCurrentMiles
Case < Me.txtStoredMiles:
MsgBox "Too small", vbOkOnly, "Entry Error"
Case > Me.txtStoredMiles + 500:
If MsgBox "R U Sure?", vbYesNo, "Verify Entry" = vbYes Then
Me.txtStoredMiles = Me.txtCurrentMiles
End If
Case Else:
Me.txtStoredMiles = Me.txtCurrentMiles
End Select



Randy