source row

[mlopes1]

I have table called Companies with accompanying company id #.

1 Company C
2 Company X
etc.

The value that I store throughout the db is the number. I now am trying to write code that takes selected company and opens its related spreadsheet. The path name always begins:

C:/Files/Spreadsheets/

then the company name, exactly as stored in the table. so if the company ID was 2 I would want to open

C:/Files/Spreadsheets/Company X/file1.xls

How do I retrieve the company name, given the ID. I am writing this in VB using the Shell method and seem to be doing it correctly. I just need to extract the company name. Any sample code is much appreciated. Thank you as always,

Marco

 

[pono1]

Marco,

Assuming the file name never changes, experiment with two tables and a form.

1. TblCompany with fields CompanyID, Name
2. TblOrders with fields OrderID, Date, CompanyID

Create a form based on TblOrders, using a combo box (named cboCompany in this sample) for the CompanyId field. Make sure the combo box pulls id and name from TblCompany, has a column count of 2, column widths of 0";1"

On the same form, create a command button called, say, CmdOpenE, and put the following code in the button's On Click event and experiment...

Private Sub CmdOpenE_Click()

Dim TheFullPath As String
Dim TheCompany As String

'this part of the path will never change
'may have to change for your PC...
Const FirstPart = "C:\Program Files\Microsoft Office\Office\Excel.Exe " & _
"C:\Files\Spreadsheets\"

'this part of the path will never change
'assuming file name never changes
Const LastPart = "\File1.xls"

'combo box pulls two values from Company table:
'the id and the name
TheCompany = Me.CboCompany.Column(1) 'this gets the name

'tie the strings together
TheFullPath = FirstPart & TheCompany & LastPart

'test
Debug.Print TheFullPath
'test

'let it fly
Shell TheFullPath, vbNormalFocus

End Sub

A little rough but I'm sure you'll be able to smooth out the edges...

Regards,
Tim