Solved

ms/access report does not open query

Posted on 2016-11-15
20
33 Views
Last Modified: 2016-11-16
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?
0
Comment
Question by:jirdeaid
  • 10
  • 5
  • 5
20 Comments
 
LVL 84
ID: 41888115
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.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41888257
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.
0
 

Author Comment

by:jirdeaid
ID: 41888401
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...
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41888445
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.
0
 
LVL 84
ID: 41888447
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.
0
 

Author Comment

by:jirdeaid
ID: 41888562
from the navigation pane runs smoothly....
0
 

Author Comment

by:jirdeaid
ID: 41888734
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.
 

ss1
ss2

Screen-Shot-2016-11-15-at-20.39.42.png

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
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41888773
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.
0
 

Author Comment

by:jirdeaid
ID: 41888788
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...
0
 

Author Comment

by:jirdeaid
ID: 41888833
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:


ss1

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


ss2

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!
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 84
ID: 41888837
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)?
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41889104
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?
0
 

Author Comment

by:jirdeaid
ID: 41889273
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
0
 
LVL 84
ID: 41889471
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.
0
 

Author Comment

by:jirdeaid
ID: 41889605
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!
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 41889610
I'm still at a loss to understand why you insist on creating a table. It seems to be unnecessary, but it could simply be that I don't fully understand your goal in this process.

If your goal is to present a report based on filtered data, then:

Does the query named Q17_SelectOT show the data you want on the report - not the FILTERED data, but instead the data on which the report would be based?

If it does, the set your report to use that as the Recordsource and then use the OpenReport method I showed earlier.

If it does not - what Query (or table) contains the data you would see in your report (and, again, NOT filtered data, but instead the full raw data feed used for the report prior to filtering)?
0
 

Assisted Solution

by:jirdeaid
jirdeaid earned 0 total points
ID: 41890362
Hi -

I do not insist. this is a harmless leftover - the DoCmd for it is a comment....

the filtered and final data is Q30, not Q17. Q30 depends on Q17, It is filtered simple because it comes from a form that competed and stored it.

Now, I have followed your suggestion and took off filtering from Q17:


Private Sub btn_gerarOT_Click()

 
   
    Dim sireshDB As DAO.Database
    Dim Q17_selectTicketSet As DAO.Recordset
    Dim Q30_selectOT As DAO.Recordset
    Dim Q17_selectTicketDef As QueryDef
    Dim strSetSQL As String
    Dim strChaveTabanca As String

    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 & "' " & ")); "
    strSetSQL = strSetSQL & ";"
    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
    Debug.Print strSetSQL
    'DoCmd.OpenReport "rpt_OT", acViewPreview
    Debug.Print "codigo ticket:" & Me.fld_codigoTicket
    DoCmd.OpenReport "rpt_OT", acViewPreview, , "codigoTicket='" & Me.fld_codigoTicket & "'"


End Sub


and got the same result - empty report


BUT!!!!
 but then I took off ALL filters including the  

DoCmd.OpenReport "rpt_OT", acViewPreview, , "codigoTicket='" & Me.fld_codigoTicket & "'"

so now I should have all records in the base table.


and no, I got all except the record that I need that is still in the form memory and not in the table. THAT is the REASON! :)

now I need to figure out a way to store the form BEFORE invoking the report..

I used


DoCmd.RunCommand acCmdSaveRecord


just before invoking the report and .... VOILÀ! it is working!
0
 

Author Closing Comment

by:jirdeaid
ID: 41890364
Thanks for the help pointing me in the right direction!!!!!

2 hards days - I can't pay you
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41890385
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.
0
 

Author Comment

by:jirdeaid
ID: 41890468
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!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now