Photo's in Access Report

[NewDirections]

I have developed a Access 2000 database to run my business. I use it to track products, Sales, Customers, format Newsletters (select products) for mailing, and etc. What I need to do is add photos of my products in my newsletter report. The photo's are stored in a directory on my network under a grouping by type, and a jpg format. I have been able to add photos to my product form page, but I am not sure how to update the report so that it shows the proper photo. The code that I am using in the form page is:

Private Sub Form_AfterUpdate()
On Error Resume Next
Me![ImageNewLetter].Picture = Me![Photo Location] & Me![FileName]
Me![ImageNewLetter] = Me![Photo Location] & Me![FileName]


End Sub

And this seems to work well on my forms. I tried help at Microsoft, but the code example just got me lost more. If there is any one out there that can set me in the correct direction please let contact me.

Len
New Directions

[Jim Doherty]

I have developed a Access 2000 database to run my business. I use it to track products, Sales, Customers, format Newsletters (select products) for mailing, and etc. What I need to do is add photos of my products in my newsletter report. The photo's are stored in a directory on my network under a grouping by type, and a jpg format. I have been able to add photos to my product form page, but I am not sure how to update the report so that it shows the proper photo. The code that I am using in the form page is:

Private Sub Form_AfterUpdate()
On Error Resume Next
Me![ImageNewLetter].Picture = Me![Photo Location] & Me![FileName]
Me![ImageNewLetter] = Me![Photo Location] & Me![FileName]


End Sub

And this seems to work well on my forms. I tried help at Microsoft, but the code example just got me lost more. If there is any one out there that can set me in the correct direction please let contact me.

Len
New Directions

If your form is not based on a query may I suggest that you make it so? you can create a column in the query grid made up of a concatenation of the [Photo Location and FileName fields to make a column called FilePath
like this:

FilePath: [Photo Location] &" "& [FileName]

You base your report on the same query the fully formed filepath then becomes a field that the Picture Property of your image frame in the report can identify with and thus return the image to the control.

You might want to reconsider however a slight design issue with your DB in that you really only need to store a single reference to the image ie: C:\Products\images\glass_fish.jpg in a 'single' field called Filepath in your table.

As it is you have to rely on something being in 'two' fields in order to display your image and any image display would fail including the query above method if by chance you had a data for filename but none in location for whatever reason and vice versa.

This single field approach is the usual standard for handling images and unless you have any other reason for so having two fields namely 'location' AND 'filename' I'm sure one of those fields could be binned leaving your table more optimum rather than having a field it doesnt actually need.

Even if you thought... well I need the filename for other things, it is quite easy to extract off the filename from a fullpath by identifying the last backslash in a filepath string and parsing it in effect to an additional column in the same query

Hope this helps you

Jim

[NewDirections]

Jim: I would like to thank you for your reply. My form and report are based on the query's of a same table. The location in a table lookup and the filename is the only field that I have to input. I see that a single reference in most cases would work better, but since I am using the location field for other items it seem like it is easier to just input the filename that I wish.

There are really two difference reports that I am trying to get photo on.

Let me give you a little background: From a form I select (check button) which items that I want to add to my newsletter. I run a query based on that check mark so that I know which products to list on my newsletter. In the past I only showed the information (text) with out a photo. What I am trying to do is print the same report without photo and run a 2nd query and report (in label format) with just the photo's, Part No., & Part Name. The reason is the printing cost and mailing cost. I can then print my pages in both black and white and color when needed. What I can't get to work is the correct code to bring up these pictures.

I also have a second part of my report that I brings up select items in the header section of my newsletter (Used for sale items). What I did last time was embed the photo of the items directory into the report (changing it from sending to sending based on what Item that I put on sale).

Does any of this make sense? I am not sure what the code needs to look like

Len

[Jim Doherty]

Jim: I would like to thank you for your reply. My form and report are based on the query's of a same table. The location in a table lookup and the filename is the only field that I have to input. I see that a single reference in most cases would work better, but since I am using the location field for other items it seem like it is easier to just input the filename that I wish.

There are really two difference reports that I am trying to get photo on.

Let me give you a little background: From a form I select (check button) which items that I want to add to my newsletter. I run a query based on that check mark so that I know which products to list on my newsletter. In the past I only showed the information (text) with out a photo. What I am trying to do is print the same report without photo and run a 2nd query and report (in label format) with just the photo's, Part No., & Part Name. The reason is the printing cost and mailing cost. I can then print my pages in both black and white and color when needed. What I can't get to work is the correct code to bring up these pictures.

I also have a second part of my report that I brings up select items in the header section of my newsletter (Used for sale items). What I did last time was embed the photo of the items directory into the report (changing it from sending to sending based on what Item that I put on sale).

Does any of this make sense? I am not sure what the code needs to look like

Len

OK well I'm not quite what you have there. I'm kind of blind 'this end' not actually knowing exactly the fundamental design of your system.

If the basic aim is to display the photo as part of your reports record then you were not off the mark with your initial post in that you have to assign the .picture property of the image control to the full path from where the photo can be located on disk.

Provided you have the filepath as an actual part of your main record and by that I mean the location field and filename field then concatenating them together as you did for the form will perform the same for the report you merely have to put the code in the 'on format' event of the 'detail' section for the report

Reports!ReportName![ImageNewLetter].Picture = Reports!ReportName![Photo Location] & Reports!ReportName![FileName]

Hope this helps

Jim

[NewDirections]

Thank let me work with this and I will let you know how I am doing. again thank it nice to talk to someone who know what he is doing

Len

[NewDirections]

Tried inputing the code (in section on open and on activate) as you out lined and got errors

1st it said can't find report so I added the name of the Report LabelAugustPhotos
then it said it could not find Photo Location so I added the path to the table
same with the File Name


Reports!LabelAugustPhotos![ImageNewLetter].Picture = Reports!LabelAugustPhotos![Products.Photo Location] & Reports!LabelAugustPhotos![Products.FileName]

Now it is coming back with

You entered a expression that has no value

I guest I still don't get it. I have the following fields open on the lable

1) ImageNewsletter (image)
2) Products.ProductID
3) Text2 (Name of the item)
4) Products.Photo Location
5) Products.FileName

[Jim Doherty]

Tried inputing the code (in section on open and on activate) as you out lined and got errors

1st it said can't find report so I added the name of the Report LabelAugustPhotos
then it said it could not find Photo Location so I added the path to the table
same with the File Name


Reports!LabelAugustPhotos![ImageNewLetter].Picture = Reports!LabelAugustPhotos![Products.Photo Location] & Reports!LabelAugustPhotos![Products.FileName]

Now it is coming back with

You entered a expression that has no value

I guest I still don't get it. I have the following fields open on the lable

1) ImageNewsletter (image)
2) Products.ProductID
3) Text2 (Name of the item)
4) Products.Photo Location
5) Products.FileName

Len,

If the list you give there is your fieldnames then you have INVALID field names being presented in your report ie fieldnames should not be using a fullstop (period) you need to change them alias them or something in your underlying query else it will not work.

I know in essence that what you have been trying to make work there from the result of my last post only mirrors what I posted back to you and in essence I was wrong to do that, but because I was merely trying to get across the 'principle' of the thing based on what you have I didnt go into the basics of database design and that is to say this...... field namings should have a naming convention and there should be no spaces unwanted syntax like periods slashes etc which of course is what is in the command you have been working with FROM the result sent by my last post . So blame me on that one I put my hands up because what I have done there is add to the pile of errors you already have.

But as importantly you are not entering the code in the correct place either. In my first post I mention the ON FORMAT event you mention the ON OPEN and ON ACTIVATE. Neither the ON OPEN or ON ACTIVATE are relevant to this because no records are formatted at the point that the report is opened or activated it is only when the records forming part of the DETAIL section are trundled through by the report does the updating of the image control become relevant and that is when the DETAIL section gets FORMATTED so it is only the ON FORMAT event you need to work with

By entering the command you have into the reports ON OPEN AND ON ACTIVATE events this will cause Access to throw errors about not knowing this field or that field because it doesn't indeed know until it actually gets to FORMAT them if you understand me. This whole sequence if you like is an order of events that must take place in sequence and in order. Have a look at 'form events' in help it will give you the idea as to the order of them..... the same in principle exists for reports.

And as for a man who knows what he's doing that may well be the case but I only consider that for myself, if I steer you in the RIGHT direction and if thus far is anything to go by, I would have lost a few shillings for myself in the PR dept if I had been you at your end ......LOL

OK.....so let us assume that you have aliased your fieldnames so that you have fields with the names as this

1) ImageNewsletter
2) ProductID
3) Text2
4) PhotoLocation

then the below WILL absolutely work

Reports!LabelAugustPhotos![ImageNewLetter].Picture = Reports!LabelAugustPhotos![PhotoLocation] & Reports!LabelAugustPhotos![FileName]


if you have the below (notice the space in location):

1) ImageNewsletter
2) ProductID
3) Text2
4) Photo Location

then the below will STILL absolutely work

Reports!LabelAugustPhotos![ImageNewLetter].Picture = Reports!LabelAugustPhotos![Photo Location] & Reports!LabelAugustPhotos![FileName]

BUT when you have spaces like that you are obliged to wrap the name of the field in square brackets and extra unnecessary step you might think and that would be true because what Access is saying unto itself is....... "Hey I don't like this!! I need to know where the space is that you put in there, therefore I am going to wrap the whole string after the last exclamation mark in brackets so I know what I am working with here!"

if you have the below (notice NO space in location):

1) ImageNewsletter
2) ProductID
3) Text2
4) PhotoLocation

then the following will work

Reports!LabelAugustPhotos!ImageNewLetter.Picture = Reports!LabelAugustPhotos!PhotoLocation & Reports!LabelAugustPhotos!FileName

And to cap it ALL and possibly add to the confusion even more for you to consider

The reports knows the name of itself so you could actually reduce all the text you have to type there by simply introducing the keyword ME on the occasions where the report, or form for that matter is referring to itself

So going on that principle the folowing will also work:

Me!ImageNewLetter.Picture = Me!LabelAugustPhotos!PhotoLocation & Me!LabelAugustPhotos!FileName

I apologise for 'adding' to any problem rather than taking a little more time to explain fully. Sometimes we have to assume certain things and presume outcomes and in this case I assumed wrongly and presumed too much

I hope you get your result this time (unfortunately I am going on vacation for a week in Cyprus as of today so may not see any post back from you but I will keep this threaded on my list to ensure you get a resolution

Regards

Jim

[NewDirections]

Jim:

I would like to thank you for all your help and I hope you enjoy your vacation in Cyprus (Wish I was going with you, Sounds like a lot of fun). Back to the problem.
Thank to you there is NO LONGER A PROBLEM. I got the reports working the way that I wanted them to. Now all I need to do is compile my newsletter, get it printed, introduce my customers to the new improved format and and mail it.

Again I would like to thank you. I been working on this problem for over 3 week and without your help I would still have the problem. Also thanks for the lesson about Access it will help in the future.

Len Bartz



Regards

Jim