change print preview record source

[alaric01]

hi i have form where user use it as an analytical form. every time a user set a certain criteria to the data, the form's record source change based on the criteria.

in that form i've placed a print button that opens a report for that form. in onclick event of that button i've set the report's record source is equal to the form's record source.

the problem occurs when i view the report in print preview. i've got an error message saying that record source cannot be change in print preview.



any idea?

thx

[HiTechCoach]

The way I handle this is to use DAO to modify the QueryDef of the Report's record source before opeinf then report.

You could also try use the Report's On Open event to change the record source to match the form's record source.

If you are applying a filter to the form, you can apply the form's filter to the report.

[alaric01]

thx for the reply.

i am sorry i am a newbie in this access world, could you give an example of how to use DAO to modify the QueryDef?

as for modifying the recordsource on open event i still got the same error, i've also tried on load event.

[HiTechCoach]

as for modifying the recordsource on open event i still got the same error, i've also tried on load event.

On Load? Reports don't have an "On Load" event?

So let's start with this:

What is the name of the form?

What is the code used to open the Report?

Will you post the code you used in the On Open event?

[alaric01]

reports do have on load event..

ok the form's name is frm_sales_report. in the form there's a button named btn_open_report_sales.

and the report's name is rpt_sales_report

in the onclick event of the button (in frm_sales_report) the code is:


-----------------------------------
'open the report
DoCmd.OpenReport "rpt_sales_report", acViewReport, "", "", acNormal

'modify the report's record source
Reports!rpt_sales_report.RecordSource = Forms!frm_sales_report.Form.RecordSource

Reports!rpt_sales_report.requery

------------------------------------

when the report is open there's no problem, showing the same data as in the form. The problem only occurs when i view the report in print preview.

i've tried to move that code to the on open and on load event of the report

-------------

Private Sub Report_Load()

me.recordsource = Forms!frm_sales_report.form.RecordSource

me.requery

end sub

-----------------

still the same problem occurs when viewing in print preview

oh yeah, also tried to open the report by using

---------------
DoCmd.OpenReport "rpt_sales_report", acViewPreview, "", "", acNormal

---------------

in the onclick event of the form's button. did not work.

[HiTechCoach]

reports do have on load event..

My Bad. I did not see you were using 2007. I thought you were using 2002/2003.

The On Load event for reports was added starting with 2007.

I will give it a test with Access 2007

[alaric01]

thank you for the help, really appreciate it

looking forward for your reply

[alaric01]

ok i know how to get around it by using filter in the onload event of the report instead of changing the recordsource. i've got no more error message and the data displays correctly.

However, i also have a label that suppose to display the date in the report and was chosen in the frm_sales_report. the date will show up in the report view. when i view in print preview the label shows nothing.

here's my code now
------------

Private Sub Report_Load()

    If IsNull(Forms!frm_main_sales_report.txt_dari_tgl) Or _
    IsNull(Forms!frm_main_sales_report.txt_sampai_tgl) Then

    Me.txt_date_title.Value = ""
    Me.Filter = ""
    Me.FilterOn = False
    
    txt_jumlah_grand_total = Forms!frm_main_sales_report.txt_jumlah_grand_total.Value
    txt_total_pembayaran = Forms!frm_main_sales_report.txt_total_pembayaran.Value
    
    Else
    
    Me.Filter = "this is the filter criteria"
    Me.FilterOn = True
  
    Me.txt_date_title.Value = Forms!frm_main_sales_report.txt_dari_tgl & " - " & _
    Forms!frm_main_sales_report.txt_sampai_tgl
    
    txt_jumlah_grand_total = Forms!frm_main_sales_report.txt_jumlah_grand_total.Value
    txt_total_pembayaran = Forms!frm_main_sales_report.txt_total_pembayaran.Value
  
  End If
    
End Sub

-----------------------

in print preview txt_date_title , txt_total_pembayaran and txt_jumlah_grand_total are blank. hopefully you're still following my thread HiTechCoach

[HiTechCoach]

Still here.

Since you are using Access 2007, do you have all the Office 2007 Service Packs install to at least SP2?

Report View and Print Preview are very different. I generally never use the report view for reports I an designing to be printed.

I have not ever trying your method of filtering. I usually have the the form that is collecting the criteria to pass the desired filter as a parameter of the Docmd.OpenReport. Another method I use is to modify the QueryDef() for the saved query for the report's record source before the report opens.

On the report have you tried setting the control source for the text box txt_date_title to

=IIF(IsNull(Forms!frm_main_sales_report.txt_dari_tgl),"",Forms!frm_main_sales_report.txt_dari_tgl & " - " & Forms!frm_main_sales_report.txt_sampai_tgl )

[alaric01]

yes i think i have all the Office 2007 service pack installed.

also tried the setting the control source to

=IIF(IsNull(Forms!frm_main_sales_report.txt_dari_tgl),"",Forms!frm_main_sales_report.txt_dari_tgl & " - " & Forms!frm_main_sales_report.txt_sampai_tgl )

i don't understand. it's not about changing the recordsource or filtering anymore. cause even a simple vba code in the report such as

Me.txt_jumlah_grand_total = "test"

will show up in the report view but not in the print preview

[HiTechCoach]

Does it work if you print it out onto paper?