Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Dec 2007
    Location
    Sacramento, California, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    specific report (Access 2003)

    I have a table that includes customers and the specific vendors they are associated with. I want to allow the end user to be able to choose the vendor (from a drop down list) to run a report with those customers specific to the chosen vendor.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: specific report (Access 2003)

    Create a report the usual way. Make sure that the vendor is part of the record source of the report.
    Name the report rptCustomers.
    Create an unbound form (i.e. the record source of the form is blank).
    Place a combo box on the form; name it cboVendors.
    Set the row source of the combo box to a table or query that lists the vendors (each vendor should be displayed once).
    Place a command button on the form, next to the combo box; name it cmdOK and set its caption to OK or similar.
    With the command button selected, activate the Event tab of the Properties window.
    Click in the On Click event.
    Select [Event Procedure] from the dropdown menu.
    Click the ... button to the right of the dropdown arrow.
    This will open the Visual Basic Editor; you'll see the code module associated with the form.
    The "skeleton" for the event procedure has already been created:
    <code>
    Private Sub cmdOK_Click()

    End Sub
    </code>
    The insertion point is in the empty line between Sub and End Sub.
    Make it look like this - you can type the code yourself or copy/paste it:
    <code>
    Private Sub cmdOK_Click()
    If IsNull(Me.cboVendors) Then
    MsgBox "Please select a vendor.", vbExclamation
    Me.cboVendors.SetFocus
    Exit Sub
    End If
    DoCmd.OpenReport ReportName:="rptCustomers", View:=acViewPreview, WhereCondition:="VendorID=" & Me.cboVendors
    End Sub
    </code>
    Notes:
    1) Replace VendorID with the name of the field that identifies the vendor.
    2) I have assumed that VendorID is a number field. If it is a text field, use
    <code>
    ..., WhereCondition:="VendorID=" & Chr(34) & Me.cboVendors & Chr(34)
    </code>
    Chr(34) is the code equivalent of the double quote ", this is needed to enclose a text value.

    When you have finished entering the code, switch back to Access and close/save the form.
    Then open it in form view, select a vendor and click the button.

  3. #3
    Lounger
    Join Date
    Dec 2007
    Location
    Sacramento, California, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: specific report (Access 2003)

    Thank you. I realized I had a typo.

    It is allowing me to choose a vendor and running a report, but the report still has all of the vendors and not just the records that contain the vendor that was chosing from the drop down list.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: specific report (Access 2003)

    Note: this was originally a reply to a reply that was deleted; I've attached it to my previous reply to "heal" the thread

    cboVendors is the name of the combo box. If you have given it a different name, you should use that name in the code both times.

    BTW, it should be

    ..., WhereCondition:=Chr(34) & Me.cboVendors & Chr(34)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: specific report (Access 2003)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  6. #6
    Lounger
    Join Date
    Dec 2007
    Location
    Sacramento, California, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: specific report (Access 2003)

    Attached
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: specific report (Access 2003)

    Thanks, there was some confusion that arose out of the post you deleted. Here is the correct instruction to open the report:
    <code>
    DoCmd.OpenReport ReportName:="rptCustomers", View:=acViewPreview, _
    WhereCondition:="F4 = " & Chr(34) & Me.cboVendors & Chr(34)
    </code>
    (The space and _ let you continue an instruction on the next line)

    I have attached the database with some bells and whistles.
    Attached Files Attached Files

Posting Permissions

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