Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-08-2017, 03:24 PM   #1
Exi
Newly Registered User
 
Join Date: May 2017
Location: Perth Western Australia
Posts: 23
Thanks: 4
Thanked 0 Times in 0 Posts
Exi is on a distinguished road
Unhappy How to display 10 Images horizontally on a continuous form using vba

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.

Exi is offline   Reply With Quote
Old 05-08-2017, 07:06 PM   #2
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,145
Thanks: 0
Thanked 503 Times in 499 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: How to display 10 Images horizontally on a continuous form using vba

Yes, post code and explain why it was unsuccessful, what happens - error message, wrong results, nothing?
June7 is offline   Reply With Quote
Old 05-09-2017, 06:37 AM   #3
Exi
Newly Registered User
 
Join Date: May 2017
Location: Perth Western Australia
Posts: 23
Thanks: 4
Thanked 0 Times in 0 Posts
Exi is on a distinguished road
Re: How to display 10 Images horizontally on a continuous form using vba

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.


Last edited by Exi; 05-09-2017 at 10:03 AM. Reason: messed up code
Exi is offline   Reply With Quote
Old 05-09-2017, 08:44 AM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,030
Thanks: 13
Thanked 4,063 Times in 3,998 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: How to display 10 Images horizontally on a continuous form using vba

Quote:
Originally Posted by Exi View Post
Well I posted a reply just waiting on moderators approval ?
Approved, replying to trigger notification email.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 05-10-2017, 12:50 AM   #5
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 823
Thanks: 4
Thanked 194 Times in 180 Posts
static will become famous soon enough static will become famous soon enough
Re: How to display 10 Images horizontally on a continuous form using vba

see

https://www.access-programmers.co.uk...d.php?t=290674
https://www.access-programmers.co.uk...d.php?t=290777
static is offline   Reply With Quote
Old 05-10-2017, 10:32 AM   #6
Exi
Newly Registered User
 
Join Date: May 2017
Location: Perth Western Australia
Posts: 23
Thanks: 4
Thanked 0 Times in 0 Posts
Exi is on a distinguished road
Re: How to display 10 Images horizontally on a continuous form using vba

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
Exi is offline   Reply With Quote
Old 05-10-2017, 11:38 AM   #7
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,081
Thanks: 110
Thanked 2,738 Times in 2,500 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: How to display 10 Images horizontally on a continuous form using vba

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

Code:
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
Attached Images
File Type: png PhotosReportDesign.PNG (9.7 KB, 75 views)
File Type: png ImagePathData.PNG (22.4 KB, 83 views)
File Type: jpg PhotosReport.jpg (90.7 KB, 62 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 05-10-2017, 02:19 PM   #8
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 823
Thanks: 4
Thanked 194 Times in 180 Posts
static will become famous soon enough static will become famous soon enough
Re: How to display 10 Images horizontally on a continuous form using vba

Quote:
Originally Posted by Exi View Post
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.
static is offline   Reply With Quote
Old 05-10-2017, 03:21 PM   #9
Exi
Newly Registered User
 
Join Date: May 2017
Location: Perth Western Australia
Posts: 23
Thanks: 4
Thanked 0 Times in 0 Posts
Exi is on a distinguished road
Re: How to display 10 Images horizontally on a continuous form using vba

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 is offline   Reply With Quote
Old 05-10-2017, 04:25 PM   #10
Exi
Newly Registered User
 
Join Date: May 2017
Location: Perth Western Australia
Posts: 23
Thanks: 4
Thanked 0 Times in 0 Posts
Exi is on a distinguished road
Re: How to display 10 Images horizontally on a continuous form using vba

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.
Exi is offline   Reply With Quote
Old 05-11-2017, 01:21 PM   #11
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,081
Thanks: 110
Thanked 2,738 Times in 2,500 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: How to display 10 Images horizontally on a continuous form using vba

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
Attached Images
File Type: jpg ImageForm.jpg (91.1 KB, 122 views)
File Type: jpg ImageReport.jpg (101.8 KB, 61 views)
Attached Files
File Type: zip FolderImages.zip (68.6 KB, 79 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 05-11-2017, 03:27 PM   #12
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 823
Thanks: 4
Thanked 194 Times in 180 Posts
static will become famous soon enough static will become famous soon enough
Re: How to display 10 Images horizontally on a continuous form using vba

Quote:
Originally Posted by ridders View Post
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.

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.

@Exi - in your first post you said you could post an example. If you still need help with this I suggest you do that.
static is offline   Reply With Quote
Old 05-11-2017, 07:00 PM   #13
Exi
Newly Registered User
 
Join Date: May 2017
Location: Perth Western Australia
Posts: 23
Thanks: 4
Thanked 0 Times in 0 Posts
Exi is on a distinguished road
Re: How to display 10 Images horizontally on a continuous form using vba

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 is offline   Reply With Quote
Old 05-11-2017, 07:08 PM   #14
Exi
Newly Registered User
 
Join Date: May 2017
Location: Perth Western Australia
Posts: 23
Thanks: 4
Thanked 0 Times in 0 Posts
Exi is on a distinguished road
Re: How to display 10 Images horizontally on a continuous form using vba

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.
Exi is offline   Reply With Quote
Old 05-12-2017, 01:13 AM   #15
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,081
Thanks: 110
Thanked 2,738 Times in 2,500 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: How to display 10 Images horizontally on a continuous form using vba

Hi Static

Quote:
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:

Code:
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>" '"&nbsp;"
            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
Attached Images
File Type: jpg FolderImagesWB.jpg (101.5 KB, 41 views)
File Type: jpg FolderImagesWBPreview.jpg (94.8 KB, 30 views)
File Type: png Help&SupportFormWB.PNG (91.5 KB, 53 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Images (Attachments) To Show Horizontally Madmart1gan Reports 0 08-24-2015 04:07 PM
Creating a Continuous Form that Continues Horizontally as well as Vertically M_S_Jones Forms 3 05-19-2009 04:58 AM
Display images on a continuous form D.W. Schingenga Modules & VBA 0 10-04-2007 04:16 AM
Displaying Continuous Forms Horizontally zakster Forms 6 09-05-2007 09:00 PM
Images in continuous form mchow Forms 2 06-24-2003 02:36 AM




All times are GMT -8. The time now is 06:41 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World