Results 1 to 15 of 15
  1. #1
    jarheadvet is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Posts
    8

    Filtering a report with combo box

    Hi



    I am a very new user of Access. I have a rather simple database that includes two tables, one query, one report and one form.

    My database
    One table is named tblPortfolio and the other table is titled tblAccreditedTests

    The tblPortfolio has two fields in it; one is the autonumber field (primary key) and a field titled "Type" which gives the type of equipment serviced by our service team.

    The tblAccreditedTests table has four fields; one is the autonumber (primary key), a field named "Type" (same as in the tblPortfolio), a field named "TestNumber" which has a two or three digit number, and a field titled "TestName" which is a short text field that has a short description of a test we perform.

    My Query (titled tblAccreditedTests Query) is based off tblAccreditedTests.

    I have one Report titled "tblAccreditedTests" Query which houses all relevant tests for all the equipment we service. It is around 220 pages long.

    I have one Form titled "AccreditedTestSearch". On this form I have a combo box (Combo0) which is populated from tblPortfolio. The Combobox populates as I would expect it to populate.

    I added a command button (Command6) which opens the Report tblAccreditedTests Query.


    My Goal
    What I want to do is to have the end user use the combo box to choose the instrument they want to test then press the command button. Then I want the report to be filtered to just those tests that are relevant to that particular instrument.


    My Problem
    I've tried everything I can think of to filter my report but to no avail. I imagine this is a fairly simple task but no matter what I do, I don't get a filtered report. I have stripped my database of my efforts in hopes that someone here can direct me how to create this simple filter command.

    Thanks in advance for your help.

    Patrick

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    using the combobox , filter the report or not..

    Code:
    if IsNull(combobox) then
       docmd.openreport "rptMyReport"
    else
       docmd.openreport "rptMyReport",,,"[field]='" & combobox & "'"            'filter here
    endif

  3. #3
    jarheadvet is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Posts
    8
    Thanks! I placed your supplied code but the combo box didn't do anything to filter. What I got was a popup Parameter box. When I entered a type of instrument in there I received a perfectly filtered report.

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    docmd.OpenReport rpt,acViewPreview,,"[field]='" & comboBox & "'"

    did you change the rpt and combobox to YOUR names?

  5. #5
    jarheadvet is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Posts
    8
    Yes. I placed previous code in Event Procedure. Event was "after click" on Command button.

    My combobox was populated correctly.

    Where does the code you just supplied belong?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Type is a reserved word. Should not use reserved words as names https://support.microsoft.com/en-us/kb/286335\

    Why did you 'strip' the db since you did not provide it for analysis?

    Are you sure the event 'does nothing' - where is the popup coming from? Is there a parameter in report RecordSource query?

    Use command button Click event.

    Step debug. Review link at bottom of my post for debugging guidelines.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    jarheadvet is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Posts
    8
    So it comes back with popup

    Run-time error '2497':
    The action or method requires a Report Name argument.

    I copied and pasted your code into the event procedure and substituted my report name, combobox and command button

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Post your exact code for analysis.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    jarheadvet is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Posts
    8
    Private Sub Command2_Click()
    DoCmd.OpenReport rptAccreditedTestsQ, acViewPreview, , "[Model]='" & ComboBox0 & "'"
    End Sub

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If the report's name is "rptAccreditedTestsQ" and that is not a VBA variable, it must be within quote marks.

    Your report has 'rpt' prefix in its name?

    Model is a text type field?

    The combobox value is model descriptive text, not a numeric ID?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    jarheadvet is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Posts
    8
    No I don't have rpt in my report name. I have changed that and renamed some things. My report is now "AccreditedTestReport". I made the report off a table, "tblAccreditedTests". The combo box populates from the table "tblPortfolio".

    The Model field is a short text field since we use letter designation and not numbers as the primary model indicators. Numbers are used occasionally but that is just to differentiate between various options within a particular model. For example, one model is the CMW. Within that model one may have a CMW270 or a CMW500 depending how it is configured. So I chose the model field to be short text to accommodate both text and numbers.

    I do want to say thanks for your help. I realize what I've done may be confusing to a more experienced Access user/developer. I appreciate your patience with me.

  12. #12
    jarheadvet is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Posts
    8
    Here is the code for my command button:
    Private Sub Command24_Click()
    DoCmd.OpenReport "AccreditedTestReport", acViewPreview, , "[Model]='" & ComboBox0 & "'"
    End Sub

    The command button opens a report but it is blank (no test data, just header and footer data).

    Click image for larger version. 

Name:	Capture.JPG 
Views:	19 
Size:	17.2 KB 
ID:	21830

    However, when I click on the report, the entire report opens (all 214 pages).

    Click image for larger version. 

Name:	Capture2.JPG 
Views:	19 
Size:	22.2 KB 
ID:	21831

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Then I suspect the value in the combobox is not Model text value.

    Post the combobox RowSource SQL statement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    jarheadvet is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Posts
    8
    Works fine now. Had to adjust word ComboBox0 in my code to Combo0

    Thanks for your help.

  15. #15
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    Jarhead,

    You may want to consider defining a query which only outputs your desired records and using that query as the data source for your report. Using this approach there is no filtering in the report itself, yet the net effect is that you receive a filtered report because only those rows which pass the query criteria are sent to the report.

    I like this method because it allows me to debug issues in the data criteria separately from the report design.

    Good luck,

    Jeff

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 10-02-2013, 07:28 AM
  2. Replies: 1
    Last Post: 07-12-2012, 08:39 AM
  3. Replies: 4
    Last Post: 06-12-2012, 11:49 AM
  4. Replies: 5
    Last Post: 03-12-2012, 02:58 AM
  5. Replies: 0
    Last Post: 08-17-2008, 12:19 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums