Report Generation from with a Form

[amangupts]

Hi,



I am trying to create a report based on a form. There are mutliple text boxes which need to be transferred to the report. Report is to be in exact same format as the form itself. Kindly help me with the following issues:

a) Some textboxes at times will contain 0 value. When data is transferred to report, these textboxes should not get displayed.
b) Is there a way to auto adjust the report based on transfer of relevant data?
c) This report is created on "Click" of a command button.
d) Can this report be saved at the same time in a specific folder with a specific name?



Thanks a lot.
Aman

[June7]

a. use an expression in the textbox ControlSource: =IIf(fieldname=0,Null,fieldname)

b. what is it you want to adust?

c. DoCmd.OpenReport "reportname"

d. this requires exporting the report to another format, such as PDF

[amangupts]

Hi,

Thanks for your reply. Is it possible to hide the field completely instead of making it Null. Also can the report be directly generated into a PDF format when command button is clicked so that it becomes non-editable once it has been generated.

Aman

[June7]

So you don't want the textbox and its associated label to show at all? You can use the report's Detail section Format event to set visibility of controls.

Example: If Me!fieldname = 0 Then Me.textbox.Visible = False

Automating export to PDF requires VBA code. Here is one example http://tek-tips.com/faqs.cfm?fid=1635

[amangupts]

Hi,

Thanks for the information. Where can I find this Format option as it is not getting listed under event criterias.

Aman

[June7]

Click on the Detail section of the report. Should now see the Format event.

[amangupts]

Thanks a lot. I was able to locate and do what I needed to. Just a piece of information, when I tried to view report using command button, changes to the report format were not highlighted. But when I used preview option, it highlighted changes. Is it an error or a function?

[pbaldy]

That's a "feature". The section format events fire in Preview or Print modes, but not in the new Report view. Report view functions more like a form.

[amangupts]

Thanks a lot for the information. My project is almost near completion. However I am still stuck with two issues:

a) I have used send object criteria for mailing the report. But whenever I cancel it, it shows an error with the number 2501. How can I overcome this problem.
b) I am still not able to save it with a predefined name and in a specific folder on click of a button.

Please help me with these two issues.

[June7]

1. Maybe error handler code would work for this error. http://msdn.microsoft.com/en-us/libr...(v=VS.80).aspx

2. You are saving report to pdf? Post your code for analysis.

[amangupts]

Hi,

Actually there is no code. I am using send object for mailing purposes. Through that I am able to convert it to a PDF but only with the report name. This event is happening on click of a button named "Mail". This is where I am facing error no. 2501 with the message "SendObject action cancelled".

There is another button called "Generate" which I want to use for saving report with a Naming Convention and saving in a specified folder.

I went through the link but couldn't understand which error handler code would work for this error.


Still stuck with these two issues.

Thanks
Aman

[June7]

Check this thread http://bytes.com/topic/access/answer...8-error-2501-a

What is your second issue? The Generate button is not working? What happens - error message, wrong results, nothing? Post code for analysis.

[amangupts]

Hi,


I checked the link you gave and tried to convert the code as follows:

Public Function MailAccess()
On Error GoTo CheckError
DoCmd.SendObject acSendReport
ExitHere:
Exit Function
CheckError:
If Err.Number = 2501 Then Resume ExitHere
MsgBox "Err.Description & vbCrLf & Error # & Err.Number", vbOKOnly + vbCritical, "Error in MailAccess"
End Function

I created a seperate module for this, but still its not working. Kindly check if the code reworded by me is even correct or not.

As far as second issue is concerned, I have been able to write only the following code:

Public Sub Vol_Click()
DoCmd.OpenReport "OL", acViewPreview
End Sub

I want this report to get saved in a specified folder with a predefined name and mail should with this name. Currently it is going by the name of "OL" i.e. the name of the report. Mail Code is as follows:

Private Sub MailOL_Click()

DoCmd.SendObject acSendReport, "OL", acFormatPDF, Me.CanMail.Value, "", "", " OL" & "_" & Me.VendorName.Value & "_" & Format(Me.Text6.Value, "dd - mmm - yyyy"), "Kindly go through the attachment."

End Sub

Saving to PDF issue: I tried links given by you but I think I am not able to configure it properly as I am very new to VB and have only limited understanding of code formation.

Aman

[June7]

I see now you were originally using a macro with SendObject code. I don't use macros so don't know if they can have error handler code. Conversion to the VBA looks okay. The report must of course be open. I tested the SendObject code you posted and works nicely for me. I used other arguments of the SendObject to further automate.

This worked for me.

Public Sub MailAccess()
On Error GoTo CheckError
DoCmd.OpenReport "ContactList", acViewPreview
DoCmd.SendObject acSendReport, , acFormatPDF, "email address here", , , "Test", "This is a Test"
DoCmd.OutputTo acOutputReport, , acFormatPDF, "C:\Test.pdf"
ExitHere:
Exit Sub
CheckError:
If Err.Number = 2501 Then Resume ExitHere
MsgBox "Err.Description & vbCrLf & Error # & Err.Number", vbOKOnly + vbCritical, "Error in MailAccess"
End Sub

[amangupts]

Thanks a lot. With few modifications I have created separate command buttons for saving and mailing and thankfully both are working fine without any errors.

Please be a little patient with me, now this is the last leg of the code. Is is possible to attach the file saved in the folder on click of mail button. The objective behind this is to send file with same name as is saved in the folder otherwise there may be difficulty in tracking.

Kindly see if anything can be done about this.