combo box

J

Jessica

Hello

I have a table with records of word documents and excel spreadsheets.

tblDocuments
strDocumentID
strDocumentDescription
strDocumentPathName
strDocumentFileName

I have a combo box on a form with a command button but don't know what the
event procedure should be to open what is selected in the combo box.

TIA,
Jess
 
B

Brendan Reynolds

Assuming that the bound column of the combo box contains the complete path
and name, including extension, of the file ...

Private Sub Command2_Click()

Application.FollowHyperlink ("file://" & Me.Combo0)

End Sub
 
J

Jessica

Brendan

Depending on which document is selected in the combo box from the
strDocumentID field will this procedure open the path and file name listed
in its record or do I have to write the address for each document?

TIA,
Jess
 
B

Brendan Reynolds

I don't know what is stored in your fields, Jessica. We need the complete
path and name, including extension, of the file to be opened. If that data
is stored in the bound column of the combo box, we can get to it simply
using the name of the combo box. If the data is in a column of the combo box
other than the bound column, you can get at it using the Column property of
the combo box. The Column property is zero-based - the first column is
Column(0) - so, for example, if the path and name were in the second column
....

Application.FollowHyperlink ("file://" & Me.Combo0.Column(1))

If the path and name are stored separately, we can concatenate the values of
the separate columns - for example, if the path is in the second column, and
the name, including extension, is in the third column ...

Application.FollowHyperlink ("file://" & Me.Combo0.Column(1) & "\" &
Me.Combo0.Column(2))
 
J

Jessica

Hi Brendan,

I'm sorry I'm not getting it. In my table I have in field 1 a document
number which is my primary key, field 2 document title, field 3 document
path (located on a server), field 4 file name with extension. On my form I
have a combo box that shows the document numbers and a command button which
I haven't named yet but now is command.

Thanks,
Jessica
 
B

Brendan Reynolds

OK, what we need in the combo box is field 3 (strDocumentPathName) and field
4 (strDocumentFileName). If your combo box is being used only for this
purpose, then the simplest solution would be to have the combo box return a
single column that concatenates these two fields. This means that the row
source property of the combo box would be a query or SQL statement that
looked like so ...

SELECT strDocumentPathName & "\" & strDocumentFileName FROM tblDocuments

This assumes that strDocumentPathName does not include the trailing "\"
character that separates the path from the file name - if it does, just
leave the "\" out of the expression above.

If, for some reason, you need to continue using strDocumentID as the bound
column of the combo box, then you can add the concatenation above as a
second column. In that case, the row source would look like so ...

SELECT strDocumentID, strDocumentPathName & "\" & strDocumentFileName FROM
tblDocuments

.... the VBA code behind the command button would refer to the expression via
the Column() property of the combo box, as in my previous example.
 
J

Jessica

YES Finally. Thank you so much for your patience Brendan you've been a big
help. Just out of curiosity say an excel file had two worksheets based on
what you have showed me could I get it to open to a specific worksheet.
Thank you again Brendan
Jess :eek:)
 
B

Brendan Reynolds

The FollowHyperlink method takes an optional second argument, SubAddress,
which can be used to point to a named location in a document. For example
....

1) Add a column named SubAddress to the table.
2) Enter the text Sheet2!A1 in that field.
3) Modify the row source of the combo box to include that field in a second
column like so ...

SELECT tblTest.PathName, tblTest.SubAddress
FROM tblTest;

4) Make sure the Column Count property of the combo box is set to the number
of columns returned by the row source - 2 in this example.

5) Modify the code behind the command button to assign the value from the
second column of the combo box to the SubAddress argument of the
FollowHyperlink method, using the NZ() function to handle the possibility
that no named location may have been entered for some documents ...

Application.FollowHyperlink "file://" & Me.Combo0,
NZ(Me.Combo0.Column(1), "")
 
Top