Solved

Wrong value when coding  rsOld.RecordCount > 1

Posted on 2009-04-01
13
199 Views
Last Modified: 2013-11-25
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
Comment
Question by:titorober23
  • 5
  • 5
  • 2
13 Comments
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 total points
ID: 24040928
<<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
 
LVL 57
ID: 24040940
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
 
LVL 75
ID: 24041484
JD ... WHAT ... is that skunk head, lol ???

mx
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:titorober23
ID: 24041647
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
 
LVL 75
ID: 24041693
"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
 
LVL 57
ID: 24041708
<<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
 
LVL 75
ID: 24041731
".MoveFirst after that right?"

Not really necessary.

mx
0
 
LVL 57
ID: 24041742
<<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
 
LVL 75
ID: 24041760
Now that I look closer, It might be a beaver!

mx
0
 
LVL 75
ID: 24041784
You know what ... it's a Panda!!!

mx
0
 
LVL 57
ID: 24041796
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
 

Accepted Solution

by:
titorober23 earned 0 total points
ID: 24041882
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now