Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Access 2007 image lookup from spreadsheet


  • Please log in to reply

#1
tiroshii

tiroshii

    Member

  • Member
  • PipPipPip
  • 423 posts
Setting up a Customer database which is fed by a data input form. The form is designed to take information such as name, address etc as well as what products the customer wants, along with the colour. What we want it to do, if possible, is to display a preview of the product/colour when it is selected from the drop down box (combo box). We tried various vlookup commands and nothign seems to want to work.

Is this possible?

Edited by tiroshii, 26 April 2010 - 08:03 AM.

  • 0

Advertisements


#2
tiroshii

tiroshii

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 423 posts
Another thing we would like to do is make different information available in a combo box depending what is selected in another combo box. For example, if 'Worktop Type' is set to 'Corian', only the Corian colours are available, or if it is Laminate, only the Laminate colours are available in the following 'Worktop Colour' Combo box
  • 0

#3
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts
Greetings tiroshii,

Welcome to Geeks To Go.

What we want it to do, if possible, is to display a preview of the product/colour when it is selected from the drop down box (combo box). We tried various vlookup commands and nothign seems to want to work.


This should be achievable also, and could be farily basic or complex depending on the number of options, etc. Are you looking to use just one combobox to drive the image selection? Is this image to be displayed in a Form or Report?

(Quick note this one may be beyond my skill level, but others that read this topic and want to assist would find the additional information helpful) :)

Another thing we would like to do is make different information available in a combo box depending what is selected in another combo box. For example, if 'Worktop Type' is set to 'Corian', only the Corian colours are available, or if it is Laminate, only the Laminate colours are available in the following 'Worktop Colour' Combo box


This is certainly possible with comboboxes and/or list boxes. It is generally described and or searched as Cascading or dependant lists.

If you are comfortable with VBA (and it sounds like you are), here is a good link to one of Martin Green's Access Tips describing 3 methods of implementing Cascading lists with Combo boxes.


Sincerely,

dm
  • 0

#4
tiroshii

tiroshii

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 423 posts
Thatks for your reply. Will look into the Cascading lists.

As regards to your furst question

Are you looking to use just one combobox to drive the image selection?


One combo box per each image 'area', so, for the taps combo box, a set of images, and another for sinks, colours etc.

Is this image to be displayed in a Form or Report?


A form, sorry forgot to mention that one!

Edited by tiroshii, 27 April 2010 - 02:29 AM.

  • 0

#5
tiroshii

tiroshii

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 423 posts
SOrted this earlier, thanks again.

Still having issues with the images though.
  • 0

#6
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts
Hi again,

So you solved your cascading list issues then? If so, that is great...

I haven't had time to test any of the image methods, but hopefully I will get a chance tonight or tomorrow.

3 Questions:
1. Where are the image paths stored (local hard drive or network drive)?
2. How many images will you be working with?
3. What is the largest image file size?

dm
  • 0

#7
tiroshii

tiroshii

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 423 posts

Hi again,

So you solved your cascading list issues then? If so, that is great...

I haven't had time to test any of the image methods, but hopefully I will get a chance tonight or tomorrow.

3 Questions:
1. Where are the image paths stored (local hard drive or network drive)?
2. How many images will you be working with?
3. What is the largest image file size?

dm

Yes the cascading list now works perfectly. Had to recode some of the example used however to feed two combo boxes by one but I got it working and they're happy with it. Thanks.

1. The images and the database are stored on a network drive.
2. Tops of around 50 images (ouch) but not all for the same combo box
3. No idea until I get back into work (work Monday and Tuesday) but they only need to be low quality thumbnail previews so I can downsize them a fair bit. They are just colour swatches mainly, with a few images of taps and a few of sinks, I will probably just re-use the 150x150 thumbnails I used on the website...

For example:

http://www.supremaco...bnails/tap2.gif

Edited by tiroshii, 28 April 2010 - 04:42 AM.

  • 0

#8
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts
Hi,

Been working through some solutions, and hope to have some more information for you tomorrow or over the weekend.


Best regards,


dm
  • 0

#9
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts
HI Tiroshii,

Progress...I now have pictures based on a combo-box in a form. :)

I have some code for you to try with your DB, but first I want to let you know I had to make a couple of assumptions with my set-up.
  • The combo box record source is a table (Products) with a Primary Key assigned
  • I added the file path for the images for each value in the respective table
  • I tested this for one combo box and one image object, and it works fine, just add additional code to refer to any additional objects
Obviously you want to use a back up DB or set up some dummy tables and forms for this first.

My Test DB
2 tables
  • Table Products- This is my record source for the combo box used in the Form
    • ID field: Primary Key, Auto number data type
    • Product field: One word product name, Text data type
    • Location field: Full file path for the product image, Text data type
  • Table Orders
    • ID field: Primary Key, Auto number data type
    • Product field: Foreign Key (the PK of the Products table), Long Integer data type
    • Quantity field: number data type, just to fill out the table
    • Price field: currency data type, just to fill out the table
The names in red will need to be changed to match your control names

FrmOrders
This form is based on the Orders table and contains all those items. In addition, I've added the two item below:
  • Unbound text box labeled Image Path (it's optional and it's Visible property can be set to No, if you don't want the user to see it, since it's only purpose is to display the image's file path. It was helpful in troubleshooting for me)
  • Image Control control name image33
Now, the Product item in the form is a combo box (control name cboProducts) and its record source is the Products table. So under the combo box properties you want to set the following items:
  • Column count: 3 (or what is required to ensure the file path is included in the combo box)
  • Column widths: 0";1";0" (or what is required to ensure only the Product description is visible to the user. the 3rd column in my test DB is the file path field)
If you wish to use the text box to view and or display the image file path set its Control Source to: =[cboProducts].[column](2)

So, once the above items are ready (Image control, combo box record source has the file paths entered), it's time to add the two Subs to the Form.
This is done under the Change Event for the Combo Box and Current Event for the Form. Don't forget to change the control names in the code to match your DB.

Option Compare Database
Private Sub cboProducts_Change()
Me.Image33.Picture = Me.cboProducts.Column(2)
End Sub

Private Sub Form_Current()
If IsNull(Me.cboProducts.Column(2)) Then
Me.Image33.Picture = ""
Else
Me.Image33.Picture = Me.cboProducts.Column(2)

End If

End Sub

So, what does the code do?
  • The Change Event will bring up the appropriate image once a Product is selected from the Comb Box.
  • The second code, checks to see if the Combo Box is empty (like it would be if you are entering a new record) and if so will display no picture. However, if the combo box field is populated will display the proper image.
If you want to check out my Test DB, I've also uploaded it to MediaFire here: http://www.mediafire...mage Test.accdb

Hope this work for you and that my assumptions regarding your DB's set up are accurate.

Sincerely,

dm

Edit: Added missing brackets

Edited by dm27, 04 May 2010 - 09:13 AM.

  • 0

#10
tiroshii

tiroshii

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 423 posts
Wow thank you very much. Will look through this on Tuesday when I get back in the office. Thanks again
  • 0

Advertisements


#11
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts
Hi tiroshii,

Sounds good :)


Hope you had a pleasant weekend.


dm
  • 0

#12
tiroshii

tiroshii

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 423 posts
Opened your demo db up and replaced the filepaths of the images etc but not getting anything in the blank image box. Attached screen grab of what I entered into the Products Table (sorry to be dificult!!)

Edited by tiroshii, 04 May 2010 - 05:16 AM.

  • 0

#13
tiroshii

tiroshii

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 423 posts
Okay I appear to have fixed it. All I did was change the Control Source of the Image Frame to =[cboProducts].[column](2) and the images show up :) Thanks!

Edited by tiroshii, 04 May 2010 - 08:25 AM.

  • 0

#14
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts
hello again,

Glad you got it working and I went back to edit my post to add those missing brackets...


I imagine you can go ahead and scale it up with your additional combo boxes now...


Best regards,

dm

Edited by dm27, 04 May 2010 - 09:15 AM.

  • 0

#15
tiroshii

tiroshii

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 423 posts

hello again,

Glad you got it working and I went back to edit my post to add those missing brackets...


I imagine you can go ahead and scale it up with your additional combo boxes now...


Best regards,

dm

Working through that as we speak. Thanks again
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP