Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2012
    Posts
    6

    Unanswered: Filter a report based on a combo box

    I'm new to Access. I saw this done in a class last month, but it was done with the expression builder, I think. It was not done with Code (this was only a level 2 Access class offered at work)

    I am trying to produce a individual employee data report. The report I built originally processed all 67 records. I then added a filter in the report

    [ccn]=[Enter CCN ID]

    and got the results I wanted an employee data report on one individual.

    But, I want to select from a dropdown list, not have to type in the record ID that I want. So I created a form with a combobox and command button. I've tried it with the expression builder, I've tried it with the macro builder, now I'm trying it with code. I've received results ranging from producing a single record report with errors in every field to all 67 records in one report with all of the data correct.

    Option Compare Database

    Private Sub cboCCN_AfterUpdate()

    End Sub

    Private Sub cmdProcessCMO_Click()

    On Error Resume Next

    'User must select a value from the combo box
    If IsNull(cboCCN) = True Then
    MsgBox "You must select a Employee Number."

    'Open report called rptCMO for the CCN selected
    Else
    DoCmd.OpenReport "rptCMO", acViewPreview, , "CCN = cboCCN"
    End If

    End Sub

    the field the report looks to to populate is [CCN]

    The code above produces a single record report, but still prompts me to enter the CCN

    What am I doing wrong?
    Last edited by mdnuffer; 02-22-12 at 11:28.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Here's how the syntax works:

    Open a second form to the record

    The wherecondition argument is the same for OpenReport as for OpenForm.
    Paul

  3. #3
    Join Date
    Feb 2012
    Posts
    6
    Thanks for the reply.

    This eliminated the extra prompt, but I'm still missing something

    I replaced the text with

    DoCmd.OpenReport "rptCMO", acViewPreview, , "CCN = '" & Me.cboCCN & "'"

    and got a single record report with errors in every field.


    I then replaced the text with

    DoCmd.OpenReport "rptCMO", acViewPreview, , "CCN = '" & Me.CCN & "'"

    and got a single record report with all fields populated, but only for the first record in the db, regardless of which record was selected in the dropdown

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    My guess would be that the bound column of the combo isn't what you're expecting. Do you know how to set a breakpoint and check the value? Or put in a message box? Or can you post the db here?
    Paul

  5. #5
    Join Date
    Feb 2012
    Posts
    6
    Thanks,

    I don't know how to set a breakpoint and check the value, the db contains Personally Identifiable Information (PII), so I can't post it.

    But, you hit the nail on the head with the bound column of the combo. I changed the bound coulmn and it works perfectly. :-)

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Glad you got it sorted out. For future problems:

    Debugging
    Paul

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
  •