Pictures in subform

[Ambyr via AccessMonster.com]

Oh I hope someone can dumb this down for me and help me out.
I have one table, Items which stores all information about inventory items
(title, description, cost, picture, etc). The info from this table needs to
be pulled into a form called Trades. However, I need this subform of Items to
allow for a selection, a dropdown combobox.
For lack of a better way, I created another table as a lookup to be the
subform.

either way it goes, my biggest issue is the picture. Using that secondary
table (TradeLookUp) as a subform, I can pull all the info I need except the
picture.
I used a query to perform this, but the query is slow, can't update until
after a new row is selected (or until that record is saved), and doesn't
perform the query on just that Trade.

What I would like to happen is that when i use the combo box to pull the
correct Item, the other fields fill in (as they do now), and the picture
fills in as well. The picture doesn't have to be stored, per say, but it has
to be viewable as the users scroll through the form records.

Thoughts? Suggestions?
Thanks.

 

[stevehaley]

I have image functionality in some of my apps and I learnt very early
on that you never rely on the ole embedding functionaly of the
database be it sql or jet. The best method is slightly more
complicated as you store the image in binary format (blob). This
speeds up the retreival & storage immensly as well as reducing the
storage requirements significantly. Secondly you ALWAYS store blobs/
pictures in a seperate table to the data especially in jet databases.
This reduces problems and speeds the DB. Even if you dont want to go
the BLOB route this will speed the access.

When I ned to display the picture I simply retrieve it from the
database into a tmp file then pass the tmp file to an image control.
You can be clever than this and do it all in memory but personally I
found little speed increase and a few more problems.
This web site has the routines I used originally
http://www.ammara.com/access_image_faq/read_write_blob.html
This isnt the simplest way but it certainly is the fastest and most
controlled - you can also put in a check to ensure that the pictures
are kept small. There are plent of utils out there to reduce picture
sizes.

I put some function wrappers arround these BLOB routines to handle the
loading and saving to my table (see below).
When the form loads I load the combo box with 2 cols of data and use
the hidden col to store the Primary index field.
In the AfterUpdate event of the combo you fire the retrival code ie

If GetImgFromTbl(Me.DistribID, SFName) Then
On Error Resume Next
Me.imgAccount.Picture = SFName
If err.Number <> 0 Then
err.Clear
On Error GoTo 0
End If
End If

The form has an additional 3 buttons associated with the image control
Delete, Load New Picture & Save (Export).
Delete has the code
DelImgFromTbl Me.DistribID
Me.imgAccount.Picture = ""

Load New (this uses a common control frontend to retrieve the file
name but hopefully you get the idea)

strFilter = adhAddFilterItem(strFilter, "Image
files(*.*)", "*.*")
strRet = adhCommonFileOpenSave( _
OpenFile:=True, _
Filter:=strFilter, _
flags:=cdlOFNFileMustExist +
cdlOFNPathMustExist, _
InitDir:=CurrentProject.Path, _
DialogTitle:="Image file to load:")

If Nz(Trim(strRet)) <> "" Then
On Error Resume Next
Me.imgAccount.Picture = strRet
If err.Number = 0 Then
Me.chkNewImg = True
SaveImgToTbl Me.DistribID, strRet
Else
err.Clear
End If
On Error GoTo 0
End If

Pardon the on error resume next - this is just for ease in this case -
you should put in your own error routines to trap the common errors.


================================================
Public Function GetImgFromTbl(lDistribID As Long, SFName As String) As
Boolean

Dim rst As ADODB.Recordset
Dim sSQL As String
Dim varItem As Variant
Dim sFileExt As String

Set rst = New ADODB.Recordset

sSQL = "select * from tblDistLogos where distribid=" &
CStr(lDistribID)

rst.Open _
Source:=sSQL, _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenStatic, _
LockType:=adLockOptimistic, _
options:=adCmdTableDirect
With rst
If Not .EOF And Not .BOF Then
.MoveFirst
sFileExt = .Fields("FileExt")
SFName = SFName & "." & sFileExt
If BlobToFile(SFName, .Fields("distlogo")) > 0 Then
GetImgFromTbl = True
End If
End With
rst.Close
Set rst = Nothing
End Function


Public Function SaveImgToTbl(lDistribID As Long, SFName As String) As
Boolean

Dim rst As ADODB.Recordset
Dim sSQL As String
Dim varItem As Variant
Set rst = New ADODB.Recordset
sSQL = "select * from tblDistLogos where distribid=" &
CStr(lDistribID)
If Dir(SFName) <> "" Then
rst.Open _
Source:=sSQL, _
ActiveConnection:=CurrentProject.Connection, _
LockType:=adLockOptimistic, _
options:=adCmdTableDirect
With rst
If Not .EOF And Not .BOF Then
.MoveFirst
Else
.AddNew
.Fields("DistribID") = lDistribID

End If
.Fields("fileext") = fnFileExtFromString(SFName)
If FileToBlob(SFName, .Fields("distlogo")) = True Then
SaveImgToTbl = True
End If
.Update
.Close
End With
Set rst = Nothing
End If
End Function

Function DelImgFromTbl(lDistribID As Long) As Boolean
Dim rst As ADODB.Recordset
Dim sSQL As String
Dim varItem As Variant
Set rst = New ADODB.Recordset
sSQL = "select * from tblDistLogos where distribid=" &
CStr(lDistribID)
rst.Open _
Source:=sSQL, _
ActiveConnection:=CurrentProject.Connection, _
LockType:=adLockOptimistic, _
options:=adCmdTableDirect
With rst
If Not .EOF And Not .BOF Then
.MoveFirst
.Delete
DelImgFromTbl = True
End If
.Close
End With
Set rst = Nothing
End Function