How to display 10 Images horizontally on a continuous form using vba

[Exi]

I have a form with 10 image boxes side by side set to continuous,
I use it to display pictures from a folder.
this I've done using a cross tab query to divide the picture column into 10 columns then assign a column to each image box.
This works fine but its slow when running the query nearly 45 secs each time and that's with only 4000 images also it is limited in sorting due to the cross tab query.
So I was wondering can it be done in vba, that way I can sort on all the fields in my main table.
After a lot of searching I found some code but was unsuccessful in adapting it to work which I could post if needed.

 

[June7]

Yes, post code and explain why it was unsuccessful, what happens - error message, wrong results, nothing?

 

[Exi]

This is the section I used
-----------------------------------------------------------
First create a string and make it the row source for a listbox. Create the listbox somewhere on your form but make it invisible. Let's call is lst0

On your form make a number of image box and name each one in sequence eg Img1, Img2,Img3,Img4 etc...

Then you can use vba to change the background of each textbox.
---------------------------------------------
Dim strFileName As String
Dim strSourceFolder As String
Dim strRowSource As String

strSourceFolder = FolderPath & ""
'Enter the path of the folder that has the photos

strFileName = Dir(strSourceFolder)

While strFileName <> ""
strRowSource = strRowSource & strFileName & ";"
strFileName = Dir

Wend
strRowSource = Left(strRowSource, Len(strRowSource) - 1)

Me.lst0.RowSourceType = "Value List"
Me.lst0.RowSource = strRowSource

'This gives you a listbox with the path of all the photos

'Now we will display these photos

Dim i as Integer For i = 0 to Me.lst0.ListCount -1
Me("Img" & i+1).Picture="'" & Me.lst0.Column(0, i)

Next i

-----------------------------------------------------------
so what I tried so far is
using a form with 10 image boxes horizontally
based on a query with 2 tables the main and a path table
i placed the code in the forms on open property
created a blank list box called lst0
input the folders path to the code excluding the last \
the error I got was the setting for this property is too long in this row
Me.lst0.RowSource = strRowSource with the strRowSource showing the first 4 image file names without the path
the second method I tried was creating a list box with 2 fields the first movieID hidden and the second the full path name to the image and commented out the code to the line
Dim i As Integer
the errors I get are Ms access cant open the file "1"
or if I change the column count form 0 to 1
then Ms access cant open the file "C:\AA MOVIE DATABASE\Covers\1-#Horror--2015-.jpg" The first file in the list.
I know its probably me and its something simple like the code in the wrong box etc.

 

[pbaldy]

Well I posted a reply just waiting on moderators approval ?

Approved, replying to trigger notification email.

 

[static]

see

https://www.access-programmers.co.uk/forums/showthread.php?t=290674
https://www.access-programmers.co.uk/forums/showthread.php?t=290777

 

[Exi]

Thank You static, I found those before but knowing nothing about htm code I was unable to resize the pictures and kept getting a memory exceeded error as I currently have only imputed 4000 of the 10,000 or so records I have.
The crosstab method Im using allows me to select any picture and load a details form from it but it is limited in the fields it has, so in order to sort by genre ABCDEF etc, movie year etc etc I have to sort the data each time and regenerate the temp table Im using and that takes around 50 secs each time.
While it works its not practical.
So to avoid all the work arounds I was looking for a VBA solution to displaying in a form 10 horizontal images using my main table and path table as this will be my main form for viewing and sorting.
Hope that's clearer than mud lol

 

[isladogs]

Hi

Bit late to the party on this topic
As you are still having problems here is a slightly different approach which you may be able to adapt

Attached is a report of student photos (example images used here rather than 'real' students). As each class has about 30 students, the images are resized so up to 35 fit on each page (5 per row)
I've also shown the report design for info
The images are stored as BMP files (2000+) on the server and Access uses the image path.
The report loads instantly - even when a whole year group is used (approx 300)

The report uses this procedure to get the image path, substituting with a default 'no photo available' image where no photo exists

Function SetImagePath()
    Dim strImagePath As String
    On Error GoTo PictureNotAvailable
    Me.ImageFrame.PictureType = 1 
    strImagePath = Me.ImagePath
    Me.ImageFrame.Picture = strImagePath
    Exit Function

PictureNotAvailable:
  strImagePath = DefaultImage
  Me.ImageFrame.Picture = strImagePath

  End Function

There is no reason why the same idea can't be used on a form with 10 per row as required.
If this is of any use to you, let me know & I'll supply more details

 

[static]

Thank You static, I found those before but knowing nothing about htm code I was unable to resize the pictures and kept getting a memory exceeded error as I currently have only imputed 4000 of the 10,000 or so records I have.
The crosstab method Im using allows me to select any picture and load a details form from it but it is limited in the fields it has, so in order to sort by genre ABCDEF etc, movie year etc etc I have to sort the data each time and regenerate the temp table Im using and that takes around 50 secs each time.
While it works its not practical.
So to avoid all the work arounds I was looking for a VBA solution to displaying in a form 10 horizontal images using my main table and path table as this will be my main form for viewing and sorting.
Hope that's clearer than mud lol

It is as clear as mud. The memory exceeded error is nothing to do with image resizing. Maybe the html document is very long, but why would you want to display so many images at once anyway? A report is only really useful if it is filtered.

 

[Exi]

Thanks for your reply ridders I would be glad to try if it works on a form,
I tried your code and after changing Me. for Forms!Form1b I managed to get it to work buts it displays the same image over and over in continuous mode.
But I guess the trick is getting the other horizontal image box to display the next image at the same time, thats why I thought using a list box to increment each image box should of worked.

 

[Exi]

In answer to your question static
What I have at the moment is the same as your form1 except its 10 columns wide
I can click on any cover get that covers plot,actors etc and play that movie.
the problem I have is the cross tab temptable has to regenerated each time I wish to sort the form which takes to long.

 

[isladogs]

Hi

Somewhat confused by your last post
Reports can have more than 1 column but forms can't!

Anyway, I've tried out Static's movie database & its very neat.
It works absolutely perfectly for me.
For online image locations, a web browser form control is the only way to go.
Unfortunately, I couldn't get this to work as a report.

However, if you have image files stored in a folder on your computer, a different approach works well using a form & report.
I have made a form to populate a list box with all image files in a selected folder.
It took about 4 seconds to do this with a folder containing 5000 image files. Selecting an item in the listbox displays its image.

I tried your approach with multiple image controls on the form and it didn't work well for me.
Instead, a report is used to view all images in the folder.
This loads in a couple of seconds even with a very large number of images.

See screenshots of form and report

The attached zip file contains the Access database & a default image.
Place both items in the same folder

 

[static]

Anyway, I've tried out Static's movie database & its very neat.
It works absolutely perfectly for me.
For online image locations, a web browser form control is the only way to go.
Unfortunately, I couldn't get this to work as a report.

Works for local images too.
Not sure what you mean by the report thing. I've never used Access Reports, but I'm not sure why anybody would ever want to.

That example used images that were all the same size. For it to work with images of different sizes and properly put them into columns you'd need to set the width.

<img [B]width='150px'[/B] src ='" & .Fields("photo") & "'>

Number of columns are not set, they split automatically based on screen width. If you were to print the page - right click print... it would adjust to fit the paper.

@Exi - in your first post you said you could post an example. If you still need help with this I suggest you do that.

 

[Exi]

First of all thank you guys for all your help it is mightly appreciated.
ridders thanks for your sample database I can see bits and pieces that will help me a lot.
when I tried the database with my pictures they appeared all washed out.
My cover folder which only has 4000 Covers is already 1.22 gb
The main database I link to is 600mb
I could make a thumbnail folder I suppose but the loading time of your database was actually longer than mine using the cross tab so its probably the file sizes.

 

[Exi]

Thanks for that static I havent posted any pics or an example yet as I havent reached 10 posts
this is my 10th so I will try and cut down a small working version of what I have and post that.
Also I will post Pics as soon as possible.

 

[isladogs]

Hi Static

Not sure what you mean by the report thing. I've never used Access Reports, but I'm not sure why anybody would ever want to.

That example used images that were all the same size. For it to work with images of different sizes and properly put them into columns you'd need to set the width.

Code:
<img width='150px' src ='" & .Fields("photo") & "'>
Number of columns are not set, they split automatically based on screen width. If you were to print the page - right click print... it would adjust to fit the paper.

Sorry - had a bit of a brain fade & forgot about right clicking to view a web browser in print preview. Doh!

I assume you mean you've 'never used Access reports' with a web browser rather than never used them at all.
There are obviously lots of reasons why standard reports are widely useful.

I've occasionally used web browser controls in my own programs as a way of displaying formatted text & images - see 'Help & Support Form WB' example image. I use a Print button instead of right clicking ... which is why I had forgotten about it

Anyway I've now added a WB control to my own folder image viewer as in attached images. It also includes a 'print' button - actually its print preview mode

Incidentally, the images are those I use with the help form

The form and 'report' i.e. print preview both work perfectly.

However the print preview has a noticeable time lag - approx 5sec for 900+ images whereas the 'standard report' I used before is much faster to load

3 questions:
1. For 'cosmetic' reasons I would like the image labels to be centred under each image. How do I code that?
2. I'd also like them to line up horizontally in each row.
I can do that if I fix the image heights as well as the widths but this would distort the images as they are different sizes. Is there another way?
3. I'd like to edit the text in the preview header / footer. For example to replace the 'about blank' in the preview footer with the folder path. Do you know how to do that?

Here is the relevant code I'm using:

Private Sub genrpt()
    Dim wb As WebBrowser
    Set wb = WebBrowser0.Object
    With wb.Document.body.Style
        .backgroundcolor = "white" '"black"
        .Color = RGB(255, 0, 0) '"blue" '"grey"
        .FontSize = "9pt"
        .FontWeight = "normal"
        .fontfamily = "calibri"
    End With
            
    With CurrentDb.OpenRecordset("select * from tblImageFiles order by [" & Combo1 & "]")
        Do Until .EOF
            s = s & "<span style='width:220px;height:120px;vertical-align:top;margin:10px;'>"
            
            'next line will distort image proportions if not all equal sized
           ' s = s & "<img width='250px' <img height='150px' src ='" & .Fields("ImagePath") & "'><div>"
           'use this instead
            s = s & "<img width='200px' src ='" & .Fields("ImagePath") & "'><div>"
            
           ' s = s & "<img src ='" & .Fields("ImagePath") & "'><div>"
            s = s & .Fields("ImageName") & "</div>" '" "
            s = s & .Fields("DateCreated") & "</div>"
            s = s & "</span>"
            .MoveNext
        Loop
    End With
    wb.Document.body.innerhtml = s
End Sub

Let me know if its worth uploading my new version

 

[static]

Nope never used Access reports and I've used Access since version 2.

I can't see the point in creating reports that can't be shared electronically. Ok there's the report viewer, but, ugh.

1. For 'cosmetic' reasons I would like the image labels to be centred under each image. How do I code that?

Well the style aligns tops using 'vertical-align:top' so to align things horizontally you use ... something else because horizontal-align:center would be too easy.
<span style='vertical-align:top;text-align:center;

2. I'd also like them to line up horizontally in each row.
I can do that if I fix the image heights as well as the widths but this would distort the images as they are different sizes. Is there another way?

There is a width on the span, set that to the width you want and set the width of the image to 100% instead.

3. I'd like to edit the text in the preview header / footer. For example to replace the 'about blank' in the preview footer with the folder path. Do you know how to do that?

Not sure you can change the URL other than save the generated page out to file and loading that.
Or set the document title with wb.Document.title = currentproject and change header and footers in page setup.

 

[isladogs]

Nope never used Access reports and I've used Access since version 2.

OK you beat me on that as I didn't start till A97

In one of my production databases there are almost 400 reports - more than the number of forms!
There are many occasions when printed output is unavoidable e.g. for meetings where no screen is available
For emails, I attach a PDF but it still needs a report!
From memory, the unlamented snapshot viewer died years ago - A2007?

1. Centre align text:

Well the style aligns tops using 'vertical-align:top' so to align things horizontally you use ... something else because horizontal-align:center would be too easy.
<span style='vertical-align:top;text-align:center;

Yup I'd tried horizontal-align before posting
Text-align works nicely - thanks

2. Horizontally align text:

There is a width on the span, set that to the width you want and set the width of the image to 100% instead.

Tried that, it didn't work.

 s = s & "<img width='100%' src ='" & .Fields("ImagePath") & "'><div>"

looks exactly the same as:

 s = s & "<img width='200px' src ='" & .Fields("ImagePath") & "'><div>"

3. Editing the printed output header/footer:

Not sure you can change the URL other than save the generated page out to file and loading that.
Or set the document title with wb.Document.title = currentproject and change header and footers in page setup.

As suggested, I got this to work using:

wb.Document.Title = GetFolderPath()

and then changing the sections in page setup to show Title rather than URL.
I'd already tried modifying the URL but that didn't work

New output attached

For info, I added an update folder control on the WB form.
I found that requerying the WB didn't update the images
However, 'silently' reloading the form did the job
This took less than 2 seconds for a folder of around 1000 images which is fine
Another folder of 4000 images took about 15 sec - not so good!

 

[Exi]

The 3 images
I uploaded is the working cross tab one
Statics with my Images
And the Current one Im trying to Get with Vba
The current one manages to get 10 different images horizontally
but in continuous mode repeats the same 10
but all I used was a list box and 4 lines of code which I hacked with a blunt Knife
Private Sub genrpt()
Dim I As Integer
For I = 0 To Forms!Covers_Horizontal.lst0.ListCount - 1
Me("Img" & I + 1).Picture = Me.lst0.Column(1, I)
Next I
End Sub

I need a loop to stop it looking for img11 and a eof I think

 

[static]

@ridders

try vertical-align:middle

As I said earlier a report is only really useful if it is filtered.
Dynamically generating and displaying that much data at once is bound to be slow. Why would you want 4000 images on screen at once? A normal report or web page with that amount of data would either be filtered, have multiple pages or saved to disk with an index.

Otherwise, a normal form would be more efficient because recordsets have pages and the form doesn't need to load data that is off screen.

 

[isladogs]

@Static

try vertical-align:middle

Already tried that & vertical-align:center & vertical-align:bottom
The images are shifted but the text follows immediately below the image
What would be needed is a way of fixing the text separately.
Probably not worth further efort

As I said earlier a report is only really useful if it is filtered.
Dynamically generating and displaying that much data at once is bound to be slow. Why would you want 4000 images on screen at once? A normal report or web page with that amount of data would either be filtered, have multiple pages or saved to disk with an index.

Otherwise, a normal form would be more efficient because recordsets have pages and the form doesn't need to load data that is off screen.

I agree - was just testing out of curiosity

As a final step, I've been trying to see if its possible to set the defaults for print preview mode
e.g. landscape, 70%
A Google search led me to this site:
https://www.vistax64.com/vb-script/186051-how-print-web-page-vba.html
However, I've not got any further.
Again, probably not worth further effort