err 3265 <Item not found in this collection.>

[RekhillB]

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:

Select qry_EventCategoryTotals_VBA_subquery.CategoryName, Sum(tblWinnersItems.Amount) AS CategoryTotals
From qry_EventCategoryTotals_VBA_subquery Right Join tblWinnersItems On qry_EventCategoryTotals_VBA_subquery.ItemID = tblWinnersItems.ItemID
Group By qry_EventCategoryTotals_VBA_subquery.CategoryName;
 
 

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.

 
Option Compare Database
Option Explicit
Option Base 1
Public EventGrand_Total As Long 'works with different comBut
Public LiveAuction As Long
Public SilentAuction As Long
Public BigBoard As Long
Public SandwichBoard As Long
Public RaffleTickets As Long
Public PaddlesUp As Long
 
Private Sub comBut_CatTotals_Click()
 
    Dim rst As DAO.Recordset
    Dim db As DAO.Database
 
    Dim strCategoryName As Long   '..see below for different
        'strCategoryName = "BigBoard" ' Methods to return data
    Dim RaffleTickets As Long          'none are working
 
 
    Set db = CurrentDb
 
    Set rst = CurrentDb.OpenRecordset("qry_EventCategoryTotals_VBA")
 
            rst.MoveLast
                MsgBox "Number of records:     " & rst.RecordCount
            rst.MoveFirst
                                '... 6 ... records are shown ...see values below
                                                            '... from Debug.Print...
            Dim i As Long
            Dim eventResults As Long
                Do While Not rst.EOF
                        For i = 0 To rst.Fields.Count - 1
                          Debug.Print rst.Fields(i).Value '...see values below...
                                MsgBox rst.Fields(i).Value '...this works for
                        Next i                              '...my purpose
 
                    rst.MoveNext
             Loop
 
             MsgBox "Loop_Finished"  '.....finishes OK
 
        With rst
 
            Do While Not .EOF
                                  'Debug.Print rst.Fields(i).Value '...see values below...
 
        strCategoryName = rst.Fields(3).Value                   '1
                                                                '33500
            '..  = <Item not found in this collection.>
 
        'SilentAuction = rst.Fields(2).Value                    '2
                                                                '20990
            '..  = <Item not found in this collection.>
 
        'BigBoard = rst.Fields(4).Value                         '4
                                                                '2200
             '..  = <Item not found in this collection.>
 
        'SandwichBoard = rst.Fields(5).Value                    '5
                                                                '3100
             '..  = <Item not found in this collection.>
 
        'RaffleTickets = rst.Fields(6).Value                    '6
                                                                '480
             '..  = <No current record.>
 
        'PaddlesUp = rst.Fields(8).Value                        '8
                                                                '22500
             '..  = <Item not found in this collection.>
 
      rst.MoveNext
 
    Loop
 
  End With
 
        '...MsgBox values are all =  0
 
    'MsgBox "Live Auction Total=    " & LiveAuction _
    '        & vbCrLf & vbCrLf _
    '& "Silent Auction Total=    " & SilentAuction _
    '        & vbCrLf & vbCrLf _
    '& "Big Board Total=    " & BigBoard _
    '        & vbCrLf & vbCrLf _
    '& "Sandwich Board Total=    " & SandwichBoard _
    '        & vbCrLf & vbCrLf _
    '& "Raffle Tickets Total=    " & RaffleTickets _
    '        & vbCrLf & vbCrLf _
    '& "Paddles Up Total=    " & PaddlesUp, vbInformation, "Event Category Totals"
 
 
End Sub
 
 

[twinnyfo]

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!

[RekhillB]

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.

[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).

Option Compare Database
Option Explicit
Option Base 1
 
Private Sub comBut_CatTotals_Click()
On Error GoTo EH
    Dim db              As DAO.Database
    Dim rst             As DAO.Recordset
    Dim strEvents()     As String
    Dim intEvent        As Integer
    Dim lngGrandTotal   As Long
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("qry_EventCategoryTotals_VBA")
    With rst
        If Not (.BOF And .EOF) Then
            Call .MoveLast
            ReDim strEvents(.RecordCount, 2)
            Call .MoveFirst
            intEvent = 0
            lngGrandTotal = 0
 
            'Determine all events and numbers
            Do While Not .EOF
                intEvent = intEvent + 1
                strEvents(1, 1) = .Fields(0)
                strEvents(1, 2) = .Fields(1)
                lngGrandTotal = lngGrandTotal + .Fields(1)
                Call .MoveNext
            Loop
        End If
        .Close
    End With
    Call db.Close
    Set rst = Nothing
    Set db = Nothing
 
    'Display the results
    For intEvent = 1 To UBound(strEvents())
        Call MsgBox( _
            "Event: " & strEvents(intEvent, 1) & vbCrLf & _
            "Event Total: " & strEvents(intEvent, 2), _
            vbOKOnly, _
            "Event Total")
    Next intEvent
 
    Call MsgBox( _
        "Grand Total: " & lngGrandTotal, _
        vbOKOnly, _
        "Grand Total")
 
    Exit Sub
EH:
    MsgBox _
        "There was an Error!" & vbCrLf & vbCrLf & _
        "Number: " & Err.Number & vbCrLf & _
        "Descriptionis: " & Err.Description, _
        vbOKOnly + vbCritical, _
        "Error!"
    Exit Sub
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!

[RekhillB]

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!

[twinnyfo]

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!

[NeoPa]

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 :-)