Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / January 2006

Tip: Looking for answers? Try searching our database.

module and microsoft mailmerge

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Mike - 09 Jan 2006 17:06 GMT
   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 - 10 Jan 2006 17:34 GMT
> 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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.