Opening MS Word document from a database in VB6

[Gazerdood]

I want to have an access database table which links to various MS Word documents.

These will be displayed in a VB6 form via an Adodc.

I want to be able search the database within the VB form and then click on the one I want to open the document.

Is this possible and if so what coding is required and where?

[patjones]

What fields does the table contain, and what are you displaying on the form? I'm just trying to get a sense of whether you have full paths for the files stored in the table, and a corresponding column for just the file's name, which is perhaps what you're searching.

[Gazerdood]

Hello, thanks for the reply.

Yes that is what I am wanting my fields will be something like 'file name', 'type' and 'path' maybe unless you can determine anything better.

Heres what I want to be able to do:

1. Search query in textbox on VB6 form and click search button.
2. This will search in the 'type' field of the table.
3. Display the found files in a datagrid or whatever other object reccomended.
4. Click on the file or an area on the file to open up word and the file in it.

I don't know the best way to achieve this or what code will be needed atall. Thanks!

[patjones]

The following code is based on a table that has columns file_name, file_type and file_path. The table stores just the name of the file in file_name, while it stores the file name with the full path in file_path. The form has a text box txtTypeToGet, command button cmdSearch and list box lstFileResults:

Private Sub cmdSearch_Click()

Dim strFileType As String

If Me.txtTypeToGet <> "" And Not IsNull(Me.txtTypeToGet) Then

    strFileType = Replace(Me.txtTypeToGet, "'", "''")
    Me.lstFileResults.RowSource = "SELECT tblFiles.file_path, tblFiles.file_name FROM tblFiles WHERE tblFiles.file_type = '" & strFileType & "'"

Else

    MsgBox "Please enter a file type to query on...", vbExclamation + vbOKOnly, "File Type Search"
    Exit Sub
    
End If

End Sub

Private Sub lstFileResults_DblClick(Cancel As Integer)

Dim strFilePath As String
Dim oWordDoc As Object

    'Path to the word document
    strFilePath = Nz(Me.lstFileResults.Value, "")

    If Dir(strFilePath) = "" Then
        
        MsgBox "Document not found.", vbInformation + vbOKOnly, "File Type Search"

    Else
        
        'Create an instance of MS Word
        Set oWordDoc = CreateObject(Class:="Word.Application")
        oWordDoc.Visible = True

        'Open the Document
        oWordDoc.Documents.Open FileName:=strFilePath

    End If
    
End Sub

Double-clicking on the file name will open the file. Note that if you try to search for a non-existent file type, nothing will happen...you might want to bring up a message in that case. I found that if you double-click the list box without anything in it, then Word opens up, but without any document showing.

In order to make this work, you may need to set the proper reference in Access. In the VBA window go Tools > References, and make sure Microsoft Office 12.0 Object Library is checked off. The code for actually establishing an instance of MS Word and opening the file is something I found here...it has information on how to open other applications as well.

Let me know if you have any questions.

Pat

[patjones]

As a kind of afterthought, you might want to change the text box to a combo box with standardized entries for the file type. In this manner you can avoid having to deal with the situation of the user typing in a meaningless file type to search on.

Pat

[Gazerdood]

Hi Pat thanks for the reply thats fantastic. I am still rather baffled though so I hope you can answer my questions.

What I have done: Created table with the defined fields, added the text box, command button and list box to the form with the appropriate names and dropped the code on that form.

1. Okay I have created a table in Access with the fields you said but I dont see how in the code that this is linked to from vb? Dont't I need an ADO object or anything?

2. What should I name the table?

3. How should I add the MS Word file to the database table? File name under that field, File type under that field and the path e.g. 'C:\.....'?

Thanks!

[patjones]

I did this with the table and form together in the same file, so there was no need to use ADO. Even if your table is in some back end file, you can link to it via the Linked Table Manager (under Database Tools) and still avoid using ADO.

For the table name, I called it "tblFiles", but you can call it anything you want, as long as you correspondingly modify it in the embedded SQL statement for the list box row source:

Me.lstFileResults.RowSource = "SELECT tblFiles.file_path, tblFiles.file_name FROM tblFiles WHERE tblFiles.file_type = '" & strFileType & "'"

In the table, file_path has to have the FULL path to the file, as in C:\Documents and Settings\Deskto p\SomeDocument. docx. Notice that this includes the file's name. The column file_name would contain just the name of the file (i.e. "SomeDocume nt") for display purposes in the list box.

I should also point out that in the properties for the list box, you'll want to have Column Count set to 2, and Column Widths set to 0", 2" (or whatever you want the width of the second column to be), and Bound Column = 1. Notice in the SELECT statement for the list box row source that the file_path column is picked first. This means that the file's path gets put in the list box, but it goes in the zero-width column...so the user doesn't see it. But, because the bound column is 1, whatever the user picks from the list box will cause the corresponding value from the zero-width column to become the list box's value - in other words, the path to the file to be opened.

Remember that the code is connected to the On Click event for the command button, and Double Click event for the list box. If there's a mismatch between what you named the controls and what the subroutine names are, it won't work (for instance, when you click "cmdSearch" , the sub cmdSearch_Click () runs).

[Gazerdood]

I still cannot get an understanding i'm afraid so what I have done is include a screenshot of my form and a screenshot of my access table.

I would like to know how to link it to the access table so the code you provided can work? (I have to use an access table)

Thanks!

[patjones]

If you open the form in Design mode, click on the command button, and bring up it's property sheet, you'll see a bunch of tabs...one of them is "Event". Under the Event tab there is an On Click option...click the "..." next to it. This will open up the VBA code window, which is where the code for the command button goes.

You would do the same thing for the list box, except I used the On Dbl Click event. There are two subroutines in the code I wrote for you yesterday. The first sub is the code for the command button; the second sub is the code for the list box.

This is generally how building user interfaces in Access works. It is event-driven. The controls all have various events that could possibly occur in relation to them, and you make the thing do what you want by putting the appropriate code in whatever event for whatever control you need to perform some function.

After you have put the code in, hit Debug > Compile, save the code, and go back to the Access window and try it.

Pat