Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Location
    Sunnyvale, CA
    Posts
    14

    Question Unanswered: DoCmd.Openreport

    Hi,

    I'm an experienced SW developer, but new to Access and Visual Basic. I have a table called "tblMembers" which lists the members of an organization. I want to produce a report that shows a subset of the members based on user-defined criteria. My approach was to create a report based on the table, and then call DoCmd.Openreport in a VB procedure, passing it the search criteria. The problem is that I can't find any documentation explaining the details about the parameters I have to pass, either online or in the manual. For example, the online help tells me that there's a filtername parameter, but it offers no explanation of how to create the filter or name it. Also, there's a wherecondition parameter, but no info on its syntax. Would someone please be kind enough to tell me what kind of values I have to put in these parameters in order to, for example, show all members where lastname = Smith. Also, could you please point me toward a documentation source that gives detailed descriptions of all the built-in functions, including detailed parameter info. I'm running Access 2000.

    Thanks,
    Marty

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: DoCmd.Openreport

    Hi Marty!

    I know what you mean.... It's not easy to find what you are looking for... lol...

    I didn't learn a thing about Access at school... and VBA is only a subset of VB and is soooo different that it took quite a lot of reading for me to learn... I knew DB2 SQL and VB (among many other programming languages) but it still took a while before I was going with this stuff...

    Anyway... I'll do my best to explain...

    The DoCmd object has many methods we can use... one of which is OpenReport...

    The syntax is...
    DoCmd.OpenReport reportname[, view][, filtername][, wherecondition]
    (any argument in square brackets is optional...)

    OpenReport offers you two different ways of filtering the records of the report...

    The first is "filtername"... What you can do is create and save a query to filter the records of the recordset and simply put the name in the correct position of the argument in your DoCmd statement... For example...

    DoCmd.OpenReport "Sales Report", acViewNormal, "Report Filter"
    (where "Report Filter" is the name of the saved query... )

    The second (which is the one I always use... ) is the "wherecondition"... The way to use this is create a string for your where condition... It's easier to show you than tell you so I'll do a few examples...

    DoCmd.OpenReport "Sales Report", acViewNormal,, "[fldSales] > 10000"
    (where [fldSales] is a field in the underlying recordset, and is numeric)

    DoCmd.OpenReport "Sales Report", acViewNormal,, "[fldSaleDate] = #12/07/2003#"
    (where [fldSaleDate] is a field in the underlying recordset and is Date data type)

    DoCmd.OpenReport "Sales Report", acViewNormal,, "[fldProductSold] = 'Pipe'"
    (where [fldProductSold] is a field in the underlying recordset and is Text/String data type)

    So, date values must be surrounded by # signs... string values must be surrounded by single quotes (or sets of double quotes)... and numeric values are not surrounded by anything...

    For more information on the DoCmd object, type "docmd" in the Answer area of Access 2000 Help... and then select the link for the DoCmd Object... If you look at the Methods link you'll get individual information on each item listed...

    For more information on the quotes in strings, type "quotes" in the Answer area of Access 2000 Help... The search should take you directly to a page Titled "Quotation Marks In Strings"...

    To learn the Built In Functions available, I would open the Expression Builder and select (on the left) Functions -> Built-in Functions ...and then you can select by category or alphabetically and hit the Help button to get information on each....

    There are a lot of good sites out there with Access information, but I'd recommend you check out the link below... There are some really good examples and explanations of real world issues...

    The Access Web

    And finally, of course, you can always ask us...


    HTH

  3. #3
    Join Date
    Jan 2004
    Location
    Sunnyvale, CA
    Posts
    14

    Talking

    Hi Trudi,

    All of your advice was right on! It not only solved my immediate problem, but showed me where to get solutions for other problems.

    Thanks,
    Marty

  4. #4
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    Would anyone know why a DoCmd.OpenReport starts printing the report rather than opening it? It was working a second ago, and now its printing the report..i dont know wat change i did

  5. #5
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by Jerrie
    Would anyone know why a DoCmd.OpenReport starts printing the report rather than opening it? It was working a second ago, and now its printing the report..i dont know wat change i did
    The second argument in the DoCmd.OpenReport command is view... This is what it says in Access 97 Help...
    view
    One of the following intrinsic constants:
    acViewDesign
    acViewNormal (default)
    acViewPreview
    acViewNormal prints the report immediately. If you leave this argument blank, the default constant (acViewNormal) is assumed.
    If you want it to open in Preview mode... make sure it says...
    DoCmd.OpenReport "ReportName", acViewPreview...

  6. #6
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    aha, thx for the reply; it worked!

Posting Permissions

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