CurrentDB path

[imperator]

Hi All

The code below attaches a word doc to an email and works fine:


If recBookingQry("Workshop") = "Facilitated" And recBookingQry("OnSiteOffSite") = 1 Then
.Attachments.Add ("\\barton\home\15311\Museum_Usage_Database\Attachments\Facilitated_Workshop_Visit_Confirmation_Form.doc")



however, I want to replace the path (up to "Attachments...") with a non-specific path based on the CurrentDB path. I'm using this code to try and achieve this:


Public Function GetDBPath() As String
Dim strFullPath As String
Dim I As Integer
strFullPath = CurrentDb().name
For I = Len(strFullPath) To 1 Step -1
If Mid(strFullPath, I, 1) = "\" Then
GetDBPath = Left(strFullPath, I)
Exit For
End If
Next
End Function


?GetDBPath in the immediate window returns:

\\barton\home\15311\Museum Usage Database\

which is good.

but my attempts to use GetDBPath into the attachments path returns an error. Here is my wrong attempt:


.Attachments.Add (GetDBPath & "Attachments\Facilitated_Workshop_Visit_Confirmation_Form.doc")


Can someone point out my error and give me the correct solution?

Many thanks.

 

[imperator]

Just to be clear, the attachments will be in their own folder within the folder holding the database .mdb

 

[spikepl]

If three bits put together do not work, then the normal practice is to verify that each works on its own.

put your path into a sting
debug.print the string

and when you get an error and ask for help, say what the error is.

For debugging in general, follow this: http://www.access-programmers.co.uk/forums/showthread.php?t=149429 in the section Debugging Tips

 

[imperator]

If three bits put together do not work, then the normal practice is to verify that each works on its own.

put your path into a sting
debug.print the string

and when you get an error and ask for help, say what the error is.

For debugging in general, follow this: http://www.access-programmers.co.uk/forums/showthread.php?t=149429 in the section Debugging Tips

Thanks for the reply Spikepl. I used debugging and discovered the code was fine but the filename of the doc was slightly different than in the code. Embarrassing.
Cheers

 

[gemma-the-husky]

note that the folder in which the dbs is stored is given directly by

currentproject.path (no trailing "\" character) - so you don't actually need to mess around with the database name

 

[imperator]

note that the folder in which the dbs is stored is given directly by

currentproject.path (no trailing "\" character) - so you don't actually need to mess around with the database name

Excellent Dave, a much neater solution. No need for the Function.
Many thanks.
Ray

 

[gemma-the-husky]

some of these things happened after A97.

A97 was superb(still is - but won't run on W7), but among other things didn't have currentproject, InStrRev, Split, and other stuff such as conditional formatting.

I think a lot of us have had to write code to do stuff like this in A97. It still all works, but could now be replaced by simpler functionality from later versions of Access