Solved

Wrong value when coding  rsOld.RecordCount > 1

Posted on 2009-04-01
13
197 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
 

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

746 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

15 Experts available now in Live!

Get 1:1 Help Now