Wrong value when coding rsOld.RecordCount > 1

Hi Guys
I am transfering data from one table(ms access) to another using vb, if in the old table there is more than 1 location, the new value in the new table should be "Multiple".
for some reaon i am getting the location value instead of the word "multiple"

lets say
old-table
ID       location
1         arm
1         stomach

in the new table should be
ID       location
1         multiple

but i am getting
ID       location
1         arm


any comments?

Public Sub CopyLocPTDiag(ByVal POldID As Long, ByVal PNewID As Long)
    
    Dim rsOld As DAO.Recordset
    Dim rsNew As DAO.Recordset
    
    Set OldDb = DBEngine.Workspaces(0).OpenDatabase("C:\Documents and Settings\rpazmino\Desktop\Migration-Module\Patient data.mdb")
    Set rsOld = OldDb.OpenRecordset("SELECT * FROM [Locations of Primary Tumors at Diagnosis] WHERE [Patient Number] = " & POldID)
    Set rsNew = CurrentDb.OpenRecordset("TumorLocationDetails")
    
    'Iterate through records from old table
    Do While rsOld.EOF = False
    
        'Add record into new table
        rsNew.AddNew
        LocID = LocID + 1
        'Copy values over
        rsNew!TumorLocationID = LocID
        rsNew!PatientID = PNewID
        rsNew!Event = "Diagnosis"
        rsNew!TumorType = "Primary Tumor"
        If rsOld.RecordCount > 1 Then
            rsNew!TumorLocation = "Multiple"
            Exit Do
        Else
            rsNew!TumorLocation = rsOld![Location of Primary Tumor at Diagnosis]
    
        End If
        
        'Save
        rsNew.Update
        
        rsOld.MoveNext
    Loop
    rsOld.Close
    rsNew.Close
    
End Sub

Open in new window

titorober23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<rsOld.RecordCount >>
  You can't count on recordcount to be correct when you first open a recordset other then it being 0 to indicate that there are records.  
  To make it accurate, you first need to do a .MoveLast.
JimD.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I should have said:
"You can't count on recordcount to be correct when you first open a recordset other then it being >0 or 0 to indicate that there are records or not".
JimD.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
JD ... WHAT ... is that skunk head, lol ???

mx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
titorober23Author Commented:
I used .Movelast
did not work, with movelast is not giving any record for those id with multiple locations.

what else can be done
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"visible only in the premium skin"
My favorite of course ! That's the best looking skunk I've ever seen, lol !

EE's joke huh ... yeah, well ... lets hope Conficker does not hit EE !!!

mx
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I used .Movelast
did not work, with movelast is not giving any record for those id with multiple locations.>>
  You did do a .MoveFirst after that right?
JimD.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
".MoveFirst after that right?"

Not really necessary.

mx
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<JD ... WHAT ... is that skunk head, lol ???>>
I almost missed that LOL. I use the Expert skin all the time, so I didn't see anything. And my guess it is a skunk knowing certain Admins<g>
JimD.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Now that I look closer, It might be a beaver!

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You know what ... it's a Panda!!!

mx
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Actually, now that I look again, it is a badger.  Skunks have a more pointed nose and are not as marked on their faces as much as that.
  I think it would have been funnier as a skunk though; Peppie L'Pue (sp?) would have been a riot.
  OK, enough of that...this is not the lounge and I've got way too much work to do<g>.
JimD.
0
 
titorober23Author Commented:
I got, my mistake was that i did not update before the Exit Do. So it was not saving "multiple".

        If FRC > 1 Then
            rsNew!TumorLocation = "Multiple"
            rsNew.Update
            Exit Do

Now is working

thanks a lot

   
    Do While rsOld.EOF = False
        rsOld.MoveLast
       
        If rsOld.EOF Then
            FRC = 0
        Else
            rsOld.MoveLast
            FRC = rsOld.RecordCount
        End If
        'Add record into new table
        rsNew.AddNew
        LocID = LocID + 1
        'Copy values over

        If FRC > 1 Then
            rsNew!TumorLocation = "Multiple"
            rsNew.Update
            Exit Do
        Else
            rsNew!TumorLocation = rsOld![Location of Primary Tumor at Diagnosis]
   
        End If
       
        'Save
        rsNew.Update
       
        rsOld.MoveNext
    Loop
    rsOld.Close
    rsNew.Close
   
End Sub
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.