João serras-pereira
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("Q1 7_selectTi cket", dbOpenDynaset)
strSetSQL = "SELECT ticketCA.codigoTicket, ticketCA.codigoTabanca, ticketCA.dataAbertura, ticketCA.dataEvento, ticketCA.dataMarcacao, ticketCA.fechoTicket, ticketCA.dataPrevistaFecho , ticketCA.tempoDuracaoInter vencao, ticketCA.observacoesTicket , ticketCA.fonteInformacao, ticketCA.codigoPonto, ticketCA.anomaliaVerificad a, ticketCA.anomaliaObservaco es, ticketCA.tipoEvento, ticketCA.descricaoEvento, ticketCA.tempoInicioInterv encao, ticketCA.criticidadeAnomal ia, ticketCA.contactoReparador , ticketCA.chaveTicketPrevio , tabancasBase.NomeTabanca, regioesGuineBissau.nomeReg ia, sectoresGuineBissau.nomeSe ctor, ticketCA.dataEfetivaFecho, contactos.nomeContacto, contactos.alcunhaContacto, contactos.moradaTabanca, contactos.tipoContacto AS tc, contactos.telefone1Contact o, contactos.telefone2Contact o "
strSetSQL = strSetSQL & "FROM (regioesGuineBissau INNER JOIN sectoresGuineBissau ON regioesGuineBissau.codigoR egiao = sectoresGuineBissau.codigo Regiao) INNER JOIN ((ticketCA INNER JOIN tabancasBase ON ticketCA.codigoTabanca = tabancasBase.codigoTabanca ) LEFT JOIN contactos ON ticketCA.fonteInformacao = contactos.codigoContacto) ON (sectoresGuineBissau.codig oSector = tabancasBase.codigoSector) AND (regioesGuineBissau.codigo Regiao = tabancasBase.codigoRegiao) "
strSetSQL = strSetSQL & "WHERE (((ticketCA.codigoTicket)= "
strSetSQL = strSetSQL & "'" & Me.fld_codigoTicket & "' " & ")); "
Set Q17_selectTicketDef = sireshDB.QueryDefs("Q17_se lectTicket ")
Debug.Print "Gerar OT:" & strSetSQL
Q17_selectTicketDef.SQL = strSetSQL
Set Q17_selectTicketSet = sireshDB.OpenRecordset("Q1 7_selectTi cket", dbOpenDynaset)
Forms![_commonVariables]![ currentTic ket] = 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?
to open the report I am using the following code:
Set sireshDB = CurrentDb
Set docsDataSet = sireshDB.OpenRecordset("Q1
strSetSQL = "SELECT ticketCA.codigoTicket, ticketCA.codigoTabanca, ticketCA.dataAbertura, ticketCA.dataEvento, ticketCA.dataMarcacao, ticketCA.fechoTicket, ticketCA.dataPrevistaFecho
strSetSQL = strSetSQL & "FROM (regioesGuineBissau INNER JOIN sectoresGuineBissau ON regioesGuineBissau.codigoR
strSetSQL = strSetSQL & "WHERE (((ticketCA.codigoTicket)=
strSetSQL = strSetSQL & "'" & Me.fld_codigoTicket & "' " & ")); "
Set Q17_selectTicketDef = sireshDB.QueryDefs("Q17_se
Debug.Print "Gerar OT:" & strSetSQL
Q17_selectTicketDef.SQL = strSetSQL
Set Q17_selectTicketSet = sireshDB.OpenRecordset("Q1
Forms![_commonVariables]![
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?
Create a query and save it. In the DoCmd.OpenReport method, specify the where argument.
docmd.OpenReport "OT",acViewPreview,,"ticke tCA.codigo Ticket = " & """" & 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.
docmd.OpenReport "OT",acViewPreview,,"ticke
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.
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...
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.
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.
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.
ASKER
from the navigation pane runs smoothly....
ASKER
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.
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
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.
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.
ASKER
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...
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...
ASKER
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:
tested it and the report, using the menu, showed everything.
then, I swiched the query to SQL view and copied to my code:
Set docsDataSet = sireshDB.OpenRecordset("Q1 7_selectTi cket", dbOpenDynaset)
strSetSQL = "SELECT ticketCA.codigoTicket, ticketCA.codigoTabanca, ticketCA.dataAbertura, ticketCA.dataEvento, ticketCA.dataMarcacao, ticketCA.fechoTicket, ticketCA.dataPrevistaFecho , ticketCA.tempoDuracaoInter vencao, ticketCA.observacoesTicket , ticketCA.fonteInformacao, ticketCA.codigoPonto, ticketCA.anomaliaVerificad a, ticketCA.anomaliaObservaco es, ticketCA.tipoEvento, ticketCA.descricaoEvento, ticketCA.tempoInicioInterv encao, ticketCA.criticidadeAnomal ia, ticketCA.contactoReparador , ticketCA.chaveTicketPrevio , tabancasBase.NomeTabanca, regioesGuineBissau.nomeReg ia, sectoresGuineBissau.nomeSe ctor, ticketCA.dataEfetivaFecho, contactos.nomeContacto, contactos.alcunhaContacto, contactos.moradaTabanca, contactos.tipoContacto AS tc, contactos.telefone1Contact o, contactos.telefone2Contact o "
strSetSQL = strSetSQL & "FROM (regioesGuineBissau INNER JOIN sectoresGuineBissau ON regioesGuineBissau.codigoR egiao = sectoresGuineBissau.codigo Regiao) INNER JOIN ((ticketCA INNER JOIN tabancasBase ON ticketCA.codigoTabanca = tabancasBase.codigoTabanca ) LEFT JOIN contactos ON ticketCA.fonteInformacao = contactos.codigoContacto) ON (sectoresGuineBissau.codig oSector = tabancasBase.codigoSector) AND (regioesGuineBissau.codigo Regiao = tabancasBase.codigoRegiao) "
strSetSQL = strSetSQL & "WHERE (((ticketCA.codigoTicket)= "
strSetSQL = strSetSQL & "'" & Me.fld_codigoTicket & "' " & ")); "
Set Q17_selectTicketDef = sireshDB.QueryDefs("Q17_se lectTicket ")
Debug.Print "Código OT : "; Me.fld_codigoTicket
Debug.Print strSetSQL
Q17_selectTicketDef.SQL = strSetSQL
Set Q17_selectTicketSet = sireshDB.OpenRecordset("Q1 7_selectTi cket", dbOpenDynaset)
Set Q30_selectOTSet = sireshDB.OpenRecordset("Q3 0_selectOT ", dbOpenDynaset)
Forms![_commonVariables]![ currentTic ket] = 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.tipoReparador Especialid ade, Q30_selectOT.dataEvento, "
strSetSQL = strSetSQL & " Q30_selectOT.dataMarcacao, Q30_selectOT.fechoTicket, Q30_selectOT.dataPrevistaF echo, Q30_selectOT.tempoDuracaoI ntervencao , Q30_selectOT.observacoesTi cket, Q30_selectOT.fonteInformac ao, Q30_selectOT.codigoPonto, Q30_selectOT.anomaliaVerif icada, "
strSetSQL = strSetSQL & " Q30_selectOT.anomaliaObser vacoes, Q30_selectOT.tipoEvento, Q30_selectOT.descricaoEven to, Q30_selectOT.tempoInicioIn tervencao, Q30_selectOT.criticidadeAn omalia, Q30_selectOT.contactoRepar ador, Q30_selectOT.chaveTicketPr evio, "
strSetSQL = strSetSQL & " Q30_selectOT.NomeTabanca, Q30_selectOT.nomeRegia, Q30_selectOT.nomeSector, Q30_selectOT.dataEfetivaFe cho, Q30_selectOT.nomeContacto, Q30_selectOT.alcunhaContac to, Q30_selectOT.moradaTabanca , Q30_selectOT.tc, Q30_selectOT.telefone1Cont acto, Q30_selectOT.telefone2Cont acto "
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.tipoReparador Especialid ade, Q30_selectOT.dataEvento, Q30_selectOT.dataMarcacao, Q30_selectOT.fechoTicket, Q30_selectOT.dataPrevistaF echo, Q30_selectOT.tempoDuracaoI ntervencao , Q30_selectOT.observacoesTi cket, Q30_selectOT.fonteInformac ao, Q30_selectOT.codigoPonto, Q30_selectOT.anomaliaVerif icada, Q30_selectOT.anomaliaObser vacoes, Q30_selectOT.tipoEvento, Q30_selectOT.descricaoEven to, Q30_selectOT.tempoInicioIn tervencao, Q30_selectOT.criticidadeAn omalia, Q30_selectOT.contactoRepar ador, Q30_selectOT.chaveTicketPr evio, Q30_selectOT.NomeTabanca, Q30_selectOT.nomeRegia, Q30_selectOT.nomeSector, Q30_selectOT.dataEfetivaFe cho, Q30_selectOT.nomeContacto, Q30_selectOT.alcunhaContac to, Q30_selectOT.moradaTabanca , Q30_selectOT.tc, Q30_selectOT.telefone1Cont acto, Q30_selectOT.telefone2Cont acto 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!
being desperate I tried another approach: create a Table using the "Make Table" query.
So I developed a new query, Q33 as below:
tested it and the report, using the menu, showed everything.
then, I swiched the query to SQL view and copied to my code:
Set docsDataSet = sireshDB.OpenRecordset("Q1
strSetSQL = "SELECT ticketCA.codigoTicket, ticketCA.codigoTabanca, ticketCA.dataAbertura, ticketCA.dataEvento, ticketCA.dataMarcacao, ticketCA.fechoTicket, ticketCA.dataPrevistaFecho
strSetSQL = strSetSQL & "FROM (regioesGuineBissau INNER JOIN sectoresGuineBissau ON regioesGuineBissau.codigoR
strSetSQL = strSetSQL & "WHERE (((ticketCA.codigoTicket)=
strSetSQL = strSetSQL & "'" & Me.fld_codigoTicket & "' " & ")); "
Set Q17_selectTicketDef = sireshDB.QueryDefs("Q17_se
Debug.Print "Código OT : "; Me.fld_codigoTicket
Debug.Print strSetSQL
Q17_selectTicketDef.SQL = strSetSQL
Set Q17_selectTicketSet = sireshDB.OpenRecordset("Q1
Set Q30_selectOTSet = sireshDB.OpenRecordset("Q3
Forms![_commonVariables]![
' 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,
strSetSQL = strSetSQL & " Q30_selectOT.dataMarcacao,
strSetSQL = strSetSQL & " Q30_selectOT.anomaliaObser
strSetSQL = strSetSQL & " Q30_selectOT.NomeTabanca, Q30_selectOT.nomeRegia, Q30_selectOT.nomeSector, Q30_selectOT.dataEfetivaFe
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,
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)?
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?
1. Are you being prompted for a value when you run the query by double clicking on it?
ASKER
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
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.16 1115'"
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.
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.16
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.
ASKER
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.tipoReparador Especialid ade, Q30_selectOT.dataEvento, "
strSetSQL = strSetSQL & " Q30_selectOT.dataMarcacao, Q30_selectOT.fechoTicket, Q30_selectOT.dataPrevistaF echo, Q30_selectOT.tempoDuracaoI ntervencao , Q30_selectOT.observacoesTi cket, Q30_selectOT.fonteInformac ao, Q30_selectOT.codigoPonto, Q30_selectOT.anomaliaVerif icada, "
strSetSQL = strSetSQL & " Q30_selectOT.anomaliaObser vacoes, Q30_selectOT.tipoEvento, Q30_selectOT.descricaoEven to, Q30_selectOT.tempoInicioIn tervencao, Q30_selectOT.criticidadeAn omalia, Q30_selectOT.contactoRepar ador, Q30_selectOT.chaveTicketPr evio, "
strSetSQL = strSetSQL & " Q30_selectOT.NomeTabanca, Q30_selectOT.nomeRegia, Q30_selectOT.nomeSector, Q30_selectOT.dataEfetivaFe cho, Q30_selectOT.nomeContacto, Q30_selectOT.alcunhaContac to, Q30_selectOT.moradaTabanca , Q30_selectOT.tc, Q30_selectOT.telefone1Cont acto, Q30_selectOT.telefone2Cont acto "
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!
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,
strSetSQL = strSetSQL & " Q30_selectOT.dataMarcacao,
strSetSQL = strSetSQL & " Q30_selectOT.anomaliaObser
strSetSQL = strSetSQL & " Q30_selectOT.NomeTabanca, Q30_selectOT.nomeRegia, Q30_selectOT.nomeSector, Q30_selectOT.dataEfetivaFe
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help pointing me in the right direction!!!!!
2 hards days - I can't pay you
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.
ASKER
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!
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!
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.