Page 2 of 2 FirstFirst 12
Results 16 to 19 of 19
  1. #16
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737

    This will take some time. The report doesn't work because the query it's based on isn't there. It looks like you might be trying to save that query def, but I haven't gotten that far yet to see if that's the case. There's no need for prm = Eval(prm.Name) since you've already assigned it a value with
    Code:
    qdf.Parameters("[Forms]![frmtest]![Text2]").Value = [Forms]![frmTest]![Text2]
    If you don't want to wait, let me know because I'd hate to review it all only to have you say you're taking a different approach or something.

  2. #17
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi Micron

    im very helpful for the help and im really sorry about the long winded way i have gone around this.
    please take the time you need if your able as im sure i will need to be required to add other reports using this process in the future, so understanding whats gone wrong and what i need to do in the future will really aid me in learning.

    i have really enjoyed my small projects that i have done so far.

    and i am really pleased that you have taken the time and the patents to help me out now and in the passed.

    many thanks again

    steve

  3. #18
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Took less time than I figured - then again, I didn't look at everything because there's a few critical flaws.

    Re: qdf field count: Never done this; research shows that you have to use a recordset based on the qdf and count the rs fields. This block
    * For Each FieldName In qdf.Fields
    doesn't even run.
    * Thus FieldList is always "", iCountFieldNames is always 0, iCountFields is always 0 (loop always is 0 to 20)

    You build the sql (which always has 20 fields due to the foregoing) and assign it to the qdf, then destroy the qdf before the report finishes opening??
    Then the report finishes opening and its record source, which is specified as qryTestResultsPivotQuery in the property sheet, doesn't exist at all (at least not in what you posted). If it was in the Nav pane and you try to create it again, I think you'd generate an error anyway.

    Shouldn't the report record source become the newly fashioned qdf?
    Shouldn't the procedure that builds this qdf be a function so it can return something to the report opening?
    - I'd build the sql, forget the qdf, and pass the sql back to the report during the report open event, as in

    Me.RecordSource = CreateReportSql
    ( FYI: Call keyword not req'd when parameters are not being passed, but not req'd here anyway as I'm assigning it to the RecordSource in one step using the line above)
    The open event would branch to the sql creating function

    Function CreateReportSql () As String <-- As String defines the output as a string data type
    Dim stuff...
    create the sql
    CreateReportSql = strSql <--assigning the string to the function "passes" the sqll to Me.RecordSource
    End Function

    First, you need to address the * items. Perhaps go back to where you got the code from and see if you missed something. Maybe look here too
    https://access-programmers.co.uk/for...ad.php?t=40862
    Not much, but all I could find was a few answers that indicate you have to build a recordset from a query or sql and count those fields. Puzzling, because a qdf seems to have a Fields collection.
    That's all for now, I guess; except I always tried to avoid creating reports from crosstabs. Can't recall what happens when a report with 10 controls on it only gets 9 fields from a volatile crosstab.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #19
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi

    wow that's great, i will certainly look into the link you posted and yes i'm guessing that the report record source should be the qdf.

    what i cant understand or get my head around is that i remove the parameters and criteria from the query and then set the criteria as 5 for the productionorderid

    after removing the parameter code from the report, when the report loads its presented with data as expected.

    i was kind of thinking that if the underlining code on the report was wrong it should not work when using either a number as the criteria of the ProductionOrderID or when using a form reference.

    steve

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 07-03-2017, 12:59 PM
  2. Replies: 5
    Last Post: 12-19-2014, 04:38 PM
  3. Replies: 1
    Last Post: 02-27-2014, 07:59 PM
  4. report built on query
    By Daryl2106 in forum Access
    Replies: 4
    Last Post: 12-09-2012, 07:07 PM
  5. Query built in VBA
    By doci4a in forum Programming
    Replies: 1
    Last Post: 03-16-2011, 01:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums