Looping in Module in Access Help

[Kitana907]

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:

 
Public Sub Inventory()
 
 Set Rs = db.OpenRecordset(tblstoreinv)
db.OpenRecordset (tbldcinv)
Rs.MoveLast
iRecCount = Rs.RecordCount
Rs.MoveFirst
For x = 1 To iRecCount
With Rs
If .Needed Is Null And .Needed < [tbldcinv]![DCOH] Then
.Needed = .Shipped
[tbldcinv]![DCOH] = [tbldcinv]![DCOH] - .Shipped
 
 
ElseIf .Needed Is Null And .Needed > [tbldcinv]![DCOH] Then
.Shipped = 0
 
End If
.MoveNext 'Next record and loop
End With
 
Next x
End Sub
 
 

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...

[MMcCarthy]

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?

[Kitana907]

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.

[MMcCarthy]

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.

 
Public Sub Inventory()
Dim db as Database
Dim rs1 as Recordset
Dim rs2 as Recordset
 
   Set db = CurrentDb
   Set rs1 = db.OpenRecordset(tblstoreinv)
   Set rs2 = db.OpenRecordset (tbldcinv)
 
   rs1.MoveFirst
   Do until rs1.EOF
      rs2.MoveFirst
      Do unitl rs2.EOF
         If rs1!ISBN = rs2!ISBN Then
            If IsNull(rs1!Needed) Or rs1!Needed < rs2!DCOH Then
               rs1.Needed = rs1.Shipped
               rs2!DCOH =rs2!DCOH - rs1.Shipped
            ElseIf rs1!Needed  > rs2!DCOH Then
               rs1!Shipped = 0
            End If
         End If
         rs2.MoveNext 'Next record and loop
      Loop
      rs1.MoveNext 'Next record and loop
   Loop
 
   rs1.close
   rs2.close
   Set rs1 = Nothing
   Set rs2 = Nothing
   Set db = Nothing
 
End Sub