Looping in Module in Access Help

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Kitana907
    New Member
    • Sep 2006
    • 4

    #1

    Looping in Module in Access Help

    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 "tblstorein v"

    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:

    Code:
    Public Sub Inventory()
    
    [B]Set Rs = db.OpenRecordset(tblstoreinv)[/B]
    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.OpenRecordse t(tblstoreinv)) , it says "Object Required." Any idea how to fix this and make it run correctly?

    Thanks for any help you can give...
  • MMcCarthy
    Recognized Expert MVP
    • Aug 2006
    • 14387

    #2
    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?

    Comment

    • Kitana907
      New Member
      • Sep 2006
      • 4

      #3
      Originally posted by 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?
      The field in common is a text field called ISBN.

      Comment

      • MMcCarthy
        Recognized Expert MVP
        • Aug 2006
        • 14387

        #4
        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.
        Code:
         
        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

        Comment

        Working...