module and microsoft mailmerge

[Guest]

Hello. I have narrowed my problem down to my modules that are used in
queries in my database. my problem is that when I perform a mailmerge to a
word document the modules used seem to freeze-up the mailmerge. the modules
are used to summarize multiple records fields into a single field.
Below you will find an example of my code. It lists a patients X-rays
according to the date of visit. can anyone tell why this would give me
trouble when it is used in a query that is used in a mailmerge? and are
there any solutions?

Public Function ListXRay(TheSSN As String, TheDateofVisit As String) As
String
Dim rst As DAO.Recordset
Dim XRList As String
Set rst = CurrentDb.OpenRecordset("SELECT SSN, Radiograph,
DateOfVisit FROM [Imaging Studies] WHERE SSN='" & TheSSN & "' And
DateOfVisit=#" & TheDateofVisit & "#")
With rst
Do Until .EOF
XRList = XRList & ![Radiograph] & "; "
.MoveNext
Loop
End With
If Len(XRList) Then
XRList = Left(XRList, Len(XRList) - 2)
End If
ListXRay = XRList
End Function

 

[Tim Ferguson]

can anyone tell why this would give me
trouble when it is used in a query that is used in a mailmerge?

What I guess you are doing is that the SQL query says something like

SELECT
Pt.FullName,
Pt.DateOfBirth,
ListXRay(Pt.SSN, Pt.DateOfVisit),
etc
FROM DataSource

From the point of view of a MS Word mailmerge, though, there is no VBA or
modules. If your SQL references a user-defined VBA function it will just
fail.

You will need to do a proper Join between the tables you are using at the
moment and the ImagingStudies, then some fancy work with <<next record>>
fields in the Word document to format them horizontally rather than
vertically. It might be possible to use VBA within Word to get the list
of XRays, but I am not sure what a suitable event would be.

It's a _lot_ easier with the Access report designer!!

All the best


Tim F