Prev: Annoying Data Type Conversion Error
Next: Final Reminder - Microsoft Responds to the Evolution of Community
From: Kaliman on 28 May 2010 12:23
I have a continuous form in Access 2003. I have a form with a txtbox
(txtbox1) control showing file documents. I need in a command button to read
the record in txtbox1, then search in the path C:\BZ\REF and do one of the
following:

1. If the record in txtbox1 exists in the path then open the file

2. If the record does not exist then show a message “This file is not
in the database”
From: Lord Kelvan on 30 May 2010 21:27
Ok file management this is what I do

For future proofing your db I would dynamically reference the folder

If the folder c:\BZ is the location of your mdb file use this code
‘=========Code Start=========
Dim strDBPath As String
Dim strDBFile As String
Dim myDocumentfilepath As String
strDBPath = CurrentDb.name
strDBFile = Dir(strDBPath, vbHidden)
myDocumentfilepath = Left$(strDBPath, Len(strDBPath) - Len(strDBFile))
& "REF\"

If Dir(myDocumentfilepath & textbox1.value) = textbox1.value Then
‘I know this is not where you place this dim but it is just
here for now
Dim objword As Word.Application
Set objword = New Word.Application
objword.ChangeFileOpenDirectory (myDocumentfilepath)
objword.Application.Documents.Open (textbox1.value)
objword.Application.Visible = True
objword.Application.WindowState = wdWindowStateMaximize
Set objword = Nothing
Else
Msgbox “This file is not in the database”
End if
‘=========Code end=========
Alternatively if the documents in that folder are not word documents
you can use

Application.followhyperlink myDocumentfilepath & textbox1.value

Instead of all that objword code. It will just open in Internet
explorer that’s all.

If the BZ folder is not the location of your mdb file use this code
‘=========Code Start=========
Dim myDocumentfilepath As String
myDocumentfilepath = " C:\BZ\REF\"

If Dir(myDocumentfilepath & textbox1.value) = textbox1.value Then
‘I know this is not where you place this dim but it is just
here for now
Dim objword As Word.Application
Set objword = New Word.Application
objword.ChangeFileOpenDirectory (myDocumentfilepath)
objword.Application.Documents.Open (textbox1.value)
objword.Application.Visible = True
objword.Application.WindowState = wdWindowStateMaximize
Set objword = Nothing
Else
Msgbox “This file is not in the database”
End if
‘=========Code end=========
Again replace the objword code with the followhyperlink code if they
are not word documents.

As I said in the first statement for the effort for future proofing
keep the files in the same folder as your mdb file. Means you can
move the whole database anywhere on the drive and it is not bound to a
certain location. If it is bound to a certain location you can never
move it. And though you might not think you need to move it you never
know what the future might bring.

Hope this helps

Regards
Kelvan
From: Kaliman on 31 May 2010 11:55
Lord Kelvan



Now all my documents are pdf

I wrote the code as you suggest, I hope I did it well. However, when I click
button Open only the message is displayed. Do I need something more in the
code to open the document in txtbox1.



Thank you very much for your help



Private Sub bOpen_Click()

Dim strDBPath As String

Dim strDBFile As String

Dim myDocumentfilepath As String

strDBPath = CurrentDb.Name

strDBFile = Dir(strDBPath, vbHidden)

myDocumentfilepath = Left$(strDBPath, Len(strDBPath) - Len(strDBFile)) &
"REF\"



If Dir(myDocumentfilepath & txtbox1.Value) = textbox1.Value Then

'I know this is not where you place this dim but it is just here for now

Application.FollowHyperlink myDocumentfilepath & textbox1.Value

Else

MsgBox "This file is not in the database"

End If

End Sub

"Lord Kelvan" wrote:

> Ok file management this is what I do
>
> For future proofing your db I would dynamically reference the folder
>
> If the folder c:\BZ is the location of your mdb file use this code
> '=========Code Start=========
> Dim strDBPath As String
> Dim strDBFile As String
> Dim myDocumentfilepath As String
> strDBPath = CurrentDb.name
> strDBFile = Dir(strDBPath, vbHidden)
> myDocumentfilepath = Left$(strDBPath, Len(strDBPath) - Len(strDBFile))
> & "REF\"
>
> If Dir(myDocumentfilepath & textbox1.value) = textbox1.value Then
> 'I know this is not where you place this dim but it is just
> here for now
> Dim objword As Word.Application
> Set objword = New Word.Application
> objword.ChangeFileOpenDirectory (myDocumentfilepath)
> objword.Application.Documents.Open (textbox1.value)
> objword.Application.Visible = True
> objword.Application.WindowState = wdWindowStateMaximize
> Set objword = Nothing
> Else
> Msgbox “This file is not in the database”
> End if
> '=========Code end=========
> Alternatively if the documents in that folder are not word documents
> you can use
>
> Application.followhyperlink myDocumentfilepath & textbox1.value
>
> Instead of all that objword code. It will just open in Internet
> explorer that's all.
>
> If the BZ folder is not the location of your mdb file use this code
> '=========Code Start=========
> Dim myDocumentfilepath As String
> myDocumentfilepath = " C:\BZ\REF\"
>
> If Dir(myDocumentfilepath & textbox1.value) = textbox1.value Then
> 'I know this is not where you place this dim but it is just
> here for now
> Dim objword As Word.Application
> Set objword = New Word.Application
> objword.ChangeFileOpenDirectory (myDocumentfilepath)
> objword.Application.Documents.Open (textbox1.value)
> objword.Application.Visible = True
> objword.Application.WindowState = wdWindowStateMaximize
> Set objword = Nothing
> Else
> Msgbox “This file is not in the database”
> End if
> '=========Code end=========
> Again replace the objword code with the followhyperlink code if they
> are not word documents.
>
> As I said in the first statement for the effort for future proofing
> keep the files in the same folder as your mdb file. Means you can
> move the whole database anywhere on the drive and it is not bound to a
> certain location. If it is bound to a certain location you can never
> move it. And though you might not think you need to move it you never
> know what the future might bring.
>
> Hope this helps
>
> Regards
> Kelvan
> .
>
From: Lord Kelvan on 31 May 2010 16:39
Works for me you have a mistake in your code

If Dir(myDocumentfilepath & txtbox1.Value) = textbox1.Value Then

You have spelt textbox1 wrong

If that doesn’t fix it please explain exactly what happens and what
value is in textbox1

Regards
Kelvan
From: Kaliman on 2 Jun 2010 11:25
I Have been trying you code, but I had no success. I upload in this path
"c:\BZ\REF" two images where you can see the message, the code, the path and
the folder where the file is stored

"Lord Kelvan" wrote:

> Works for me you have a mistake in your code
>
> If Dir(myDocumentfilepath & txtbox1.Value) = textbox1.Value Then
>
> You have spelt textbox1 wrong
>
> If that doesn't fix it please explain exactly what happens and what
> value is in textbox1
>
> Regards
> Kelvan
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Annoying Data Type Conversion Error
Next: Final Reminder - Microsoft Responds to the Evolution of Community