Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    form with 2 queries (2000)

    Is there anyway to create a form with 2 separate queries that are side by side? I have one parameter query that I want to put on a form and another parameter query that I would like to put on the same form. I don't know if this can be done. They want to be able to do a search on one query then search the other query but have them both on the same form.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: form with 2 queries (2000)

    I'm not sure we have enough detail to give you specific advice, and I'm curious what you mean by "They want to be able to do a search on one query then search the other query " - are they going to use Find after they have put the parameter in for each of the queries?

    In any event, it is possible to put two subforms on a form side-by-side, and give them different data sources, and not link them to a value on the main form. That should let you display the results of the two different queries. But I suspect there may be more to the problem than that - is the data coming from two different tables, and how will they go about comparing them? It seems like there must be some sort of linkage involved?
    Wendell

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    New Orleans, Louisiana
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: form with 2 queries (2000)

    I have a similar problem. I have a report based on a table to give me monthly sales totals and a subreport based on a query to give me ytd sales totals. This query is based on the same table as the monthly report. Is there any way to enter the parameters for both the report and subreport (Month and YTD)?

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: form with 2 queries (2000)

    As long as the parameter is the same for both queries, you can do it with one parameter prompt. To do that, make the parameter the same for both queries. If the queries require different parameters, then you should probably use an unbound form, have the user input data into the text boxes on the form, validate it and then open the report and have the query use the values on the form.
    Wendell

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    New Orleans, Louisiana
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: form with 2 queries (2000)

    The parameters are not the same - 1 is mtd and 1 is ytd. I'm not sure I understand what you mean by validate and how I would let the query use the dates from the form. Are you talking about a dialog box form?

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

    Re: form with 2 queries (2000)

    If you have a parameter in a query, like [Enter Month] or [Enter Year], you can use a form with unbound text boxes instead.

    Say you create a form frmSelection with text boxes txtMonth and txtYear, and a command button cmdReport with caption "OK" or "Open Report".

    In the query/queries, you can replace the parameter [Enter Month] by a reference to the text box: [Forms]![frmSelection]![txtMonth], and the parameter [Enter Year] by a similar reference: [Forms]![frmSelection]![txtYear]

    In the On Click code of the command button, you can verify that the user has entered a valid month and year, then open the report. Example code:

    Privat Sub cmdReport_Click()
    If [txtMonth]< 1 or [txtMonth] > 12 Then
    MsgBox "Enter a month between 1 and 12 (inclusive)", vbExclamation
    [txtMonth].SetFocus
    Exit Sub
    End If

    ' more testing goes here

    DoCmd.OpenReport ReportName:="rptMyReport", View:=acViewPreview
    End Sub

    Note: this is just an example. In reality, using combo boxes to restrict user input is often better. You would refer to a combo box in a query the same way you would refer to a text box.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: form with 2 queries (2000)

    Hans has given you the same advice I would - just use the criteria to be:
    Forms![MyForm]![txtMyDate]
    and replase the form name and control name with the names you use.
    Wendell

  8. #8
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: form with 2 queries (2000)

    Hans,

    Is it possible to use a 'Between' statement in the query design grid with a custom form, where both parameters are on the same form?

    Between ([Forms]![frmDateParameters]![txtFromDate]) And ([Form]![frmDateParameters]![txtToDate])

    When I ran the query it called the default (Access) parameter form with my query statement, rather than frmDateParameters.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: form with 2 queries (2000)

    Bryan,

    Yes, it should work with a Between ... And ... criterion too.

    If Access doesn't recognize a form parameter, you must specify it explicitly in the design of the query:
    <UL><LI>Open the query in design view.
    <LI>Select Query/Parameters.
    <LI>In the Parameter column, enter the first parameter exactly as written in the criteria line: [Forms]![frmDateParameters]![txtFromDate]
    <LI>In the Data Type column, select Date/Time from the dropdown list.
    <LI>Repeat for the other parameter [Forms]![frmDateParameters]![txtToDate] (I suppose that [Form]!... is only a typo.)[/list]Any difference between the parameter declaration and the criterion will result in the standard parameter prompt.

  10. #10
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: form with 2 queries (2000)

    Uh...yeah...it's only a typo .... the kind that makes things not work.

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Think I'll go crawl back under my rock now <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  11. #11
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: form with 2 queries (2000)

    Hmmm...I spoke too soon.

    PARAMETERS
    (Parameter): [Forms]![frmDateParameters]![txtFromDate] (Data Type): Date/Time
    (Parameter): [Forms]![frmDateParameters]![txtToDate] (Data Type): Date/Time

    CRITERIA
    Between ([Forms]![frmDateParameters]![txtFromDate]) And ([Forms]![frmDateParameters]![txtToDate])

    I can't spot the error <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: form with 2 queries (2000)

    What is the actual error you are getting?
    Pat

  13. #13
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: form with 2 queries (2000)

    It's not an error "message", it's just that I'm getting the default parameter form rather than frmDateParameters when I run the query. All I'm really trying to do is bring the parameter window into conformance with everything else I have as far as entering certain criteria. The default form does not allow for an input mask and I'm pretty sick of having to make allowances for input where parameters are concerned.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: form with 2 queries (2000)

    Me neither. It works in a small test I created, so it seems the problem must be somewhere else. The form *is* open, I hope...

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: form with 2 queries (2000)

    Is the form frmDateParameters loaded and not in design mode? If the form is not in memory you will get that/those dialog boxes popping up for parameters.
    Pat

Page 1 of 2 12 LastLast

Posting Permissions

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