Hi I'm relatively new to VBA and could really do with some help please!! This is going to sound really long winded i'm sorry but I hope it will paint a picture of what i'm trying to do.
Basically here is what I want to do: I want a form (Selector) to have 4 check boxes and a Run command button. When the user clicks run another form (ReportsMenu) will appear giving various options. Importantly though, when the user clicks OK (in the ReportsMenu form) I want (depending on which check boxes are checked in the Selector form) to output the reports as pdf files. The code currently outputs the reports as snapshot or rich text format (depending on which radio button is checked in ReportsMenu) but they would prefer it if instead of this it just outputted straight to PDF when OK was clicked.
However currently the Selector form has 5 radio buttons (one is used to select all) so the user can only select all or one of the 4 options (which is why they want it to have check boxes instead so they can pick more than one but not necessarily all (which I have changed in the Selector form)). The problem (eventually got there i'm sorry!!) is with the ReportsMenu form. The original code for the ReportsMenu is very messy and, as i'm not very familiar with VBA (nor with the logic of the person who wrote the code), i'm not sure which parts to change (to print as PDF) and what to leave as it is. (I would just start the whole thing from scratch but there is some code linked to outlook that im not sure affects anything else at all or not)
Here is the code (it is very long I apologise): -
Option Compare Database
-
-
Function ExSnap1()
-
On Error GoTo Macro1_Err
-
-
Dim MyChoice As String
-
Dim MyReport As String
-
Dim MySchool As String
-
-
If [Forms]![ReportsMenu]![ReportFrame] = 1 Then
-
MyChoice = "Full Special Needs (Students and Invigilators) by Faculty"
-
MyReport = "FullSpecialNeeds"
-
Else
-
If [Forms]![ReportsMenu]![ReportFrame] = 2 Then
-
MyChoice = "FullTimetablebyFaculty"
-
MyReport = "FullTimetable"
-
Else
-
If [Forms]![ReportsMenu]![ReportFrame] = 3 Then
-
MyChoice = "StudentTimeTablesbyProgFaculty"
-
MyReport = "StudentTimeTables"
-
Else
-
If [Forms]![ReportsMenu]![ReportFrame] = 4 Then
-
If [Forms]![Selector]![chkall].Value = False Then
-
Call TimetableBySchool 'individual school
-
GoTo Ending
-
Else 'all schools
-
MyChoice = "Date Order Timetable with Locations"
-
MyReport = "DateOrderTimeTables"
-
End If
-
Else
-
If [Forms]![ReportsMenu]![ReportFrame] = 5 Then
-
Call AllSnap 'all of the above
-
Exit Function
-
End If
-
End If
-
End If
-
End If
-
End If
-
-
If [Forms]![Selector]![frmFileFormat] = 1 Then
-
DoCmd.OutputTo acReport, MyChoice, "SnapshotFormat(*.snp)", "c:\" & MyReport & ".snp", False, ""
-
Else
-
DoCmd.OutputTo acReport, MyChoice, acFormatRTF, "c:\" & MyReport & ".rtf", False, ""
-
End If
-
-
-
-
If [Forms]![ReportsMenu]![ReportFrame] <> 4 Then
-
MySchool = [Forms]![Selector]![School]
-
-
Dim rst As Recordset
-
Set rst = CurrentDb.OpenRecordset("Select Contact, Faculty_ID from SchoolContacts where Faculty_ID = '" & MySchool & "'")
-
-
rst.MoveFirst
-
-
Dim myName As String
-
Dim mySchoolName As String
-
myName = rst.Fields(0).Value
-
End If
-
-
Dim oOutlook As Outlook.Application
-
Dim oMessage As Outlook.MailItem
-
Dim sFileNames As String
-
Dim oRecip As Outlook.Recipient
-
Dim oAttach As Outlook.Attachment
-
-
DoCmd.Echo True, "Emailing Report"
-
-
Set oOutlook = CreateObject("Outlook.Application")
-
-
Set oMessage = oOutlook.CreateItem(olMailItem)
-
-
With oMessage
-
.ReadReceiptRequested = True
-
If [Forms]![ReportsMenu]![ReportFrame] <> 4 Then
-
Set oRecip = .Recipients.Add(myName)
-
oRecip.TYPE = olTo
-
oRecip.Resolve
-
Else
-
MySchool = "All Schools"
-
End If
-
-
If [Forms]![Selector]![frmFileFormat] = 1 Then
-
-
.Subject = Format(Now(), "yyyy-mm-dd") & " Exam Timetabling: Snapshot Report"
-
.Body = "Find attached Snapshot Report: " & MyChoice & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
.Body = .Body & "If you do not have Snapshot viewer, download it from http://www.microsoft.com/downloads/details.aspx?amp;amp;displaylang=en&familyid=B73DF33F-6D74-423D-8274-8B7E6313EDFB&displaylang=en" & vbCrLf & vbCrLf
-
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".snp")
-
-
Else
-
-
.Subject = Format(Now(), "yyyy-mm-dd") & " Exam Timetabling: Report"
-
.Body = "Find attached Report: " & MyChoice & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".rtf")
-
-
End If
-
-
.Save
-
'.send
-
-
End With
-
Set oOutlook = Nothing
-
Macro1_Exit:
-
Exit Function
-
-
Macro1_Err:
-
MsgBox Error$
-
Resume Macro1_Exit
-
Ending:
-
End Function
-
-
Function TimetableBySchool() 'date order timetable for the selected school
-
On Error GoTo Macro1_Err
-
-
Dim MyChoice As String
-
Dim MyReport As String
-
Dim MySchool As String
-
-
MyChoice = "Date Order Timetable with Locations by School"
-
MyReport = "DateOrderTimeTablesBySchool"
-
-
If [Forms]![Selector]![frmFileFormat] = 1 Then
-
DoCmd.OutputTo acReport, MyChoice, "SnapshotFormat(*.snp)", "c:\" & MyReport & ".snp", False, ""
-
Else
-
DoCmd.OutputTo acReport, MyChoice, acFormatRTF, "c:\" & MyReport & ".rtf", False, ""
-
End If
-
-
MySchool = [Forms]![Selector]![School]
-
-
Dim rst As Recordset
-
Set rst = CurrentDb.OpenRecordset("Select Contact, Faculty_ID from SchoolContacts where Faculty_ID = '" & MySchool & "'")
-
-
rst.MoveFirst
-
-
Dim myName As String
-
Dim mySchoolName As String
-
myName = rst.Fields(0).Value
-
-
Dim oOutlook As Outlook.Application
-
Dim oMessage As Outlook.MailItem
-
Dim sFileNames As String
-
Dim oRecip As Outlook.Recipient
-
Dim oAttach As Outlook.Attachment
-
-
DoCmd.Echo True, "Emailing Report"
-
-
Set oOutlook = CreateObject("Outlook.Application")
-
-
Set oMessage = oOutlook.CreateItem(olMailItem)
-
-
With oMessage
-
.ReadReceiptRequested = True
-
-
If [Forms]![Selector]![frmFileFormat] = 1 Then
-
.Subject = Format(Now(), "yyyy-mm-dd") & " Exam Timetabling: Snapshot Report"
-
.Body = "Find attached Snapshot Report: " & MyChoice & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
.Body = .Body & "If you do not have Snapshot viewer, download it from http://www.microsoft.com/downloads/details.aspx?amp;amp;displaylang=en&familyid=B73DF33F-6D74-423D-8274-8B7E6313EDFB&displaylang=en" & vbCrLf & vbCrLf
-
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".snp")
-
-
Else
-
.Subject = Format(Now(), "yyyy-mm-dd") & " Exam Timetabling: Report"
-
.Body = "Find attached Report: " & MyChoice & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".rtf")
-
-
End If
-
.Save
-
'.send
-
-
End With
-
Set oOutlook = Nothing
-
Macro1_Exit:
-
Exit Function
-
-
Macro1_Err:
-
MsgBox Error$
-
Resume Macro1_Exit
-
-
End Function
-
-
-
Function AllSnap() 'all of the above option
-
On Error GoTo Macro1_Err
-
-
Dim MyChoice As String
-
Dim MyReport As String
-
Dim MySchool As String
-
-
MyChoice = "Full Special Needs (Students and Invigilators) by Faculty"
-
MyReport = "FullSpecialNeeds"
-
MyChoice1 = "FullTimetablebyFaculty"
-
MyReport1 = "FullTimetableby"
-
MyChoice2 = "StudentTimeTablesbyProgFaculty"
-
MyReport2 = "StudentTimeTables"
-
-
If [Forms]![Selector]![frmFileFormat] = 1 Then
-
DoCmd.OutputTo acReport, MyChoice, "SnapshotFormat(*.snp)", "c:\" & MyReport & ".snp", False, ""
-
DoCmd.OutputTo acReport, MyChoice1, "SnapshotFormat(*.snp)", "c:\" & MyReport1 & ".snp", False, ""
-
DoCmd.OutputTo acReport, MyChoice2, "SnapshotFormat(*.snp)", "c:\" & MyReport2 & ".snp", False, ""
-
Else
-
DoCmd.OutputTo acReport, MyChoice, acFormatRTF, "c:\" & MyReport & ".rtf", False, ""
-
DoCmd.OutputTo acReport, MyChoice1, acFormatRTF, "c:\" & MyReport1 & ".rtf", False, ""
-
DoCmd.OutputTo acReport, MyChoice2, acFormatRTF, "c:\" & MyReport2 & ".rtf", False, ""
-
End If
-
-
MySchool = [Forms]![Selector]![School]
-
-
Dim rst As Recordset
-
-
Set rst = CurrentDb.OpenRecordset("Select Contact, Faculty_ID from SchoolContacts where Faculty_ID = '" & MySchool & "'")
-
-
rst.MoveFirst
-
-
Dim myName As String
-
Dim mySchoolName As String
-
myName = rst.Fields(0).Value
-
-
Dim oOutlook As Outlook.Application
-
Dim oMessage As Outlook.MailItem
-
Dim sFileNames As String
-
Dim oRecip As Outlook.Recipient
-
Dim oAttach As Outlook.Attachment
-
Dim oAttach1 As Outlook.Attachment
-
Dim oAttach2 As Outlook.Attachment
-
-
DoCmd.Echo True, "Emailing Report"
-
-
Set oOutlook = CreateObject("Outlook.Application")
-
-
Set oMessage = oOutlook.CreateItem(olMailItem)
-
-
With oMessage
-
.ReadReceiptRequested = True
-
If [Forms]![ReportsMenu]![ReportFrame] <> 4 Then
-
Set oRecip = .Recipients.Add(myName)
-
Else
-
Set oRecip = .Recipients.Add("rgbf1")
-
End If
-
oRecip.TYPE = olTo
-
oRecip.Resolve
-
-
If [Forms]![Selector]![frmFileFormat] = 1 Then
-
.Subject = Format(Now(), "yyyy-mm-dd") & " Exam Timetabling: Snapshot Report"
-
.Body = "Find attached Snapshot Reports: " & MyChoice & ", " & MyChoice1 & " and " & MyChoice2 & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
.Body = .Body & "If you do not have Snapshot viewer, download it from http://www.microsoft.com/downloads/details.aspx?amp;amp;displaylang=en&familyid=B73DF33F-6D74-423D-8274-8B7E6313EDFB&displaylang=en" & vbCrLf & vbCrLf
-
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".snp")
-
Set oAttach1 = .Attachments.Add("c:\" & MyReport1 & ".snp")
-
Set oAttach2 = .Attachments.Add("c:\" & MyReport2 & ".snp")
-
Else
-
.Subject = Format(Now(), "yyyy-mm-dd") & " Exam Timetabling: Report"
-
.Body = "Find attached Snapshot Reports: " & MyChoice & ", " & MyChoice1 & " and " & MyChoice2 & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".rtf")
-
Set oAttach1 = .Attachments.Add("c:\" & MyReport1 & ".rtf")
-
Set oAttach2 = .Attachments.Add("c:\" & MyReport2 & ".rtf")
-
-
-
End If
-
.Save
-
'.send
-
-
End With
-
Set oOutlook = Nothing
-
Macro1_Exit:
-
Exit Function
-
-
Macro1_Err:
-
MsgBox Error$
-
Resume Macro1_Exit
-
-
End Function
-
-
As you can see the code is very messy and hard to follow for a simpleton like me. If you could help me to adjust the code accordingly that would be great!
If you need me to clarify anything else let me know
Thankyou I really appreciate it!
Sep 17 '09
22 9321 NeoPa 32,579
Recognized Expert Moderator MVP @g diddy
I hope you appreciate that doing that for you is not what we're about.
We will provide examples of what's required but we expect you to go through your own code and apply it where necessary. We don't need to see large quantities of your code in the post.
Thank you for your quick response ajalwaysus I will give it a try now.
you need to open your report and then you are supposed to feed it to the Leban function from the link, it will in turn convert it to a PDF.
I've changed the code with one check box for the time being to test it and I get the compile error: Sub or function not defined with the following highlighted (ConvertReportT oPDF). -
blRet = ConvertReportToPDF("MyChoice", vbNullString, _
-
strFullPath, False, False, 150, "", "", 0, 0, 1)
-
Just wondering where was ConvertReportTo PDF defined? Or is this something that I need to change for my code?
Thanks
Best Regards
Did you import the "modReportToPDF " and "clsCommonDialo g" modules that were in the Leban database? Because you need this before you can run my code.
-AJ
No I hadn't, my apologies. I have added them in and now get the error Compile Error - Exit Sub not allowed in Function or Property. Then the "Exit Sub" line is highlighted. Sorry to be a pain but I really am a novice :$ I tried removing it then I got an error saying that Macro1 wasn't there. Here is the end of the code: -
Macro1_Exit:
-
Exit Sub
-
-
Macro1_Err:
-
MsgBox Error$
-
Resume Macro1_Exit
-
Ending:
-
End Function
-
With just a quick glance, because this is a whole lot of code, I think it is because you are using an "Exit Sub" but the final line in your code indicates this is a Function. Perhaps replacing "Exit Sub" with "Exit Function" will work.
Let me know if this works,
-AJ
NeoPa 32,579
Recognized Expert Moderator MVP @ajalwaysus
It will. Nice catch AJ.
Yes that was the problem there, school boy error by me missing that! thanks for spotting it so quickly!
I now get the error: -
The report name 'MyChoice' you entered in either the property
-
sheet or macro is misspelled or refers to a report that doesn't exist.
-
Dim MyChoice As String is the variable and it holds the name of the report. I have a few of these variables as there are a few reports. MyReport is the name of the file when it is opened/sent/saved - it has worked with rtf so i've obviously messed up with the new code somewhere. How do you call the variable in the code?
To give you an idea of what I have for the check box I am testing: -
*chunk of code declaring variables etc missed out*
-
MyChoice = "Full Special Needs (Students and Invigilators) by Faculty"
-
MyReport = "FullSpecialNeeds"
-
DoCmd.OpenReport "MyChoice", acViewPreview, , , acHidden
-
strFullPath = "C:\MyReport.pdf"
-
blRet = ConvertReportToPDF("MyChoice", vbNullString, _
-
strFullPath, False, False, 150, "", "", 0, 0, 1
-
-
* big chunk of code to do with other things missed out*
-
-
.Body = "Find attached Report: " & MyChoice & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".pdf")
-
If I can get one working I should hopefully be able to fix the rest myself. The only problem is i'm useless at troubleshooting !!
Thanks again!!
NeoPa 32,579
Recognized Expert Moderator MVP
In lines #4 & #6 you use the string "MyChoice" instead of the variable MyChoice. Have fun.
I love you
Thank you NeoPa and Ajalwaysus for all your help!!
I really appreciate it. Thank you :D
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: LarsenMTL |
last post by:
I have a long running python cgi which in the end returns a link to a
pdf file. While it runs it generates a log that uses stdout right into
the html. I use the sys.stdout.flush() to make this log output
relatively realtime.
The log, however, has grown too long. I wish to output it instead
into a textarea. I thought I could just use the textarea tags before
the output starts. This works, but my output is no longer "real time"
even...
|
by: Jacek Dziedzic |
last post by:
Hello!
I have a piece of code that needs to display a formatted
table of pointers using streams, with the pointers represented
as hex values. It looks more or less like this:
#include <iostream>
#include <iomanip>
using namespace std;
|
by: Andy |
last post by:
Hi
I'm really stuck outputting a double number to the console with three
decimal places if the furthest right value is a zero.
I can coutput the number 4.546 as 4.546 but then if I output 0.220 it comes
out as 0.22 and drops the zero. Also, outputting 1.000 outputs as 1.
How can I format it to include the zeros?
|
by: cephelo |
last post by:
I have no problems outputting the attribute value when the node is in
context, for example, @id when an <status> node is in context.
However, I am having trouble outputting it in a <xsl:value-of />
element:
<xsl:value-of select="procresults/settings@priority />
where it looks like
|
by: DeniseY |
last post by:
I have an Access report that is created on the fly by the user selecting the fields to be included. The Access report comes out fine, but I want it to automatically output to an Excel spreadsheet. Again, I have this part working. But the fields in the resulting spreadsheet are in a different order than the Access report. (Example: The fields in the Access report might go FirstName, LastName,Address,City--in the Excel spreadsheet, they come up...
| |
by: Peter Nimmo |
last post by:
Hi,
I am writting a windows application that I want to be able to act as if
it where a Console application in certain circumstances, such as error
logging.
Whilst I have nearly got it, it doesn't seem to write to the screen in
the way I would expect.
The output is:
|
by: Matt |
last post by:
Hello. I'm having a very strange problem that I would like ot check
with you guys.
Basically whenever I insert the following line into my programme to
output the arguments being passed to the programme:
printf("\nCommand line arguement %d: %s.", i , argv );
The porgramme outputs 3 of the command line arguements, then gives a
segmentation fault on the next line, followed by other strange
|
by: phong.lee |
last post by:
Hello all,
I was wondering if someone can assist me in outputting 6 reports into
a pdf file? I created a macro that generates the 6 reports and right
now it's saved as a snapshot on my drive. Any ideals or sample will
be greatly appreciated.
|
by: billelev |
last post by:
This is probably a very easy question to answer:
I have been outputting some text to a message box, similar to the following:
strOutput = "---" & Chr(10) & Chr(10)
strOutput = strOutput & "VAR:" & AddTabs(1) & Format(rsVar!, "currency")
strOutput = strOutput & Chr(10) & Chr(10)
strOutput = strOutput & "Position Value:" & AddTabs(1) & Format(rsVar!, "currency") & Chr(10)
strOutput = strOutput & "Position Risk:" & AddTabs(1) &...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |