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?
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
JD ... WHAT ... is that skunk head, lol ???
mx
mx
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
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
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.
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
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.
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
mx
You know what ... it's a Panda!!!
mx
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"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.