473,324 Members | 2,567 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Recordset looping fails to compute in ms access 2007

Hi there.
I'm trying to write vba code to loop through a table and add to the total wherever a certain condition is met, based on the form input. Here is the code:
Expand|Select|Wrap|Line Numbers
  1. 'Assign date values(form input) into variables for easy manipulation
  2.     Dim StartDate As Date, EndDate As Date
  3.         StartDate = Me.datStart.Value
  4.         EndDate = Me.datEnd.Value
  5.  
  6.     'Create recordsets for storing database objects
  7.     Dim myRevenueSet As DAO.Recordset
  8.  
  9.     'Declare variable to store amounts
  10.     Dim curRevenueSum As Currency
  11.     curRevenueSum = 0
  12.  
  13.     'Create SQL statement to be used during looping
  14.     Dim myRevenueSQL As String
  15.     myRevenueSQL = "SELECT datRevenue, curRevenueAmount FROM tblRevenue;"
  16.  
  17.     Set myRevenueSet = DBEngine(0)(0).OpenRecordset(myRevenueSQL, dbOpenDynaset)
  18.  
  19.       'Start to loop in a table
  20.       Do While Not myRevenueSet.EOF
  21.  
  22.       'Check to see if the current record is within date range and if so add its value to a total
  23.              If DateDiff("d", StartDate, myRevenueSet!datRevenue) <= 0 And DateDiff("d", EndDate, myRevenueSet!datRevenue) >= 0 Then
  24.               curRevenueSum = curRevenueSum + myRevenueSet!curRevenueAmount
  25.              End If
  26.             'Move to the next record within the table
  27.              myRevenueSet.MoveNext
  28.       Loop
  29.       'Assign totals in specified textbox in the form
  30.       Me.curSales.Value = curRevenueSum
  31.       'Reset the recordset object
  32.       myRevenueSet.Close
  33.       Set myRevenueSet = Nothing
  34.  
I don't see anything wrong with the code, but it doesn't work. Anybody with the idea of what am I missing?
Jul 9 '14 #1

✓ answered by twinnyfo

I really think you are over-complicating things. This could be much slower if you had thousands and thousands of records.

Here is all you need:

Expand|Select|Wrap|Line Numbers
  1.     Dim myRevenueSet As DAO.Recordset
  2.     Dim myRevenueSQL As String
  3.     Dim curRevenueSum As Currency
  4.     curRevenueSum = 0
  5.     myRevenueSQL = "SELECT Sum(curRevenueAmount) AS TotalRevenue " & _
  6.         "FROM tblRevenue " & _
  7.         "WHERE datRevenue >= #" & Me.datStart & _
  8.         "# AND datRevenue <= #" & Me.datEnd & "#;"
  9.     Set myRevenueSet = DBEngine(0)(0).OpenRecordset(myRevenueSQL, dbOpenDynaset)
  10.     If Not myRevenueSet.EOF Then
  11.         curRevenueSum = myRevenueSet!TotalRevenue
  12.     Else
  13.         currevenusum = 0
  14.     End If
  15.     Me.curSales.Value = curRevenueSum
  16.     myRevenueSet.Close
  17.     Set myRevenueSet = Nothing
I would also recommend that you validate that the Start Date is before the End date, otherwise you could get some unexpected results.

Hope this helps!

7 1275
twinnyfo
3,653 Expert Mod 2GB
When and where is this code executing? How does it not work? Are you getting an error? Is it returning wrong values? Is it doing nothing?

Please explain so we can guide you to a solution.
Jul 9 '14 #2
I have put the code on the onClick event of the button that is on the input form, the same form where the result is supposed to be displayed, but on different textbox.
The code does nothing, retuns neither an error nor wrong values. Hence the textbox results dispay only its default value 0.0 as I have set it to the currency data type.
Hope this explanation will help.
Jul 9 '14 #3
twinnyfo
3,653 Expert Mod 2GB
I really think you are over-complicating things. This could be much slower if you had thousands and thousands of records.

Here is all you need:

Expand|Select|Wrap|Line Numbers
  1.     Dim myRevenueSet As DAO.Recordset
  2.     Dim myRevenueSQL As String
  3.     Dim curRevenueSum As Currency
  4.     curRevenueSum = 0
  5.     myRevenueSQL = "SELECT Sum(curRevenueAmount) AS TotalRevenue " & _
  6.         "FROM tblRevenue " & _
  7.         "WHERE datRevenue >= #" & Me.datStart & _
  8.         "# AND datRevenue <= #" & Me.datEnd & "#;"
  9.     Set myRevenueSet = DBEngine(0)(0).OpenRecordset(myRevenueSQL, dbOpenDynaset)
  10.     If Not myRevenueSet.EOF Then
  11.         curRevenueSum = myRevenueSet!TotalRevenue
  12.     Else
  13.         currevenusum = 0
  14.     End If
  15.     Me.curSales.Value = curRevenueSum
  16.     myRevenueSet.Close
  17.     Set myRevenueSet = Nothing
I would also recommend that you validate that the Start Date is before the End date, otherwise you could get some unexpected results.

Hope this helps!
Jul 9 '14 #4
Thanks for the reply twinnyfo. I at last manage to spot the real problem and make it work by the help of your code version and mostly by your ending hint;
I would also recommend that you validate that the Start Date is before the End date, otherwise you could get some unexpected results.
The problem was with the logic that I used in conditional statement while validating the Start Date and the End Date.
Thanks again for your help.
Jul 9 '14 #5
twinnyfo
3,653 Expert Mod 2GB
Glad I could be of service. Hope to see you on this forum again in the future!
Jul 9 '14 #6
Pretty sure about that. This is the second time I get help within hours for what has been my headache for days. So much appreciating the readiness of you guys to help.
Jul 9 '14 #7
For the sake of forum users:
If you are to use the code provided in post #4, it will work only if your data returns something. But when it returns nothing, such as it returns Null, an error 'invalid use of null' will occur. In order to avoid that, change that code into :
Expand|Select|Wrap|Line Numbers
  1. Dim myRevenueSet As DAO.Recordset
  2. Dim myRevenueSQL As String
  3. Dim curRevenueSum As Currency
  4.   curRevenueSum = 0
  5.   myRevenueSQL = "SELECT curRevenueAmount " & _
  6.         "FROM tblRevenue " & _
  7.         "WHERE datRevenue >= #" & Me.datStart & _
  8.         "# AND datRevenue <= #" & Me.datEnd & "#;"
  9. Set myRevenueSet = DBEngine(0)(0).OpenRecordset(myRevenueSQL, dbOpenDynaset)
  10. Do While Not myRevenueSet.EOF
  11.  curRevenueSum = curRevenueSum + myRevenueSet!curRevenueAmount
  12.  myRevenueSet.MoveNext
  13. Loop
  14. Me.curSales.Value = curRevenueSum
  15. myRevenueSet.Close
  16. Set myRevenueSet = Nothing
  17.  
All the best!
Jul 13 '14 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: bobdydd | last post by:
Hi Everybody I am currently updating a 2000.mdb to a 2007.accdb and I am trying to use the 3.6 DAO Object Library in the tools>references in the vb editor. This produces an error "Name conflict...
17
by: Neil | last post by:
A client of mine likes some of the new bells and whistles in Access 2007, and is thinking about converting our A03 format MDB to an A07 format file. However, while some of the users have A07, many...
2
by: ARC | last post by:
Just curious if anyone is having issues with Acc 2007 once the number of objects and complexity increases? I have a fairly large app, with many linked tables, 100's of forms, queries, reports, and...
2
by: Tom van Stiphout | last post by:
The code below works in Access 2000, not in Access 2007. ADO returns that it supports AddNew, but when I call that method it gives me an error: "Object or provider is not capable of performing...
6
by: tony.abbitt | last post by:
I have recently installed Office 2007 (SP1) retaining the previous installation of Office 2003. I have converted an Access 2003 database to Access 2007. The database contains the VBA code...
2
by: curran.george | last post by:
'add one textbox to form1 with Control Source property = ID 'copy/paste the form_load code below: 'Then open the form and then attempt to sort the datasheet 'crashes 2003, error 3450 Access 2007 -...
10
by: Arno R | last post by:
Hi all, So I bought a new laptop 10 days ago to test my apps with Vista. (home premium) Apparently Office 2007 is pre-installed. (a time limited but complete test version, no SP1) So I take the...
3
by: coalminer | last post by:
I am updating an Access 97 DB w/ VBA code to Access 2007. The VBA code uses DAO objects that Access 2007 references when I converted the db files (first from 97 to 2002, then to 2007). My problem...
1
by: JD79 | last post by:
I have an access 2007 database (on an XP machine) that extracts a large set of data from an Oracle db using ODBC. The data extraction is done with a dynamic passthru query that is then called in a...
5
by: jbrown8253 | last post by:
Microsoft Access 2007 Export Report for each record within a recordset I am attempting to click a button on a form that will export a report that is unique to each record. I created a query that...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.