Link to home
Start Free TrialLog in
Avatar of João serras-pereira
João serras-pereiraFlag for Portugal

asked on

ms/access report does not open query

I have a report that is dependent on a specific query. The query is Ok and the proper rst available.

to open the report I am using the following code:

Set sireshDB = CurrentDb
    Set docsDataSet = sireshDB.OpenRecordset("Q17_selectTicket", dbOpenDynaset)
    strSetSQL = "SELECT ticketCA.codigoTicket, ticketCA.codigoTabanca, ticketCA.dataAbertura, ticketCA.dataEvento, ticketCA.dataMarcacao, ticketCA.fechoTicket, ticketCA.dataPrevistaFecho, ticketCA.tempoDuracaoIntervencao, ticketCA.observacoesTicket, ticketCA.fonteInformacao, ticketCA.codigoPonto, ticketCA.anomaliaVerificada, ticketCA.anomaliaObservacoes, ticketCA.tipoEvento, ticketCA.descricaoEvento, ticketCA.tempoInicioIntervencao, ticketCA.criticidadeAnomalia, ticketCA.contactoReparador, ticketCA.chaveTicketPrevio, tabancasBase.NomeTabanca, regioesGuineBissau.nomeRegia, sectoresGuineBissau.nomeSector, ticketCA.dataEfetivaFecho, contactos.nomeContacto, contactos.alcunhaContacto, contactos.moradaTabanca, contactos.tipoContacto AS tc, contactos.telefone1Contacto, contactos.telefone2Contacto "
    strSetSQL = strSetSQL & "FROM (regioesGuineBissau INNER JOIN sectoresGuineBissau ON regioesGuineBissau.codigoRegiao = sectoresGuineBissau.codigoRegiao) INNER JOIN ((ticketCA INNER JOIN tabancasBase ON ticketCA.codigoTabanca = tabancasBase.codigoTabanca) LEFT JOIN contactos ON ticketCA.fonteInformacao = contactos.codigoContacto) ON (sectoresGuineBissau.codigoSector = tabancasBase.codigoSector) AND (regioesGuineBissau.codigoRegiao = tabancasBase.codigoRegiao) "
    strSetSQL = strSetSQL & "WHERE (((ticketCA.codigoTicket)= "
    strSetSQL = strSetSQL & "'" & Me.fld_codigoTicket & "' " & ")); "
    Set Q17_selectTicketDef = sireshDB.QueryDefs("Q17_selectTicket")
    Debug.Print "Gerar OT:" & strSetSQL
    Q17_selectTicketDef.SQL = strSetSQL
    Set Q17_selectTicketSet = sireshDB.OpenRecordset("Q17_selectTicket", dbOpenDynaset)
    Forms![_commonVariables]![currentTicket] = Me.fld_codigoTicket
    DoCmd.OpenReport "OT", acViewPreview

but when the report is open, all fields are set to null.

When I try to use the "open" event, using the following code:

Private Sub Report_Open(Cancel As Integer)
    'Me.Requery
    'Debug.Print "OT impressa: " & Me.fld_codigoTicket
End Sub


it crashes because it sates the the object is not open
 
can anyone help?
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

It looks as if you're opening a Query, and then opening a Report.

Is the report based on that query? If so, you don't need to open the query first - just open the report after setting the SQL of the QueryDef.
Create a query and save it.  In the DoCmd.OpenReport method, specify the where argument.

docmd.OpenReport "OT",acViewPreview,,"ticketCA.codigoTicket = " & """" & Me.fld_codigoTicket & """"

Opening the query independently doesn't affect the report.  The report will open the query all by itself when it runs the report's Load event.  So change the RecordSource property of the report to reference the name of the saved querydef.
Avatar of João serras-pereira

ASKER

actually there are 2 queries: Q17 and Q30. Q17 needs to be dynamically twisted, so that's why I am opening it before. Q30 depends on Q17, and was not opening it until I started with the issue on the Report.

Before calling in the docmd.openreport  I ensure that Q30 (the confirmed source of the Report) is really OK. And is - it contains a record with all the necessary data - I just want to print it ((!)

then I open the report and, voilà, I am getting a totally empty report (!). Trying to apprehend what is happening, I am checking both the load and on the open events, and getting the error as if the report dos not exist or it is not loaded.
then, I stop the app and independently open the report - it works!

I have tried Pat's command, but it just yields the same result...
Opening the queries outside of the report has nothing to do with anything.  The report will run the query itself.  Make sure that the query in the report's Recordsource is correct.

I showed you how to run the report WITHOUT modifying the query.  Save the query with no selection criteria or the fixed selection criteria if there is any.  Then the variable criteria is passed using the OpenReport method.
What happens if you open the report by double-clicking it from the Nav Pane?

If you still have issues doing it that way, you may have troubles with the report. Make a backup, then try these steps:

1. Compact the database
2. Compile your code - from the VBA Editor click Debug - Compile. Fix any errors, and continue doing this until the Compile option is disabled
3. Compact again

You may also need to decompile your database. Make a backup then create a standard Shortcut with a Target like this:

"full path to msaccess.exe" /decompile

Run that shortcut and open your database. Access will decompile your database when it opens. After it opens, run the 3 steps again.

If you still have troubles you may have a corrupt report, or a corrupt database container. Create a new, blank database and import everything into the new database. If that doesn't fix the issue, you may have to recreate the report from scratch, or from a known good backup.
from the navigation pane runs smoothly....
I compressed and repaired, decompiled it... totally cleaned out by compiling with no errors, then recompressed and started MS/ACCE from scratch. still exactly the same behaviour...
I started a blank report and copied the fields onto it... same behaviour - when started from VBA it shows a blank sheet (i.e. the background of the fields is there but NO content (please check the screenshot.
 

User generated image
User generated image

User generated image

I me definitely wrongdoing something.

I just do no know what it may by.

the report  looks as unloaded without access to the source data.

I even tried to start in "design" mode to validate that everything is OK (included the Q30) and it is (!).

But it kept the behaviour. when I manually changed the "design" to "preview" it only contained blanks. this is driving me nuts
How are the parameters being filled?  Are you prompted when you double click on the report?  The parameters are not in the saved query you know.
which parameters? I just construct 2 queries and run them in sequence. the output (Q30) is the record source for the report. When I open the report in VBA a blank report appears. When I run from the menu it works alright...
When I use the "debug into" to go each step, just before the docmd.openreport Q30 contains the correct data. after opening the report, it contains the correct data... and the report is blank, sigh...
just a quick hint.
being desperate I tried another approach: create a Table using the "Make Table" query.

So I developed a new query, Q33 as below:


User generated image

tested it and the report, using the menu, showed everything.


User generated image

then, I swiched the query to SQL view and copied to my code:

Set docsDataSet = sireshDB.OpenRecordset("Q17_selectTicket", dbOpenDynaset)
    strSetSQL = "SELECT ticketCA.codigoTicket, ticketCA.codigoTabanca, ticketCA.dataAbertura, ticketCA.dataEvento, ticketCA.dataMarcacao, ticketCA.fechoTicket, ticketCA.dataPrevistaFecho, ticketCA.tempoDuracaoIntervencao, ticketCA.observacoesTicket, ticketCA.fonteInformacao, ticketCA.codigoPonto, ticketCA.anomaliaVerificada, ticketCA.anomaliaObservacoes, ticketCA.tipoEvento, ticketCA.descricaoEvento, ticketCA.tempoInicioIntervencao, ticketCA.criticidadeAnomalia, ticketCA.contactoReparador, ticketCA.chaveTicketPrevio, tabancasBase.NomeTabanca, regioesGuineBissau.nomeRegia, sectoresGuineBissau.nomeSector, ticketCA.dataEfetivaFecho, contactos.nomeContacto, contactos.alcunhaContacto, contactos.moradaTabanca, contactos.tipoContacto AS tc, contactos.telefone1Contacto, contactos.telefone2Contacto "
    strSetSQL = strSetSQL & "FROM (regioesGuineBissau INNER JOIN sectoresGuineBissau ON regioesGuineBissau.codigoRegiao = sectoresGuineBissau.codigoRegiao) INNER JOIN ((ticketCA INNER JOIN tabancasBase ON ticketCA.codigoTabanca = tabancasBase.codigoTabanca) LEFT JOIN contactos ON ticketCA.fonteInformacao = contactos.codigoContacto) ON (sectoresGuineBissau.codigoSector = tabancasBase.codigoSector) AND (regioesGuineBissau.codigoRegiao = tabancasBase.codigoRegiao) "
    strSetSQL = strSetSQL & "WHERE (((ticketCA.codigoTicket)= "
    strSetSQL = strSetSQL & "'" & Me.fld_codigoTicket & "' " & ")); "
    Set Q17_selectTicketDef = sireshDB.QueryDefs("Q17_selectTicket")
    Debug.Print "Código OT : "; Me.fld_codigoTicket
    Debug.Print strSetSQL
    Q17_selectTicketDef.SQL = strSetSQL
    Set Q17_selectTicketSet = sireshDB.OpenRecordset("Q17_selectTicket", dbOpenDynaset)
    Set Q30_selectOTSet = sireshDB.OpenRecordset("Q30_selectOT", dbOpenDynaset)
    Forms![_commonVariables]![currentTicket] = Me.fld_codigoTicket
   
    ' because something is deadly wrong we create a 1 record table with the OT
    If fTableExists("tbl_rptOT") Then
        strSQL = "DROP TABLE tbl_rptOT"
        DBEngine(0)(0).Execute strSQL, dbFailOnError
    End If
    ' now create a table with the data
    strSetSQL = "SELECT Q30_selectOT.codigoTicket, Q30_selectOT.codigoTabanca, Q30_selectOT.dataAbertura, Q30_selectOT.nomeReparador, Q30_selectOT.nomeEmpresa, Q30_selectOT.tipoReparador, Q30_selectOT.tipoReparadorEspecialidade, Q30_selectOT.dataEvento, "
    strSetSQL = strSetSQL & " Q30_selectOT.dataMarcacao, Q30_selectOT.fechoTicket, Q30_selectOT.dataPrevistaFecho, Q30_selectOT.tempoDuracaoIntervencao, Q30_selectOT.observacoesTicket, Q30_selectOT.fonteInformacao, Q30_selectOT.codigoPonto, Q30_selectOT.anomaliaVerificada, "
    strSetSQL = strSetSQL & " Q30_selectOT.anomaliaObservacoes, Q30_selectOT.tipoEvento, Q30_selectOT.descricaoEvento, Q30_selectOT.tempoInicioIntervencao, Q30_selectOT.criticidadeAnomalia, Q30_selectOT.contactoReparador, Q30_selectOT.chaveTicketPrevio, "
    strSetSQL = strSetSQL & " Q30_selectOT.NomeTabanca, Q30_selectOT.nomeRegia, Q30_selectOT.nomeSector, Q30_selectOT.dataEfetivaFecho, Q30_selectOT.nomeContacto, Q30_selectOT.alcunhaContacto, Q30_selectOT.moradaTabanca, Q30_selectOT.tc, Q30_selectOT.telefone1Contacto, Q30_selectOT.telefone2Contacto "
    strSetSQL = strSetSQL & " INTO tbl_rptOT "
    strSetSQL = strSetSQL & " FROM Q30_selectOT; "
    DoCmd.RunSQL strSetSQL

    Debug.Print "create Table: "
    Debug.Print


yielding the string:

SELECT Q30_selectOT.codigoTicket, Q30_selectOT.codigoTabanca, Q30_selectOT.dataAbertura, Q30_selectOT.nomeReparador, Q30_selectOT.nomeEmpresa, Q30_selectOT.tipoReparador, Q30_selectOT.tipoReparadorEspecialidade, Q30_selectOT.dataEvento,  Q30_selectOT.dataMarcacao, Q30_selectOT.fechoTicket, Q30_selectOT.dataPrevistaFecho, Q30_selectOT.tempoDuracaoIntervencao, Q30_selectOT.observacoesTicket, Q30_selectOT.fonteInformacao, Q30_selectOT.codigoPonto, Q30_selectOT.anomaliaVerificada,  Q30_selectOT.anomaliaObservacoes, Q30_selectOT.tipoEvento, Q30_selectOT.descricaoEvento, Q30_selectOT.tempoInicioIntervencao, Q30_selectOT.criticidadeAnomalia, Q30_selectOT.contactoReparador, Q30_selectOT.chaveTicketPrevio,  Q30_selectOT.NomeTabanca, Q30_selectOT.nomeRegia, Q30_selectOT.nomeSector, Q30_selectOT.dataEfetivaFecho, Q30_selectOT.nomeContacto, Q30_selectOT.alcunhaContacto, Q30_selectOT.moradaTabanca, Q30_selectOT.tc, Q30_selectOT.telefone1Contacto, Q30_selectOT.telefone2Contacto  INTO tbl_rptOT  FROM Q30_selectOT;


and now I am getting a ms/access warining telling me that it want to paste    --- 0 ZERO - recordos onto the new table!
Then I'd suspect you're building that query incorrectly. We can't know that, of course, since we don't have access to your database.

If you open a NEW query, switch to SQL View, and copy/paste the output of the Debug window (the SQL you show above) - does this show any records?

Is the Report bound to your query? Or are you trying to set the Recordsource in some other manner?

Are you opening the Report in Preview mode (and not Report or layout mode)?
ticketCA.codigoTicket is the parameter.  You are supplying a value when you run the SQL string to open the query in the event procedure but this argument is not being passed to the report.

1. Are you being prompted for a value when you run the query by double clicking on it?
I am uploading the database.
the report name is [rpt_OT] and it is invoked by a click event in the button [btn_gerarOT] in the form [OTNova].
I am not prompted and the query result is ok...

thanks for the help


/joao
siresh-gb.03.13.accdb
As I mentioned earlier, your queries are return no records, so your report will show no data.

We don't know what that query SHOULD show, or how it should be constructed, so it's hard to provide suggestions for fixing it. From what I can see, you're filtering for a value in ticketCA.codigoTicket of "OT000038.16116". There is a single record in that table with that value, but that record holds no value in the codisoTabanca or fonteInformacao fields, so you'd have not values in the related tables either.

In other words - your query seems to be returning the correct data, based on the filter you included.

Also, as Pat mentioned it's generally a better idea to base your report on a query and then pass parameters into your report when opening it. I'm not sure at this point exactly what your report should show, so it's hard to suggest how to make those changes, but it would seem that "Q17_SelectOT" would be the one you'd use. If I remove the filter from Q17_SelectOT (the one for ticketCA.codigoTicket), and base the report on that query, the report returns multiple records. If I then modify the Click event of btn_gerarOT to include ONLY this line:

DoCmd.OpenReport "rpt_OT", acViewPreview, , "codigoTicket='OT000036.161115'"

The report returns multiple records. If I change the value of codigoTicket to OT000036.161116 the report returns 0 records (as expected, since as indicated above there are no related records).

To summarize:

I don't see any reason to open recordsets or manipulate querydefs. Some are under the mistaken impression that you must "run" a SELECT query before using it for a report, but that's not the case. Access will handle all of that behind the scenes and all you need to do is base your report off your query and let Access do the rest.
Hi -

I have modified the code accordingly to your suggestion. In addition, just before issuing the docmd.openreport I am printing out the parameter. And, on the report Load and on the report no data events I did put a debug.print to check out.

the prints are:


codigo ticket:OT000041.161116 (just before invoking the report)
Report NO DATA event (no data event)
Report Load codigo.ticket: - yelds a null (load event)


the code is:

(before opening the report)

    strSetSQL = "SELECT Q30_selectOT.codigoTicket, Q30_selectOT.codigoTabanca, Q30_selectOT.dataAbertura, Q30_selectOT.nomeReparador, Q30_selectOT.nomeEmpresa, Q30_selectOT.tipoReparador, Q30_selectOT.tipoReparadorEspecialidade, Q30_selectOT.dataEvento, "
    strSetSQL = strSetSQL & " Q30_selectOT.dataMarcacao, Q30_selectOT.fechoTicket, Q30_selectOT.dataPrevistaFecho, Q30_selectOT.tempoDuracaoIntervencao, Q30_selectOT.observacoesTicket, Q30_selectOT.fonteInformacao, Q30_selectOT.codigoPonto, Q30_selectOT.anomaliaVerificada, "
    strSetSQL = strSetSQL & " Q30_selectOT.anomaliaObservacoes, Q30_selectOT.tipoEvento, Q30_selectOT.descricaoEvento, Q30_selectOT.tempoInicioIntervencao, Q30_selectOT.criticidadeAnomalia, Q30_selectOT.contactoReparador, Q30_selectOT.chaveTicketPrevio, "
    strSetSQL = strSetSQL & " Q30_selectOT.NomeTabanca, Q30_selectOT.nomeRegia, Q30_selectOT.nomeSector, Q30_selectOT.dataEfetivaFecho, Q30_selectOT.nomeContacto, Q30_selectOT.alcunhaContacto, Q30_selectOT.moradaTabanca, Q30_selectOT.tc, Q30_selectOT.telefone1Contacto, Q30_selectOT.telefone2Contacto "
    strSetSQL = strSetSQL & " INTO tbl_rptOT "
    strSetSQL = strSetSQL & " FROM Q30_selectOT; "
    'DoCmd.RunSQL strSetSQL

    Debug.Print "create Table: "
    Debug.Print
    Debug.Print strSetSQL
    'DoCmd.OpenReport "rpt_OT", acViewPreview
    Debug.Print "codigo ticket:" & Me.fld_codigoTicket
    DoCmd.OpenReport "rpt_OT", acViewPreview, , "codigoTicket='" & Me.fld_codigoTicket & "'"


(btw - the queries return the correct record)



Private Sub Report_Load()
  Debug.Print "Report Load codigo.ticket:" & Nz(Me.fld_codigoTicket, "null codigo ticket")
End Sub

Private Sub Report_NoData(Cancel As Integer)
     Debug.Print "Report NO DATA event"
End Sub


and now, if I open the report from the toolbar, it is perfect!
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the help pointing me in the right direction!!!!!

2 hards days - I can't pay you
This was way more difficult than it needed to be and I don't understand why you persist in opening a recordset prior to opening the report despite everyone telling you it isn't necessary.  There is also no need to do the make table since the report can be opened using the where argument.  All that the temp table is doing is bloating your database unnecessarily.  Apparently, the recordset isn't large or we would be having the "why is my database bloating" discussion also.
Hi -

the table is not there - I told you before - and the recordset filter also not  - but they were not the problem  they were correct (well... the table was a desperado situation).
the problem was that the record was not in the record set because I was not saving it. When I opened the query on the menu, the form had to be closed so the record was automatically saved upon form close... and I could not figure out it.

Now I am cleaning all the useless code. The RST size is not a challenge today, but it may be a challenge in the future as the application starts to be used. So, all in all, I am following your suggestion - do not previously open the RST...

thanks again!