can grow / can shrink image control Access 2007 report

**ADezii** · May 14 '10, 06:36 PM

Originally posted by newguytom

What I am wanting to do is create layout drawings of a product that has modular components that can be configured in any configuration. Each image of a modular part is the same width with a variable height so when they are displayed in a report they create a 2D image of the finished product.

The user would simply choose the part that they want in the drawing (via a yes/no data type) and sort the order by a number.

I have check the properties in the report for an image control and I can't seem to be able to find a way to do this like you can with a text box. I was hoping that somebody could point me in the right direction or if anybody would know a VBA code that could accomplish this.

Thank you.

You can dynamically control the Height, Width, Top, and Left Properties of an Image Control for each Record in the Format() Event of the Detail Section of a Report. The following code, based on the Employees Table as the Record Source of a Report, will maintain an Image Control's original dimensions if the Value in the Country Field = 'USA' otherwise it will reduce the Height and Width Properties by 50% while increasing the Left and Top Properties by 50%. Hope this helps.

Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Measurements in Twips where 1,440 Twips = 1 inch
Const conORIG_IMAGE_HT As Single = (1.375 * 1440)
Const conORIG_IMAGE_WD As Single = (1.6667 * 1440)
Const conORIG_IMAGE_LEFT As Single = (1.375 * 1440)
Const conORIG_IMAGE_TOP As Single = (0.5417 * 1440)

With Me![Image1]
  If Me![Country] = "USA" Then
    .Height = conORIG_IMAGE_HT
    .Width = conORIG_IMAGE_WD
    .Left = conORIG_IMAGE_LEFT
    .Top = conORIG_IMAGE_TOP
  Else
    .Height = (conORIG_IMAGE_HT) / 2
    .Width = (conORIG_IMAGE_WD) / 2
    .Left = (conORIG_IMAGE_LEFT * 1.5)
    .Top = (conORIG_IMAGE_TOP * 1.5)
  End If
End With
End Sub

**newguytom** · May 15 '10, 04:35 PM

Hi ADezii,

Thank you for the code, it sounds like it is what i am after. I have tried it however I get an error "Run-time error 438, Object doen't support this property or method" and the debugger stops on " .Height = conORIG_IMAGE_H T".

I am not sure about this, can you shed some light?

Regards

**ADezii** · May 15 '10, 04:38 PM

Originally posted by newguytom

Hi ADezii,

Thank you for the code, it sounds like it is what i am after. I have tried it however I get an error "Run-time error 438, Object doen't support this property or method" and the debugger stops on " .Height = conORIG_IMAGE_H T".

I am not sure about this, can you shed some light?

Regards

Kindly post the code using the appropriate Code Tags.

**newguytom** · May 17 '10, 12:33 AM

First of all, a big thanks to everybody that has contributed so far. Below is the code that I have found that does most of the job

Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If (Me.length) = "0.0265" Then
Me.Image18.Picture = Me.path
Me.Image18.Visible = True
Me.Image18.Height = "285"
Me.Image18.Width = "2000"

Else

If (Me.length) = "0.1325" Then
Me.Image18.Picture = Me.path
Me.Image18.Visible = True
Me.Image18.Height = "2550"
Me.Image18.Width = "2000"

Else

If (Me.length) = "0.2385" Then
Me.Image18.Picture = Me.path
Me.Image18.Visible = True
Me.Image18.Height = "4500"
Me.Image18.Width = "2000"

Else

Me.Image18.Picture = Me.path
Me.Image18.Visible = True
Me.Image18.Height = "285"
Me.Image18.Width = "2000"

End If
End If
End If

End Sub

This works well however if I have multiple modular components that are the same length the above code will only modify the size of the image control for the first one in the table, the second component that is the same length does not size the image control correctly.

I hope somebody can help me with this.

Regards

**ADezii** · May 17 '10, 12:55 AM

Originally posted by newguytom

Hi All,

First of all, a big thanks to everybody that has contributed so far. Below is the code that I have found that does most of the job

Private Sub Detail_Format(C ancel As Integer, FormatCount As Integer)

If (Me.length) = "0.0265" Then
Me.Image18.Pict ure = Me.path
Me.Image18.Visi ble = True
Me.Image18.Heig ht = "285"
Me.Image18.Widt h = "2000"

Else

If (Me.length) = "0.1325" Then
Me.Image18.Pict ure = Me.path
Me.Image18.Visi ble = True
Me.Image18.Heig ht = "2550"
Me.Image18.Widt h = "2000"

Else

If (Me.length) = "0.2385" Then
Me.Image18.Pict ure = Me.path
Me.Image18.Visi ble = True
Me.Image18.Heig ht = "4500"
Me.Image18.Widt h = "2000"

Else

Me.Image18.Pict ure = Me.path
Me.Image18.Visi ble = True
Me.Image18.Heig ht = "285"
Me.Image18.Widt h = "2000"

End If
End If
End If

End Sub

This works well however if I have multiple modular components that are the same length the above code will only modify the size of the image control for the first one in the table, the second component that is the same length does not size the image control correctly.

I hope somebody can help me with this.

Regards

Code:

If (Me.length) =

makes no sense to me, what are you trying to accomplish, and what is Me supposed to represent?

**newguytom** · May 17 '10, 01:13 AM

Sorry, I copied the wrong code, please see below:

Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If [length] = "0.0265" Then
Image18.Picture = [path]
Image18.Visible = True
Image18.Height = "285"
Image18.Width = "2000"

Else

If [length] = "0.1325" Then
Image18.Picture = [path]
Image18.Visible = True
Image18.Height = "2550"
Image18.Width = "2000"

Else

If [length] = "0.2385" Then
Image18.Picture = [path]
Image18.Visible = True
Image18.Height = "4500"
Image18.Width = "2000"

Else

Image18.Picture = [path]
Image18.Visible = True
Image18.Height = "285"
Image18.Width = "2000"

End If
End If
End If

End Sub

I have a table that has two columns, one called path which contains the file path for the image and one called length which contains the physical size of the component and controls the size of the image control.

Attached is what I am getting. As you can see the first image with the length of 0.0265 has the correct size however the second one at the bottom is not correct.

Please let me know what you think.

regards.

Attached Files

example.jpg (4.0 KB, 563 views)

**newguytom** · May 17 '10, 01:41 AM

A better example of what is going on with a border around the image control

Attached Files

example.jpg (4.7 KB, 584 views)

**ADezii** · May 17 '10, 12:43 PM

Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim img As Image

Set img = Me![Image18]
Me![Image18].Picture = Me![Path]

img.Visible = True

Select Case Me![Length]
  Case "0.0265"
    img.Height = 285        'Height & Width expressed
    img.Width = 2000        'in Twips and are not Strings
  Case "0.1325"
    img.Height = 2550
    img.Width = 2000
  Case "0.2385"
    img.Height = 4500
    img.Width = 2000
  Case Else
    img.Height = 285
    img.Width = 2000
End Select
End Sub

**newguytom** · May 17 '10, 09:52 PM

Thanks ADezii for the code however I am still having the same problem. If the larger image comes first (height = 4500) the image control is set at this height for all image records that come after it.

So what I would have to do is sort the images in order of size from smallest to largest however this defeats the purpose as then the images are in the wrong order. I don't know if the image control size can be set for each record as it formats or whether it will only can grow not can shrink.

Regards.

**ADezii** · May 17 '10, 10:18 PM

Originally posted by newguytom

Thanks ADezii for the code however I am still having the same problem. If the larger image comes first (height = 4500) the image control is set at this height for all image records that come after it.

So what I would have to do is sort the images in order of size from smallest to largest however this defeats the purpose as then the images are in the wrong order. I don't know if the image control size can be set for each record as it formats or whether it will only can grow not can shrink.

Regards.

Are you able to Upload the Database, so that I can have a look at it? All that would be needed is the Report and some Sample Data from its Record Source.

**newguytom** · May 17 '10, 10:48 PM

Hi ADezii,

I really appreciate your help however I have found the solution!

I have used:

Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    
If [length] = "0.0265" Then
Image18.Picture = [path]
Image18.Visible = True
Image18.Height = "285"
Image18.Width = "2000"
Me.Section(acDetail).Height = "285"

Else

If [length] = "0.1325" Then
Image18.Picture = [path]
Image18.Visible = True
Image18.Height = "2550"
Image18.Width = "2000"
Me.Section(acDetail).Height = "2550"

Else

If [length] = "0.2385" Then
Image18.Picture = [path]
Image18.Visible = True
Image18.Height = "4500"
Image18.Width = "2000"
Me.Section(acDetail).Height = "4500"

Else

Image18.Picture = [path]
Image18.Visible = True
Image18.Height = "4500"
Image18.Width = "2000"
Me.Section(acDetail).Height = "4500"


End If
End If
End If

End Sub

The extra piece of code shrinks the area to the size of the image if it is smaller than the one that came before it.

My next challenge to convert the following data into a table by some sort of append. Below will be what the table will hold:

Type Quantity

part1 1
part2 3
part6 2
part8 1

So this table would say there are 7 parts

I need to append it somehow to a new table so the data looks like:

Type Quantity

part1 1
part2 1
part2 1
part2 1
part6 1
part6 1
part8 1

This table also says that there are 7 parts however there is a line for each part.

Do you have any ideas?

Regards.

**ADezii** · May 17 '10, 11:52 PM

ASSUMPTIONS:

Your Table name is tblOriginal with the following Fields:
- [Type] {STRING}
- [Quantity] {LONG]
Create a New Table named tblNew with the following Fields:
- [Type] {STRING}
- [Quantity] {LONG]

Code:

Dim MyDB As DAO.Database
Dim rstOrig As DAO.Recordset
Dim rstNew As DAO.Recordset
Dim lngCtr As Long

Set MyDB = CurrentDb
Set rstOrig = MyDB.OpenRecordset("tblOriginal", dbOpenForwardOnly)

'Clear the Results Table (tblNew)
CurrentDb.Execute "DELETE * FROM tblNew;", dbFailOnError

With rstOrig
  Do While Not .EOF
    For lngCtr = 1 To ![Quantity]
      CurrentDb.Execute "INSERT INTO tblNew ([Type], [Quantity]) VALUES ('" & ![Type] & _
                        "', 1);", dbFailOnError
    Next
      .MoveNext
  Loop
End With

rstOrig.Close
Set rstOrig = Nothing

**newguytom** · May 18 '10, 12:00 AM

Hi ADezii,

Once again thanks for the code.

Please excuse my inexperience however were would I put this code? This is the first time that I have attempted something like this.

Regards.

**ADezii** · May 18 '10, 12:22 AM

Originally posted by newguytom

Hi ADezii,

Once again thanks for the code.

Please excuse my inexperience however were would I put this code? This is the first time that I have attempted something like this.

Regards.

Because of the nature of the code, it can be placed anywhere: in the Click() Event of a Command Button, Open() Event of a Form. etc...

can grow / can shrink image control Access 2007 report

can grow / can shrink image control Access 2007 report

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment