Printable View
Please could someone help me understand how to terminate a loop using the FindNext method in VBA. I'm trying to search through sets of records in a recordset to add up some values when my search criteria are met, so want the FindNext loop to terminate when the search criteria are no longer met. I'm sure this is a very simple request but can't work out what to do. All pointers appreciated.
Thank you.
PHB
Wouldn't you use the Exit keyword? Such as Exit For?Quote:
Originally Posted by PHB010108
What does the FindNext method in VBA have to do with VB.NET?
Don't think so, need to know when the FindNext completes ....Quote:
Originally Posted by Fromethius
Don't know. Are you telling me I'm asking in the wrong place? or are you trying to be helpful?Quote:
Originally Posted by jmcilhinney
Are you asking for help with VB.NET code? If not then you're in the wrong forum, given that this is the VB.NET forum.
Quote:
Originally Posted by jmcilhinney
Yes I'm asking for help with VB.NET code. The FindNext method seems to be valid for working with RecordSets in the VB.NET environment.
Can you help?
Well it's got nothing to do with VBA then. It's a member of the Recordset class.
In what context are you using a Recordset? It would generally be advisable to forgo ADO altogether and use ADO.NET instead.
Quote:
Originally Posted by jmcilhinney
Thanks. I've got a (large) set of records with multiple entries having the same date. I'm trying to subtotal the value of a particular field for the current date then write a record back to the record set for the next date using the subtotalled value, ie a simple bit of summation SQL won't work because each date sub-total is dependent on the previous date.
I'm therefore trying to use FindNext to find all records for a particular date so that they can be subtotalled. The problem I'm having is in understanding how to know when the FindNext "fails", ie does not find another record for the current date, ie so that I'm able to know to do the calculations/write the new record for the next date.
Hope that makes sense? - that you can help.
Many thanks.
Well, if you truly are using .NET, then you should be using ADO.NET and not ADO ... Then you would be able to do something like this:
-tgCode:For Each dRow As DataRow In myDataTable
.
.
.
'Do a calculation with dRow
.
.
.
Next
Lose the recordset, pull out the value in a DataTable. Use the DataTable.Compute method to sum up all the values where the date column has the date you need.
Why not do the whole thing in one query? Aren't you just getting the SUM of one field WHERE date = x? That could be written in a single SQL statement and returned using ExecuteScalar. However, if there are other needs for the records other than just the sum, then the datatable solution might well be better.
If you need to do multiple calculations then you'd get all your data into a DataTable and then use its Compute method, e.g.Follow the Data Access link in my signature to see how to get the data into the DataTable in the first place.vb.net Code:
Do indeed need other records which will be created as part of the partial summation of previous dates so seems like datatable is the way to go.Quote:
Originally Posted by Shaggy Hiker
Thanks for your response.
Thank you for your responseQuote:
Originally Posted by techgnome
Quote:
Originally Posted by jmcilhinney
Fantastic help - much appreciated, especially the Data Access link, then further links to the connection strings - fantastic!
(used to be a "classic" Fortran programmer working on giant geophysical modelling simulators so VB is a bit of a learning curve for me - will come back to you if get stuck if you don't mind)
I should point out that Compute returns an Object reference because it can return different types. That means that with Option Strict On, which it should be, you would need to cast the result as its actual type, e.g.vb.net Code: