Displaying a picture from a table in a bound OLE field

[Kevin]

I have a database which tracks trademark applications. I had initially
built it so that the users could paste a picture in an OLE field so
that the users would know what the trademark looked like. That was
problematic because the DB got too big (600+ records, each with a
picture made the DB over 80MB!). I worked around this by splitting the
pictures into a separate table (tblTMTypes) in the DB, and linked this
table to the registrations (tblRegistrations) table. This way there
were only 10 pictures in the whole DB.

On the main data entry form (frmMaster), the user selects a trademark
type which is stored in tblRegistrations.TMTypeName. TblTMTypes and
tblRegistrations are linked through the TMTypeName field which each
table has. I have a bound OLE field on frmMaster to display the
picture for the chosen TMTypeName. To get the picture to display, I
created a query which merges tblRegistrations with tblTMTypes. It is
this query which frmMaster references.

The problem I have run across with this method, is when the user
deletes a registration record, the linked record in tblTMTypes will
also be deleted, which I don't want to happen. When that happens, the
next time frmMaster is opened, not all the records will display,
because not all tblRegistrations records have a corresponding
TMTypeName in tblTMTypes.

What I am looking to do is to not have the main form linked to a query
merging the two tables. I just want the form to reference
tblRegistrations. This will fix the problem of linked TMTypes getting
deleted, and it will also speed up the opening of frmMaster which is
taking too long since it references a query which merges tables.
However, I still need the picture to display. I need the OLE field on
frmMaster to display the tblTMTypes.TMTypePic for the corresponding
tblRegistrations.TMTypeName when tblTMTypes.TMTypeName =
tblRegistrations.TMTypeName. Also, if possible, when the user makes a
selection for the TMTypeName on frmMaster, the OLE field should
automatically be updated to display the picture for the new TMTypeName
chosen.

I've searched through google and have not found anything satisfactory
to help with this problem. Most suggestions were to reference the
pictures outside of the DB. I'm not going to do that because I can't
trust the pictures staying in the same place or not being deleted by
someone not in the know. I need the DB to be self contained, since I
will not be around after it is completed to administer the DB. I'm not
a code expert, so any help in writing code to perform the tasks I
outlined above would be greatly appreciated.

Thanks for the help.

 

[Kevin]

I figured out a work around to my own problem. It may not be elegant,
but it works without any code.

I created a query containing only the field TMTypeName and TMTypePic
from tblTMTypes. I set the criteria for TMTypeName to the value of the
TMTypeName combo box on frmMaster (which is linked to the actual
TMTypeName value in tblRegistrations). So, this pulls the TMTypePic
for the TMTypeName for the registration record. To display the picture
on frmMaster, I created a subform with the new query as the recordset.
I put a bound OLE field on the form using the query's TMTypePic as the
value. I then removed all the navigation etc from the form to make it
just look like a plain field. So, this subform displays the picture
just like it was any other field, and if a registration record is
deleted, the linked TMType is not deleted. I also had to set the
subform to not be editable, so that users do not try to paste new pics
into the subform, which is possible if not locked down.