Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Record Source (A2K)

    In A2K, I currently read selections off of a form, build an SQL statement which is an argument for a db.CreateQueryDef. The created query is in the Record Source property of a report. This works fine . . .

    *except* that it is a DAO procedure and I want to do everything in ADO.

    ADO does not have an equivalent CreateQueryDef procedure, so my thought was to just stuff the SQL I constructed directly into the Record Source of the Report.

    Problem: I can't figure out the syntax for putting something into the Record Source property with VBA.

    Any ideas?

    TIA
    Donald

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report Record Source (A2K)

    You *can't* do everything with ADO, so be warned. There is code you could use, but it adds the query to either the Views or the StoredProcedures collections, and neither of those is visible in an Access MDB. This is a situation where it makes more sense to use DAO than to try and force ADO to fit.

    As for setting the report's recordsource, what exactly is your question? You create the string and set the report's recordsource to that string. This is pure Access/Jet, no ADO involved. No, I take that back, you *could* do it with ADO using the Catalog object, but it would be several times as much work and doesn't make any sense in this context.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Record Source (A2K)

    If my SQL string is in strSQL and my report is named rptEventsRequired, what, exactly, is the syntax of the statement that puts the string into the record source property of the report?
    TIA
    Donald

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report Record Source (A2K)

    The RecordSource is a property of the report, but you can only get at it when the report has been instantiated as an object, and you can't set it once you've opened the report for printing. If you're trying to do this in code, it would be a lot simpler to either create a new querydef or just pass the report a WhereCondition or Filter. Otherwise, you're probably going to have to open the report in design view and then use automation to instantiate the report as an object so you can set the recordsource property. Here's an example, but please note that this doesn't save the recordsource and you'll have to handle the dialog asking if you want to save the object when you close it.

    <pre> Dim rpt As Report

    DoCmd.OpenReport "rptOrders", acViewDesign
    Set rpt = Reports!rptOrders
    rpt.RecordSource = "SELECT * FROM Orders WHERE CustomerID = 'ERNSH';"
    DoCmd.OpenReport "rptOrders", acViewPreview
    Set rpt = Nothing</pre>

    Charlotte

Posting Permissions

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