vba insert image in ms word form, from a access db table

[SnakeEyes909]

ok, so i've spent the last 2 days looking through the treads here, and have found alot of interesting stuff, but i have not found an answer to my problem. I have found things that are close, but will not work for me. if some one could point me in the right direction....

my problem is i have a Access db that has 2 tables "tblCustomers" and "tblDocs" and a form "frm_customers". i also have 2 ms word forms.
one is used by a ccc to input data to the customers table. this all works great. the frm_customers is basicly a list of customer names, and you click on the customer name it opens up the 2nd document and poupulates the fields from the db. In the 2nd word form there are 5 or 6 fields that are modified with data. i then have a sub that will inser/update the record with this "new" data. everything is working great here also. Currently i have a line at the bottom of the page for a image signature, that i am pulling from a folder. and this is also working. what i need to do is change this part to pull the image out of the "tblDocs" table and insert it into the bkmName(test) bookmark.

here is the code. basicly when a signature button is pressed it runs this code.
Sub Signature()

Dim bkmName As String
Dim SigFile As String
Dim cnn As ADODB.Connection
Dim strConnection As String
Dim strSQL As String
Dim strPath As String
Dim SIG_DATE As String
Dim CUST_NUMBER As String
Dim RX_DATE As String
Dim PATIENT_NAME As String
Dim CPAP_CMH2O As String
Dim BIPAP_CMH2O_1 As String
Dim BIPAP_CMH2O_2 As String
Dim O2_LPM As String
Dim O2_HPD As String
Dim O2_VIA As String
Dim bytContinue As Byte
Dim lngSuccess As Long

Set doc = ThisDocument
bkmName = "test"
SigFile = "c:\sigs\signature.TIF"
doc.InlineShapes.AddPicture FileName:=SigFile, LinkToFile:=False, SaveWithDocument:=True, Range:=doc.Bookmarks(bkmName).Range

On Error GoTo ErrHandler

SIG_DATE = Chr(39) & doc.FormFields("SIG_DATE").Result & Chr(39)
CUST_NUMBER = Chr(39) & doc.FormFields("CUST_NUMBER").Result & Chr(39)
RX_DATE = Chr(39) & doc.FormFields("RX_DATE").Result & Chr(39)
PATIENT_NAME = Chr(39) & doc.FormFields("PATIENT_NAME").Result & Chr(39)
CPAP_CMH2O = Chr(39) & doc.FormFields("CPAP_CMH2O").Result & Chr(39)
BIPAP_CMH2O_1 = Chr(39) & doc.FormFields("BIPAP_CMH2O_1").Result & Chr(39)
BIPAP_CMH2O_2 = Chr(39) & doc.FormFields("BIPAP_CMH2O_2").Result & Chr(39)
O2_LPM = Chr(39) & doc.FormFields("O2_LPM").Result & Chr(39)
O2_HPD = Chr(39) & doc.FormFields("O2_HPD").Result & Chr(39)
O2_VIA = Chr(39) & doc.FormFields("O2_VIA").Result & Chr(39)

strSQL = "UPDATE tblCUSTOMER SET SIG_DATE = " & SIG_DATE & ", CPAP_CMH2O = " _
& CPAP_CMH2O & ", BIPAP_CMH2O_1 = " & BIPAP_CMH2O_1 & ", BIPAP_CMH2O_2 = " _
& BIPAP_CMH2O_2 & ", O2_LPM = " & O2_LPM & ", O2_HPD = " & O2_HPD & ", O2_VIA = " _
& O2_VIA & "WHERE CUST_NUMBER = " & CUST_NUMBER & " AND RX_DATE = " & RX_DATE

Debug.Print strSQL

'Substitute path and connection string with DSN if available.

strPath = "C:\db1.mdb"

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source = " & strPath
Debug.Print strConnection
Set cnn = New ADODB.Connection
cnn.Open strConnection
cnn.Execute strSQL, lngSuccess
cnn.Close

Set docs = Nothing
Set cnn = Nothing

ActiveDocument.HasRoutingSlip = True
With ActiveDocument.RoutingSlip
.Subject = "Signed Script for" & PATIENT_NAME & "Cust# " & CUST_NUMBER
.AddRecipient "rdavis@godasco.com"

.Delivery = wdAllAtOnce
End With
ActiveDocument.Route

ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

Exit Sub

ErrHandler:
MsgBox Err.Number & ": " & Err.Description, _
vbOKOnly, "Error"
On Error GoTo 0
On Error Resume Next
cnn.Close
Set docs = Nothing
Set cnn = Nothing
End Sub

 

[kzutter]

Years ago I gave up on trying to keep images in an Access DB.
My solution was to keep all of the images in a folder in the same location as the backend DB and store the filenames of the images in the DB. I think this this could be a viable solution for you as it sounds like you already have things working with file-based images.

Good luck...