Access table to Mail Merge

[Jamie Steenberge]

I have a table with this information:

StudentID | LastName | FirstName | Grade | Subjects
103289 | Doe | Jane | 7 |
MathChr(9)43Chr(9)1,2,5Chr(13)Chr(10)ScienceChr(9)61Chr(9)3,4
64897 | Does | John | 6 |
ArtChr(9)43Chr(9)1,2,5Chr(13)Chr(10)HistoryChr(9)61Chr(9)3EnglishChr(9)64Chr(9)4,8
54678 | Doer | Jill | 8 |
EnglishChr(9)38Chr(9)1

I need to know two things.

1 - Each row should be on it's own page

2 - The Subjects column is tab delimited, and if there are multiple records
there are return characters at the end of the preceding lines. I could make
it whatever for the delimiters though. I need to be able to place the
Subjects column in a table on the report.

How can this be done?

Thanks in advance,

Jamie

 

[Peter Jamieson]

Assuming I have understood correctly and
a. your Subjects column is actually a memo field in Access and
b. what you want is something like

StudentID: 103289
LastName: Doe
FirstName: Jane
Grade: 7

-------------------------
| Subject | % | XXX |
-------------------------
| Math | 43 | 1,2,5 |
-------------------------
| Science | 61 | 3,4 |
-------------------------

<next page>

StudentID: 64897
etc.

where | and - represent real table cell borders, then I think your best
bet is probably to do the merge to a new document, inserting your
Subject field content as is, but surrounded by recognisable markers and
any trim text that needs to go in the table, e.g. so you have something like

+++
Subject<tab>%<tab>XXX<cr><lf>
Math<tab>43<tab>1,2,5<cr><lf>
Science<tab>61<tab>3,4
---

then use a VBA macro to select each chunk of text between +++ and ---
and use ConvertTextToTable to delete the +++ and ---, convert the text
to a table, and format it how you want.

e.g.

Sub Macro1()
Dim rngSearch As Word.Range
Dim tblCurrent As Word.Table
Set rngSearch = ActiveDocument.Content
With rngSearch
With .Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "(+++)(*)(---)"
.Replacement.Text = "\2"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchKashida = False
.MatchDiacritics = False
.MatchAlefHamza = False
.MatchControl = False
.MatchByte = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
End With
.Find.Execute Replace:=True
While .Find.Found
Set tblCurrent = .ConvertToTable(Separator:=vbTab)
With tblCurrent
' do any table formatting in here, e.g.
.Borders.Enable = True
.Columns(1).Width = 100
End With
Set tblCurrent = Nothing
' then find the next chunk
rngSearch.SetRange _
Start:=rngSearch.End, _
End:=ActiveDocument.Content.End
.Find.Execute Replace:=True
Wend
End With
End Sub

See, e.g. Graham Mayor's article at

http://www.gmayor.com/installing_macro.htm

for hints on how to install macros.

There are other ways to do this, but if that looks as if it does the
basics, I suspect it's the simplest approach in this case.


Peter Jamieson

http://tips.pjmsn.me.uk