Repeat an Image by Number of Time in Access Report (1 Viewer)

Justin. · Oct 27, 2013

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.

pbaldy · Oct 27, 2013

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.

JHB · Oct 27, 2013

Justin. said:
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.

MLUCKHAM · Oct 28, 2013

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....

Mihail · Oct 28, 2013

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 !

Justin. · Oct 30, 2013

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.

pbaldy · Oct 30, 2013

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.

Justin. · Oct 31, 2013

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.

pbaldy · Oct 31, 2013

Is that the name of the textbox containing the quantity? Also, where is the code?

Justin. · Oct 31, 2013

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

pbaldy · Oct 31, 2013

You missed:

pbaldy said:
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.

Repeat an Image by Number of Time in Access Report (1 Viewer)

Justin.

New member

Attachments

pbaldy

Wino Moderator

JHB

Have been here a while

MLUCKHAM

Registered User.

Mihail

Registered User.

Justin.

New member

pbaldy

Wino Moderator

Justin.

New member

pbaldy

Wino Moderator

Justin.

New member

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread