We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Mail Merge w/ Access table/query

Medium Priority
557 Views
Last Modified: 2012-05-11
I need to do a mail merge with a query from access. I have an email and need to insert the course name (which is a field in my access database).  Some people have 1 course which is easy, however some people have 2,3, or 4 courses.

How can I insert more then one course for a person?
Comment
Watch Question

CERTIFIED EXPERT

Commented:
What does your table look like?

Person 1, Course 1
Person 1, Course 2
person 1, Course 3

or

Person 1, Course 1, Course 2, Course 3 (All in one record?)

Author

Commented:
It looks like the first example you posted.

Person 1, course 1
person 1, course 2
person 1, course 3
person 2, course 1
person3
and it continues on......

Person 2(or any person) can have anywhere from 1-4 courses listed. I need a mail merge that will only create one letter per person and list out their courses
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
I've been thinking about this.
Why don't we 'cheat' a little?
It's hard to get a query to convert rows to columns.
On the other hand, it isn't very hard to get Access to build an Excel worksheet...
And there's a LOT of granular control there.

Is that an approach you're confortable with?
How's your VBA coding skills?

Author

Commented:
Pretty good at vba. I need to do a mailmerge though. What are you thinking in excel
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
So lets build a worksheet.
1 row for each student with names, emails etc
Then for each row, find out how many courses, and add a column for each course.
Then make the excel sheet the recordset for the mail merge, rather than Access
CERTIFIED EXPERT

Commented:
I was able to produce the results of the query by creating a funciton within VBA.

I set up a table called "People"
it contains two columns... PERSON and COURSE both text

I set up a query called "Distinct_People_query"
SELECT DISTINCT people.[Person] FROM people;

Next set up a query that gets it's result from the above query... called wrapper query:
SELECT person, concatCourses(person) as Courselist
FROM Distinct_People_Query;

WrapperQuery is the query that would be called by your mailmerge function:

Within wrapper query a call is made to a VB function written for this called concatCourses:
Attached is the code for concatCourses.  This code would be inserted as a VBA Module.... not a Class Module.

Here are the results from wrapperquery:

person      Courselist
------------------------------------------------------------
Person1      Course1a,Course2a,Course3a
Person2      Course1b,Course2b
Person3      Course1c
Public Function ConcatCourses(thisPerson As String) As String


    Dim rst As DAO.Recordset
    Dim strResults As String
    Dim strSql As String
    
    
    strSql = "select people.course from people where person='" & thisPerson & "';"
 '   strSql = "select course from people"
    'open a recordset based on the query
    
    Set rst = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
 
    'loop though the query and build the results string
    'on the first time through the loop do not add the delimiter
    While Not rst.EOF
        If Len(strResults) > 0 Then
            strResults = strResults & "," & rst.Fields(0)
        Else
            strResults = rst.Fields(0)
        End If
        rst.MoveNext
    Wend
 
    'return the results string
    ConcatCourses = strResults

End Function

Open in new window

CERTIFIED EXPERT

Commented:
This is what my "People" table looked like to start with:
ID      Person      Course
----------------------------------------------------
3      Person1      Course1a
4      Person1      Course2a
5      Person1      Course3a
6      Person2      Course1b
7      Person2      Course2b
8      Person3      Course1c
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Ok,

It's done
I built a test db
Three tables, two queries and a form.

It'll build a spreadsheet in the same directory as it is put in that will be suitable as a mail merge source

Let me know how it plays
courses.mdb
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
It's an elegant little thing to keep around anyway.
Building spreadsheets from Access data via code can be very handy.
Data that users can screw up to their hearts' content--that you can create, or they can--with a button click

Author

Commented:
What do you mean a spreadsheet in the samd directory
CERTIFIED EXPERT

Commented:
attached is access db... wrapperquery will return the results you were asking for.
Database4.accdb

Author

Commented:
I am trying to follow Nick67s example.

I just built 2 querys has follows:

Qry1: Learner ID- text field, first name, last name, date, course, required date, supervisor
Qry2: Learner ID- text field, first name, last name, count of learnerId.

I keep getting an error with the code

Please Help

Author

Commented:
So i am also having trouble because I need the dates listed next to the courses as shown below in a sample letter. Also, I need the letter in PDF format.

Dear [first name] [last name],

The following courses are overdue:

[Course Title] [Required Date]
[Course Title] [Required Date]

Thank you

CC: [Supervisor Name]

Author

Commented:
buttersk-

your way is easier however it won't produce the results I described above in my new post. Perhaps you can help me alter the query
CERTIFIED EXPERT

Commented:
I am not clear on what you want to see in the results of your query.

Are you wanting results from two separate queries?  

(from your post above)

Qry1: Learner ID- text field, first name, last name, date, course, required date, supervisor
Qry2: Learner ID- text field, first name, last name, count of learnerId.


Author

Commented:
In Nick67's way you need 2 querys. With yours I only need one which is what I prefer, but how do I get it in the formats I explained above?
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
My sample is designed such that the code behind the button creates an Excel spreadsheet named mailmerge.xls
It puts that file in the same folder as courses.mdb is located.

You then create a Word mail merge document and use mailmerge.xls as the data source.

Here are all three

Put them on the desktop to start.
The mail merge is VERY dependent on where it thinks the Excel sheet is.
You'll undoubtably need to re-point it because your username isn't mine :)
courses.mdb
mailmerge.xls
The-merge.doc
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
How are you making out with this part of your puzzle?

Author

Commented:
Nick67-

Let me explain a little bit of where I am with what I am trying to do. Attached is a sample database I created. I just took the query "qryCreateMemo-Single" and launched the mail merge wizard through access. I wrote the memo and placed the fields where I needed them and created all the memos. I had aboout 450 so I ran the code above to break them up and save them individually to a specific folder.

Now I need all these to be a pdf. I dont mind going through the steps again, but perhaps we can add on to my code. Also, I still need to figure out from what I have how to do the MULITPLE courses. I only did the learner who have SINGLE courses so far.
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Last night after I wrote
<I suppose you now want it to email yet to boot?>
I had the palm-smack-forehead moment

You want to email out pdf's with specific data on them.
palm-smack-forehead
palm-smack-forehead

You want a report, you want to save it as a pdf, you want to email it.
That is TRIVIALLY easy compared to what we have been doing
No Excel, no mail merge, no monster query.
Just a report, a subreport, a loop to run one record at a time, and some code to create emails.

Oh well, it was a learning experience :)
palm-smack-forehead

P.S. You didn't attach a file
Do so, and I'll get it beat into shape

Author

Commented:
I think I do need a mail merge.

Did you read my previous post? So on the sample DB i sent you I have a memo template and am using the access fields to create it? I did all the ones with a single course, however I still need to figure out how to do multiples.

I can send you a sample of the memo template and email template if you need this.
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
There are no files attached to your posts

Author

Commented:
oh sorry

I thought I attached it.
Database1.mdb
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
<I can send you a sample of the memo template and email template if you need this. >

Sure, post them.
Oh BTW.  I did a compact-and-repair after I downloaded your db
It went from 34 MB to 236 KB :)

Author

Commented:
thank you. Its just a sample. my actual db is close to 2 gb
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
That's what I figured
I couldn't see what was making it so huge that it took four minutes to download
Just empty space :)

<I can send you a sample of the memo template and email template if you need this. >
Please?
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Yup, definitely
<Just a report, a subreport, a loop to run one record at a time and save as pdf, and some code to create emails.>

Give me a bit

Author

Commented:
ok
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Here is the interim.
Have a look at rptLearnerDelinquencyNotice.

Now,
Save each page as PDF and create emails.
Give me some feedback
kwarden.mdb

Author

Commented:
Nick67-

Look great! However, I need a seperate memo for each person and need to save each one individually as a PDF. Also, I would like to automate the email to be generated and attach the email. Also, a read receipt feature.

I know I am asking a lot.

Author

Commented:
Oh and each PDF needs to be saved as the learner ID in a folder on my drive.
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Remember the palm-smack-forehead moment? :)

I take it you don't have a lot of experience with Access reports?

Author

Commented:
No I usually export everything to excel
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
<grin>
That works, and reports are sometimes the most difficult thing to grapple with.
Once you get the down pat, though, they rock.
</grin>

Author

Commented:
So can you help automate the things stated above?

<.... need a seperate memo for each person and need to save each one individually as a PDF. Also, I would like to automate the email to be generated and attach the email. Also, a read receipt feature.>
<each PDF needs to be saved as the learner ID in a folder on my drive>

I really need this. I wish I could give you like triple points or something
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
You've got a lot of questions open :)
And you can ask a related question.

...and yes, I am working on it :)
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
There's a bunch of stuff to tweak,
But voila
kwarden.mdb
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Oh, read receipt I didn't do

Just add

.ReadReceiptRequested

in below the

.HTMLBody stuff

Author

Commented:
I guess I am missing something. I think I need to go back to the other way. I apologize but I dont think I can use what you have. It doesn't match the initial format and when I run it there is no pdf attachment w/ the email. I am more concerned with getting the pdf attachments right at the moment. I will worry about emailing after.
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
It plays on my side, forming up mail messages, pdf's and attaching them.
It depends on a folder c:\tempPDF existing, and being able to write/delete from

The body of the mail message is:

Jane Smith



The attached Notice to File is being placed in your Personnel File for failure to complete your required compliance training by the due date.
Please CLICK HERE to take your courses immediately. This training is mandatory and critical to ensure you are aware of important Company policies and procedures.
In the future, failure to complete your Compliance training by the due date may lead to disciplinary action, up to and including termination.

The PDF is attached
I can send you the sample email if you have an address


123456789.pdf

Author

Commented:
Where is the PDF stored?

My email is knw.fsu@gmail.com

Author

Commented:
Also, if you go to the design view of "rptLearnerDelinquencyNotice". How do I get the field under the learner ID header to show up

Author

Commented:
Also, how do I get the header on every page/memo. (the memorandum header and fields under the learner id header.
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
That is some of the possible tweaking that will be needed.
By default, they go to C:\tempPDF, and Outlook looks for them there to attach them
Tweak as needed

When they are created
From SaveAsOfficePDF
DoCmd.OutputTo acOutputReport, stdocname, FormatValue, "c:\tempPdf\" & LearnerID & ".pdf"

from modEmailReports
AttachmentPath = "c:\tempPDF\" & filename

Author

Commented:
ok so I changed the memo and that works fine. I just need to do some more tweaking to formatting and stuff. I'll play around and see. But do you how to get the headers and such as mentioned above
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
<Also, if you go to the design view of "rptLearnerDelinquencyNotice". How do I get the field under the learner ID header to show up >
if you want that stuff to show, you have to right-click the header, Properties, Visible = yes
I had that stuff hidden.

Reports are funny.
You can't use a field in code unless it exists in a control
What's more, if it is in a part of a control that doesn't get done over and over (ie report header) it doesn't change value
The learner id header exists solely to hise those controls and ensure they update as txtBlurb's value gets built in code

The mulit-page report is not the thing you are interested in printing/emailing
Anything in report header/footers is produced once
Everything in page header/footers winds up on each sheet

Tweaking :)
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
You wanted by Learner ID, so what you see on page one will be the format you see on each individual report, when things are filtered down to one user, and then looped.
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
In the email you must tweak the CLICK HERE <a href>  Google isn't where you want them to go!
   .HTMLBody = .HTMLBody & "Please <a href = 'http://www.google.com'>CLICK HERE</a> to take your courses immediately.   This training is mandatory and critical to ensure you are aware of important Company policies and procedures.<br>"

I don't know if you need the BCC

   ' Add the BCC recipient(s) to the message.
    Set objOutlookRecip = .Recipients.Add("Kwarden")
    objOutlookRecip.Type = olBCC

Comment or delete it, or put a proper email or Outlook user name in
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
More tweaks
Open report subDelinquencies
Take the stuff from the report header and put it in the footer
Take the stuff from the page header and put it in the report header
Make the report header visible, make the report fotter invisible
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
More tweaks in modEmailReports

Put Public EnoughPrompts as long

at the top of modEmailReports

Tweak this

if  EnoughPrompts = 0 then

MsgBox "The email is about to be created!" & vbCrLf & _
        "If nothing appears to be happening, the Outlook security box may be hiding behind an open window." & vbCrLf & _
        "Click the Outlook icon on the taskbar to bring it to the front, if necessary."

end if
EnoughPrompts = 1

Then put
EnoughPrompts = 0
as the last line in cmdCreateReports_Click()
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Ah,

I have a logic error in the cmdCreateReports.
I wanted emails only for delinquents, but I didn't specify Distinct, so I have one for each delinquency, and not one for each delinquent

My bad.

This is hard for me to test because Access 2003 can't save to PDF :0

Author

Commented:
Attached is an example of the format of the PDF I need to generate for every memo. I am going to continue trying to tweak, but it would be great if you could help! :)
Example.doc
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
change the first set rs... in cmdCreateReports to

Set rs = db.OpenRecordset("select distinct [learner id] from qryDelinquencies", dbOpenDynaset, dbSeeChanges)
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
By memo, do you mean the PDF or the email body?
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Will the From: be coming from a table field or just hard-code 'Training Dept.'?

Author

Commented:
Again I hate to do this, however the other way with the mail merge and splitter code I have below works great for the people who have only one course. I would rather see if I can create the same sort of query for the people with multiple courses.

Here is the PDF code
 
Sub SplitMergeLetter()
' splitter Macro modified to save individual letters with

' information from data source. The filename data must be added to

' the top of the merge letter - see web article.

Dim sName As String
Dim docName As String
Dim Letters As String
Dim Counter As Long
Dim oDoc As Document
Dim oNewDoc As Document
Set oDoc = ActiveDocument
'oDoc.Save

Selection.EndKey Unit:=wdStory
Letters = Selection.Information(wdActiveEndSectionNumber)
Selection.HomeKey Unit:=wdStory
Counter = 1
While Counter < Letters
     Application.ScreenUpdating = False
     With Selection

          .HomeKey Unit:=wdStory
          .EndKey Unit:=wdLine, Extend:=wdExtend
          .MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend

     End With
     sName = Selection
     docName = "J:\Memos\Single Course\" & sName & ".pdf"

     oDoc.Sections.First.Range.Cut
     Set oNewDoc = Documents.Add

    'Documents are based on the Normal template

    'To use an alternative template follow the link.
     With Selection
         .Paste
         .HomeKey Unit:=wdStory
         .MoveDown Unit:=wdLine, Count:=1, Extend:=wdExtend
         .Delete
     End With
     oNewDoc.SaveAs FileName:=docName, _
        FileFormat:=wdFormatPDF, _
        AddToRecentFiles:=False
     oNewDoc.Saved = True
     ActiveWindow.Close
     Counter = Counter + 1
     Application.ScreenUpdating = True

   
Wend

'oDoc.Close wdDoNotSaveChanges
End Sub

Open in new window














I know you have been working hard on this and I will still use the email/Outlook functionality but I think for uniform purposes the mail merge to a PDF is so much cleaner. i just can't figure out how to do multiple courses.

Author

Commented:
A hard code for the From Field

Author

Commented:
but then again  if I can't do the multiple courses with the mail merge/query technique then I will need to use your code/process,

Author

Commented:
All I would need for the other process (mail merge/query) is the right/correct  query and to tweak the letter to accomodate the courses.

Your process is very in-depth. I have no doubt it works, however I do worry about maintaining it since I will be handing this off when ready to do so
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
That is what code documentation is for :)

And we are almost all the way there now

Note just the first page of the sample
225256255.pdf

Author

Commented:
It looks great, just take out the date sent field. Also, kinda pety but I am guessing I can change the fonts and color. I want it all black. Also, can we make the course title and required date closer together and only underlined under the words.

Sorry for all the pety stuff but I am sending this out to over 10,000 people.

             
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Almost anything can be done

Tweaking :)

<only underlined under the words. > ?

Author

Commented:
I meant only underline under course title and required date; Not the whole way across. Also push the fields closer together

Author

Commented:
I am definitely going to take the sample and play around with reports after today. I didn't realize they could be so useful.
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Fonts?
Sizes?
Italics?
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
sample
225256255.pdf

Author

Commented:
I like this a lot! I think I can figure out the fonts and stuff.

THANK YOU SO MUCH :)
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Hang tough.

Here comes the spitting image of your memo.
Now, when you get Outlook to actually SEND messages, it forces a 5 second wait between messages.
I've never had a need to over-ride that
ClickYes can get you over that hump
http://www.contextmagic.com/express-clickyes/
These guys are good too
http://www.mapilab.com/outlook/security/

I have it set to display emails not send them.
That can be tweaked too


DisplayMsg = True
....
   ' Should we display the message before sending?
   If DisplayMsg Then
       .Display
   Else
       .Save
       .Send
   End If
kwarden.mdb

Author

Commented:
Yes the message definitely needs to be displayed before sending. Can we break out the creation of the PDFs and the actual sending of the memo into 2 buttons?
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Sure.

Where it now does two calls in the loop, instead just duplicate the whole code block and deep-six one call
instead of cmdCreateReports-Click()

 
Private sub cmdCreatePdfs_Click()
'this is the half the biggie, sewing it all together
Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("select distinct [learner id] from qryDelinquencies", dbOpenDynaset, dbSeeChanges)
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF = True
    Set rs1 = db.OpenRecordset("Select * from qryLearnerDetails where [Learner ID] = " & Chr(34) & rs![Learner ID] & Chr(34), dbOpenDynaset, dbSeeChanges)
    Call SaveAsOfficePDF(rs![Learner ID])
    rs1.Close
    Set rs1 = Nothing
    rs.MoveNext
Loop

end sub

Open in new window


and
Private Sub cmdCreateEmails_Click()
'this is the other half of the biggie, sewing it all together
Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("select distinct [learner id] from qryDelinquencies", dbOpenDynaset, dbSeeChanges)
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF = True
    Set rs1 = db.OpenRecordset("Select * from qryLearnerDetails where [Learner ID] = " & Chr(34) & rs![Learner ID] & Chr(34), dbOpenDynaset, dbSeeChanges)
    Call CreateAnEmail(rs![Learner ID] & ".pdf", rs1!FullName, Nz(rs1!Email, ""), Nz(rs1![Supervisor Email], ""))
    rs1.Close
    Set rs1 = Nothing
    rs.MoveNext
Loop

EnoughPrompts = 0
End Sub

Open in new window

Author

Commented:
Also, where do I find the txtBlurb?

Author

Commented:
I have no idea what you talking about with the code above?!?
<Where it now does two calls in the loop, instead just duplicate the whole code block and deep-six one call
instead of cmdCreateReports-Click()>

CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Ok.

Throw the form into Design View
Right click the 'Create Reports' button.
Open the On Click event

Author

Commented:
I still dont see it. I am trying to view the actual memo text, not the fields but the paragraphs
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
On rptLearnerDelinquencyReport

txtBlurb is the unbound control between Subject: and the sub report

txtBlurbB is the unbound control just above the page footer
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
The paragraphs are all built in VBA code, in the Detail_Format event

Author

Commented:
I see the unbound control, but where is the actual text. I just need  to tweak a word
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Reports are very different from MS Word.
Text has to come from fields in tables/queries or be built and assigned to me.somecontrol.value by VBA code
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
The email body text is built in code too
in modEmailReports

vbcrlf is a carraige return

Author

Commented:
so how do I go in and change a word

Author

Commented:
Just found it
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
in the code window
CTRL-F
find your phrase in the code, and tweak it

Let's say 'Compliance Training'

it's here in the report Detail_format event

Me.txtBlurbB.Value = Me.txtBlurbB.Value & "In the future, failure to complete your Compliance training by the due date may lead to disciplinary action, up to and including termination.  A copy of this memo will be placed in your Personnel File.  " & vbCrLf & vbCrLf


change it to whatever

Me.txtBlurbB.Value = Me.txtBlurbB.Value & "I am going to eat your babies  " & vbCrLf & vbCrLf
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
I gotta pick up my kid from school and eat lunch.
1 hr, 1:15 or so

Look through everything in the code window

Author

Commented:
I will thank you. I am going to try and break out the code into 2 steps:

1. Create PDFs
2. Create Emails

Thank you again! Ill post if I have any issues

Author

Commented:
I keep getting "expression has no value error" on the line of code below:

 
Me.txtBlurb.Value = [First] & " " & [Last] & ", you are receiving this Notice to File for failure to complete your required compliance training by the due date.  You were required to complete:" & vbCrLf & vbCrLf

Open in new window




Also, If I hover with my mouse over "Me.txtBlurb.Value"  it equals isNull. I can't figure out the issue. If I just open the report it is fine, its when I run the below code from the button on the form.

 
Private Sub cmdCreatePdfs_Click()
'this is the half the biggie, sewing it all together
Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("select distinct [learner id] from qryDelinquencies", dbOpenDynaset, dbSeeChanges)
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF = True
    Set rs1 = db.OpenRecordset("Select * from qryLearnerDetails where [Learner ID] = " & Chr(34) & rs![Learner ID] & Chr(34), dbOpenDynaset, dbSeeChanges)
    Call SaveAsOfficePDF(rs![Learner ID])
    rs1.Close
    Set rs1 = Nothing
    rs.MoveNext
Loop

End Sub

Open in new window









CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
[First] & " " & [Last]  are field from the query.
Do you have a adata booboo where one of those values was left Null?
Null is hated by concatenation

try Nz(first,"Damn!")  & " " & nz([Last],"it!")
Nz catches null and substitutes in what you wrote after the comma.

In production, put nicer things in.
It's just good in debugging top have the app do all the swearing for you :)

Author

Commented:
No records have a null values in those fields. I tried it with 2 records

Author

Commented:
Its says is null on the txtblurb field
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Null & "something" makes null
Another possibility is that rs or rs1 aren't returning any records.
I didn't build in checks for

if rs.recordcount = 0
if rs1.recordcount = 0
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Playing with something that extends it, I get the same thing.
Give me a bit and I'll push up some revisions
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Reports have a No_Data event.
You can catch and cancel and event that has no valid records sent to it

Private Sub Report_NoData(Cancel As Integer)
MsgBox "The query and/or filtered query returned no records.  The report won't open"
Cancel = True
End Sub
CERTIFIED EXPERT
Most Valuable Expert 2014
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Thank you. Ill test it out
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
No Problem.

Have a good weekend

Author

Commented:
Nick67-

You have been so thorough and patient with me. Thank you so much. Your solution works great!
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
<sheepish uncertainty>

Is it good?
We are an awfully long way from where we started!
Do you understand it?
Commandment 8, you know?
http://www.mvps.org/access/tencommandments.htm

Nick
</sheepish uncertainty>

Author

Commented:
Hi Nick67-

I had another question. So I took what you did and replicated it for each Region ( because each region has its own folder). So the createPDF button need an error handler I think, since that particular region might not have any records.

Not sure how to create this though. I am sure it's simple. Basically if I run the createPDF click event on the button and there are no records in the querys to be created then it ends. However i need it to quit that click event and go on to the next.

CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
You'll have to give me a couple days.  No nice way to say this:  tomorrow I have to bury my older brother
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
I am not certain how you altered the cmdCreatePDFs_Click event.
Did you create a loop within the code?
I take it [BA] is your regions?
Add it to qryDelinquencies
Add it to qryLearnerDetails
Create a table (most elegant) or an array to hold the path where each region will store its PDFs

I'm guessing
See if my speculations point you the right direction
Sorry for the post on the 26th.  I don't text well.  My older brother passed unexpectedly on the 18th
His funeral was the 27th.  Tough days.  Life goes on though.  No choice.

If you build the loop in, it won't need to error handle as the altered recordset will be empty for regions with no delinquents and nothing will print

 
Private Sub cmCreatePDFs_Click()
'half
Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset
Dim rsRegions As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("select distinct [learner id] from qryDelinquencies", dbOpenDynaset, dbSeeChanges)
Set rsRegions = db.OpenRecordset("select distinct [BA] from qryDelinquencies", dbOpenDynaset, dbSeeChanges) 'recordset with the possible regions
rs.MoveLast
rs.MoveFirst
rsRegions.MoveLast
rsRegions.MoveFirst
Do Until rsRegions.EOF = True 'loop through regions
    Do Until rs.EOF = True 'loop through each learner in the region
        Set rs1 = db.OpenRecordset("Select * from qryLearnerDetails where [Learner ID] = " & Chr(34) & rs![Learner ID] & Chr(34) & " and [BA] = " & Chr(34) & rsRegions![BA] & Chr(34), dbOpenDynaset, dbSeeChanges)
        Call SaveAsOfficePDF(rs![Learner ID], DLookup("RegionPath", "tblRegionPaths", "[BA] = " & Chr(34) & rsRegions![BA] & Chr(34)))
        'SaveAsOfficePDF now takes the learnerID for the file name and a full path to store the file in
        'path needs a trailing \  ie c:\tempPDF\
        'I presumed you'd create a table
        'if not, you need to code another method to pass in that path
        rs1.Close
        Set rs1 = Nothing
        rs.MoveNext
    Loop
    rsRegions.MoveNext
Loop
End Sub

Open in new window

Private Sub SaveAsOfficePDF(LearnerID As String, RegionPath As String)
Dim FormatValue As String
If Application.Version > 11 Then
    FormatValue = "PDF Format (*.pdf)"
Else
    FormatValue = acFormatRTF
End If

Dim stdocname As String
stdocname = "rptLearnerDelinquencyNotice"

DoCmd.OpenReport stdocname, acPreview, , "[Learner ID] = " & Chr(34) & LearnerID & Chr(34)
DoCmd.OutputTo acOutputReport, stdocname, FormatValue, RegionPath & LearnerID & ".pdf"
DoCmd.Close acReport, stdocname, acSaveYes
End Sub

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Ah, there were some logic bugs in cmCreatePDFs_Click()
I didn't build the loop quite right.

Here's a working sample.
Change values in tblRegionPaths as appropriate
Folder structure must exist prior to running

PS I didn't look for how this effects the emailing!
kwarden.mdb
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.