By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,962 Members | 2,545 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,962 IT Pros & Developers. It's quick & easy.

err 3265 <Item not found in this collection.>

P: 3
A form has 2 comButtons. The purpose is to provide user with MsgBox info related to Category Totals and one related to the sum of a table field.

The category query follows:
Expand|Select|Wrap|Line Numbers
  1. Select qry_EventCategoryTotals_VBA_subquery.CategoryName, Sum(tblWinnersItems.Amount) AS CategoryTotals
  2. From qry_EventCategoryTotals_VBA_subquery Right Join tblWinnersItems On qry_EventCategoryTotals_VBA_subquery.ItemID = tblWinnersItems.ItemID
  3. Group By qry_EventCategoryTotals_VBA_subquery.CategoryName;
  4.  
  5.  
The following VBA code does not return values from the recordset BUT I can not assign those values to the variable used in the one MsgBox at the bottom of code. So, as a 80/20 work around, I've used the values and field indexes shown in the Debug.Print loop to display the values in "multiple" MsgBoxs. I read the post about the 4 Methods that will produce the same results and I've tried them all. None of those methods work. Please Help. For reference, I am an old retired guy with a love of Access, SQL and VBA and this database is for an upcoming Non-Profit Fundraising Event. Thanks for your Help.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3. Option Explicit
  4. Option Base 1
  5. Public EventGrand_Total As Long 'works with different comBut
  6. Public LiveAuction As Long
  7. Public SilentAuction As Long
  8. Public BigBoard As Long
  9. Public SandwichBoard As Long
  10. Public RaffleTickets As Long
  11. Public PaddlesUp As Long
  12.  
  13. Private Sub comBut_CatTotals_Click()
  14.  
  15.     Dim rst As DAO.Recordset
  16.     Dim db As DAO.Database
  17.  
  18.     Dim strCategoryName As Long   '..see below for different
  19.         'strCategoryName = "BigBoard" ' Methods to return data
  20.     Dim RaffleTickets As Long          'none are working
  21.  
  22.  
  23.     Set db = CurrentDb
  24.  
  25.     Set rst = CurrentDb.OpenRecordset("qry_EventCategoryTotals_VBA")
  26.  
  27.             rst.MoveLast
  28.                 MsgBox "Number of records:     " & rst.RecordCount
  29.             rst.MoveFirst
  30.                                 '... 6 ... records are shown ...see values below
  31.                                                             '... from Debug.Print...
  32.             Dim i As Long
  33.             Dim eventResults As Long
  34.                 Do While Not rst.EOF
  35.                         For i = 0 To rst.Fields.Count - 1
  36.                           Debug.Print rst.Fields(i).Value '...see values below...
  37.                                 MsgBox rst.Fields(i).Value '...this works for
  38.                         Next i                              '...my purpose
  39.  
  40.                     rst.MoveNext
  41.              Loop
  42.  
  43.              MsgBox "Loop_Finished"  '.....finishes OK
  44.  
  45.         With rst
  46.  
  47.             Do While Not .EOF
  48.                                   'Debug.Print rst.Fields(i).Value '...see values below...
  49.  
  50.         strCategoryName = rst.Fields(3).Value                   '1
  51.                                                                 '33500
  52.             '..  = <Item not found in this collection.>
  53.  
  54.         'SilentAuction = rst.Fields(2).Value                    '2
  55.                                                                 '20990
  56.             '..  = <Item not found in this collection.>
  57.  
  58.         'BigBoard = rst.Fields(4).Value                         '4
  59.                                                                 '2200
  60.              '..  = <Item not found in this collection.>
  61.  
  62.         'SandwichBoard = rst.Fields(5).Value                    '5
  63.                                                                 '3100
  64.              '..  = <Item not found in this collection.>
  65.  
  66.         'RaffleTickets = rst.Fields(6).Value                    '6
  67.                                                                 '480
  68.              '..  = <No current record.>
  69.  
  70.         'PaddlesUp = rst.Fields(8).Value                        '8
  71.                                                                 '22500
  72.              '..  = <Item not found in this collection.>
  73.  
  74.       rst.MoveNext
  75.  
  76.     Loop
  77.  
  78.   End With
  79.  
  80.         '...MsgBox values are all =  0
  81.  
  82.     'MsgBox "Live Auction Total=    " & LiveAuction _
  83.     '        & vbCrLf & vbCrLf _
  84.     '& "Silent Auction Total=    " & SilentAuction _
  85.     '        & vbCrLf & vbCrLf _
  86.     '& "Big Board Total=    " & BigBoard _
  87.     '        & vbCrLf & vbCrLf _
  88.     '& "Sandwich Board Total=    " & SandwichBoard _
  89.     '        & vbCrLf & vbCrLf _
  90.     '& "Raffle Tickets Total=    " & RaffleTickets _
  91.     '        & vbCrLf & vbCrLf _
  92.     '& "Paddles Up Total=    " & PaddlesUp, vbInformation, "Event Category Totals"
  93.  
  94.  
  95. End Sub
  96.  
  97.  
4 Weeks Ago #1

✓ answered by twinnyfo

First, I think you are confusing (within your code, not necessarily within your mind) the difference between fields and records. Your query is returning 6 records, but your code is trying to look at 6 fields.

I've crunched together your code to make it a little prettier, more useable and safer. Here is what I have (and I will explain below).

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Option Base 1
  4.  
  5. Private Sub comBut_CatTotals_Click()
  6. On Error GoTo EH
  7.     Dim db              As DAO.Database
  8.     Dim rst             As DAO.Recordset
  9.     Dim strEvents()     As String
  10.     Dim intEvent        As Integer
  11.     Dim lngGrandTotal   As Long
  12.  
  13.     Set db = CurrentDb()
  14.     Set rst = db.OpenRecordset("qry_EventCategoryTotals_VBA")
  15.     With rst
  16.         If Not (.BOF And .EOF) Then
  17.             Call .MoveLast
  18.             ReDim strEvents(.RecordCount, 2)
  19.             Call .MoveFirst
  20.             intEvent = 0
  21.             lngGrandTotal = 0
  22.  
  23.             'Determine all events and numbers
  24.             Do While Not .EOF
  25.                 intEvent = intEvent + 1
  26.                 strEvents(1, 1) = .Fields(0)
  27.                 strEvents(1, 2) = .Fields(1)
  28.                 lngGrandTotal = lngGrandTotal + .Fields(1)
  29.                 Call .MoveNext
  30.             Loop
  31.         End If
  32.         .Close
  33.     End With
  34.     Call db.Close
  35.     Set rst = Nothing
  36.     Set db = Nothing
  37.  
  38.     'Display the results
  39.     For intEvent = 1 To UBound(strEvents())
  40.         Call MsgBox( _
  41.             "Event: " & strEvents(intEvent, 1) & vbCrLf & _
  42.             "Event Total: " & strEvents(intEvent, 2), _
  43.             vbOKOnly, _
  44.             "Event Total")
  45.     Next intEvent
  46.  
  47.     Call MsgBox( _
  48.         "Grand Total: " & lngGrandTotal, _
  49.         vbOKOnly, _
  50.         "Grand Total")
  51.  
  52.     Exit Sub
  53. EH:
  54.     MsgBox _
  55.         "There was an Error!" & vbCrLf & vbCrLf & _
  56.         "Number: " & Err.Number & vbCrLf & _
  57.         "Descriptionis: " & Err.Description, _
  58.         vbOKOnly + vbCritical, _
  59.         "Error!"
  60.     Exit Sub
  61. End Sub
Comments:
Lines 1-3: Good on you for having Option Explicit. I encourage you to have that as your default. Option Base 1 is only required if you are using Arrays and you want to start the Array at 1 instead of 0. Because I decided to use an Array, I left it in.

Line 6, 53-60: Try to get in the habit of always including some form of Error handling in your code. This is a basic set of code, but some folks can get quite elaborate with their error handling.

Lines 7-11: It is a good practice to declare all your variables up front (even if you end up not using them. This keeps your code clean, easier to track down and you don't have to worry if you have declared your variables yet. No need for a public variable, so I included it here. If you need it publicly, you've shown you know how to pull that out.

Lines 13-14, 32, 35-36: For all objects, if you set it to something, set it back to nothing. If you open something, close it. You close/set to nothing objects in the reverse order that you opened/set them. Notice the order in my code.

Lines 17-21: Notice how I am dimensioning an array dynamically. I've dimmed it for two columns wide (one column for the event and one for the number). It is a string array, which can accept numerical values, but a numerical array could not accept text. Just know what types of data you are dealing with when declaring your arrays. Even though you "know" that there are six records, what happens if you add some more events (or take some away)? You want to have the flexibility built into your code so that making simple changes like adding another event does not result in hours of recoding. As it is written now, you can add/remove events at will without any required recoding. I've also cleared some variables for future use in these lines.

Lines 24-30: This simply takes the values of your Query and assigns them to the Array. It also adds the number to your Grand Total.

Lines 38-45: You get a Message Box for each Event.

Line 47-50: Your grand total is displayed.

Hope this hepps!

Share this Question
Share on Google+
6 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,284
RekhillB,

Welcome to Bytes!

First, thanks for posting both your Query and the VBA code you are working with. That is always helpful, and exemplary for a first-timer on our forum.

If I understand you correctly, the Query you provide at the top of the thread is the same as "qry_EventCategoryTotals_VBA"? If so, your error is occurring because there are only two fields in that query, and starting in Line 50 of your Code, you are asking for the fourth and subsequent fields.

However, if the query is different from what you list up front, then your issue may stem from the fact that in line 45 and following, you begin looping through the recordset again. You have already reached the end of your recordset (from the previous code), so no values will be found. At a minimum, you would need a .MoveFirst prior to the second Do While ...

There are several other problematic areas with your code, but none that deal directly with your question. I'd be glad to clarify if you need me to.

Hope this hepps!
4 Weeks Ago #2

P: 3
twinnyfo, Thank you for quick response. Basis your comments I commented out the first "loop", also, revised the code to create the query, i.e., Set qdf = db.CreateQueryDef("", strSQL)...the strSQL is the same as posted earlier. The results is as you described, i.e., I replaced line 50 with the following: LiveAuction = rst.Fields(1).Value and the MsgBox (line 82) returned the correct value BUT the value corresponding to line 70 (PaddlesUp = rst.Fields(8).Value), rather than line 50's correct value?

If I un-comment the remainder (lines 83 - 92), I'll get err 3265. What I am totally confused about is the fact that I can use the first loop to return all six (6) Category Totals and they all are identical to running the query in Access?

Why can I "see" the other 5 totals in the recordset, even if I am using a two field query... 1st field for CategoryNames and 2nd field for the Totals's Values?

Also, why does the query/recordset confirm 6 records?

I know this is off the "thread" but I would appreciate your comments on the code, as I am completely self taught!...just reading books and using the internet? Thanks for your help, I really appreciate it.
4 Weeks Ago #3

twinnyfo
Expert Mod 2.5K+
P: 3,284
First, I think you are confusing (within your code, not necessarily within your mind) the difference between fields and records. Your query is returning 6 records, but your code is trying to look at 6 fields.

I've crunched together your code to make it a little prettier, more useable and safer. Here is what I have (and I will explain below).

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Option Base 1
  4.  
  5. Private Sub comBut_CatTotals_Click()
  6. On Error GoTo EH
  7.     Dim db              As DAO.Database
  8.     Dim rst             As DAO.Recordset
  9.     Dim strEvents()     As String
  10.     Dim intEvent        As Integer
  11.     Dim lngGrandTotal   As Long
  12.  
  13.     Set db = CurrentDb()
  14.     Set rst = db.OpenRecordset("qry_EventCategoryTotals_VBA")
  15.     With rst
  16.         If Not (.BOF And .EOF) Then
  17.             Call .MoveLast
  18.             ReDim strEvents(.RecordCount, 2)
  19.             Call .MoveFirst
  20.             intEvent = 0
  21.             lngGrandTotal = 0
  22.  
  23.             'Determine all events and numbers
  24.             Do While Not .EOF
  25.                 intEvent = intEvent + 1
  26.                 strEvents(1, 1) = .Fields(0)
  27.                 strEvents(1, 2) = .Fields(1)
  28.                 lngGrandTotal = lngGrandTotal + .Fields(1)
  29.                 Call .MoveNext
  30.             Loop
  31.         End If
  32.         .Close
  33.     End With
  34.     Call db.Close
  35.     Set rst = Nothing
  36.     Set db = Nothing
  37.  
  38.     'Display the results
  39.     For intEvent = 1 To UBound(strEvents())
  40.         Call MsgBox( _
  41.             "Event: " & strEvents(intEvent, 1) & vbCrLf & _
  42.             "Event Total: " & strEvents(intEvent, 2), _
  43.             vbOKOnly, _
  44.             "Event Total")
  45.     Next intEvent
  46.  
  47.     Call MsgBox( _
  48.         "Grand Total: " & lngGrandTotal, _
  49.         vbOKOnly, _
  50.         "Grand Total")
  51.  
  52.     Exit Sub
  53. EH:
  54.     MsgBox _
  55.         "There was an Error!" & vbCrLf & vbCrLf & _
  56.         "Number: " & Err.Number & vbCrLf & _
  57.         "Descriptionis: " & Err.Description, _
  58.         vbOKOnly + vbCritical, _
  59.         "Error!"
  60.     Exit Sub
  61. End Sub
Comments:
Lines 1-3: Good on you for having Option Explicit. I encourage you to have that as your default. Option Base 1 is only required if you are using Arrays and you want to start the Array at 1 instead of 0. Because I decided to use an Array, I left it in.

Line 6, 53-60: Try to get in the habit of always including some form of Error handling in your code. This is a basic set of code, but some folks can get quite elaborate with their error handling.

Lines 7-11: It is a good practice to declare all your variables up front (even if you end up not using them. This keeps your code clean, easier to track down and you don't have to worry if you have declared your variables yet. No need for a public variable, so I included it here. If you need it publicly, you've shown you know how to pull that out.

Lines 13-14, 32, 35-36: For all objects, if you set it to something, set it back to nothing. If you open something, close it. You close/set to nothing objects in the reverse order that you opened/set them. Notice the order in my code.

Lines 17-21: Notice how I am dimensioning an array dynamically. I've dimmed it for two columns wide (one column for the event and one for the number). It is a string array, which can accept numerical values, but a numerical array could not accept text. Just know what types of data you are dealing with when declaring your arrays. Even though you "know" that there are six records, what happens if you add some more events (or take some away)? You want to have the flexibility built into your code so that making simple changes like adding another event does not result in hours of recoding. As it is written now, you can add/remove events at will without any required recoding. I've also cleared some variables for future use in these lines.

Lines 24-30: This simply takes the values of your Query and assigns them to the Array. It also adds the number to your Grand Total.

Lines 38-45: You get a Message Box for each Event.

Line 47-50: Your grand total is displayed.

Hope this hepps!
4 Weeks Ago #4

P: 3
twinnyfo,
Thank You! Yes it "...hepps!..."
I really appreciate the code and the choice of an array. I've been trying to understand how to use arrays in vba and this is going to really help!...It'll take me a bit to digest all of this. Yes. I was confusing "...Your query is returning 6 records, but your code is trying to look at 6 fields...." I finally am beginning to understand there is a difference!
Again, Many Thanks!
4 Weeks Ago #5

twinnyfo
Expert Mod 2.5K+
P: 3,284
RekhillB,

It was all my pleasure. Please keep in mind that I am completely self-taught in Access as well. Many of the advanced formulations and "better understandings" I've learned over the years have come from this site. I am more than happy to give back.

Also, it is also refreching to find a first time poster who has put in the effort to try things and try different things and yet come up empty and then share their efforts for all to see. We get way too many posters here who say, "I want to do this! It doesn't work! Here is my DB! Fix it for me!"

I will put in extra time if you have put in the effort to work through a problem--which it was clear that you have tried a few things here and there which were not going to get you far. At least we were able to point you in the right direction and get a solution.

So glad I could hepp!
4 Weeks Ago #6

NeoPa
Expert Mod 15k+
P: 31,492
I would like to confirm everything that Twinny has said. As a long-time user of this site we always feel really quite comfortable going the extra mile when we can see that the OP (Original Poster - In this case you ReckhillB.) has put in the effort and has considered what would be required in order to be able to be helped.

So, congratulations on a great start and welcome to Bytes.com :-)
4 Weeks Ago #7

Post your reply

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