Linking to a picture

[Leesha]

It does when I open it before making any changes. However when I change the [imgPicture] box on the report to have the dimensions of 3.85 x 4.125 the picture gets cropped. I tried 4.125 x 3.85 just for the heck of it but the same thing happens.

[HansV]

Sorry, I cannot explain that.

[Leesha]

OK, I'll keeping testing. Thanks!

[Leesha]

Found it! I reset the column printing settings and that did it.

Thanks again for everything,
Leesha

[HansV]

Good to hear that!

[Leesha]

Hi Hans,
First and most important, this code is working great and the end user is thrilled! The only glitch they are running into is that after about 21 pages or 84 pictures the report begins to randomly leave out pictures resulting in a blank space, and then eventually prints blank pages. I've already verified that the jpgs are in fact in the file location, are the correct size and in the correct format. I'm thinking that after 21 pages it is exceeding Access's resources. I'm wondering if there is a way in code to make subesequent reports open every time the report hits 20 pages, 40 pages, 60 pages etc. There are times where they could be printing in excess of 500 labels.

Thanks,
Leesha

[HansV]

That looks like a resource problem indeed. You could open the report repeatedly, each time with a WhereCondition to select the next batch of labels. With the repetition of identical labels, it is a bit more difficult to determine where a batch should end.

[Leesha]

My goal is to automate as much as possible for them. Wracking my brains on approaches without of no knowledge of how to code it :-)

Since the query that is bound to the report has a Quantity field, is it possible, in code, to have the subsequent reports, if needed, open everytime the sum of the quantity of hits 80 labels?

Leesha

[HansV]

I'll look at it, but don't expect a reply immediately...

[Leesha]

NP! I'm working on it too (don't laugh too hard).

Leesha

[HansV]

The attached version contains a form frmOpenReport with a command button to open the report in batches.
The report is now based on a table tblTemp; this table has the data plus a sequence number.
The code behind the command button first clears tblTemp, then fills it.
Next, the report is printed repeatedly, each time with (at most) lngBatchSize labels.
This constant is currently set to 60 (i.e. 15 pages of labels).

x1294.png

If this works well, you can experiment with increasing lngBatchSize to 80 (i.e. 20 pages).

ImageDemoLeesha.zip

[Leesha]

Thanks Hans! I can't wait to take a look at it and play around and as always, learn something new!!

Leesha

[Leesha]

Hi Hans,

That works perfectly even with 80 pictures. It's even so much faster than when I was putting in 80 PLU's at a time vs all of them! My end user with will be simply tickled!

Thanks again,
Leesha

[johnwatkins35]

i have down loaded this database. What if i want to import them into another database how do i do that? when i do import it this is what happens. I placed a pic.

[HansV]

Welcome to EileensLounge!

Perhaps OPENFILENAME has been declared twice in your code? Do a search across the project for OPENFILENAME.

[johnwatkins35]

thanks Hansv

Option Compare Database
Option Explicit

Private Sub cmdClose_Click()
On Error Resume Next
DoCmd.Close acForm, Me.Name
End Sub

Private Sub cmdErasePic_Click()
If Not IsNull([PicFile]) Then
If MsgBox("The image will be removed from this record. Are you sure?", vbYesNo + vbQuestion) = vbYes Then
[imgPicture].Picture = ""
[PicFile] = Null
SysCmd acSysCmdClearStatus
End If
End If
End Sub

Private Sub cmdInsertPic_Click()
Dim OFN As OPENFILENAME
On Error GoTo Err_cmdInsertPic_Click

' Set options for dialog box.
With OFN
.lpstrTitle = "Images"
If Not IsNull([PicFile]) Then .lpstrFile = [PicFile]
.flags = &H1804 ' OFN_FileMustExist + OFN_PathMustExist + OFN_HideReadOnly
.lpstrFilter = MakeFilterString("Image files (*.bmp;*.gif;*.jpg;*.wmf)", "*.bmp;*.gif;*.jpg;*.wmf", _
"All files (*.*)", "*.*")
End With

If OpenDialog(OFN) Then
[PicFile] = OFN.lpstrFile
[imgPicture].Picture = [PicFile]
SysCmd acSysCmdSetStatus, "Afbeelding: '" & [PicFile] & "'."
End If
Exit Sub

Err_cmdInsertPic_Click:
MsgBox Err.Description, vbExclamation
End Sub

Private Sub cmdPreview_Click()
On Error GoTo HandleErr

If IsNull([ItemID]) Then
MsgBox "There is no data for this report. Canceling report...", vbInformation
Else
RunCommand acCmdSaveRecord
DoCmd.OpenReport "rptInventory", acPreview, , "[ItemID] = " & [ItemID]
End If
Exit Sub

HandleErr:
MsgBox Err.Description, vbExclamation
End Sub

Private Sub Form_Current()
On Error GoTo HandleErr
If Not IsNull([PicFile]) Then
[imgPicture].Picture = [PicFile]
SysCmd acSysCmdSetStatus, "Image: '" & [PicFile] & "'."
Else
[imgPicture].Picture = ""
SysCmd acSysCmdClearStatus
End If
Exit Sub

HandleErr:
If Err = 2220 Then
[imgPicture].Picture = ""
SysCmd acSysCmdSetStatus, "Can't open image: '" & [PicFile] & "'"
Else
MsgBox Err.Description, vbExclamation
End If
End Sub

[johnwatkins35]

so it only shows it one time i posted it. sorry i used your code. but i was just curious what was going on with it.

[HansV]

I'd search other modules too - I still suspect that you have

Public Type OpenFileName

twice in the code in your database.

[mwal]

Dear Hans,

I have checked the sample DB. I have a similar problem in my own database and I'm wondering if it is possible the output is saved to pdf in a new directory if it does not exists? If the output is more pdf's maybe they can be numbered?
I use the folowing code for generating a pdf:

Code: Select all

Me.Repaint

Dim MyFilter As String
Dim MyPath As String
Dim MyFilename As String

MyFilter = Me!DesignID


If Len(Dir("Q:\000_CE\", vbDirectory)) = 0 Then
   MkDir "Q:\000_CE\"
End If
 
MyFilter = Me!DesignID

If IsNull(Forms!Design!Designselectaproduct.Form.[SerialNumber]) Then

'Let's print and save. Once you see it works, you can change True to False so that the file created is not opened after completion.
DoCmd.OpenReport "ReportName", acViewPreview, , MyFilter


'Let's close our previewed report
'DoCmd.Close acReport, "ReportName"


Else
MyFilter = Me!DesignID
MyPath = "Q:\000_CE\"
MyFilename = " - Rapport".pdf"

'Let's print and save. Once you see it works, you can change True to False so that the file created is not opened after completion.
DoCmd.OpenReport "ReportName", acViewPreview, , MyFilter
DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename, False

'Let's close our previewed report
'DoCmd.Close acReport, "ReportName"

I'll hope you can help me out with this

[HansV]

Welcome to Eileen's Lounge!

I'm not sure where the code that you posted would be used - in the sample database, there is no export to PDF as far as I can tell.