By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,224 Members | 1,143 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

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

P: 12
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!
Aug 12 '07 #1
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,669
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.
    Expand|Select|Wrap|Line Numbers
    1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MyRS_2 As DAO.Recordset
    2. Dim strUniqueIDs As String, intCounter As Integer, MySQL As String
    3.  
    4. 'Generate Unique [OrderID]s
    5. strUniqueIDs = "SELECT DISTINCT OrderID FROM tblItems;"
    6.  
    7. Set MyDB = CurrentDb()
    8. Set MyRS = MyDB.OpenRecordset(strUniqueIDs, dbOpenDynaset)
    9.  
    10. MyRS.MoveFirst
    11. Do While Not MyRS.EOF
    12.   If DCount("[ItemNo]", "tblItems", "[OrderID]=" & MyRS![OrderID]) <= 10 Then
    13.     DoCmd.SetWarnings False
    14.       DoCmd.RunSQL "Update tblItems Set [UsageType]= 'pre-paid' Where [OrderID]=" & MyRS![OrderID]
    15.     DoCmd.SetWarnings True
    16.   Else     '> 10 Items for this OrderID
    17.     MySQL = "Select * From tblItems Where [OrderID] = " & MyRS![OrderID]
    18.     Set MyRS_2 = MyDB.OpenRecordset(MySQL, dbOpenDynaset)
    19.       MyRS_2.MoveLast: MyRS_2.MoveFirst
    20.       For intCounter = 1 To MyRS_2.RecordCount
    21.         If intCounter <= 10 Then    '1st 10 are pre-paid
    22.           MyRS_2.Edit
    23.             MyRS_2![UsageType] = "pre-paid"
    24.           MyRS_2.Update
    25.         Else   'overages
    26.           MyRS_2.Edit
    27.             MyRS_2![UsageType] = "invoice"
    28.           MyRS_2.Update
    29.         End If
    30.         MyRS_2.MoveNext
    31.       Next
    32.   End If
    33.   MyRS.MoveNext
    34. Loop
    35.  
    36. MyRS.Close
    37. MyRS_2.Close
Aug 12 '07 #2

P: 12
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.
Aug 12 '07 #3

ADezii
Expert 5K+
P: 8,669
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!
Aug 12 '07 #4

P: 12
That worked perfectly! Thank you so much for your help.
Aug 12 '07 #5

P: 12
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.
Aug 12 '07 #6

ADezii
Expert 5K+
P: 8,669
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.
Aug 12 '07 #7

P: 12
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!
Aug 12 '07 #8

ADezii
Expert 5K+
P: 8,669
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!
Aug 13 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.