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

Looping in Module in Access Help

P: 4
Hi-
I'm attempting to write a module that uses and updates info from two tables and does the following:

Opens the recordset of a table called "tblstoreinv"

If the Needed Field in the tblstoreinv table is null and is less than the DCOH field from the tbldcinv then:
The Shipped Field in the tblstoreinv table equals the Needed Field and the DCOH field from tbldcinv = DCOH - Shipped
Else If Needed from tblstoreinv is > DCOH from tbldcinv, then Shipped = 0.

The module would loop for every record.

Below is the code that I am using:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub Inventory()
  3.  
  4. Set Rs = db.OpenRecordset(tblstoreinv)
  5. db.OpenRecordset (tbldcinv)
  6. Rs.MoveLast
  7. iRecCount = Rs.RecordCount
  8. Rs.MoveFirst
  9. For x = 1 To iRecCount
  10. With Rs
  11. If .Needed Is Null And .Needed < [tbldcinv]![DCOH] Then
  12. .Needed = .Shipped
  13. [tbldcinv]![DCOH] = [tbldcinv]![DCOH] - .Shipped
  14.  
  15.  
  16. ElseIf .Needed Is Null And .Needed > [tbldcinv]![DCOH] Then
  17. .Shipped = 0
  18.  
  19. End If
  20. .MoveNext 'Next record and loop
  21. End With
  22.  
  23. Next x
  24. End Sub
  25.  
  26.  
When I get to this line (Set Rs = db.OpenRecordset(tblstoreinv)), it says "Object Required." Any idea how to fix this and make it run correctly?

Thanks for any help you can give...
Oct 13 '06 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry if this is a double post but the previous post I sent doesn't seem to be showing. To help you I need to know the relationship between the two tables. What is the field in common?
Oct 14 '06 #2

P: 4
Sorry if this is a double post but the previous post I sent doesn't seem to be showing. To help you I need to know the relationship between the two tables. What is the field in common?
The field in common is a text field called ISBN.
Oct 16 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
I had problems following some of your logic but I think this is what you are trying to do. If needed is null then it can't be less than DCOH so I changed And to Or. If needed is null is checked for the first if statement you can't check it again for the second if statement.
Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub Inventory()
  3. Dim db as Database
  4. Dim rs1 as Recordset
  5. Dim rs2 as Recordset
  6.  
  7.    Set db = CurrentDb
  8.    Set rs1 = db.OpenRecordset(tblstoreinv)
  9.    Set rs2 = db.OpenRecordset (tbldcinv)
  10.  
  11.    rs1.MoveFirst
  12.    Do until rs1.EOF
  13.       rs2.MoveFirst
  14.       Do unitl rs2.EOF
  15.          If rs1!ISBN = rs2!ISBN Then
  16.             If IsNull(rs1!Needed) Or rs1!Needed < rs2!DCOH Then
  17.                rs1.Needed = rs1.Shipped
  18.                rs2!DCOH =rs2!DCOH - rs1.Shipped
  19.             ElseIf rs1!Needed  > rs2!DCOH Then
  20.                rs1!Shipped = 0
  21.             End If
  22.          End If
  23.          rs2.MoveNext 'Next record and loop
  24.       Loop
  25.       rs1.MoveNext 'Next record and loop
  26.    Loop
  27.  
  28.    rs1.close
  29.    rs2.close
  30.    Set rs1 = Nothing
  31.    Set rs2 = Nothing
  32.    Set db = Nothing
  33.  
  34. End Sub
  35.  
  36.  
Oct 16 '06 #4

Post your reply

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