Do While not .eof not working as expected

[SMD]

Code was changed according to suggestions so far. So I have deleted the original post to save screen space.

Please see code below

 

[Galaxiom]

Tables are unordered and you have not ordered the recordset so it may be different from what you expect.

Also you don't appear to be reading any values from the recordet.

BTW Please post code in a code box. This will allow you to indent it too.
Open the post in the Advanced Editor, select the code and hit the # button.

 

[Rabbie]

This link may help you find a different way of solving this.

 

[SMD]

Tables are unordered and you have not ordered the recordset so it may be different from what you expect.

Also you don't appear to be reading any values from the recordet.

BTW Please post code in a code box. This will allow you to indent it too.
Open the post in the Advanced Editor, select the code and hit the # button.

Thanks, when I get the code to work I will deal with putting the records in a order that I want. The data contains information from several different cars in our fleet, so order will be an important issue, later. I have had message boxes throughout earlier versions of code, these messages should that the code is looping through, the mileage entered was being read and manipulated, but instead of inserting it into the next record, it just restarts the loop from the top over and over again. I know I am missing something, just can see it. Thanks for the info on the code box, did not know that.

 

[vbaInet]

You are missing the two main points GalaxiomAtHome mentioned:

1. Your recordset is unordered. What is the previous record relative to? Should the recordset be ordered by the ID of that table?

2. You are not reading anything from the recordset. The whole loop is pointless if nothing is being read from the recordset. All you're doing is looping and not using the values of the records returned from it.

 

[SMD]

I been told I am missing the point. Okay let us same I am. I have updated the recordset so that it it appears in order that I want. This code is an event procedure on a button in a form. The Form and the code are using the same sorting (copy the SQL from the query straight onto the code.) If the Line "CurrentMileage = [Mileage]" is not reading the record then there is some serious s??t going on in my computer because the msgbox afterward keep on giving my the what ever value I type into the first record. The only command I do not see working is ".MoveNext". Record 1 just keeps on showing up 210 times.

Here is the code again, with the changes:

Private Sub Command149_Click()
    Dim dbs As DAO.Database
    Dim newKM As DAO.Recordset
    Dim strSQL As String
    Dim OldMileage As Double
    Dim CurrentMileage As Double
    Dim Dif As Double
    Dim Record As Double
    Dim intRet As Integer
    Dim strMsg As String
    Dim Msg, Style, Response, Mystring
    
    Set dbs = CurrentDb
    strSQL = "SELECT CarMileage.Date, CarMileage.Litres, CarMileage.[Price/L], CarMileage.[Bill Cost], CarMileage.[Car Name], CarMileage.Mileage, CarMileage.Currency_ID, CarMileage.[Cal Cost], CarMileage.KM, CarMileage.[KM/L], CarMileage.[KM/Cost], CarMileage.[Cents/KM] FROM CarMileage ORDER BY CarMileage.[Car Name], CarMileage.Mileage;"
    Set newKM = dbs.OpenRecordset(strSQL)
    OldMileage = 0
    Record = 0
                
    With newKM
        If .EOF Then
            GoTo ErrorHandler:
        Else
            strMsg = "Processing Mileage ..."
            intRet = SysCmd(acSysCmdInitMeter, strMsg, 100)
        End If
        
        Do While Not .EOF
            CurrentMileage = [Mileage]
            Dif = OldMileage - CurrentMileage
            MsgBox (CurrentMileage & ", " & OldMileage & ", " & Dif & ", " & CurrentRecord)
            If Dif > 0 Then
               .Edit
               !KM = Dif
               .Update
               OldMileage = Mileage
               MsgBox (CurrentMileage & OldMileage & Dif)
            Else
                Dif = 0
                OldMileage = 0
            End If
            
            If .PercentPosition <> 0 Then
                intRet = SysCmd(acSysCmdUpdateMeter, .PercentPosition)
            End If
            Record = Record + 1
            .MoveNext
        Loop
    End With

MsgBox (Record)
    
ErrorHandler:
        intRet = SysCmd(acSysCmdRemoveMeter)
        newKM.Close
    
End Sub

Here is 4 lines of the data:
CarMileage
ID Date Litres Price/L Bill Cost Mileage Car Name Currency Cal Cost KM KM/L KM/Cost Cents/KM
2 5/27/2007 32.703 $1.07 $35.06 30,566.00 2005 Aveo Canadian $35.06 0.00 0.00 0.00
3 6/5/2007 30.112 $1.03 $31.02 30,892.00 2005 Aveo Canadian $31.02 0.00 0.00 0.00
4 6/14/2007 31.588 $1.01 $32.00 31,251.00 2005 Aveo Canadian $32.00 0.00 0.00 0.00
5 6/20/2007 26.937 $1.04 $28.01 31,569.00 2005 Aveo Canadian $28.01 0.00 0.00 0.00

Any other suggestions?

 

[Galaxiom]

[Mileage] is an unqualified reference to an object. Access will take a guess (usually) starting with the name of a control on the form belonging to the module.

If you want to refer to the recordset yo must use:

recordsetname!fieldname

or if using the With Block:

With recordsetname
!fieldname
End With

However you need to start by attending to your data structure which is denormalized for severaL reasons.

Recording derived values. One of [Litres], [Price/L] and [Bill Cost] should be removed. The last four fields are all derived and represent different versions of the same information.

Storing the CarName in this table rather than an ID as a key to a Cars table.
Storing currency as text rather than an ID from a Currency table.

(BTW Special characters and spaces should not be used in field names.)

I would use a query to make the mileage calculation and would not store the results.

 

[boblarson]

Code was changed according to suggestions so far. So I have deleted the original post to save screen space.

Next time, please do NOT do that. It makes it difficult for anyone to see what the original problem was and then how to fix it. Remember, the forums are not just for the original poster's benefit. They are there for others who may be searching for a solution.

Bob Larson
Super Moderator
Access World Forums

 

[SMD]

[Mileage] is an unqualified reference to an object. Access will take a guess (usually) starting with the name of a control on the form belonging to the module.

If you want to refer to the recordset yo must use:

recordsetname!fieldname

or if using the With Block:

With recordsetname
!fieldname
End With

However you need to start by attending to your data structure which is denormalized for severaL reasons.

Recording derived values. One of [Litres], [Price/L] and [Bill Cost] should be removed. The last four fields are all derived and represent different versions of the same information.

Storing the CarName in this table rather than an ID as a key to a Cars table.
Storing currency as text rather than an ID from a Currency table.

(BTW Special characters and spaces should not be used in field names.)

I would use a query to make the mileage calculation and would not store the results.

Thanks for your help. The "!" on the front of the field made a difference. Not sure how, but it did. Got some reading to do, I guess. Regarding the comment "One of [Litres], [Price/L] and [Bill Cost] should be removed." Yes [Bill Cost] is not really needed, but it was a test field for the code. Yes the 4 fields are derived, but there is a reason for the this. The big boss wants this in excel, it is a lot easier to export a database table. Also if it works (and seems to have) it will not be just mileage but also all cost. Repairs and more. For this reason the Car name and Currency is a separate table. This way I can easily create another table for repairs and I will not be doubling up the values.

Thanks

 

[SMD]

Sorry, would not do it again.

 

[gemma-the-husky]

what columns exactly are you trying to compare, and what output do you want

if you upload or post a csv, it will be easy for someone to give you a simple procedure.

 

[Galaxiom]

Thanks for your help. The "!" on the front of the field made a difference. Not sure how, but it did. Got some reading to do, I guess.

The ! is the Bang Operator and it designates the following term is an object. The With Block base is only applied to terms that start with a bang or a dot.

Search the forum for something like: bang dot operator difference

Regarding the comment "One of [Litres], [Price/L] and [Bill Cost] should be removed." Yes [Bill Cost] is not really needed, but it was a test field for the code.

It isn't a bad idea to have all three input into the form since that could be used as an error check. Normally though this check should be done and discrepancies resolved at the form. Usually only two the values would be stored.

Yes the 4 fields are derived, but there is a reason for the this. The big boss wants this in excel, it is a lot easier to export a database table.

A query or recordset can be exported to Excel. They can also be imported by Excel.