[X]My Assistant

[ciapul12]

Hi All,
I've got a rather annoying problem. my report displays the same image in every record and despite my attempts to resolve this issue I have yet to find solution that would work.
Everything was fine until I've changed the way I store images. In the past I had an attachment field and all images were physically stored in database which posed an issue with max capacity of database as it grew to over 2gb pretty quickly.
I have decided to store all images on a network drive and only to store path to each image in database.

My report have been also modified to accommodate this and they all work apart from this weekly report which is slightly different because I have 4 columns (see attached photos for reference)

My unbounded image frame is linked to a field called ImagePath which contains path to an image on network drive. My report query has a "Is Not Null" condition so in theory any record that has no data in ImageLink field should be excluded from query but as you can see by the photo these records still get included and the same image appears in each one of them...

What am I doing wrong here?
Thanks You in Advance
Dan
 Capture.JPG ( 97.9K )Number of downloads: 9

 Capture2.JPG ( 101.51K )Number of downloads: 4

 Capture3.JPG ( 115.28K )Number of downloads: 2

 Capture4.JPG ( 151.99K )Number of downloads: 6

[arnelgp]

make ImagePath image control Bound by adding ImageLink field as its Control Source.
This post has been edited by arnelgp: Oct 14 2019, 09:33 AM

[ciapul12]

Done that already and still doesn't work...
 Capture5.JPG ( 131.47K )Number of downloads: 3

[ciapul12]

Just run my query and I can see that issues sits with query somewhere as you can clearly see repeated image paths across multiple records. Don't have a clue how to fix this tho lol
 Capture6.JPG ( 339.48K )Number of downloads: 9

[GroverParkGeorge]

Based on the peak we've had from the screen shots, my first inclination would be to want to see the actual TABLE called Responses. I mean the data in it. It looks very much like the image field in it points to the same file for every single record. Is that the case?

[chrismbaylis]

Hey Dan - it looks as though your Data is repeating itself - the change to each new image starts the repeat again - this suggests the table order in the query may be incorrect and is resulting in duplicated (or 1:n) data

[ciapul12]

Hi There,
Data in the table seem to be ok as I have other queries and reports that are based on the same table and these work just fine...
I have no idea as to what's causing this weird behaviour..

Dan

[GroverParkGeorge]

So you DO see different image files in that field for each record when you look at the table directly in datasheet view?

What I might try next is to recreate that query from scratch. It's not unheard for a query to get corrupted somehow.

If that doesn't work, we'll need to actually see some data and the SQL for that query to try to figure out the problem.

[ciapul12]

Hey GroverParkGeorge
I have rebuilt my query but the issue hasn't gone away I'm afraid...

Here's my SQL code:

SELECT qryQuestionnaireResponses.QuestionnaireName, qryQuestionnaireResponses.Comment, qryQuestionnaireResponses.Category, qryQuestionnaireResponses.[Max Score], qryQuestionnaireResponses.[Standard Required], qryQuestionnaireResponses.Score, qryQuestionnaireResponses.Week, ResponseSessions.ResponseSessionID, ResponseSessions.[Audit Area], ResponseSessions.Shift, ResponseSessions.Site, qryQuestionnaireResponses.Score, qryQuestionnaireResponses.Target, ResponseSessions.ResponseSessionDate, Responses.ImageLink
FROM (qryQuestionnaireResponses INNER JOIN ResponseSessions ON qryQuestionnaireResponses.[ResponseSessionID] = ResponseSessions.[ResponseSessionID]) INNER JOIN Responses ON ResponseSessions.ResponseSessionID = Responses.ResponseSessionID
WHERE (((qryQuestionnaireResponses.Week)=[Forms]![frmReports]![Combo3]) AND ((Responses.ImageLink) Is Not Null));

Thank You
Dan

[GroverParkGeorge]

Okay, thanks.

BUt that looks like it's based on still another query. So we're still chasing it down. What does the SQL for THAT query look like? Does IT return the correct paths for each unique image?

Amd. just to be sure, you did confirm that the Responses.ImageLink filed does contain a different path for each image, the image assigned to that different record.

[ciapul12]

Hi,
Yes it does return correct paths..
Here's SQL:

SELECT Questionnaires.QuestionnaireID, Questionnaires.QuestionnaireName, Questions.Question, QuestionnaireQuestions.QuestionIndex, [Responses].[Answer] & (" - "+[Responses].[AnswerText]) AS ResponseAnswer, QuestionnaireAnswers.AnswerIndex, ResponseSessions.ResponseSessionID, ResponseSessions.ResponseSessionDate, Respondents.RespondentID, CanShrinkLines([Firstname] & " " & [Lastname],[AddressLine1],[AddressLine2],[AddressLine3]) AS NameAndAddress, Respondents.TelLandLine, Respondents.TelMobile, Responses.Comment, Questions.Category, Questions.[Max Score], Questions.[Standard Required], Responses.Score, Format([ResponseSessionDate],"ww") AS Week, 4.25 AS Target, ResponseSessions.[Audit Area], ResponseSessions.Shift, ResponseSessions.Site, Responses.ImageLink
FROM (Questionnaires INNER JOIN (Questions INNER JOIN (Answers INNER JOIN (QuestionnaireQuestions INNER JOIN QuestionnaireAnswers ON (QuestionnaireQuestions.QuestionID = QuestionnaireAnswers.QuestionID) AND (QuestionnaireQuestions.QuestionnaireID = QuestionnaireAnswers.QuestionnaireID)) ON (Answers.Answer = QuestionnaireAnswers.Answer) AND (Answers.QuestionID = QuestionnaireAnswers.QuestionID)) ON Questions.QuestionID = Answers.QuestionID) ON Questionnaires.QuestionnaireID = QuestionnaireQuestions.QuestionnaireID) INNER JOIN ((Respondents INNER JOIN ResponseSessions ON Respondents.RespondentID = ResponseSessions.RespondentID) INNER JOIN Responses ON ResponseSessions.ResponseSessionID = Responses.ResponseSessionID) ON (QuestionnaireAnswers.Answer = Responses.Answer) AND (QuestionnaireAnswers.QuestionID = Responses.QuestionID) AND (QuestionnaireAnswers.QuestionnaireID = Responses.QuestionnaireID);

[GroverParkGeorge]

Okay, so in THIS query, which is then incorporated into the second one shown above, Response.ImageLink does return the correct paths (different for each record), but then, in that second query, you return, not this field, but the same field again from Response.ImageLink. I'm going to guess that maybe using this field, in qryQuestionnaireResponses, instead of the second instance as was done in your other query, might give you the correct, unique, image paths.

If that doesn't work, we'll need some sample data to work with.