Linking to a picture

[Leesha]

Hi,
I have a user who prints labels for various food products. She wants to be able to store the label master jpg in a folder on the server. They she would like to be able to select the labels that will print to the report. There would be 4 labels per report. Since the labels will change and there will be many versions, she would like to have 1 report that will print whatever labels she chooses. My thought was to list the labels with their corresponding links in a table that they can choose from. I'm not sure if this would be the best approach. Also, Is is possible to put a link to a jpg in a report that will change based on the item chosen and if so how do I do this. Finally, to make matters worse, they are using Access 97.

Thanks!
Alicia

[HansV]

You could store the path and filename of the pictures in a text field in a table, and use a bit of code to display the pictures in an Image control on a form and report.
I have attached a sample database in Access 97 format.

ImageDemo.zip

[Leesha]

Hi Hans,

Thanks for the example. This will work when they want to select 1 photo at a time. There will be times when they have hundreds of files to run, some of them in more than 1 quantity. The table name is AHEB and the fields are [id], [description],[PLU],[quantity]. The [plu] field is field that has the link to the various jpegs. So if they go to print the report, the labels from the tables will all print, based on the quantity. If a specific PLU needs 3 labels, all three would print before going on to the next label. Labels should print 4 to a page. (I figured the page setup would be in a column setup in order to do this but wasn't sure.)

Can this version be done?

Thanks,
Leesha

[HansV]

So if id = 27 has quantity = 3, the report should display/print three identical labels for this id?
What is the highest value you expect in the quantity field?

[Leesha]

Hi Hans,

Yes that is correct. I'm not sure what the maximum value would be and am checking with the user, but for purposes of testing lets say 25. So if ID 25=1, ID 26=2, ID 27 = 3, ID 28 =1 it would print as follows:

ID25, 26 and the first of 27 would be on the first page printed.The remaining 2 for ID 27 and ID 28 would print on page 2.
Thanks
Leesha

[HansV]

I have attached a sample database (in Access 2000 format this time, but you can convert it to Access 97 format).
It uses an auxiliary table tblCount in the qryAHEB query to duplicate the records the desired number of times.

ImageDemo2.zip

[Leesha]

We're getting there! I can so see many uses for this in other things I'm working on as well. Thanks!

RE this one. I can see where they will vent at me for having to individually pick the jpg in the instances where there are hundreds of different labels to do. Is there any way to have the report load the labels from the table without having to use the form to hand select.

They just me this code that someone else wrote for them years ago for a similar project but it doesn't make sense to me and they state is not working. Also, I don't believe it allowing for duplicate labels. This was was on the report event..

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


If IsNull(Me!CODE_TYPE) Then
Me.UNFIImage.Picture = ""
Else
Me.UNFIImage.Picture = "Z:\Graphic Files\UNFI\Printing Graphics\" & Me.CODE_TYPE & ".jpg"
End If

End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
'Static Cnt As Integer
'If Cnt < Forms!frmLabelCriteria!txtNumberOfLabels Then
' Cnt = Cnt + 1
' MsgBox "Cnt := " & Cnt
' Me.NextRecord = True
' Me.PrintSection = True
'
'Else
' MsgBox "Cnt 2 := " & Cnt
' Me.MoveLayout = False
' Me.NextRecord = True
' Me.PrintSection = False
'End If
End Sub


Private Sub Report_Open(Cancel As Integer)
' If Not IsLoaded("frmLabelCriteria") Then
' MsgBox "You Must Run This Report From Label Criteria Form"
' Cancel = True
' End If
End Sub

[HansV]

If all jpg files are in a single folder, and if their file names are somehow related to the records in the AHEB table, the code could fetch the files automatically.
This would work if, for example, the .jpg for the record with ID = 37 was named 37.jpg, or Image37.jpg, or similar.

[Leesha]

For testing purposes I have the files saved to the root of my Cdrive in a folder called Tradelabels. I added the info the table to show the name of the label. I'm sending back the sample table with the info as I'm not sure where to go with it next.

Leesha

[HansV]

There doesn't seem to be a relation between the ID and the PLU, so there is no way to avoid entering the PLUs by hand.
(Or us there such a relationship in the real database?)

In the attached version, I have specified the folder in a public constant in the basFileOpen module:

Public Const strFolder = "C:\TradeLabels\

If you change the path, change the constant accordingly.

ImageDemoLeesha.zip

[Leesha]

Boy am I have a hard time following this one. The ID field is simply the autonumber / unique ID field. Honestly I don't know that the PLU's are unique but now looking at it through your eyes, I will assume that they are. With that being said, how would the code look using the plu field?
Thanks,
Leesha

[HansV]

I think I misunderstood you.

The form is just one way of selecting pictures.

The report can be used by itself.

[Leesha]

Hi Hans,

I finally had some time to get back to this. When I open the report there are no pictures visible. The Product PLU count is correct and it shows up the correct number of times, but there is not photo.

Leesha

[Leesha]

Almost forgot, I did a search on the basfileopen module for "tradelables" so I could be sure I had the path correct but didn't find it.

Leesha

[HansV]

Does the constant strFolder in the basFileOpen module point to the folder containing the photos?

x1271.png

Does the PLU field in the AHEB table contain the correct file names?

[Leesha]

The PLU files in table have the correct names

I don't see the code in your picture. This is the code you sent me:

Option Compare Database
Option Explicit

' Code to display standard "Open File" dialog.

Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (OFN As OPENFILENAME) As Boolean

Private Declare Function GetSaveFileName Lib "comdlg32.dll" Alias _
"GetSaveFileNameA" (OFN As OPENFILENAME) As Boolean

Private Const ALLFILES = "All files"

Function MakeFilterString(ParamArray varFilt() As Variant) As String
' Create filter string.
' Returns "" if there are no arguments.
' Expects an even number of arguments (filter name, extension).
' Adds *.* if the number of arguments is odd.

Dim strFilter As String
Dim intRes As Integer
Dim intNum As Integer

intNum = UBound(varFilt)
If (intNum <> -1) Then
For intRes = 0 To intNum
strFilter = strFilter & varFilt(intRes) & vbNullChar
Next
If intNum Mod 2 = 0 Then
strFilter = strFilter & "*.*" & vbNullChar
End If

strFilter = strFilter & vbNullChar
End If

MakeFilterString = strFilter
End Function

Private Sub InitOFN(OFN As OPENFILENAME)
With OFN
' Initialize fields user doesn't want to know about
.hwndOwner = hWndAccessApp
.hInstance = 0
.lpstrCustomFilter = vbNullString
.nMaxCustFilter = 0
.lpfnHook = 0
.lpTemplateName = 0
.lCustData = 0
.nMaxFile = 511
.lpstrFileTitle = String(512, vbNullChar)
.nMaxFileTitle = 511
.lStructSize = Len(OFN)
' Use default filter if not specified.
If .lpstrFilter = "" Then
.lpstrFilter = MakeFilterString(ALLFILES)
End If
' Pad lpstrFile with null chars.
.lpstrFile = .lpstrFile & String(512 - Len(.lpstrFile), vbNullChar)
End With
End Sub

Function OpenDialog(OFN As OPENFILENAME) As Boolean
Dim intRes As Integer
InitOFN OFN
intRes = GetOpenFileName(OFN)
If intRes Then
' Remove trailing null chars from lpstrFile.
With OFN
.lpstrFile = Left$(.lpstrFile, InStr(.lpstrFile, vbNullChar) - 1)
End With
End If
OpenDialog = intRes
End Function

[HansV]

Then you're using an older version. I'm referring to the database I attached to Post=83740 in this thread.

[Leesha]

Sorry Hans! This worked to perfection!

Thanks

[Leesha]

Hi Hans,

Back again. The problem I'm running into now is that the pictures are getting cropped. I tried all three of the size mode settings but nothing worked. The actual photo sizes are 4.023 x 4.3. I've changed the margins of the report to .25 right and left and there seems to be plenty of space. I didn't see anywhere in the code where the picture size is set so I'm not sure what I'm missing.

Thanks,
Leesha

[HansV]

If you set the Size Mode property to Zoom, the image should be enlarged or shrunk to fit the Image control without distorting it. It works that way for me...
Make sure that the page size of the report is set correctly for your printer.