Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Sheffield, Yorkshire
    Thanked 0 Times in 0 Posts

    Report Selection (2003)

    I have a report which runs from a query. Instead of filtering the report using [ ] criteria on the query, I want the user to pick the record from a drop down list, to eliminate typing errors etc. Say the report is all customer details, I want the user to pick the customer name from a drop down list, but I don't know how to achieve this.

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Cape Town, RSA
    Thanked 1 Time in 1 Post

    Re: Report Selection (2003)

    Hi Robm

    There is extensive help on this topic in Access Help. If you go to the help files and type: "Create a form to enter report criteria", Access gives you a full step by step process to develop such a form. You may need to add the combo box yourself though!

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Report Selection (2003)

    Create an unbound form.
    Turn on Control Wizards (the second button on the Toolbox), then put a combo box on the form.
    This will start the Combo Box Wizard.
    Select the first option, then click Next.
    Select the table or query that lists the customer names, then click Next.
    Select the field or fields you need, and move them to the list of selected fields. Then click Next.
    Adjust the column width if necessary, then click Next.
    Specify a caption for the label, then click Finish.
    Give the combo box a meaningful name in the Properties window, for example cboCustomer.

    Put a command button on the form, next to or just below the combo box.
    This will start the Command Button Wizard.
    Select Report Operations in the list of Categories.
    Select Print Preview in the list of actions, then click Next.
    Select the report, then click Next.
    You can specify a text caption or an icon, whichever you prefer. Then click Next.
    Specify a name for the button, then click Finish.

    Now switch to the Visual Basic Editor (Alt+F11).
    The On Click event procedure for the command button will contain a line

    DoCmd.OpenReport stDocName, acPreview

    Above this line, insert the following:

    Dim stLinkCriteria As String
    stLinkCriteria = "[CustomerID] = " & Me.cboCustomer

    and change the OpenReport line to

    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

    Note: the above assumes that your combo box has a number field CustomerID as bound column. If the combo box has a text field CustomerName as bound column, change the instruction to

    stLinkCriteria = "[CustomerName] = " & Chr(34) & Me.cboCustomer & Chr(34)

    The Chr(34) provides double quotes " around the name.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts