?
Solved

Display Ole from combo box

Posted on 2005-03-12
29
Medium Priority
?
279 Views
Last Modified: 2012-05-05
I have what I think is a simple requirement.  I have two tables tblPics and tblClients.  I want from the client form to be able to select a name of a Pic , red house, blue house, and have the picture of the red house/blue house appear in the picture box.  The client does not want to link and if I imbed, the bloat is enourmouse.  I might have many clients that will select red house and I want to have the red house pic display on each client that has selected red house from a combo box of house types.


Thanks,

Vince
0
Comment
Question by:vmccune
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 13
  • +1
29 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 13526304
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13526332
What data are you storing exactly in tblPics?
0
 

Author Comment

by:vmccune
ID: 13526343
tblpics has two fields, HouseName and Picture.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13526346
Use an Image control and not an OLE control. Assuming the picture field is a filename, all you need to do is make your combo box select both fields from tblpics, and, if the picture is in the second column, you can use some code like this in the AfterUpdate event of the combo:

Me!MyImageControl.Picture=Me!MyComboBox.Column(1)
0
 

Author Comment

by:vmccune
ID: 13526354
picture field is NOT a file name,  It is the actual picture stored into an Ole field.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13526361
Well, assuming you don't want to store OLE objects in your database, look at the link that capricorn1 posted.
0
 

Author Comment

by:vmccune
ID: 13526366
also,  I dont want to store the picture on each record as many records will have the same picture.  Basically I wnat it to work as if I had an employee ID field that had ID,Fname,Lname and then had to additional text boxes to display the firat and last name like EmpID.Column(1) and EmpID.Column(2) respectively.  I just wnat the pic to reference and display without writing to the record.  The rub is that the data in the access table already so linking is not an option.

0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 2000 total points
ID: 13526387
OK, in that case, include a field in your client table that selects which picture you want to show. That will probably be the HouseName field. Your combo box only selects the HouseName field from the picture table.

In the AfterUpdate of the combo box (and the OnCurrent event of the form), you need some code like this:
Dim rst As DAO.Recordset

Set rst=CurrentDb.OpenRecordset("SELECT * FROM tblPics WHERE HouseName='" & Me!MyPictureCombo & "'")
Me!MyOLEcontrol.OLEData=rst!Picture.GetChunk(rst!Picture.Size)
rst.Close
Set rst=Nothing

This assumes you have an unbound OLE frame on your form called MyOLEControl that is used to display your picture.
0
 

Author Comment

by:vmccune
ID: 13526554
Thanks so far.

Dim rst As DAO.Recordset

This line gives the error:

Compile Error:
User-Define type not defined.

any thoughts ?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13526571
You haven't got the DAO libray referenced (this is not included as default in A2K onwards), but it's easy enough to sort out - in the VBA window, click Tools->References and check "Microsoft DAO 3.6" (or 3.5 if that's the only one available).
0
 

Author Comment

by:vmccune
ID: 13526606
Me!Picture!OleData = Rst!Picture.GetChunk(Rst!Picture.Size)

Gives "Argument not optional"  

Maybe I misnamed something.  What are the two "Picture"'s referencing ?

Thanks.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13526622
The second ! needs to be a ., as in:

Me!Picture.OleData = Rst!Picture.GetChunk(Rst!Picture.Size)

The first Picture should be replaced with the name of the unbound OLE frame on your form that is being used to display the picture. The second and third Picture should be replaced with the name of the OLE field in your picture table that holds the picture data.
0
 

Author Comment

by:vmccune
ID: 13526805
*** RETURNS ARGUMENT NOT OPTIONAL ***


Dim Rst As DAO.Recordset
  Set Rst = CurrentDb.OpenRecordset("select * from emp1 WHERE EmployeeID ='" & Me!Combo & "'")
  Me!Pic.OleData = Rst!Photo.GetChunk(Rst!Photo.Size) ... *** RETURNS ARGUMENT NOT OPTIONAL ***
 
  Rst.Close
  Set Rst = Nothing


table is Emp1
field on form is Pic
field in Emp1 table is Photo
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13526809
Whoops, the line should read:

Me!Pic.OleData = Rst!Photo.GetChunk(0, Rst!Photo.Size)
0
 

Author Comment

by:vmccune
ID: 13526826
DATA TYPE MISMATCH IN EXPRESSION


Set Rst = CurrentDb.OpenRecordset("select * from emp1 WHERE EmployeeID ='" & Me!Combo & "'")
0
 

Author Comment

by:vmccune
ID: 13526831
Me!Combo is numeric and equals what was selected in the combo box
0
 

Author Comment

by:vmccune
ID: 13526839
RECORDSET DOES NOT SUPPORT THIS METHOD



Dim Rst As DAO.Recordset
  Set Rst = CurrentDb.OpenRecordset("select * from emp1 WHERE EmployeeID =" & Me!Combo)
  '  Set Rst = CurrentDb.OpenRecordset("select * from emp1 WHERE EmployeeID ='" & Me!Combo & "'")
  Me!Pic.OleData = Rst!Photo.GetChunk(0, Rst!Photo.Size) RECORDSET DOES NOT SUPPORT THIS METHOD
 
  Rst.Close
  Set Rst = Nothing
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13526841
No, the recordset needs to select from the picture table - this statement is selecting the appropriate picture to display in the OLE frame.

The combo box in question should select the picture names from the picture table - it's basically the combo that allows the user to select which picture is to be displayed for the record.
0
 

Author Comment

by:vmccune
ID: 13526856
I dont have picture names in the table.  The field Pic in EMp1 is Ole to and says Bitmap Image in the field.  I hope My bad explanation didnt lead us the the right pew in the wrong church.  so to speak
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13526863
By picture name, I mean the field that identifies the picture you want to show (in this case, I assume it's the HouseName field).

So the combo in question should display a list of house names, and it should be bound to a field that stores the house name in the clients table.
0
 

Author Comment

by:vmccune
ID: 13526996
OBJECT DOES NOT SUPPORT THIS PROPERTY OR METHOD  


Dim Rst As DAO.Recordset
  Set Rst = CurrentDb.OpenRecordset("select * from emp1 WHERE EmployeeID =" & Me!Combo)
  '  Set Rst = CurrentDb.OpenRecordset("select * from emp1 WHERE EmployeeID ='" & Me!Combo & "'")
  Me!Pict.OleData = Rst!Photo.GetChunk(0, Rst!Photo.Size)   OBJECT DOES NOT SUPPORT THIS PROPERTY OR METHOD  
 
  Rst.Close
  Set Rst = Nothing
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13527015
Your statement is still selecting from the the emp1 table. You say you have two tables, tblClient and tblPics. Let's get back to basics and see if we can get an understanding here.

tblPics contains your picture data. This should have two fields:
HouseName - the primary key which uniquely identifies the picture.
Picture - an OLE field containing your picture data.

tblClients contains your client details. This should have a field in it called HouseName. This stores the name of the picture that you want to display for that particular client record - this name matches the HouseName field in the tblPics table.

Is this correct?
0
 

Author Comment

by:vmccune
ID: 13527064
 Dim Rst As DAO.Recordset
  Set Rst = CurrentDb.OpenRecordset("select * from Pictbl WHERE PicID =" & Me!ClientID)
  '  Set Rst = CurrentDb.OpenRecordset("select * from emp1 WHERE EmployeeID ='" & Me!Combo & "'")
  Me!Pict.OleData = Rst!Photo.GetChunk(0, Rst!Photo.Size)
 
  Rst.Close
  Set Rst = Nothing

ClientTbl - 1 Field - Autonumber [ClientID]

PicTbl 2 Fields - [PicID] and [Photo]

Form - Emp1
2 fields on the form bound to ClientTbl
[ClientId]
[Pict] - should display the picture
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13527072
No - ClientTbl should contain a PicID as well. This identifies the picture to use for that client.

Your form should have a combo box bound to the PicID field (let's call it cboPicID). This combo box should select all PicIDs from the PicTbl table. Your Set line then looks something like this:

Set Rst = CurrentDb.OpenRecordset("select * from Pictbl WHERE PicID =" & Me!cboPicID)

So client ID 1 might have PicID 5 stored against it. The code looks up PicID 5 from Pictbl, grabs the data from it and places it in the Pict OLE frame.
0
 

Author Comment

by:vmccune
ID: 13527098
The set is working ok

Me!Pict.OleData = Rst!Photo.GetChunk(0, Rst!Photo.Size)

is failing

Pict = Unbound ole field on form

Photo = Ole Picture field in Pictbl

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13527113
Yes, it will fail because it is not finding a matching record in the Pictbl table.

You *must* store a PicID in the ClientTbl table as well, as I have described. Otherwise you are trying to look up a picID that is the same as the ClientID, and that why the line is failing, because such a PicID may not exist.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13527684
Looks almost the same as the product images in the NorthWind.mdb sample database.

The main issue for Access 2000 will be to suppress the database bloat by issuing the latest service packs:
SR1a/2:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q276367
SR3:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;326585

Nic;o)
0
 

Author Comment

by:vmccune
ID: 13528144
 And we have a winner !

  Dim Rst As DAO.Recordset
  Set Rst = CurrentDb.OpenRecordset("select * from Pictbl WHERE PicID =" & Me!ClientID)
  Me.Pict = Rst!Photo
  Rst.Close
  Set Rst = Nothing


Shane,  we we ever so close even with the first go.  The final problem was the getchunk.  I decided to through caution to the wind and look it up.  It only works with copying one Ole or activeX from one point to another.  Not just a reference and display.

Thanks for the long evening.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13528589
Cool - I've actually used the GetChunk method and it does work though :)
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question