Solved: Assign a value in a recordset

[bkudulis]

Is there a way to count through a recordset and assign a value to the amount of records that you want? I would like to have a form that I enter a number, let us say 24 as an example (This number will change for each recordset). Then I would like to open an update query (or anything else) and assign a period value of ?Period 1? to the first 24 records (That is what I entered in the form). The next 24 records I would like to enter ?Period 2? and so on until I complete ?Period 8?.

Real world situation
There is a list of list of things to be accomplished which is represented by the recordset that is in the order it is to be done in. The individual is capable of doing 24 of them in an hour. I would like to see at which point each individual will be at, or should be at, at each period.

[GP George]

Yes, you can do this with either ADO or DAO recordsets. Here's a DAO example.

Public Function fMarkItems(intStartItemCount as Integer, intEndItemCount as Integer)
Dim rs As DAO.Recordset
Dim I as Integer

Set rs = CurrentDb.OpenRecordset("tblYourTable")
With rs
For I = intStartItemCount to intEndItemCount
.Edit
!FieldtoUpDate = "Period 1"
.Update
.MoveNext
Next I
End With
End Function

=====

This code will update one field in the table, starting with the record you specify in the argument intStarttemCount and ending with the record you specify in the argument intEndItemCount.

HTH

George

[bkudulis]

I do not know if you have the time to do this or not but I am not sure how to do this. I take the code that you gave me and I create a module for it. How do I add the starting number and ending number? How do I call it from the form that I am placing the numbers in?

[bkudulis]

I was actually able to get this to somewhat work. I figured out what I asked you in my last reply but I have a different problem now.

It looks like the difference from the first number to the second number is the count of the records that gets updated from the first record. Let me see if I can say this better.

If I enter in 1 in the first field and 20 in the second it updates the 1st through the 20th records. If I put in 21 and 60 it updates the first 40 records. So it looks like it is not updating based on the position but calculating the difference and updating that number of records from the beginning. Is that what it is supposedd to be doing?

I appreciate your help, thank you.

[GP George]

You're right that the code I posted doesn't handle the second and subsequent groups of records correctly. It always STARTS with the first record.

Let me think about an alternative.

George

[GP George]

Which Version of Access are you using?

[bkudulis]

I am using Access 2000. Thanks.

[GP George]

I'm thinking about a solution which involves checking whether there is already a value in this field and only updating records where there is no value. In other words, once you've marked a group of records with, say "Period 1", those records would be ignored for the next set of marks. Would that work?

George

[GP George]

Check the demo in the attached db.

[bkudulis]

That's exactly it. Thank you very much for your help and the valuable lesson.