Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Question Unanswered: Pass Value - Need Help - May be Easy?

    I have a report with two subReports and criteria that the user enters, like Customer #. The problem is, it asks for the Customer # two additional times because each of the subReports are by Customer # too.

    How can I pass the value once input by the user to the other two subReports?

    Using MS Access 2000

  2. #2
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    The free downloadable sample database at www.bullschmidt.com/access uses the query by form concept so that on the invoices dialog one can optionally choose a rep, a customer, and perhaps a date range, click on a button that says "Input," and then have the invoice form open up showing all the invoices that match the criteria.

    And here is how the query by form concept can work.

    On the invoices dialog there are the following controls:
    InvDateMin with DefaultValue of =DateSerial(Year(Date())-1,1,1)
    InvDateMax with DefaultValue of =Date()
    InvRepNum with DefaultValue of *
    InvCustNum with DefaultValue of *

    Also on the invoices dialog there is a command button called cmdInput to open the invoices form with the following code behind the OnClick property:
    DoCmd.OpenForm "frmInv"

    And of course there could be a button to open a report the same way:
    DoCmd.OpenReport "rptInv", acViewPreview

    The invoices form (frmInv) has RecordSource property of qryInv.

    And the qryInv query's criteria for the InvDate field has:
    Between [Forms]![frmInvDialog]![InvDateMin] And [Forms]![frmInvDialog]![InvDateMax]

    And the qryInv query's criteria for the RepNum field has:
    Like [Forms]![frmInvDialog]![InvRepNum]

    And the qryInv query's criteria for the CustNum field has:
    Like [Forms]![frmInvDialog]![CustNum]

    One related point is that you probably wouldn't want to allow blanks (i.e. Nulls) in fields that are going to be used with Like in any criteria for that field. Otherwise the blanks wouldn't be shown. And to counter that you might consider creating the query's SQL statement dynamically so that the criteria on a particular field isn't used unless needed.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  3. #3
    Join Date
    Nov 2003
    Posts
    300
    Good info but I really don't see how that helps or that it is related to my particular problem..

    When I run the report, the report comes up with an INPUT PARAMETER... for the customer #... a user has to manually enter this in 3 times, one for the main report and two times more for the two subrepots which also want the Customer #...

    I want to be able to have them enter it only ONE time and produce the report...

    how can I do this???

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    hi databasemon,

    Just a question, are your subreports linked by the Customer# ??? If your tables are setup with the relationships then the reports will have that info. automatically. At least that's how my subreports work. Like I said, just a question, and looking into your situation as best I can. Can you post a sample of your database?

    Bud

Posting Permissions

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