Not sure what to do?

[Patti D]

I have an Access database containing general information about students.
Each of those students has a seperate excel spreadsheet containing their
"plan" for schooling. I need to somehow setup a link or something where I
while I am in the Access database, click a button and open that particular
students plan. I'm not sure what the best process is, or where to start.
Any suggestions?

 

[Jae]

Dear Patti,

Create a field that contains the path to that excel spreadsheet. Then create
a button and put

dim strPath as string

strPath = me!Name_Of_Field_That_Contains_File_path

followhyperlink strPath

 

[Lance]

The best process would be to have all that data reside inside Access, and
clicking the button would bring up a display within access.. but, the
hyperlink option Jae mentioned will work nicely for you.

 

[Patti D]

Thank you to both of you. I was thinking it would be best to have all of the
information in Access also. Problem is that the plan has associated costs on
it which contain formulas - and they are each taylored to the individual
students, not identical. I'm not sure that I understand how to do the link.
I'm not really up on all the programming aspects.

Was thinking I could use the tab feature (have a tab with the main
information and a tab that has the plan). The user could select a program
from a drop down list on the main tab, which would then pull up the correct
plan on the second tab. Just not sure how to do that second part - pulling
up the correct plan from a drop down list on another tab. Any idea on that?

 

[Lance]

It would be possible to allow custom plans for each student within access,
but that might be a bit more than you want to bite off right now.

Add a field to your student table to store the filename + path of the excel
file containing their info. Then in a form ( built over the same table ) put
a button with the following code:

Call FollowHyperlink(Me.Recordset.Fields("EXCEL_FILE_PATH").Value)

Where EXCEL_FILE_PATH is the name of the field containing the location of
the excel file.

OR, a bit more complicated approach..

You could imbed an OLE object in your form and use something like the
following code to load your spreadsheet:

Private Sub Command1_Click()
With Me![OLEUnbound0]
.Enabled = True
.Locked = False
' Specify what kind of object can appear in the field.
.OLETypeAllowed = acOLELinked
' Class statement--optional for Excel worksheet.
.Class = "Excel.Sheet"
' Specify the file to be linked.
' Type the correct path name.
.SourceDoc = Me.Recordset.Fields("f3").Value
' Range statement--optional for Excel worksheet.
'.SourceItem = "R1C1:R7C4"
' Create the linked object.
.Action = acOLECreateLink
' Optional size adjustment.
'.SizeMode = acOLESizeZoom
End With
End Sub

You could run that code on a form load, afterupdate, wherever.. depends on
what your form is doing. It will set the OLE object to hold and display that
specific excel sheet.

 

[Patti D]

I will try this and see if that works. Thank you so much for your input.

It would be possible to allow custom plans for each student within access,
but that might be a bit more than you want to bite off right now.

Add a field to your student table to store the filename + path of the excel
file containing their info. Then in a form ( built over the same table ) put
a button with the following code:

Call FollowHyperlink(Me.Recordset.Fields("EXCEL_FILE_PATH").Value)

Where EXCEL_FILE_PATH is the name of the field containing the location of
the excel file.

OR, a bit more complicated approach..

You could imbed an OLE object in your form and use something like the
following code to load your spreadsheet:

Private Sub Command1_Click()
With Me![OLEUnbound0]
.Enabled = True
.Locked = False
' Specify what kind of object can appear in the field.
.OLETypeAllowed = acOLELinked
' Class statement--optional for Excel worksheet.
.Class = "Excel.Sheet"
' Specify the file to be linked.
' Type the correct path name.
.SourceDoc = Me.Recordset.Fields("f3").Value
' Range statement--optional for Excel worksheet.
'.SourceItem = "R1C1:R7C4"
' Create the linked object.
.Action = acOLECreateLink
' Optional size adjustment.
'.SizeMode = acOLESizeZoom
End With
End Sub

You could run that code on a form load, afterupdate, wherever.. depends on
what your form is doing. It will set the OLE object to hold and display that
specific excel sheet.

Thank you to both of you. I was thinking it would be best to have all of the
information in Access also. Problem is that the plan has associated costs on
it which contain formulas - and they are each taylored to the individual
students, not identical. I'm not sure that I understand how to do the link.
I'm not really up on all the programming aspects.

Was thinking I could use the tab feature (have a tab with the main
information and a tab that has the plan). The user could select a program
from a drop down list on the main tab, which would then pull up the correct
plan on the second tab. Just not sure how to do that second part - pulling
up the correct plan from a drop down list on another tab. Any idea on that?