Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Queries based on OpenArg (2K)

    Hi,

    I have a form, FrmStartUp where the user selects from a combobox, their geographical area, then selects a command button CmdOpen that opens FrmMain.

    FrmMain contains various amounts of data entered by the user, plus a field called Area.

    From FrmStartUp I have an OpenArg that sets the field Area in FrmMain to the area selected from the combobox in FrmStartUp.

    This then filters all records pertaining to that area. And any new records added also go to that area.

    From this data I also have various reports. However the queries from which the reports are based, do not filter on area.

    Is there a way that I can do this so that each area can produce it's own reports(effectively based on the OpenArg)? Ideally I would also like to be able to do a global report (as it is now) encompassing all areas.

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

    Re: Queries based on OpenArg (2K)

    Create a report based on a query that returns records for all areas.
    On frmStartup or on frmMain, create two command buttons:
    - One that simply opens the report (using DoCmd.OpenReport)
    - One that opens the report filtered to show only records for the current area. The instruction in the On Click event procedure looks like this:

    DoCmd.OpenReport ReportName:="rptSomething", View:=acViewPreview, WhereCondition:="Area = " & Chr(34) & Me.Area & Chr(34)

    Of course, you must substitute the correct names. I have assumed that Area is a text field.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries based on OpenArg (2K)

    Thanks for the reply.

    I seem to be unable to get the code to work.
    The common denominator amongst all of the queries is that they have data from TblMain. In TblMain is a field Areaname (Text) which is set through FrmStartUp as the OpenArg. However when I type:

    DoCmd.OpenReport ReportName:="QryDaysOverTarget", View:=acViewPreview, WhereCondition:="AreaName = " & Chr(34) & Me.AreaName & Chr(34)

    It flags up the bold text as an error

    Another problem isis that one of the reports is a statistics report and therefore isn't based on one specific query but rather several queries.
    From these queries are various calculations to work out total numbers, percentages, etc. DSum's, DCounts and the like.

    How do I get around this?

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

    Re: Queries based on OpenArg (2K)

    Did you put the button on frmMain? And is AreaName a field in the record source of frmMain?

    For your statistics report, it's probably best to create two versions: one that returns data for all areas, and one where all the queries and expressions refer to a text box on the form bound to AreaName.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries based on OpenArg (2K)

    The report functions are available through a further form called FrmReports. This is accessed through FrmMain. FrmMain does have a field on it called AreaName, but FrmReports doesn't. On FrmReports are all of the controls linking to a preview of the report. I'm going to guess that this is going to need changing?

    With regards to the second point. Is it possible to do this as a generic query.

    So for instance in one of the text boxes I may have:

    =DCount("*","QryPastTargets")

    To count how many case have past a target date. Could I add a criteria in here to reference the OpenArg. Currently I have 3 areas and I know how to include these in 3 seperate reports, but this seems an impractical way of doing it.

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

    Re: Queries based on OpenArg (2K)

    If you want to refer to AreaName on frmMain from frmReports, you can use

    Forms!frmMain!AreaName

    instead of

    Me.AreaName

    On the statistics report, you could use

    =DCount("*", "QryPastTargets", "AreaName = " & Chr(34) & [Forms]![frmMain]![AreaName] & Chr(34))

    This will only work if frmMain is open.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries based on OpenArg (2K)

    Thanks for that.

    I now seem to have this error whenever I click on a control on the form, even the exit button.

    The controls that call up the reports are all variations of:

    Private Sub CmdOpenAllCases_Click()
    On Error GoTo Err_CmdOpenAllCases_Click
    DoCmd.OpenReport ReportName:="All Cases Within Target Date", View:=acViewPreview, WhereCondition:="AreaName = " & Chr(34) & Me.AreaName & Chr(34)


    Exit_CmdOpenAllCases_Click:
    Exit Sub

    Err_CmdOpenAllCases_Click:
    MsgBox Err.Description
    Resume Exit_CmdOpenAllCases_Click

    End Sub

    The exit button has the standard coding on it when created through the wizard.

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

    Re: Queries based on OpenArg (2K)

    The procedure you posted is OK. Check the other ones carefully for inconsistencies between the names in the On Error GoTo ... and Resume ... lines and the actual names of the labels.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries based on OpenArg (2K)

    Yes, duly resolved. In my error I had misspelt on one of them.

    My only other issue is on the Area report one of the field is:

    =DCount("*","QryAllLive","AreaName = " & Chr(34) & Forms!FrmMain!AreaName & Chr(34))

    When displayed this comes up with #Error

    I've checked through QryAllLive and it contains the field AreaName. The above procedure looks to be okay and I know that the records exist in the relevant table, and the relevant query.

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

    Re: Queries based on OpenArg (2K)

    I don't think we can solve this without seeing (a stripped down copy of) the database.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries based on OpenArg (2K)

    Attached stripped version of the database. (I hope I haven't gutted it too much!)

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

    Re: Queries based on OpenArg (2K)

    You have defined fields twice in QryAllMain, this causes confusion.

    1) clear the Show check box for Casedead
    2) remove the AreaName column.

    Both fields will still be included because you have TblMain.*.

  13. #13
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries based on OpenArg (2K)

    Thanks, this has resolved it!! Thank you for your help.

Posting Permissions

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