Link to home
Start Free TrialLog in
Avatar of titorober23
titorober23

asked on

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

SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
JD ... WHAT ... is that skunk head, lol ???

mx
Avatar of titorober23
titorober23

ASKER

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

what else can be done
"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
<<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.
".MoveFirst after that right?"

Not really necessary.

mx
<<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.
Now that I look closer, It might be a beaver!

mx
You know what ... it's a Panda!!!

mx
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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial