• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

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

0
titorober23
Asked:
titorober23
  • 5
  • 5
  • 2
2 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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)PresidentCommented:
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 MVP, Access and Data Platform)Commented:
JD ... WHAT ... is that skunk head, lol ???

mx
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
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 MVP, Access and Data Platform)Commented:
"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)PresidentCommented:
<<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 MVP, Access and Data Platform)Commented:
".MoveFirst after that right?"

Not really necessary.

mx
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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 MVP, Access and Data Platform)Commented:
Now that I look closer, It might be a beaver!

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You know what ... it's a Panda!!!

mx
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now