0

I arrived at this problem after looking at the following two posts:

Displaying a Crosstab Query with a Parameter as Subform Source Object

how can i populate textbox through VBA in MS Access

TableA (sales data before crosstab transformation):

Row_ID | Year  |  CustomerID | Product |  Location

  001     2011      A00001     Muffin     GroceryStore
  002     2011      A00002     Muffin     Supermarket
  003     2012      A00001     Muffin     GasStation
  004     2012      A00002     CandyBar   GroceryStore
  005     2012      A00002     Muffin     GasStation

Currently, I have an access form that looks as follows:

Access form

In my form, I want users to be able to select a 'Year' and 'Location' value. Once they do so, I want them to see the number of muffins sold at that particular location in the control below the 'Location' combobox, the 'Muffin Count' control.

I have written VBA (see below) for the 'Location' control's OnClick Event. It not only generates the crosstab query that transforms my original table ('TableA'), but it also takes the value from the 'Muffins' column on the new crosstab table and places it in the 'Muffin Count' control:

VBA I have so far:

Private Sub cboChoice_1_Click()

Dim SQL As String
Dim db As Database
Dim rs As DAO.Recordset

SQL = "TRANSFORM Count(TableA.CustomerID) AS CountOfCustomers " _
& "SELECT TableA.Year, TableA.Location FROM TableA " _
& "WHERE TableA.Product='Muffin' " _
& "AND TableA.Year='" & Me.cboChoice_1.Value & "' " _
& "AND TableA.Location='" & Me.cboChoice_2.Value & "' " _
& "GROUP BY TableA.Year, TableA.Location PIVOT TablaA.Product;"
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL)


Me.txt_Muffin_Count.Value = rs!Muffins

End Sub

Challenge:

This schema works except when I chose a Year-Location combination where the value is expected to be Null (the implication being that there were no Muffins sold at that particular location for that particular year).

These occurrences (where Muffin Count is expected to be Null) also generate "Run-time error '3265' error Item not found in this collection".

I want to find a way (if possible) for my VBA to handle these null instances. My ultimate goal is to be rid of the "Run-time error '3265'..." message when making a Year-Location selection that generates a Null value.

Ideally, I would also like my VBA to simply insert the text "no records" in the 'Muffin Count' textbox control when this happens as well.

Community
  • 1
  • 1
ealfons1
  • 353
  • 1
  • 6
  • 24

2 Answers2

2

In this example, I checked whether the query had any results or not. If there are results, the number of muffins is set as you were doing before. If there are no results to the query, the number of muffins is set to 0. This should avoid the error for the case where there are no results.

if not rs.EOF
   Me.txt_Muffin_Count.Value = rs!Muffins
else
   Me.txt_Muffin_Count.Value = 0
end if
S. Miller
  • 379
  • 3
  • 15
  • 2
    This answer turned up in the low quality review queue, presumably because you didn't explain the code. If you do explain this (in your answer), you are far more likely to get more upvotes—and the questioner actually learns something! – The Guy with The Hat Jun 06 '14 at 20:00
2

You do not need to make a TRANSFORM for this OR create a query. Just add some code to the OnChange events of your comboboxes, checking if both comboboxes have a valid value. If so, fill the txt_Muffin_Count like this:

Me.txt_Muffin_Count = _
    DCount("RowID","TableA","Product = 'Muffin' " & _
                            "AND Year='" & Me.cboChoice_1.Value & "' " & _
                            "AND Location = '" & Me.cboChoice_1.Value & "'")

This will return a 0 instead of NULL for "No Sales".

Roland
  • 946
  • 8
  • 20