While Not EOF() 1

[wreded]

i've looked all over the place and haven't found an answer (or at least one i can understand). i have a vehicle database, once each year all mileage values are zeroed to accept new mileage values for the next year. i'm trying to create a button to step through each record until EOF() and zero the values. It looks like this:

CODE:
Private Sub Annual_Btn_Click()
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Do You Want to Overwrite Data ?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "MsgBox Demonstration" ' Define title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
Do While Not EOF()
[vehiclemaster]![jan_Start] = [vehiclemaster]![Dec_End]
[vehiclemaster]![Jan_End] = 0
[vehiclemaster]![Feb_End] = 0
[vehiclemaster]![Mar_End] = 0
[vehiclemaster]![Apr_End] = 0
[vehiclemaster]![May_End] = 0
[vehiclemaster]![Jun_End] = 0
[vehiclemaster]![Jul_End] = 0
[vehiclemaster]![Aug_End] = 0
[vehiclemaster]![Sep_End] = 0
[vehiclemaster]![Oct_End] = 0
[vehiclemaster]![Nov_End] = 0
[vehiclemaster]![Dec_End] = 0
enddo
Response2 = MsgBox("Done", vbOKOnly, "Okey - Dokey")

Else ' User chose No.
MyString = "No" ' Perform some action.
End If
End Sub

END CODE

If i use this code i get "Compile Error: Argument not optional." If i use rs.EOF() i get "Compile Error: Sub or function not defined." How in the (*&^% do You perform a While NOT EOF() in Access?

Thanks,
Dave

 

[PHV]

A while not eof of WHAT ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rubbernilly]

You've got some other things going wrong in that code, too...

Try this for a starting point

Dim rs As DAO.Recordset, i As Long

Set rs = CurrentDb.OpenRecordset("YourQueryName")

Do While Not rs.EOF
    i = i + 1
    rs.MoveNext
Loop
MsgBox i
Set rs = Nothing

Note the Do...Loop syntax and the fact that you have to step through the recordset (rs.movenext).

To execute this code, you will need a reference to the DAO library.

HTH

 

[wreded]

Thanks, i'll try it. i'm more used to old dbase syntax where You just tell it to loop while .NOT. EOF(). You don't have to count it or anything, just do it.
Dave

 

[rubbernilly]

I'm not counting in that syntax... I'm just doing something.

Anything.

What is important are these three lines:

[red]Do While Not rs.EOF

rs.MoveNext
Loop[/red]

 

[wreded]

OK, i copied Your code verbatim into my button onClick() event and remarked out all my code. i get "User defined type not defined." i tried "ADO." and empty; all with the same results. i'm using Acess 2003 and seem to get this error rather frequently. What's the answer to this one?
Thanks,
Dave

 

[rubbernilly]

Do you have the DAO library referenced?

In the VBE window choose Tools>References and find the Microsoft DAO 3.6 library.

 

[wreded]

Ok, this brings up two issues.
One, is there a way to programmatically ensure that the correct DAO library is referenced?
Two, following this same line of logic i have to DIM a variable for the individual fields i want to change...
Something like:

CODE:

Dim rf As DAO.Field

Set rf = CurrentDb.OpenRecordset.field("AnyFieldName")

ENDCODE

Then perform any actions i want on those variables. Is my understanding correct? If so, how would i go about SETing the variables? i haven't found that information yet.
Thanks,
Dave

 

[rubbernilly]

You don't have to dimension a DAO.Field. Once you have rs set, just reference the field names based off of that object:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("YourQueryName")

With rs
  Do While Not .EOF
[red]    ![Field1] = "Some Value"
    ![Field2] = "Some Value"[/red]
    .MoveNext
  Loop
end with
Set rs = Nothing

As for making sure the reference is set through code, someone else might have to answer that, as I just don't know.

 

[wreded]

Thank You very much. It doesn't appear as complicated as i was trying to make it.
Dave