How to open search and open a Word document from Access?

[shawnntjr]

How to search and open a Word document from Access?

Hi all,

I am fairly weak at VBA coding, so forgive me if I use any wrong terms.

I was tasked to create an application where by the user enters keywords into an Access form, and when he clicks the button, it will run the keywords against the file names stored in the table and automatically open the Word document that is the best match.

I have created a table query called Directory, which contains FPath (Z:\), FName (Document1.doc) and Directory (Z:\Document1.doc).

Private Sub Command2_Click()
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Dim filepath As String
    Dim strSearch As String
 
    If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then
        MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed"
        Me.txtSearch.BackColor = vbYellow
        Me.txtSearch.SetFocus
    Else
        Me.txtSearch.BackColor = vbWhite
        strSearch = Me.txtSearch
        Set wrdApp = CreateObject("Word.Application")
        wrdApp.Visible = True
        filepath = "SELECT Directory.Directory FROM Directory WHERE ((Directory.FName Like ""*" & strSearch & "*""))"
        Set wrdDoc = wrdApp.Documents.Open(filepath)
    End If
End Sub

This is the code that I am currently using to search and open the Word documents, however, this code only manages to open Microsoft Word proogram without loading any documents. Also, there are no error messages when I click the submit button.

Can anyone point out my mistake or, better yet, show me how to correct it?

 

[JHB]

The filepath part is wrong.

filepath = "SELECT Directory.Directory FROM Directory WHERE ((Directory.FName Like ""*" & strSearch & "*""))"

Use a recordset the get the value, (code not tested).

Dim rst as DAO.Recordset
..
Set rst = CurrentDB.Openrecordset("SELECT Directory.Directory FROM Directory WHERE ((Directory.FName Like ""*" & strSearch & "*""))" 
filepath = rst![Directory]
..

 

[shawnntjr]

The filepath part is wrong.
Use a recordset the get the value, (code not tested).

Dim rst as DAO.Recordset
..
Set rst = CurrentDB.Openrecordset("SELECT Directory.Directory FROM Directory WHERE ((Directory.FName Like ""*" & strSearch & "*""))" 
filepath = rst![Directory]
..

Hi JHB!

Thanks for the quick reply
I tried implementing your recordset codes, however I am receiving "Compile Error: Syntax Error". How do I correct this? I have attempted to adjust the brackets and quotation marks but it didn't work.

OH WAIT, I managed to solve it already! Was missing out 1 quotation mark! Haha.

Thanks man, you've been a great help

 

[JHB]

As I wrote, is was not tested, try the below:

Set rst = CurrentDb.OpenRecordset("SELECT Directory.Directory FROM Directory WHERE ((Directory.FName Like '*" & strSearch & "*'))")

Or

Set rst = CurrentDb.OpenRecordset("SELECT Directory.Directory FROM Directory WHERE ((Directory.FName Like *" & strSearch & "*))")