open file from path stored in access db when user clicks button on form

Question

I'm new to Access VBA development and being asked to debug and add features to an Access 2007 application that two previous developers worked on.

A form displays records from a database and shows a button for each record. The button is supposed to open a file using the appropriate path. But when the user clicks the button, it always uses the filepath from the first record that the form displays, instead of the filepath from the correct record.

The code looks like it is trying to use a bookmark to open the correct file, but as stated above, that isn't working. Here is the relevant code from the button click event. When I try to Debug.Print form.Bookmark to the immediate window, it just displays a question mark.

Dim rs As Recordset
Set rs = form.RecordsetClone
rs.Bookmark = form.Bookmark

Edit: adding more code per @Remou's request. When button is clicked:

Private Sub OpenFile_Click()
    Form_FilingProcess.Subform_cmdOpenFile_Click Me
End Sub

Which calls:

Public Sub Subform_cmdOpenFile_Click(frm As Form)
Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark

And then it goes on to open the file.

Answer 1

If the button is for each record, there is no need for any messing around with the recordset. You can use the name of the control to get the file:

TheFile=Me.MyControl

It seems that you have both a form and subform. I am guessing from your answers that the set-up is something like this:

   |------------------------------|
   |  Main Form                   |
   --------------------------------
    Sub form
   --------------------------------
    Row                     Button
   --------------------------------
    Row                     Button
   --------------------------------

If the name of the button is OpenFile, try:

Private Sub OpenFile_Click()
    MsgBox Me.NameOfAContolHere & ""
    'Form_FilingProcess.Subform_cmdOpenFile_Click Me
End Sub

This can then be used to ope a file like so:

Private Sub OpenFile_Click()
    FollowHyperlink Me.NameOfControlWithPathToFile
    'Form_FilingProcess.Subform_cmdOpenFile_Click Me
End Sub

Answer 2

Here is what your sub should look like:

Public Sub Subform_cmdOpenFile_Click(frm As Form) 
  Dim CurrentBookmark as String
  Set rs = frm.RecordsetClone 
  CurrentBookmark = frm.Bookmark
  rs.Bookmark = CurrentBookmark
End Sub

Set a string variable to the value of the form's bookmark. Then set the recordset's bookmark to the string variable value.

Dim rs As Recordset 
dim CurrentBookmark as String

Set rs = Me.RecordsetClone 

CurrentBookmark = Me.Bookmark
rs.Bookmark = CurrentBookmark 

http://msdn.microsoft.com/en-us/library/aa223967(office.11).aspx