New topic: What record am I on?
<http://forums.realsoftware.com/viewtopic.php?t=30476> Page 1 of 1 [ 7 posts ] Previous topic | Next topic Author Message martinpaulrice Post subject: What record am I on?Posted: Wed Oct 14, 2009 9:13 am Joined: Wed Apr 11, 2007 1:17 pm Posts: 119 Location: Signal Mountain, TN I know that I can get the number of records in a RecordSet by the RecordSet.RecordCount property. But I'm not sure how to determine what record I'm on. For example, if I'm in a while loop such as: While NOT RecordSet.eof Process Process Process Process Wend How do I determine if I'm processing the last record of the set, that is, what kind of test can I do to find out? Thanks. _________________ Martin Signal Mountain, TN Top jefftullin Post subject: Re: What record am I on?Posted: Wed Oct 14, 2009 9:18 am Joined: Wed Nov 15, 2006 3:50 pm Posts: 838 Other than possibly to display a progress bar to show how much you've done, the 'last record' has no particular significance. It may not even be the same one every time, unless you actively sort the data. The while..wend loop takes care of this for you Code: While NOT RecordSet.eof . Process //one process, not (x) recordset.movenext //move to next record Wend //drops through here when all records are done. Top martinpaulrice Post subject: Re: What record am I on?Posted: Wed Oct 14, 2009 9:34 am Joined: Wed Apr 11, 2007 1:17 pm Posts: 119 Location: Signal Mountain, TN Well, in a way the fact that it drops through when all records are done is my problem because I want to do something right after the last record has been dealt with in the loop but before I drop through. But your post reminded me that a variable I'm working with within the loop still has it's value when I drop out of the loop and therefore I am able to do what I need to do. That probably doesn't make any sense to you, but nevertheless you got me looking at it differently, so Thanks! _________________ Martin Signal Mountain, TN Top DaveS Post subject: Re: What record am I on?Posted: Wed Oct 14, 2009 10:45 am Joined: Sun Aug 05, 2007 10:46 am Posts: 1659 Location: San Diego, CA be aware .... the the LAST record returned in a record set is NOT guaranteed to be the last record entered, or the last physical record in the file. A lot depends on SQLLites internal space management, the actual SQL statement you used to create the recordset etc. _________________ Dave Sisemore MacPro, OSX 10.5.8 RB2009r2 Top jefftullin Post subject: Re: What record am I on?Posted: Wed Oct 14, 2009 11:40 am Joined: Wed Nov 15, 2006 3:50 pm Posts: 838 Quote: a variable I'm working with within the loop still has it's value when I drop out of the loop There's a timebomb waiting to happen. What if there were NO records? Initialise the variable before you enter the loop. And maybe increment a record counter integer for every record you process, so that you can opt to do nothing if there were no records. Top martinpaulrice Post subject: Re: What record am I on?Posted: Wed Oct 14, 2009 12:19 pm Joined: Wed Apr 11, 2007 1:17 pm Posts: 119 Location: Signal Mountain, TN Thanks for the heads-up, Dave. I think I'm safe in this situation because of the closely defined SELECT statement I use for this record set. I posted it elsewhere in another thread, but here it is again: Code:sql = "SELECT Transactions.*,Budget.BudAcctType FROM Transactions" +_ " LEFT JOIN Budget ON Budget.BudAcctName = Transactions.TransTo " +_ "WHERE Budget.BudAcctType IN (" + "'" + "Income" + "'" + "," + "'" + "Expense" + "'" + ") and BudMonYr = " + "'" + App.LastUserInput + "'" + " ORDER BY Transactions.TransTo" What I do with the recordset that results after executing the sql is this: Code:// runTot used to keep running totals of the various accounts Dim runTot As Currency runTot = 0 // holdTransTo used for checking when the transaction account name changes Dim holdTransTo As String holdTransTo = "" // tot string to label breaking Account Totals // text appears red because of CellBackGroundPaint event Dim tot As String = "ACCOUNT TOTAL" // Get the initial account name rs.MoveFirst holdTransTo = rs.Field("TransTo").StringValue while not rs.eof // as long as the account name is the same, put the info in the listbox // and update the running total if holdTransTo = rs.Field("TransTo").StringValue then lbRepSum.AddRow(rs.Field("TransDate").StringValue) lbRepSum.Cell(lbRepSum.LastIndex, 1) = rs.Field("TransDesc").StringValue lbRepSum.Cell(lbRepSum.LastIndex, 2) = rs.Field("TransFrom").StringValue lbRepSum.Cell(lbRepSum.LastIndex, 3) = rs.Field("TransTo").StringValue lbRepSum.Cell(lbRepSum.LastIndex, 4) = rs.Field("TransAmt").StringValue runTot = runTot + val(rs.Field("TransAmt").StringValue) else // we've started a new account name -- first save it in holdTransTo // put the total for this account into the totals column on a new row holdTransTo = rs.Field("TransTo").StringValue lbRepSum.AddRow(" ") lbRepSum.Cell(lbRepSum.LastIndex,4) = tot lbRepSum.Cell(lbRepSum.LastIndex, 5) = str(runTot) bAB = lbRepSum.Cell(lbRepSum.LastIndex, 5) bAB = Format(val(bAB), "#####0.00") lbRepSum.Cell(lbRepSum.LastIndex, 5) = str(bAB) bAB = "" runTot = 0 lbRepSum.AddRow(rs.Field("TransDate").StringValue) lbRepSum.Cell(lbRepSum.LastIndex, 1) = rs.Field("TransDesc").StringValue lbRepSum.Cell(lbRepSum.LastIndex, 2) = rs.Field("TransFrom").StringValue lbRepSum.Cell(lbRepSum.LastIndex, 3) = rs.Field("TransTo").StringValue lbRepSum.Cell(lbRepSum.LastIndex, 4) = rs.Field("TransAmt").StringValue runTot = runTot + val(rs.Field("TransAmt").StringValue) end if ' Format the Account Total Amount bAB = lbRepSum.Cell(lbRepSum.LastIndex, 4) bAB = Format(val(bAB), "#####0.00") lbRepSum.Cell(lbRepSum.LastIndex, 4) = str(bAB) bAB = "" rs.MoveNext wend // Now put the total in the listbox for the last account lbRepSum.AddRow(" ") lbRepSum.Cell(lbRepSum.LastIndex,4) = tot lbRepSum.Cell(lbRepSum.LastIndex, 5) = str(runTot) So that's what you and the others have been helping me with so much over the last couple of days. So far it seems to work like a charm. Thanks!!!!!!!!! _________________ Martin Signal Mountain, TN Top martinpaulrice Post subject: Re: What record am I on?Posted: Wed Oct 14, 2009 12:23 pm Joined: Wed Apr 11, 2007 1:17 pm Posts: 119 Location: Signal Mountain, TN Jeff, I'd be notified if there were no records as far as I can tell because I test for an empty record set after I execute the select. You can see how I'm dealing with the last record in the code I included in my previous post. Thanks. _________________ Martin Signal Mountain, TN Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 7 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
