Loop through a table and update first 10 records for each ID

[AppDev63]

I have a table in Access 2000 with these fields:

OrderID
ItemNo
DateUsed
UsageType

Customers are allowed 10 items per month for free, then the overages are billed separately, so there are multiple ItemNo for each OrderID. I need to update this table by setting the UsageType field to "pre-paid" for the first 10 records for each OrderID, then "invoice" for any overages.

How can I loop through the table and for each OrderID, mark the top 10 records one way and any that exceed the 10 count limit, mark them another way?

Thanks in advance for any help!

[ADezii]

I have a table in Access 2000 with these fields:

OrderID
ItemNo
DateUsed
UsageType

Customers are allowed 10 items per month for free, then the overages are billed separately, so there are multiple ItemNo for each OrderID. I need to update this table by setting the UsageType field to "pre-paid" for the first 10 records for each OrderID, then "invoice" for any overages.

How can I loop through the table and for each OrderID, mark the top 10 records one way and any that exceed the 10 count limit, mark them another way?

Thanks in advance for any help!

1. Before you do anything, you should read this Article:
   Database Normalization
   
2. I've made certain assumptions about your Table which are listed below:
   1. Table Name: tblItems
   2. Field1 Name: ItemID [PRIMARY KEY - AUTONUMBER]
   3. Field2 Name: OrderID [LONG]
   4. Field3 Name: ItemNo [LONG]
   5. Field4 Name: DateUsed [DATE]
   6. Field5 Name: UsageType [TEXT]
3. The following code will Update the UsageType Field to 'pre-paid' for all Items for which the count for a specific Order is 10 or below. If the number of Items are > 10, the UsageType will be updated to 'invoice' for those Items.
   

   Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MyRS_2 As DAO.Recordset
   Dim strUniqueIDs As String, intCounter As Integer, MySQL As String
    
   'Generate Unique [OrderID]s
   strUniqueIDs = "SELECT DISTINCT OrderID FROM tblItems;"
    
   Set MyDB = CurrentDb()
   Set MyRS = MyDB.OpenRecordset(strUniqueIDs, dbOpenDynaset)
    
   MyRS.MoveFirst
   Do While Not MyRS.EOF
     If DCount("[ItemNo]", "tblItems", "[OrderID]=" & MyRS![OrderID]) <= 10 Then
       DoCmd.SetWarnings False
         DoCmd.RunSQL "Update tblItems Set [UsageType]= 'pre-paid' Where [OrderID]=" & MyRS![OrderID]
       DoCmd.SetWarnings True
     Else     '> 10 Items for this OrderID
       MySQL = "Select * From tblItems Where [OrderID] = " & MyRS![OrderID]
       Set MyRS_2 = MyDB.OpenRecordset(MySQL, dbOpenDynaset)
         MyRS_2.MoveLast: MyRS_2.MoveFirst
         For intCounter = 1 To MyRS_2.RecordCount
           If intCounter <= 10 Then    '1st 10 are pre-paid
             MyRS_2.Edit
               MyRS_2![UsageType] = "pre-paid"
             MyRS_2.Update
           Else   'overages
             MyRS_2.Edit
               MyRS_2![UsageType] = "invoice"
             MyRS_2.Update
           End If
           MyRS_2.MoveNext
         Next
     End If
     MyRS.MoveNext
   Loop
    
   MyRS.Close
   MyRS_2.Close

[AppDev63]

Thank you so much! I'm going to try this right now. I'll read the information about normalization also, for future use, but this is a temporary app to handle an issue that will be put into our normal production stream (we have a development blackout right now) - I'm just trying to get something to work in the interim.

[ADezii]

Thank you so much! I'm going to try this right now. I'll read the information about normalization also, for future use, but this is a temporary app to handle an issue that will be put into our normal production stream (we have a development blackout right now) - I'm just trying to get something to work in the interim.

You are quite welcome - good luck on your project!

[AppDev63]

That worked perfectly! Thank you so much for your help.

[AppDev63]

I guess I spoke too soon. It ran beautifully during test with only 25 records in the table, but I just tried to run it on a full data set (90 records) and the UsageType field values were properly set, but I got a run time error: "Run-time error 91: Object variable or With block variable not set". Clicking "Debug" highlights this line of code: MyRS_2.Close

I tried using the help feature but I couldn't get it to work.

[ADezii]

I guess I spoke too soon. It ran beautifully during test with only 25 records in the table, but I just tried to run it on a full data set (90 records) and the UsageType field values were properly set, but I got a run time error: "Run-time error 91: Object variable or With block variable not set". Clicking "Debug" highlights this line of code: MyRS_2.Close

I tried using the help feature but I couldn't get it to work.

This would appear to be a Logic Bug, and would occur when no single OrderID has more than 10 Items associated with it. In this specific case, the Else Clause in Line #16 would never get executed, MyRS_2 would have never been created, but at the end of the Procedure an attempt is made to Close the Recordset (MyRS_2.Close). You can't close a Recordset that was never created in the first place. Rem Out or Delete the MyRS_2.Close line since it is not really needed, the Object Variable goes out of scope as soon as the Procedure ends anyway. Thanks for pointing out this flaw, and let me know how you make out. The number of actual Records should have no bearing on the code execution except for the time interval.

[AppDev63]

That's an interesting flaw - I never would have found it. I'm pretty much a novice - I know just enough to get myself into trouble. I did comment out the close rs line already, just to get it to execute. Now that I know that's okay I feel better about leaving it like that.

Again, thank you very much for your help. I'm embarrassed to say how many hours I spent working on this without being able to get it right. Now I can finish up this project and get back my nights & weekends. Thank you!

[ADezii]

That's an interesting flaw - I never would have found it. I'm pretty much a novice - I know just enough to get myself into trouble. I did comment out the close rs line already, just to get it to execute. Now that I know that's okay I feel better about leaving it like that.

Again, thank you very much for your help. I'm embarrassed to say how many hours I spent working on this without being able to get it right. Now I can finish up this project and get back my nights & weekends. Thank you!

I was just glad that I could help you in a small way, good luck!