?
Solved

Wrong value when coding  rsOld.RecordCount > 1

Posted on 2009-04-01
13
Medium Priority
?
206 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
13 Comments
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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 58
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 58
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 58
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 58
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

771 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