EOF and .EOF not working with Do Until

[Robert M]

Do While Not .EOF
[CY#] = [CY#] + 1
docmd.GoToRecord , , acNext
Loop

The Above Program give me a "Compile Error Invalid or Unqualified Reference"
I have tried it with the Period an without and get the same error

What I am wanting to do is Increment the Cy# Field by 1

Using only Cy# = Cy# + 1 works great, but only for one record

What am I missing that needs to be done so that I am able to get this to run.

Thank you for your help in this problem

Robert M

 

[boblarson]

Duplicate of this thread

 

[pbaldy]

You don't show the rest of your code, but typically you wouldn't have this:

docmd.GoToRecord , , acNext

but this:

.MoveNext

You're advancing the record the form is on, rather than the record the recordset is on.

 

[Robert M]

Thank you PBaldy I have made the change to the program (which is shown in it's entirety" However, I am still getting a compile error with the EOF and .EOF Can I get any help with this?

Thank you for the help you have given me.

Robert M

 

[pbaldy]

If that's all of it, you're missing a lot. I would expect declarations, the opening of a recordset, and a With statement to precede that. What is it you're trying to accomplish?

 

[Robert M]

What I am trying to accomplish is...
The data I obtain is good for 3 years, after that new data will come in and the old data needs to be set asside.
What I have done is create a field called "Cy#" that will allow me to filter the older data with the newer data by the number in the "Cy#" field
What I am working with now is creating a loop that will automatically advance the old data "Cy#" and allow the new Data to display.
Typing this I could probably create a filter system that will keep the older data from being visible, but for me that is harder than what I am trying to do now.

Note: I have 3 books that I am using to learn Access and VBA as well as a few Internet sites like this one.
The books are...
"Step by Step Microsoft Visual Basic 6.0" (although I do not have a compiler to do the examples in it)
"Begginning Access 2000 VBA"
And
"Miscrsoft Access 2000 Bible"
I use these books as references along with the internet and the F1 key A lot of what I read I do not comprehend although I do understand what it is supposed to do.

Thanks again for your patience and help in this matter. Do you have any suggestions on the best way to handle this?

Robert M

 

[pbaldy]

Not positive I follow completely, but if you're trying to advance all records by 1, I'd use a query:

UPDATE TableName
SET [CY#] = [CY#] + 1

That said, I would just use a query to retrieve only the last 3 years data. By the way, the # symbol in the field name is not a good idea.

 

[dcb]

Timestamp your records and filter by the timestamp surely? Why update?

 

[Robert M]

That is a very good suggestion, what is timestamping (I already have a full month day year field as well as a year field) and filtering by that will cause me to hit the books again (which I don't mind) I update because we only need the data for 3 years. After that new data will come in for another 3 year period, however we will still need to keep the older data for our records for about 9 years.

 

[dcb]

If you already have a field showing when the record was created then just filter by that field

A column with
DataType set to GeneralDate and Default Value = Now()

Any record inserted will get the timestamp it was created on
If you update the record the timestamp will not change unless you tell it too

 

[Robert M]

Good morning and thank you for your help and suggestion. I think I will have to go with the filter solution as I already have the user entering the date afterwhich the computer right trims the date to get only the year in its own field. Will study up on getting a procedure that will filter out three years at a time depending on what the year the user select.

Thank you again for your help and patience with me

Robert M

 

[dcb]

good stuff
simplest solution......