Issue With Report Built From A Query

**sdel_nevo** · 10-11-2017, 04:22 AM

Hi Guys

I have an issue with a report that is being built from a query on the fly

the query that builds the report is this

Code:

Sub CreateReportQuery()

    On Error GoTo CreateReportQuery_Error
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim FieldName As DAO.Field
    Dim iCountFieldNames As Integer
    Dim FieldList As String
    Dim strSQL As String
    Dim iCountFields As Integer
   
    Set db = CurrentDb
    Set qdf = db.QueryDefs("QryTestSheetResultsCrossTabQuery")
   
    ' Now we get The field names From The Query Above
    iCountFieldNames = 0
    For Each FieldName In qdf.Fields
        If FieldName.Type >= 1 And FieldName.Type <= 8 Or FieldName.Type = 10 Then
            FieldList = FieldList & "[" & FieldName.Name & "] as Field" & iCountFieldNames & ", "
            ReportLabel(iCountFieldNames) = FieldName.Name
        End If
      
        iCountFieldNames = iCountFieldNames + 1
    Next FieldName
    
    For iCountFields = iCountFieldNames To 20
        FieldList = FieldList & "null as Field" & iCountFields & ","
    Next iCountFields
    FieldList = Left(FieldList, Len(FieldList) - 1)
    
    
    'Now We Create A New Query Using The Field names Extracted
    strSQL = "Select " & FieldList & " From QryTestSheetResultsCrossTabQuery"
    Set qdf = db.CreateQueryDef("qryTestResultsPivotQuery", strSQL)
   
   'see if the sql has been created correctly
    MsgBox strSQL
   
   'close the query def and set the memory to nothing
    qdf.Close
    Set qdf = Nothing
    Set db = Nothing
 
    On Error GoTo 0
    Exit Sub
CreateReportQuery_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CreateReportQuery of Sub Report_test"
End Sub

The Line

Set qdf = db.QueryDefs("QryTestSheetResultsCrossTabQuery")

The query QryTestSheetResultsCrossTabQuery, is a cross tab query that have parameters set against the field "ProductionOrderID"

when the parameters are hard coded , I.E the criteria ProductionOrderID is set to number "5" for example, the report is built correctly
if however the parameters use a form field such as [Forms]![frmTestSheet]![ProductionOrderID] the report always open blank

the query QryTestSheetResultsCrossTabQuery works correctly when I tested both criteria / parameters but I cant get my head around why one works and the other does not

any help would be fantastic

**sdel_nevo** · 10-11-2017, 08:49 AM

Hi All

Would I be correct in thinking that the parameter in the query expects the form to be open?

if so this is the case, the form frmTestSheet

as above, if I replace the query criteria with 5 the report opens correctly

changing the criteria back to the form reference, the report always opens blank

the form frmTestSheet opens up as a modal and is always open when the report tries to run.

I think I have got to the point now when I cant see the woods for the trees

many thanks

steve

**Micron** · 10-11-2017, 01:10 PM

I'm a bit confused. One time it seems the form with the referenced control may not be open, another maybe it is. Any form control being referenced for data requires the form to be open. It does not have to be visible, but it should not be modal as that will interrupt things. If it doesn't work when the form is open (and not modal) then without seeing the rest of the objects involved, you may have to do either (or both):
- declare the parameter(s) in the query design (right click on the query background where the table(s) are shown and see the property sheet)
- pass DAO parameters to the query def before you run it

Alternatively, you can try building the sql in code and running it from there, bypassing all this qdef and parameter stuff which usually serves to complicate things.

**sdel_nevo** · 10-11-2017, 01:27 PM

Hi micron
The form is always open, sorry for the confusion
I will amend the form so it’s not modal and try again I’m sure it’s something pretty simple that I have over looked

Many thanks for the reply 😀

Steve

**Micron** · 10-11-2017, 01:46 PM

I just noticed this too -

Code:

Set qdf = db.CreateQueryDef("qryTestResultsPivotQuery", strSQL)

Not sure, but if you're not saving that revised query, it probably won't work. Another debugging thing you can try it to output the sql for a qdef in the immediate window. You may find that the part where you expect the form value to be is empty.

Another thing you can do is create a temporary qdef by not providing a name within the name parameter (db.CreateQueryDef,"",strSQL). This is safer than repeatedly modifying an existing query, which makes the db more prone to corruption. I would avoid doing the same thing with tables as much as possible.

**sdel_nevo** · 10-11-2017, 02:00 PM

Hi

Not saving the changed query makes sense but it’s something that I have not done before in code.

I Will certainly use the intermediate window thanks for that tip.

Do you know how I would go about saving the changed query, sorry to be a pain

Steve

**Micron** · 10-11-2017, 07:24 PM

What you see in the immediate window may point you to the problem - should check that out first. The whole point of not naming the query is to not save it because it's too dynamic. Just referring to a form field that often contains different values doesn't really qualify. Let's see what the query sql looks like first.

**sdel_nevo** · 10-12-2017, 06:19 AM

Hi

I have tried to get my head around this problem but the more I look at it the more confused I'm getting.

I have created a totally new form called frmTest, this form is bound to the table tblTestResults on this form is a text field [text2] that's bound to "productionOrderID"

I have changed the parameters of the crosstab query "QryTestSheetResultsCrossTabQuery" to [Forms]![frmTest]![Text2] in both the criteria for the "ProductionOrderID" and in the Parameters form within the query design when I open the form then run the query the crosstab query is filtered as expected

But when I open the report "rptTest" that has this code on

Code:

 
Dim qdf As DAO.QueryDef
    Dim FieldName As DAO.Field
    Dim iCountFieldNames As Integer
    Dim FieldList As String
    Dim strSQL As String
    Dim iCountFields As Integer
   
    Set db = CurrentDb
    Set qdf = db.QueryDefs("QryTestSheetResultsCrossTabQuery")
   
    ' Now we get The field names From The Query Above
    iCountFieldNames = 0
    For Each FieldName In qdf.Fields
        If FieldName.Type >= 1 And FieldName.Type <= 8 Or FieldName.Type = 10 Then
            FieldList = FieldList & "[" & FieldName.Name & "] as Field" & iCountFieldNames & ", "
            ReportLabel(iCountFieldNames) = FieldName.Name
        End If
      
        iCountFieldNames = iCountFieldNames + 1
    Next FieldName
    
    For iCountFields = iCountFieldNames To 20
        FieldList = FieldList & "null as Field" & iCountFields & ","
    Next iCountFields
    FieldList = Left(FieldList, Len(FieldList) - 1)
    
    
    'Now We Create A New Query Using The Field names Extracted
    strSQL = "Select " & FieldList & " From QryTestSheetResultsCrossTabQuery"
    Set qdf = db.CreateQueryDef("qryTestResultsPivotQuery", strSQL)
   
   'see if the sql has been created correctly
    MsgBox strSQL
   
   'close the query def and set the memory to nothing
    qdf.Close
    Set qdf = Nothing
    Set db = Nothing
 
    On Error GoTo 0
    Exit Sub
CreateReportQuery_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CreateReportQuery of Sub Report_test"
End Sub

it always open blank.

so it would appear that the criteria of is not working using [Forms]![frmTest]![Text2]

I'm abit lost now as the report is bound to the query "qryTestResultsPivotQuery"

if I changed the code

Code:

Set qdf = db.CreateQueryDef("qryTestResultsPivotQuery", strSQL)

to

Code:

Set qdf = db.CreateQueryDef("", strSQL)

how would I populate the report?

I feel I'm abit out of my depth here

Steve

**aytee111** · 10-12-2017, 01:24 PM

Post your database, we can take a look at it and give you some further pointers.

**Micron** · 10-12-2017, 01:53 PM

I guess we're down to that. I keep waiting to see the sql output from the immediate window while the focus seems to be on playing around with the query.

**sdel_nevo** · 10-13-2017, 01:02 AM

Hi Micron

really sorry about not posting the sql output

the output I get when then the ProductionOrderID is hard coded as "5" in the QryTestSheetResultsCrossTabQuery query is

Code:

?debug.Print strsqlSelect [ProductionOrderID] as Field0, [TestSheetID] as Field1, [Reel Number] as Field2, [Reel Weight] as Field3, [Tested On] as Field4, [Part Number] as Field5, [Production Number] as Field6, [Customer] as Field7, [Description] as Field8, [Material] as Field9, [Specification] as Field10, [Revision Number] as Field11, [Production Process] as Field12, [Elongation (%)] as Field13, [Lay Direction "LH or RH"] as Field14, [Lay length (mm)] as Field15, [Resistance (Ohm/m)] as Field16, null as Field17,null as Field18,null as Field19,null as Field20 From QryTestSheetResultsCrossTabQuery

and I get this when the criteria and parameters are set in the QryTestSheetResultsCrossTabQuery query to a form reference pointing to [forms]![frmtest]![text2]

Code:

Select null as Field0,null as Field1,null as Field2,null as Field3,null as Field4,null as Field5,null as Field6,null as Field7,null as Field8,null as Field9,null as Field10,null as Field11,null as Field12,null as Field13,null as Field14,null as Field15,null as Field16,null as Field17,null as Field18,null as Field19,null as Field20 From QryTestSheetResultsCrossTabQuery

steve

**sdel_nevo** · 10-13-2017, 02:08 AM

test.zip

Hi aytee111
I have attached the DB as requested.

the form frmTest is used to select the production order that I want the report to open

the report rtpTest is the report that holds the code that I am having issues with

The query QryTestSheetResultsCrossTabQuery is the crosstab query that holds the parameter values

many thanks

Steve

**Micron** · 10-13-2017, 11:00 AM

In your attached db, the query upon which the report is based is missing, or you have specified the wrong one. I'm not going to worry about the sql posted for now. The missing query is more important at present. The report is looking for qryTestResultsPivotQuery

**sdel_nevo** · 10-13-2017, 11:19 AM

Hi Micron

the query is created when the report runs, on the close event of the report it’s deleted
i forgot to comment out this code before I uploaded it

if required I can upload the database again with the query present