Retrieve filename from a PDF OLE object

[Steve123]

The easiest way to create multiple attachments to a single Record is with a Child Table. A 1 to MANY Relationship will exist between the Parent and Child Tables via an ID Field (tblMain.[ID](1) ==> tblChild.ID([MANY]). The Attachments could then be viewed within a Sub-Form contained within the Main Form.

I created the relationships and the sub-form, and the application works as expected. However, I am having a little trouble with obtaining the replication ID for the record my main form is retrieving data from.

My form, Form1, contains a text box called Report_ID where I display the ID number from my table, called Table1, for the current record. The text box displays the report ID number as expected. However, in my VB code, when I try to use Me.Report_ID(1) , I receive a number that does not seem to relate to the actual report ID in Table 1.

Is this the proper code to retrieve a replication ID from a form/table?

Thank you,

Steve

[ADezii]

I created the relationships and the sub-form, and the application works as expected. However, I am having a little trouble with obtaining the replication ID for the record my main form is retrieving data from.

My form, Form1, contains a text box called Report_ID where I display the ID number from my table, called Table1, for the current record. The text box displays the report ID number as expected. However, in my VB code, when I try to use Me.Report_ID(1) , I receive a number that does not seem to relate to the actual report ID in Table 1.

Is this the proper code to retrieve a replication ID from a form/table?

Thank you,

Steve

when I try to use Me.Report_ID(1) , I receive a number that does not seem to relate to the actual report ID in Table 1.

The (1) simply refers to the 1 side of a 1 to MANY relationship and should not be used in the actual coding. The reference should be:
[CODE=vb]Me.Report_ID[/CODE]

[Lebbsy]

It could be 1 of 2 possibilities, the 1st of which would be a typographical error on my part and I apologize for this mistake. I am referring to Message #11, List Item #8:

[CODE=vb]If IsNull(Me![txtFilePath]) Then
Me!cmdViewPDF.E nables = False
Else
Me!cmdViewPDF.E nables = True
End If[/CODE]-------------------- SHOULD BE ------------------

[CODE=vb]If IsNull(Me![txtFilePath]) Then
'corrected line
Me!cmdViewPDF.E nabled = False
Else
'corrected line
Me!cmdViewPDF.E nabled = True
End If[/CODE]

Let me know how you make out.

I had already corrected that. Thank you for your help I have already found a solution to that. I hadnt divided the code between public module and form module. The code now works fine.

[ADezii]

I had already corrected that. Thank you for your help I have already found a solution to that. I hadnt divided the code between public module and form module. The code now works fine.

Glad it worked for you.

[Steve123]

The (1) simply refers to the 1 side of a 1 to MANY relationship and should not be used in the actual coding. The reference should be:
[CODE=vb]Me.Report_ID[/CODE]

ADezii,

That is what I thought, and tried originally, but when I use a message box to display Me.Report_ID, the string contains question marks. My VBA code is located in the Form1 class object, so I think the "Me" is referencing the form, which is then attached to the table where the replication IDs are located, so it should be able to retrieve the replication IDs. Do you know why this would not work?

Also, when I tried to use a direct reference to the replication ID that is located in Table1, as you suggested with tblMain.[ID] in post 13, I received an Object Required error. Why am I unable to access Table1 that Form1 is linked to directly from the Form1 class object?

Thank you again,

Steve

[Steve123]

ADezii,

I found that I need to use the function StringFromGUID( ) in order to properly retrieve the Report_ID number, since Access cannot display the GUID 16-byte array. I am now able to request files from the user, place copies of the files into a new folder that uses the Report_ID for the folder name, add the file names to an attachments table, and display the file names in a subform.

The only other thing I need help with is creating a link to the files that are in the new folder. I made the data type a "hyperlink" in my file name column, but I cannot follow the link when I click on it. I right clicked on the file name text and looked at the hyperlink, but there is no path or file name in the hyperlink text box.

Any suggestions?

Thank you,

Steve

[ADezii]

ADezii,

I found that I need to use the function StringFromGUID( ) in order to properly retrieve the Report_ID number, since Access cannot display the GUID 16-byte array. I am now able to request files from the user, place copies of the files into a new folder that uses the Report_ID for the folder name, add the file names to an attachments table, and display the file names in a subform.

The only other thing I need help with is creating a link to the files that are in the new folder. I made the data type a "hyperlink" in my file name column, but I cannot follow the link when I click on it. I right clicked on the file name text and looked at the hyperlink, but there is no path or file name in the hyperlink text box.

Any suggestions?

Thank you,

Steve

The only other thing I need help with is creating a link to the files that are in the new folder

I'm sorry Steve, but I thought that we were storing the Absolute Path to each File, and then opening them at will within Adobe via the ShellExecute() Function? Please clarify.

[Steve123]

I'm sorry Steve, but I thought that we were storing the Absolute Path to each File, and then opening them at will within Adobe via the ShellExecute() Function? Please clarify.

ADezii,

I am sorry for the confusion. I never clearly explained what I am actually doing. Essentially, I am making a user form where the user is allowed to "attach" files to a database record. However, I did not want my database to become too large, so I decided to save all of the attachments in a directory and then just make absolute references to the files, which is where your ShellExecute() function recommendation fit perfectly. Please see the following for a more detailed description.

I have a form with a button called Attachments. When the user clicks this button, they are presented with an msoFileDialogFi lePicker to allow them to select files to attach to the record. The code then copies the file(s) they selected into a new directory. The code also places the new path name and name of each file into an attachments table. The main form then has a subform that displays the contents of the attachments table based upon the replication ID. I was unsure of the proper way to use the ShellExecute() function with multiple files in the subform, so I tried using a hyperlink instead.

Do you know of an easy way to access the files from the subform, using either hyperlinks or the ShellExecute() function?

Thank you,

Steve

[Steve123]

ADezii,

I was able to resolve my issue by using your ShellExecute() script in the OnClick event of my subform. I placed the following code in my subform class, where File_Name and File_Path are the names of columns in the table my subform is attached to:

Private Sub File_Name_Click()
    Success = Execute_Program(Me.File_Name, "", Me.File_Path)
End Sub

Thank you again for all of your help,

Steve

[EJaques]

ADezii,

I am sorry for the confusion. I never clearly explained what I am actually doing. Essentially, I am making a user form where the user is allowed to "attach" files to a database record. However, I did not want my database to become too large, so I decided to save all of the attachments in a directory and then just make absolute references to the files, which is where your ShellExecute() function recommendation fit perfectly. Please see the following for a more detailed description.

I have a form with a button called Attachments. When the user clicks this button, they are presented with an msoFileDialogFi lePicker to allow them to select files to attach to the record. The code then copies the file(s) they selected into a new directory. The code also places the new path name and name of each file into an attachments table. The main form then has a subform that displays the contents of the attachments table based upon the replication ID. I was unsure of the proper way to use the ShellExecute() function with multiple files in the subform, so I tried using a hyperlink instead.

Do you know of an easy way to access the files from the subform, using either hyperlinks or the ShellExecute() function?

Thank you,

Steve

Can you tell me how you copy the file into a new directory?

[ADezii]

Can you tell me how you copy the file into a new directory?

The simplest way is via the FileCopy Statement, as in:

Dim strSource As String
Dim strDestination As String

strSource = "C:\Test\SomeFile.XXX"
strDestination = "C:\Windows\System32\SomeFile.XXX"

FileCopy strSource, strDestination