How to change Directory location in Albert Kallal's Word Merge program??

[Minddumps]

[SOLVED] How to change Directory location in Albert Kallal's Word Merge program??

I’m using Albert Kallal’s word merge program and trying to tell the program to look up a certain location for the Word folder, instead of searching for it in the same location that the access file is location. This is because I have a split database with many people having the front end copied on their desktop, but I need everyone to be able to access the Word mail merge folder in a central location.


In the "WordCode" module, I tried replacing this code:

Optional strDir As String = "Word\", _

With this code that I copied from some login code that looksup where the master version location is and edited to use with wordmerge:

Optional strDir As String = DLookup("[s_masterlocation] ", "Wordmerge_ location"), _

But I get a

“Compile Error: Constant expression required”.

And “DLookup” is highlighted with this code text:

 Public Function MergeSingleWord(Optional strDir As String = DLookup("[s_masterlocation]", "Wordmerge_ location"), _

I’m a VBA novice, can anyone help me figure out how to fix what I’m attempting?

 

[GinaWhipp]

You can't use a DLookup there. Though I have never tried this, the below might work...

Put this at the top of the Function under the other declarations...

Dim MyPath As String
MyPath = DLookup("[s_masterlocation] ", "Wordmerge_ location")

Then change...

Optional strDir As String = "Word\", _

...to...

Optional strDir As String = MyPath, _

 

[pr2-eugin]

Why not just pass the strDir from the Calling function using DLookUp?

 

[Minddumps]

Well I tried a few different things with still no success. The system tells me "Compile Error: Variable not defined" for "MyPath, _"

Any other ideas?

This is the changed code, did I put something in the wrong place?

Public Function MergeSingleWord(Optional strDir As String = MyPath, _
                                Optional bolFullPath As Boolean = False, _
                                Optional strOutPutDoc As String = "", _
                                Optional bolShowDelete As Boolean = True)

'''''''''''MYADD
Dim MyPath As String
MyPath = DLookup("s_masterlocation", "Wordmerge_ location")
'''''''''''MYADD

   
   ' Main Word merge function
   ' Albert D. Kallal  (c) copyright
   ' kallal@msn.com
   
   ' I do retain all copyrights - please contact me if you wish to publish this
   ' as part of a book, or article.
   '
   '
   ' starts the whole process of a "merge" template (single record) in.
   '
   ' Simply place this command behind a button on a form.
   ' A function was used here in place of a "sub". This was done since a
   ' custom menu bar can call this code by placing =MergeSingleWord() in the
   ' menu's on-action. Thus, if you use custom menu bars, this code will work!
   ' This code thus picks up the active screen name, and functions from that.
   
   ' Parms are:
   '  strDir            optional dir (include the \) the dir name - relative to applicaton dir
   '  bolfullPath       optinal flag. Set to TRUE if the above dir is a full path name. If you
   '                    do NOT set this flag (or leave it as false), the the path name is relative
   '                    to the appliction dir.
   '  stroutPutDoc      Name of the document to be saved to disk. (full path requied here)
   '  bolShowDelete     Shows the delete buttion if you want users to delete templates
   
   Dim frmF             As Form
   Dim strDirPath       As String      ' full path name to working dir
   
   Set frmF = Screen.ActiveForm
   frmF.Refresh
   
   strDirPath = DirToPath(strDir, bolFullPath)
   
   ' output our simple merge file
   
   If MakeMergeText(frmF, strMergeDataFile) Then
      DoCmd.OpenForm "GuiWordTemplate", , , , , acDialog, _
                     strDirPath & "~" & strOutPutDoc & "~" & bolShowDelete
      MergeSingleWord = strTemplate
   End If

End Function

 

[GinaWhipp]

Try putting a Breakpoint on the *MyPath* line and when it comes up check what it shows for the path.

 

[spikepl]

You are trying to define a constant at runtime - Access won't have it. Go with Paul's thought.

 

[Minddumps]

Ok so I figured it out after reading through Kallal's notes again.... the absolute location is specified in the form event procedure NOT in the actual module... for some reason I couldn't get that wrapped around my brain!!

For those looking for solution to this:

Private Sub cmdWord_Click()
Dim strMypath As String

strMypath = DLookup("s_masterlocation", "Wordmerge_location")
'For those not aware, it's ("name of column", "name of table") of where location is held

   MergeSingleWord strMypath, True

End Sub

Private Sub cmdMergeAll_Click()
Dim strMypath As String

strMypath = DLookup("s_masterlocation", "Wordmerge_location")
      
   Me.Refresh
   MergeAllWord ("select * from qryContactsMerge where " & Me.Filter), strMypath, True
      
      'Note that you can use a condtion in the above sql

End Sub

 

[pr2-eugin]

That is exactly what was suggested in Post#3

Why not just pass the strDir from the Calling function using DLookUp?

Glad you have it sorted.. Good Luck..