CODEWITHSUNDEEP
ms-accessvba
Shinilrk
Shinilrk

Reputation: 47

How to use two loops in recordset to update data

I'm having two tables which is having claims data and user name respectively
Tblimport- Contains 10 claim no.s and. tbl_Settings- Contains 3 username . I want to assign those 10 claims to the 3 users
Condition: same claim should not assign to different users and everyone should get the claims equally. I'm planning to use two loops with two record set. but problem while using do loop it's coming the same claim id to every one 

strqry = "Select [claimId] from Tblimport"
strqry2 = "Select [username] from tbl_Settings"
rs1.Open strqry2, cn1, adOpenStatic
rs2.Open strqry, cn2, adOpenStatic


rs2.MoveFirst
Do While Not rs2.EOF
If rs1.EOF Then
        rs1.MoveFirst
    End If

    claim = CStr(rs2![ClaimID])
    User = CStr(rs1![UserName])

    Debug.Print claim & "-" & User
        rs1.MoveNext
        If rs1.EOF Then
        rs1.MoveFirst
        End If

    rs2.MoveNext
Loop

Output I'm Getting 

100021245-shp  
100023633-abc  
114552236-kjh

In the 4th time while loop coming to User = CStr(rs1![UserName]) it's showing Run time error 94, invalid usse of null

Tblimport
100021245
100023633
114552236
121223333
122333444
112123345
111223344
112344543
322344455

tbl_Settings
shp
abc
kjh

Upvotes: 0

Views: 155

Answers (1)

HansUp
HansUp

Reputation: 97101

Seems your code mismatched recordsets for the ClaimID and UserId values. Or maybe I'm just confused.

Anyway what I think you want is to move through the ClaimID values, and for each of those sequentially fetch the "next" UserId. After you've fetched the "last" UserId, move back to the first again for the following ClaimID.

Sorry I can't think how to describe that more clearly. But what it come down to is revise your looping strategy.

rs2.MoveFirst
Do While Not rs2.EOF
    'Claim = CStr(rs1![ClaimID])
    'User = CStr(rs2![UserId])
    Claim = CStr(rs2![ClaimID])
    User = CStr(rs1![UserId])

    Debug.Print Claim & "-" & User

    rs1.MoveNext
    If rs1.EOF Then
        rs1.MoveFirst
    End If
    rs2.MoveNext
Loop

Upvotes: 2

Related Questions