Query to Display Picture from File System (not Database)

[OldBirdman]

What a mess this question is. I have spent 2 weeks trying to make it concise and clear, and I can't. I do not have the vocabulary for the question. I cannot even TITLE it correctly. Here is my best effort.

Conditions:
80,000+ pictures (These are pictures of birds, ID by Species, Sex, Age, etc. (If known))
Hierarchy of folders:
Continent->Country->State(if India, Mexico, USA, Canada, & Australia) (No state for other countries)

I am developing an Access program to manage these. Of course, the main table will contain 80,000+ records, 1 per picture, with fields for all properties mentioned above. It would also contain the [Drive:\Path\] filename to the picture.

I am writing a "Filter" routine to select a subset of the pictures, i.e. Ravens in USA; or all birds of Romania, or Genus=Passer (House Sparrow, etc).

If I put the pictures in the table within Access, it becomes very large and slow, and it is difficult to manage. I would like to have the pictures in the hierarchy of folders mentioned above, and let Microsoft manage them. This also allows me to get to them with various photo programs, i.e. PhotoShop. So I reject any solution that has the pictures imbedded in Access.

If the result of my FilterQuery is to be 1 picture at a time, I can do that. But if I want a form with continuous records, each showing the correct picture, I don't know how to do that. Any ideas?

To state the problem another way, I need a query that returns the subset requested (I can do that OK), but has another field, PictureOfBird, that is an image. Then form would display all pictures found. The SQL Statement would have to be something like "SELECT Key, PictureFileName , PictureOfBird AS xxx AS Image FROM TablePictures;" This is not a valid SQL statement. I am using the keyword "AS" incorrectly. In SQL, "AS" means alias, within AccessVB, "AS" means DataType. My point is that I want a table with 3 fields. Field 1=Record Key; Field 2=PictureFileNa me(Drive&Path&F ileName to Image); Field 3=Image Field with picture.

This is not clear. If my table stored the image, I could query "Select Key, PictureName, PictureOfBird FROM TablePictures WHERE Genus='Corvus'" and have a continuous form showing all my pictures of Crows, and Ravens.

I hope some of this makes sense, as I don't have the vocabulary to really define what I want. Thank you.

OldBirdman

[NeoPa]

I can't look at this now old-buddy, but I think I get the question at least.
Let me see what I can find out later.

[NeoPa]

It comes down to being able to upload a picture on to the form as you go.
The information as to where the picture is stored can be retrieved easily enough for each record. From there the question is :-
Is it possible to go off, get and display the picture, on a record by record basis, as the query results are shown?

[Minion]

Sorry if this is a bit crude in the logic department as I'm more or less teaching myself as I go on things, so pleae bear with me.

Here it goes:

It seems to me that the best way to do this would be generate a recordset with the values you desire (which seem to be primarily the Key and the path). From there the recordset would be bound to a form with two text boxes (one for the Key, one for the path info) and an image object. The trick is making the image object linked and setting the path dynamically from path returned by the recordset.

Unfortunately, I am unable to provide the necessary coding here as I'm not sure of each step involved. However, this is the direction I would look as it seems that it would provide a dynamic solution without having to produce a permanent table.

Hope this helps.

Minion

[OldBirdman]

Minion defines the problem more clearly than I did. NeoPa asks questions I don't know.

When a control on a continuous form has properties other than the data in the underlying table, is it possible to have different records have different properties. For instance, could I set background color to yellow if the value were positive, and to violet if the value were negative? Or alternate ForeColor on every other record. Seems similar.

If I put an unbound image field in each record, Access wants a picture name (i.e. Directory location). If I try to delete the name in design mode, Access deletes the image field, No Picture, No Control. If I give an empty string for the picture, Access says "Can't open file".

I can put the image field in the Footer or Header, and when I select a record in the Detail Section (Continuous), display the picture. But that doesn't allow me to grab the scrollbar and visually scan faster than clicking each record.

I know that there are commercial picture management programs out there. The ones I have looked at don't easily and quickly allow complex filtering. Most of the filtering is either on the file name, or if they have other fields within the database, they must be filled individually. So Slow.

If I don't find an answer in a week, I'm going to abandon the attempt with Access as it currently is.

OldBirdman

[NeoPa]

Minion defines the problem more clearly than I did. NeoPa asks questions I don't know.

I was just asking the question that, as I see it, is at the heart of your question.

When a control on a continuous form has properties other than the data in the underlying table, is it possible to have different records have different properties. For instance, could I set background color to yellow if the value were positive, and to violet if the value were negative? Or alternate ForeColor on every other record. Seems similar.

No! This is a fairly frequently asked question here at TSDN.
However, using conditional formatting you can produce similar results.

If I put an unbound image field in each record, Access wants a picture name (i.e. Directory location). If I try to delete the name in design mode, Access deletes the image field, No Picture, No Control. If I give an empty string for the picture, Access says "Can't open file".

This concept may be worth progressing with. Is it possible to have a low-overhead picture in here which is changed on-the-fly using the OnCurrent event procedure perhaps?

I can put the image field in the Footer or Header, and when I select a record in the Detail Section (Continuous), display the picture. But that doesn't allow me to grab the scrollbar and visually scan faster than clicking each record.

This may have to be the fallback option if we can find no better way to do it.

I have asked some of the other Access experts to give this question a look. Some of them certainly understand graphics issues better than I do.

[ADezii]

What a mess this question is. I have spent 2 weeks trying to make it concise and clear, and I can't. I do not have the vocabulary for the question. I cannot even TITLE it correctly. Here is my best effort.

Conditions:
80,000+ pictures (These are pictures of birds, ID by Species, Sex, Age, etc. (If known))
Hierarchy of folders:
Continent->Country->State(if India, Mexico, USA, Canada, & Australia) (No state for other countries)

I am developing an Access program to manage these. Of course, the main table will contain 80,000+ records, 1 per picture, with fields for all properties mentioned above. It would also contain the [Drive:\Path\] filename to the picture.

I am writing a "Filter" routine to select a subset of the pictures, i.e. Ravens in USA; or all birds of Romania, or Genus=Passer (House Sparrow, etc).

If I put the pictures in the table within Access, it becomes very large and slow, and it is difficult to manage. I would like to have the pictures in the hierarchy of folders mentioned above, and let Microsoft manage them. This also allows me to get to them with various photo programs, i.e. PhotoShop. So I reject any solution that has the pictures imbedded in Access.

If the result of my FilterQuery is to be 1 picture at a time, I can do that. But if I want a form with continuous records, each showing the correct picture, I don't know how to do that. Any ideas?

To state the problem another way, I need a query that returns the subset requested (I can do that OK), but has another field, PictureOfBird, that is an image. Then form would display all pictures found. The SQL Statement would have to be something like "SELECT Key, PictureFileName , PictureOfBird AS xxx AS Image FROM TablePictures;" This is not a valid SQL statement. I am using the keyword "AS" incorrectly. In SQL, "AS" means alias, within AccessVB, "AS" means DataType. My point is that I want a table with 3 fields. Field 1=Record Key; Field 2=PictureFileNa me(Drive&Path&F ileName to Image); Field 3=Image Field with picture.

This is not clear. If my table stored the image, I could query "Select Key, PictureName, PictureOfBird FROM TablePictures WHERE Genus='Corvus'" and have a continuous form showing all my pictures of Crows, and Ravens.

I hope some of this makes sense, as I don't have the vocabulary to really define what I want. Thank you.

OldBirdman

This would be a simple matter if your were in Single Form View, since each Graphic could dynamically be loaded into an Unbound Image Control at run time via the Form's Current() Event. This would involve a simple DLookup() to the PictureFileName for the current [Record Key]. But you wish to use a Continuous Form View which will never allow the previous logic, because the Current() Event is not fired for every single Record in Continuous View. In my opinion, as long as you insist on this View in the current context, you will continue to run into a host of problems.

[NeoPa]

There appears to be a fundamental issue with this concept.
An image control can only be bound to a field in a table if the "Picture Type" is embedded (makes sense really). If a control is unbound then it is not a separate entity for each (per) record. That means you could display an unbound image control for every record, but the picture would be the same in all of them (I know - I tried it).
Otherwise, setting the "Image Address" property for the Image control for each record (cycling through then returning to top at end) in the form's "Load" event would work nicely. In practice, it simply ends up with them all showing the picture for the last record of the recordset.
This is a real shame because I thought I had the whole thing down until I discovered that :(

[NeoPa]

To confirm that, have a look at technique #2 in Handling Images with Microsoft® Access. This was something given to me by one of the other experts (puppydogbuddy) when I asked for help in this thread.
You may find the whole article interesting.

[FishVal]

Hi, everyone.

Just as an alternative the following technique may be used.

• VBA function opens the query and put shortcuts to picture files to a temporary folder
• shell command opens the folder in explorer
• moreover win explorer ActiveX control (Class: Shell32.ShellFo lderView) may be placed to an Access form

Regards,
Fish

P.S. ShellFolderView supports automation. If you find this promising I'll give you some code snippets when get acquainted a little with its object model. ;) You may do the same. MSDN: ShellFolderView class

P.S.S. Or you may use WebBrowser ActiveX control to explore folder contents on Access form. Programming of this control appears to be quite simpler.

[sierra7]

Hello
I take it from your first post that the crux of your problem is how to display your requirement as continous forms where, obviously, each record must meet a common criteria; yet you wish to select using variable criteria.

Could I therefore venture to suggest that you add a new field to your main table. It could be an Integer of a Yes/No field, call it 'Disp' or some such, then all you have to do is update it in accordance with your criteria.

So you would have an unbound master form with texts or combo controls to set the selection criteria. You will need a Public String variable for each attribute you wish to filter on and you set these each to empty string "" in the form Open event.

On the after update event of each of the controls you have something like follows to set the filter;

If Me.Control1 = "" Then
   stString1 =""
Else
  stString1 = "[Field1]=" Me.Control1
EndIf

Call DisplayPics

The criteria in the DisplayPics subroutine would just be the addition of all these 'string' you have setup, be they blank or not. The routine will not display the pics, just flag them to meet the display criteria (resetting them first) e.g

Private Sub DisplayPics()

'setup criteria string from adjusting controls
strCrit = stString1 + stString2 + stString3 + stString4 + stString5  'etc

'turn warning messages off
Application.SetOption "Confirm Action Queries", False

'reset to clear previous slection
DoCmd.RunSQL "UPDATE MainTable SET MainTable.Disp = False "

'set field Disp to affirmatory setting
DoCmd.RunSQL "UPDATE MainTable SET MainTable.Disp = True" & _
                  "  WHERE "  & stCrit  & ";"

'turn warnings back on
Application.SetOption "Confirm Action Queries", True

You will have to design you sub-form with it's picure control and there seems plenty of advise on how to do that.

You then have a choice of either filtering the data for the sub-form by using a query with the condition [Disp] = True, or just basing the sub-form on the main table and using Master/Child links to select the appropriate records. To do the latter you will need to include the field [Disp] in the sub-form (it can be hidden i.e. Visible = False), and a dummy [Disp] field on the main form which you would have to set True in the OnOpen event.

In my view that should do it, although on re-reading some of the posts it seems there may be an issue that the picure control needs a poke from the On Current event to go and find the right graphic file. That's not my recollection but I will check that out.

[sierra7]

My unreserved apologfies for the last post. I obviously missed the problem with graphics in continuous forms.

The truth is I had just come off Tesco's web site ordering whiskies for worthy clients where pictures of bottles are displayed in a continuous form (I hadn't been sampling them.. honest!) and thought "Ah this is easy, I can help here!"

On the bright side, the advice is still valid for the single form scenario but having just completed my ordering exercise I can see there may be some advantage to the user to view continuous forms, although the 'thumb-nails' are barely worth having.

I am going to try another attack where instead of having one sub-form I have four (you can't have many more on a screen because the pictures would too small to be useful), then see if I can synchronise them to seem as if they are scrolling.

Can anyone get there first !!

[NeoPa]

My unreserved apologfies for the last post. I obviously missed the problem with graphics in continuous forms.
...

Apologies not required!
I'm very pleased to see you're still offering help from your experience. That way leads to expert status if you're not careful ;)

Personally I've checked this out in my own test-bed and checked the link supplied and both lead me to believe I've reached the end of the road on this. If you manage to produce a solution though, I'll be very interested to read about it.

[ADezii]

What a mess this question is. I have spent 2 weeks trying to make it concise and clear, and I can't. I do not have the vocabulary for the question. I cannot even TITLE it correctly. Here is my best effort.

Conditions:
80,000+ pictures (These are pictures of birds, ID by Species, Sex, Age, etc. (If known))
Hierarchy of folders:
Continent->Country->State(if India, Mexico, USA, Canada, & Australia) (No state for other countries)

I am developing an Access program to manage these. Of course, the main table will contain 80,000+ records, 1 per picture, with fields for all properties mentioned above. It would also contain the [Drive:\Path\] filename to the picture.

I am writing a "Filter" routine to select a subset of the pictures, i.e. Ravens in USA; or all birds of Romania, or Genus=Passer (House Sparrow, etc).

If I put the pictures in the table within Access, it becomes very large and slow, and it is difficult to manage. I would like to have the pictures in the hierarchy of folders mentioned above, and let Microsoft manage them. This also allows me to get to them with various photo programs, i.e. PhotoShop. So I reject any solution that has the pictures imbedded in Access.

If the result of my FilterQuery is to be 1 picture at a time, I can do that. But if I want a form with continuous records, each showing the correct picture, I don't know how to do that. Any ideas?

To state the problem another way, I need a query that returns the subset requested (I can do that OK), but has another field, PictureOfBird, that is an image. Then form would display all pictures found. The SQL Statement would have to be something like "SELECT Key, PictureFileName , PictureOfBird AS xxx AS Image FROM TablePictures;" This is not a valid SQL statement. I am using the keyword "AS" incorrectly. In SQL, "AS" means alias, within AccessVB, "AS" means DataType. My point is that I want a table with 3 fields. Field 1=Record Key; Field 2=PictureFileNa me(Drive&Path&F ileName to Image); Field 3=Image Field with picture.

This is not clear. If my table stored the image, I could query "Select Key, PictureName, PictureOfBird FROM TablePictures WHERE Genus='Corvus'" and have a continuous form showing all my pictures of Crows, and Ravens.

I hope some of this makes sense, as I don't have the vocabulary to really define what I want. Thank you.

OldBirdman

How about a Single Form consisting of a series of Image Controls (either generated dynamically or statically placed) into which the results of your Filter (various Paths to filtered Graphics) would be placed, e.g. all Ravens in Boise, Idaho? All related Graphics would be loaded into multiple Image Controls on a single Form (up to a Maximum number of course). I will not even attempt to try to implement this logic unless I know you are truly interested.

[sierra7]

OK, this works (sort of)

In the attached example there is an unbound MainForm (not filtering this time, just testing the multi pictures) which has 3 instances of the same sub-form.

There are three unbound textboxes on the MainForm called F1, F2 & F3. The Form Open event stores 1,2 & 3 in these boxes.

I've added a field called FrameNo to the main table and numbered the 5 test records 1 thro 5. The first subform has Master/Child links set as F1/FrameNo; similarly the second subform is set F2/FrameNo etc.

The control buttons at the side of the form (no scroll bars) either add or subtract 1 from F1, F2, F3 so that the form does the synchronising and the pictures change.

The main problem is that as each picture load Access gives a 'Helpful' message which I can't turn off, so the scrolling is not smooth but flickers.

How to implement this?? You will have to use the criteria you set in the earlier post to creat a recordset of the selected images then loop through these and populate FrameNo from 1 to whatever. (Obviously re-setting all to zero with an SQL statement before opening the recordset)

Is it worth it?? I would probably be biased to setting up a Report and Previewing that on screen. The OnFormat event would allow you to synchronise the graphic (I believe!!) A report would scroll smoother and you may zoom into the picture by clicking the mouse but obviously it would be paged.

But if you want the info on screen and to be interactive (so you can click on an image and see full screen etc) then this is a step in that direction.

Best of 'twiching!