Filter Reports Using Combo Boxes on Pop-Up Form

[besuchanko]

Hello. I have this problem partially solved and I can't figure out the rest. I have a form that I created to run report. The form has two drop-down boxes on it. The first drop-down box is used to select the report, and the second drop-down box is used to filter the report (by session).



1) This works alright for my Checklist_Report, but generates an error if I don't make a selection in the filter drop-down. I'd like to be able to filter the report either by session, or generate the report showing all sessions. How do I do this?

2) My second report, Presenter Data Report, is actually just code that generates a formatted Excel spreadsheet. How on earth would I apply the filter to that as in #1 above??

If I can master this, I'm golden. Anyone that can help me fix this will be my hero.

Here's my code that's attached to the button (Combo5) on my form.

Private Sub Command7_Click()
If Combo5 = "Checklist Report" Then
DoCmd.OpenReport "Checklist_Report", acViewPreview, , "SessionNum = '" & Me.Combo9 & "'", acWindowNormal
ElseIf Combo5 = "Presenter Data Report" Then
Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Dim rst As DAO.Recordset
   'Start a new workbook in Excel
   Set oExcel = CreateObject("Excel.Application")
   oExcel.Visible = True
   Set oBook = oExcel.Workbooks.Add
   Set rst = CurrentDb.OpenRecordset("PresenterDataReport_Query")
   
   'Add data to cells of the first worksheet in the new workbook
   Set oSheet = oBook.Worksheets(1)
   oSheet.Range("A1:AH1").Font.Bold = True
   
   oSheet.Range("A1").Value = "PM"
   oSheet.Range("B1").Value = "Session Number"
   oSheet.Range("C1").Value = "Session Title"
   oSheet.Range("D1").Value = "Session Date"
   oSheet.Range("E1").Value = "Start Time"
   oSheet.Range("F1").Value = "End Time"
   oSheet.Range("G1").Value = "Repeat Date"
   oSheet.Range("H1").Value = "Repeat Start Time"
   oSheet.Range("I1").Value = "Repeat End Time"
   oSheet.Range("J1").Value = "Faculty Name"
   oSheet.Range("K1").Value = "Last Name"
   oSheet.Range("L1").Value = "Roles"
   oSheet.Range("M1").Value = "Salutation"
   oSheet.Range("N1").Value = "Email"
   oSheet.Range("O1").Value = "Primary Position"
   oSheet.Range("P1").Value = "Primary Employer"
   oSheet.Range("Q1").Value = "Employer City"
   oSheet.Range("R1").Value = "Employer State"
   oSheet.Range("S1").Value = "Employer Province"
   oSheet.Range("T1").Value = "Employer Country"
   oSheet.Range("U1").Value = "Biography"
   oSheet.Range("V1").Value = "Business Phone"
   oSheet.Range("W1").Value = "Home Phone"
   oSheet.Range("X1").Value = "Cell Phone"
   oSheet.Range("Y1").Value = "Address Type"
   oSheet.Range("Z1").Value = "Business Address Line"
   oSheet.Range("AA1").Value = "Address 1"
   oSheet.Range("AB1").Value = "Address 2"
   oSheet.Range("AC1").Value = "City"
   oSheet.Range("AD1").Value = "State"
   oSheet.Range("AE1").Value = "Zip"
   oSheet.Range("AF1").Value = "Complimentary Registration"
   oSheet.Range("AG1").Value = "Honorarium"
   oSheet.Range("AH1").Value = "Expenses"
   
   oSheet.Columns("D:D").NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
   oSheet.Columns("G:G").NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
   oSheet.Columns("E:F").NumberFormat = "[$-409]h:mm AM/PM;@"
   oSheet.Columns("H:I").NumberFormat = "[$-409]h:mm AM/PM;@"
   oSheet.Columns("V:X").NumberFormat = "[<=9999999]###-####;(###) ###-####"
   oSheet.Columns("AG:AH").NumberFormat = "$#,##0"

   oSheet.Columns.Autofit
   oSheet.Cells.WrapText = True
   
   oSheet.Rows.RowHeight = 30
   
   oSheet.Rows("1:1").RowHeight = 15
   oSheet.Columns("A:A").ColumnWidth = 5
   oSheet.Columns("B:B").ColumnWidth = 15
   oSheet.Columns("C:C").ColumnWidth = 30
   oSheet.Columns("D:D").ColumnWidth = 30
   oSheet.Columns("G:G").ColumnWidth = 30
   oSheet.Columns("J:J").ColumnWidth = 35
   oSheet.Columns("K:K").ColumnWidth = 15
   oSheet.Columns("L:L").ColumnWidth = 25
   oSheet.Columns("N:N").ColumnWidth = 35
   oSheet.Columns("O:O").ColumnWidth = 35
   oSheet.Columns("P:P").ColumnWidth = 35
   oSheet.Columns("U:U").ColumnWidth = 35
   oSheet.Columns("Z:Z").ColumnWidth = 35
   oSheet.Columns("AA:AA").ColumnWidth = 25
   oSheet.Columns("AB:AB").ColumnWidth = 25
   oSheet.Columns("V:X").ColumnWidth = 15
   oSheet.Columns("AC:AC").ColumnWidth = 15
   oSheet.Columns("AE:AE").ColumnWidth = 10
   lngCount = 1
 
   Do Until rst.EOF
 
    With oSheet
         .Cells(lngCount + 1, 1).Value = rst!PM
         .Cells(lngCount + 1, 2).Value = rst!SessionNum
         .Cells(lngCount + 1, 3).Value = rst!SessionTitle
         .Cells(lngCount + 1, 4).Value = rst!Date
         .Cells(lngCount + 1, 5).Value = rst!StartTime
         .Cells(lngCount + 1, 6).Value = rst!EndTime
         .Cells(lngCount + 1, 7).Value = rst!Repeat_Date
         .Cells(lngCount + 1, 8).Value = rst!Repeat_StartTime
         .Cells(lngCount + 1, 9).Value = rst!Repeat_EndTime
         .Cells(lngCount + 1, 10).Value = rst!FullName_Credentials
         .Cells(lngCount + 1, 11).Value = rst!LastName
         .Cells(lngCount + 1, 12).Value = rst!Roles
         .Cells(lngCount + 1, 13).Value = rst!Salutation
         .Cells(lngCount + 1, 14).Value = rst!Email
         .Cells(lngCount + 1, 15).Value = rst!Primary_Position
         .Cells(lngCount + 1, 16).Value = rst!Primary_Employer
         .Cells(lngCount + 1, 17).Value = rst!Employer_City
         .Cells(lngCount + 1, 18).Value = rst!Employer_State
         .Cells(lngCount + 1, 19).Value = rst!Employer_Province
         .Cells(lngCount + 1, 20).Value = rst!Employer_Country
         .Cells(lngCount + 1, 21).Value = rst!Bio
         .Cells(lngCount + 1, 22).Value = rst!BusinessPhone_Value
         .Cells(lngCount + 1, 23).Value = rst!HomePhone_Value
         .Cells(lngCount + 1, 24).Value = rst!CellPhone_Value
         .Cells(lngCount + 1, 25).Value = rst!AddressType_Text
         .Cells(lngCount + 1, 26).Value = rst!Business_Name
         .Cells(lngCount + 1, 27).Value = rst!Address_1
         .Cells(lngCount + 1, 28).Value = rst!Address_2
         .Cells(lngCount + 1, 29).Value = rst!City
         .Cells(lngCount + 1, 30).Value = rst!State
         .Cells(lngCount + 1, 31).Value = rst!Zip
         .Cells(lngCount + 1, 32).Value = rst!CompReg_Text
         .Cells(lngCount + 1, 33).Value = rst!Honorarium
         .Cells(lngCount + 1, 34).Value = rst!Expenses
         
    End With
    lngCount = lngCount + 1
    rst.MoveNext
Loop
 
rst.Close
Set rst = Nothing

End If

End Sub

[June7]

1. Maybe use wildcard in filter parameter - review http://datapigtechnologies.com/flash...earchform.html

2. You want to use code in Access to set filter in the Excel workbook? Use the macro recorder in Excel to capture code then refine it for the Access procedure.

[besuchanko]

I still don't understand. Can someone provide an example using my code?? Or am I approaching this incorrectly? It comes to mind that I may eventually build other reports that shouldn't use the filter at all, while others should have the option to do so. Either way, users should be able to use the second combo box to filter the report by a specific value or choose to see all records in the report.

[June7]

Why are you exporting data to Excel?

If what you want is a report type output why not use Access report?

Can certainly apply a filter to the query opened by recordset. For one method review
http://datapigtechnologies.com/flash...earchform.html
http://datapigtechnologies.com/flash...mtoreport.html

[besuchanko]

I'm exporting to Excel just in case anyone needs a raw data dump. Everyone is used to having a versatile "report" like this for various things. I understand it's not the best thing to do. However, I don't know anything about programming...only what I've looked up online and been able to understand, which is very little. So in a crunch, the end user has data they can work with if I'm not able or available to create a report. I'm the only one able to even attempt this...not to mention our IT department doesn't support Access.

[June7]

So does filtering the query prior to opening and exporting recordset resolve issue?

[besuchanko]

The video tutorial was super helpful. The report filter is working perfectly now. Unfortunately, my code for the Presenter Data Report that builds the Excel spreadsheet doesn't work anymore. Both reports are using the same query. Maybe that's part of the problem. I still have no idea how to code the Excel "report" that is just code to use a combo-box filter since it's not actually calling a report built in Access.

[June7]

I don't see how applying filter parameter in query would affect the Excel coding. If it's just raw data dump to a new workbook, why not use TransferSpreadsheet?

[besuchanko]

I don't understand what you mean. I should have warned everyone initially that I'm a complete novice and know nothing about programming. I've been figuring this all out as I go. Regarding the spreadsheet, I want it formatted nicely. Hence all the code. The spreadsheet generates beautifully. I'd just like the end-user to be able to apply a filter to it using a combo box in Access, so that the resulting spreadsheet contains only those records. There's got to be a way to do it. I just don't know what the code should be and where to put it.

[June7]

You have the code opening a recordset from a query PresenterDataReport_Query and exporting records from that recordset. Should be able to apply filter to the query and then the export will be only the records desired by the user.

[besuchanko]

I did that, and I receive an error message. I can't make sense of why it's doing that.

Here is a snapshot of the criteria in the query:




And here is a snapshot of the error message:



And here's what gets highlighted in the code as the source of the problem:




Why is it doing this?? I selected the criteria from the "Select Session" combo box. It doesn't make sense why the criteria isn't being applied.

[June7]

I have run into that error message with DAO recordset and never could resolve it. I would use the same code in another db without issue.

Only possible cause I can think of is if SessionNum field is null for any record.

[besuchanko]

Oh...I wonder if it could be that simple. I don't have access to my database at the moment (it's at work and I'm at home), but I do believe there is at least one record with a SessionNum that is null. I'm crossing my fingers and hoping that's all it is.