Embedded images in report-records while using SQL in recordsource

[Nico90210]

Hi, I've got a strange issue where i've been searching for some time without finding a good answer:



I have a database with embedded illustrations which I use to make sparepart lists. The records are all retrieved and nicely ordered inside a report.
In the past worked a lot with queries and this went just fine. Now recently I started to work with VBA to automate and speed up some things.
Now I make reports via SQL with the correct records (just like i had with the integrated queries before in the recordsource), however I noticed, the images are not visible in the reports

I narrowed down and figured out the following:

When I link the recordsource of an (illustrated) report to a query inside the report's properties, the images in my report (which are embedded images per record) are visible.
When however, I use an SQL statement inside the recordsource, which is exactly corresponding to the queries I used before, the images are NOT visible in my report

Does somebody knows what I should do to obtain my images in my reports especially when I want to work with SQL instead of queries (inside my report?)
I really would like to work with SQL as can easily create the correct statements/code in vba...

Thank you!

[June7]

Are you using VBA to set the RecordSource property of a report? Why?

[Nico90210]

Are you using VBA to set the RecordSource property of a report? Why?

I have around 200 reports to make (200 sparepart lists) with different data and images. I have made a script that can fully automate this saving me almost a week of time :-)
So instead I made 1 generic report and via VBA loop and modifying the recordsouce via an SQL I can retrieve the correct data.

sqlGenerateSparepartlist = "SELECT * FROM QUERY_UNIFIED_SPAREPARTLISTS WHERE (((QUERY_UNIFIED_SPAREPARTLISTS.[Units List])=55 Or (QUERY_UNIFIED_SPAREPARTLISTS.[Units List])=56) AND ((QUERY_UNIFIED_SPAREPARTLISTS.Mutoh)=Yes));"

DoCmd.OpenReport "SPL_machinetype_EUR_xxxxxxxx_blauw_ILLUSTRATE D", acViewReport 'Output generic report
'Reports![SPL_machinetype_EUR_xxxxxxxx_blauw_ILLUSTRATED].RecordSource = sqlGenerateSparepartlist 'Execute SQL query for putting correct information into report
'DoCmd.OpenReport "SPL_machinetype_EUR_xxxxxxxx_blauw_ILLUSTRATE D", acViewPreview 'Output generic report
'DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename, False
'DoCmd.Close acReport, "SPL_machinetype_EUR_xxxxxxxx_blauw_ILLUSTRATE D"

With the test I did below I have narrowed down the issue that something is happening wrong inside the report:
Now, I tried to put an embedded query inside my report 'manually' in the RecordSource property, and in this case all the pictures are visible on my report.
When I however tried to paste the SQL statement inside the RecordSource property, the pictures won't show up inside the report.

Somehow, it seems to mee, the picture field doesn't get bound to the database anymore when I use an SQL statement instead...?

[June7]

What exactly is changed in the query? If it is only the WHERE clause, try using the WHERE CONDITION argument of the DoCmd.OpenReport to pass filter criteria to the report. The base RecordSource remains unchanged.

[Nico90210]

What exactly is changed in the query? If it is only the WHERE clause, try using the WHERE CONDITION argument of the DoCmd.OpenReport to pass filter criteria to the report. The base RecordSource remains unchanged.

Hi

The variable data is built of the following string where the numbers (as well numbers, but also amount of unit list) and the last part yes/no varies.
((QUERY_UNIFIED_SPAREPARTLISTS.[Units List])=55 Or (QUERY_UNIFIED_SPAREPARTLISTS.[Units List])=56) AND ((QUERY_UNIFIED_SPAREPARTLISTS.Mutoh)=Yes))

I managed to use the filter functionality you proposed above in the docmd.openreport command and dropped the SQL execution/recordsource chang. Therefore it keeps the recordsource defined in the report (including the pictures properly).

This solved my problem, thanks for the reply and the hint !