Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
Old 10-27-2013, 05:23 AM   #1
Justin.
Newly Registered User
 
Join Date: Oct 2013
Posts: 4
Thanks: 6
Thanked 0 Times in 0 Posts
Justin. is on a distinguished road
Arrow Repeat an Image by Number of Time in Access Report

Hi Everyone.

This is my first post, therefore, I would like to take this opportunity to say hello to everyone here. And in advance thank anyone who could help me with this problem.

I am creating a simple report. I would like to add an image (already done) but repeat the number of times it appears by a numerical value i.e. [Image1] x [Quantity] (field value) so that the image repeats the number of times I require per record is this possible?

Thanks again in advance.

(I have attached an image to visually give an idea of what i mean)

Justin.
Attached Images
File Type: png Image_Repeat.png (99.1 KB, 117 views)

Justin. is offline   Reply With Quote
Old 10-27-2013, 07:41 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,247
Thanks: 13
Thanked 4,102 Times in 4,034 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: Repeat an Image by Number of Time in Access Report

Welcome Justin. My first thought is creating multiple image controls and making them hidden/visible depending on the quantity. I'd do that with a loop of the controls and the Tag property to isolate the image controls.
__________________
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
The Following User Says Thank You to pbaldy For This Useful Post:
Justin. (10-30-2013)
Old 10-27-2013, 08:31 AM   #3
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Repeat an Image by Number of Time in Access Report

Quote:
Originally Posted by Justin. View Post
I am creating a simple report. I would like to add an image (already done) but repeat the number of times it appears by a numerical value i.e. [Image1] x [Quantity] (field value) so that the image repeats the number of times I require per record is this possible?
Is it the same image there always is shown (for the same record) or does it change?
And if the record change is it the same image as in then previous record?
Should the image stays under each other?
My first thought was on a subreport.

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
The Following User Says Thank You to JHB For This Useful Post:
Justin. (10-30-2013)
Old 10-27-2013, 11:57 PM   #4
MLUCKHAM
Newly Registered User
 
Join Date: Jul 2013
Posts: 89
Thanks: 3
Thanked 8 Times in 8 Posts
MLUCKHAM is on a distinguished road
Re: Repeat an Image by Number of Time in Access Report

I would use the old cross join sql statement. So, have a table which has your image loaded once. This is then displayed in a report in the detail section using an image control. Then have a "temporary" table called "_NumberCopies" which you create in your database with one field. Call it RepeatRow. Before your report runs you need to: -

Delete every row in _NumberCopies.
Insert a row for the number of image copies you want

Do Until x <= NumberCopies
currentdb.execute "insert into _NumberCopies (RepeatRow) VALUES(1)"
x = x + 1
loop

Your report query needs to use what in SQL Server is called a cross join.

SELECT IT.ImageField FROM ImageTable as IT, _NumberCopies

Because _NumberCopies contains x number of rows you will get x images repeated in your report. If _NumberCopies contains 1 row you will get 1 image on your report.

There are other options, but this would be my solution....
MLUCKHAM is offline   Reply With Quote
The Following User Says Thank You to MLUCKHAM For This Useful Post:
Justin. (10-30-2013)
Old 10-28-2013, 01:15 AM   #5
Mihail
Newly Registered User
 
Join Date: Jan 2011
Location: Romania
Posts: 2,373
Thanks: 90
Thanked 368 Times in 363 Posts
Mihail is on a distinguished road
Re: Repeat an Image by Number of Time in Access Report

One more possible solution HERE.
Use a loop in order to create as much image controls as you need and to position each of them.

But... are you very sure you wish this ?
Are you very sure that the quantity value stay (remain) into reasonable limits ?
I think that is not reasonable to have more than 20 pics here, but this is your choice.

Cheers !
__________________
Mihail
I'm looking for a job !
Very good technical skills on Metal Sheets:
Design (SolidWorks), Programming/Operating machines (Cutting, Bending, Punching)
Mazatrol for turning machines
Mihail is offline   Reply With Quote
The Following User Says Thank You to Mihail For This Useful Post:
Justin. (10-30-2013)
Old 10-30-2013, 05:25 AM   #6
Justin.
Newly Registered User
 
Join Date: Oct 2013
Posts: 4
Thanks: 6
Thanked 0 Times in 0 Posts
Justin. is on a distinguished road
Re: Repeat an Image by Number of Time in Access Report

Hi Guys,

Thanks Paul, for the initial response and to you all. I'm sorry I didn't reply sooner we have an internet outage after a storm took out our line.

Thanks for all your responses I think we are all on the same page.

Basically the image is just one image that needs to be repeated across the report I linked i.e. [Item Barcode] [Item Barcode] [Item Barcode] [Item Barcode] etc.. based upon a numerical field in the table which is quantity.

The quantity of items doesn't go above ~16 but varies from 1-16 per record. The image file I created matches, in size, our inventory control barcodes. I could just leave a space under the record to stick them on manually. But I thought repeating the image placeholder based on quantity would look more professional and make the acceptance procedure of the equipment easier in the field for the guys sticking them on. And in turn later scanning them into the inventory database.

Is there a way to directly address the image control as per Pauls recommendation. I think due to the small quantities of items that maybe the way to go?

Kindest Regards and Thanks again.

Justin.
Justin. is offline   Reply With Quote
Old 10-30-2013, 07:11 AM   #7
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,247
Thanks: 13
Thanked 4,102 Times in 4,034 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: Repeat an Image by Number of Time in Access Report

Try creating the max number and name them something consistent, like image1, image2, etc. Make them all hidden by default, then in the detail format event:

Code:
Dim x As Integer

For x = 1 To Me.Quantity
  Me("Image" & x).Visible = True
Next x
Hopefully setting the can shrink properties of the controls and section will let it shrink when controls are hidden.

__________________
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 10-31-2013, 03:00 AM   #8
Justin.
Newly Registered User
 
Join Date: Oct 2013
Posts: 4
Thanks: 6
Thanked 0 Times in 0 Posts
Justin. is on a distinguished road
Re: Repeat an Image by Number of Time in Access Report

Hi Paul,

Thanks for that.

I created the loop you suggested and managed to make the image fields visible with the property tag you suggested. But only when I define the loop as follows to test it:

Code:
Dim x As Integer

For x = 1 To 16

Me("image" & x).Visible = True

Next x
I can't seem to get "Me.Quantity" to retrieve the values. I only get a 2424 runtime error.

I have also tried get the values directly using "Me.Quantity.Value" to no avail. Is there any ideas you have to get around this?

Kind Regards

Justin.
Justin. is offline   Reply With Quote
Old 10-31-2013, 05:40 AM   #9
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,247
Thanks: 13
Thanked 4,102 Times in 4,034 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: Repeat an Image by Number of Time in Access Report

Is that the name of the textbox containing the quantity? Also, where is the code?
__________________
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 10-31-2013, 06:58 AM   #10
Justin.
Newly Registered User
 
Join Date: Oct 2013
Posts: 4
Thanks: 6
Thanked 0 Times in 0 Posts
Justin. is on a distinguished road
Re: Repeat an Image by Number of Time in Access Report

Hi Paul,

I'm trying with just this block on the Report_Open control. When I use "On Click". I am able to click and run the loop (with no error message). I.e. click on a field with 4 quantity and 4 images are Visible.

"Quantity" is the name of the text field in the report yes.

Do i need to create a MoveNext statement to move through the records?

(I have not done any coding in a long time still refreshing please forgive me.)

Code:
Private Sub Report_Open(Cancel As Integer)

Dim x As Integer

For x = 1 To Me.Quantity

Me("image" & x).Visible = True

Next x

End Sub
This snippet allows me to retrieve all of the records in the quantity field:

Code:
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Site1")

For i = 0 To rs.RecordCount - 1
    Debug.Print rs.Fields("Quantity")
    rs.MoveNext
Next i



rs.Close
Set rs = Nothing
db.Close
Justin. is offline   Reply With Quote
Old 10-31-2013, 07:12 AM   #11
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,247
Thanks: 13
Thanked 4,102 Times in 4,034 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: Repeat an Image by Number of Time in Access Report

You missed:

Quote:
Originally Posted by pbaldy View Post
in the detail format event
The data isn't available in the open event. You may need to hide the images greater than the quantity in each record.

__________________
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
The Following User Says Thank You to pbaldy For This Useful Post:
Justin. (10-31-2013)
Reply

Tags
access , image , repeat , report

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating a unique sequential number/reference number every time a report is printed barbados2010 General 1 02-15-2013 07:36 AM
Repeat detail set number of times voodoort Reports 0 04-19-2011 05:45 AM
Repeat Number Check Error Johnthelleader Forms 0 04-19-2007 12:51 AM
to embedd image in image control using imagelist at run time sivakumari General 2 02-09-2005 06:08 AM
Creating report from a query in which number of fields vary from time to time Nancy Reports 2 11-01-2000 01:28 PM




All times are GMT -8. The time now is 01:08 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