Storing BLOBs

[ADezii]

I'm a little confused on a couple of points:

1. Where is the Control [.BlobInventory_ ID]?
   

   Forms![frm_Navigation]![NavigationSubform].Form![DocumentViewer].Object.Navigate putBLOBInFile(.BlobInventory_ID)

2. putBlobOnFile() is a Sub-Routine and doesn't return a String, it accepts a String Argument. You can't assign a Return Value from it to the Picture Property of the Image Control.
   

   .ImageDisplay.Picture = putBLOBInFile(.BlobInventory_ID)

   Public Sub PutFileIntoBLOB(strFileName As String)

[ordnance1]

BlobCategory_ID represents which type of file is to be imported. 1 = Image and 2 = PDF. Since the bulk import does not allow you to pick the file type I created to folders BlobUpload\Imag e and BlobUpload\PDF. Then code looks at BlobCatefory_ID to determine which folder to load.

In the code below if BlobCategory_ID = 1 then then it loads the image into the Image Control. If it is not 1 it loads the PDF into the Webbrowser Control.

I have come up with a work around for the problem I mentioned. I created a form set its width to .0069 Making it basically invisible set its Timer Interval to 1 and added On Timer event to close the form. I added code to the On Load event of my form in question to open the blank for as acDialog. This seems to work.

[NeoPa]

Hi Ordnance.

I'm guessing, from your reply, that you didn't quite get the significance of ADezii's questions. Certainly your responses don't answer the questions, nor indicate that you perceive what ADezii is trying to draw your attention to.

1. What you plan to use [BlobCategory_ID] for isn't the question. Where is it located on a Form? I suspect it hasn't been created which means your reference to it will not work.
2. This was about drawing your attention to the difference between Function procedures and Subroutine procedures. Procedures are sets of code that you can call. A Subroutine will run code. A Function will also run code, but on top of that will return a value. Notice the difference in the following example code :
   

   Public Sub MySub()
   ...
   End Sub

   Public Function MyFunction() As String
   ...
   MyFunction = "Return value"
   ...
   End Function

Your code was trying to use the value returned from a subroutine. This cannot work as subroutines, by definition, return no values.

As for your work-around. You may prefer working with a more normal-sized Form but hiding it so it doesn't interfere with the display. It makes it easier to work with.

As I say, I'm happy to let you two run with this without interfering, but I thought jumping in here quickly may prove helpful. Good luck with your project :-)

[ordnance1]

My apologies BlobCategory_ID produced by a combobox on the a form

[NeoPa]

I'm not sure apologies are necessary here. I wasn't taking you to task. Simply trying to guide and assist. Point you through the forest, as it were. It's easy to get lost ;-)

[ADezii]

I think that you are losing focus a little. You appear to be attempting to write a non-graphic File (*.pdf in this case) to a BLOB Field stored as a Binary Stream of Data. The exact opposite needs to occur in the Current() Event of your Form, namely the Binary Data needs to be extracted from the BLOB Field and placed into a Temporary File. Next, this File is loaded into a Web Browser Control. I didn't have much time, but I modified the Code in the Current() Event of my DB and the outcome was promising, namely the *.pdfs were displayed in the Web Browser. There were a couple of minor difficulties, but the overall approach was successful.

Private Sub Form_Current()
With Me
  If .NewRecord Then
    ![ImageX].Picture = ""
    ![txtProperties] = ""
  End If
 
  If [BlobCategory_ID] <> 1 Then
    Dim strSQL As String
    Dim rstBLOB As ADODB.Recordset
    Dim mstream As ADODB.Stream
    Dim strFullPath As String
    Dim strFolder As String
    Dim fso As FileSystemObject

    strFolder = "C:\TEMP\"
    If Dir$(strFolder, vbDirectory) = "" Then MkDir (strFolder)

    Set fso = New Scripting.FileSystemObject
  
    strSQL = "SELECT * FROM tblDemo WHERE [ID] = " & Me![txt_ID]

    Set rstBLOB = New ADODB.Recordset
    rstBLOB.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    Set mstream = New ADODB.Stream
    With mstream
      .Type = adTypeBinary
      .Open
      .Write rstBLOB.Fields("BLOB").Value
    End With
    
    strFullPath = rstBLOB![sFileName]
    mstream.SaveToFile "C:\TEMP\" & strFullPath, adSaveCreateOverWrite
    
    Forms![frmTest].DocumentDisplay.Visible = True
    Forms![frmTest].DocumentDisplay.Navigate "C:\TEMP\" & strFullPath

    rstBLOB.Close
    Set rstBLOB = Nothing
  End If
End With
End Sub

[ADezii]

but I thought jumping in here quickly may prove helpful.

Always welcome and always helpful!

IMHO, Extracting Binary Data from an OLE Object Field, saving it to a File, then loading that File (*.pdf, *.doc, *.docs) into a Web Browser is very problematic to say the least. I arrived at another solution which you may/may not like. The attached Demo contains three Records, a *.bmp, *.pdf, and *.doc. As you navigate thru them Code in the Current() Event of the Form will load preset Graphic Images into the Image Control. As far as the Word Documents and PDFs, an Open File Command Button will appear. Clicking on the Command Button will then Open the File via the FollowHyperlink Method of the Application. I strongly feel that going the Web Browser route will lead to nothing but problems. Give the Demo a shot and see what you think.

[ordnance1]

Thank you I will take a look.

[ordnance1]

Mission complete. Thanks for all the help. This never would have happened without you.

[ADezii]

You are quite welcome, good luck with your Project!

@NeoPa:
Thanks for allowing us to continue and come to a successful conclusion.

[NeoPa]

Always a pleasure to observe you in action my friend :-)

There are a lot less valuable things you could be doing with your spare time.