Form saving several reports in one go (1 Viewer)

[MRose1234]

Hi

Hope you can help me, as I can’t seem to find the answer on-line anywhere.

I currently have a FORM, where I based on a chosen Item number, open a REPORT (based on a QUERY) and safes the REPORT with a prefixed file name, made up of Item number + standard text, as a pdf file.

The VBA in the FORM goes like this:

Private Sub Save_Report_Click()
Dim Item number As String
Item number = Me.Item number

DoCmd.OpenReport "REPORT data sheet page 2", acViewReport
DoCmd.OutputTo acOutputReport, " REPORT data sheet page 2", acFormatPDF, "C:\Data sheets\" & Item number & "_data sheet MG.pdf"
DoCmd.Close acReport, " REPORT data sheet page 2"
DoCmd.Close acForm, "FORM_data sheet", acSavePrompt

End Sub

This currently works perfect, as the QUERY only contains one line with the needed item number and all fields for the report.

BUT I also have a QUERY containing several item numbers / lines, and here I would like to set up a FORM that opens the REPORT, and for each item number/ line in the QUERY, safes a separate file in accordance with the above.

So far I've gotten a REPORT containing ALL the lines/ item numbers. But I need to have several separate pdf files, each saved under correct file name.

I’m aware that I probably need some kind of COUNTER, and a DO LOOP sequence, but can’t figure it out…

Hope you guys can help.

 

[Uncle Gizmo]

Generating Multiple Copies of the Same report with different Criteria

>>> This currently works perfect <<<

No it doesn't ...

Dim Item number As String
Item number = Me.Item number

that don't work for a start...

Please explain? I'm thinking if thats wrong what else is?

Not a good start...

 

[Uncle Gizmo]

Generating Multiple Copies of the Same report with different Criteria

I've done a YouTube - On my Blog HERE:- Generate Multiple Reports

Screenshots Here - On my Blog HERE:- Generate Multiple Reports

 

[MRose1234]

Hi
Thanks a lot for you fast reply.
I get your point, I’m pretty new to Access, learning by trial and error, but your right, I didn’t get the full coding in place. Will be done in the future.

But having tried your code, (currently set up as your test) I get all the way to the Msgbox, but then nothing happens. The report stays visible, and no pdf files are saved.

So what have I missed?

Please see coding below:

Public Sub fRSL_MakeRptWithNumbers_Click()
 
Dim StrSubName As String
Dim strModuleName As String

StrSubName = "fRSL_MakeRptWithNumbers"
strModuleName = "basConstants"


Dim curdb As dao.Database
Dim rsrptnumb As dao.Recordset

Set curdb = CurrentDb

Dim strsql_rsl As String
strsql_rsl = "select tblrptnumbers.ID, tblrptnumbers.fieldrptnumb from tblrptnumbers order by tblrptnumbers.fieldrptnumb"

Dim txtfieldrptnumb As String
Dim strrptname As String
Dim strprefix As String
Dim strsufix As String
Dim strurl As String

strrptname = "REPORT data sheet page 2"
strprefix = "C:\A_Transfer\"
strsufix = " data sheet MG.pdf"

Set rsrptnumb = curdb.OpenRecordset(strsql_rsl, dbOpenForwardOnly)

Do Until rsrptnumb.EOF
txtfieldrptnumb = rsrptnumb!fieldRPTNumb

DoCmd.OpenReport strrptname, acViewReport
strurl = strprefix & txtfieldrptnumb & strsufix
MsgBox " >>> " & strurl
DoCmd.OutputTo acOutputReport, strrptname, acFormatPDF, strurl
DoCmd.Close acReport, strrptname

rsrptnumb.MoveNext
Loop


End Sub

 

[Uncle Gizmo]

Please post the contents of the message box..

 

[MRose1234]

The message box displayes the correct text, but on clicking OK-button,
nothing happens, and the REPORT stays visibel.....

I must have missed something?

 

[Uncle Gizmo]

Set a breakpoint in your code and see if you can identify exactly where the issue is happening

 

[MRose1234]

Hi

It seems to run fine, until the message box.

#
DoCmd.OpenReport strrptname, acViewReport
strurl = strprefix & txtfieldrptnumb & strsufix
MsgBox " >>> " & strurl
DoCmd.OutputTo acOutputReport, strrptname, acFormatPDF, strurl
DoCmd.Close acReport, strrptname

rsrptnumb.MoveNext
Loop
#

But it dosen't close the report or save it.

Also I've noticed, that the report contains ALL the item numbers? It should only contain, one number at a time. As the reports are suppose to be per item number?
See attached test screen shoot of report.

This might be the problem....

 

[Uncle Gizmo]

Also I've noticed, that the report contains ALL the item numbers? It should only contain, one number at a time. As the reports are suppose to be per item number?
See attached test screen shoot of report.

This might be the problem....

I didn't have your Report, I used my own report, a report with no code in it.

So you are doing something else to your report.

Create a simple report, name it:- "REPORT data sheet page 2" and the code will work.

 

[MRose1234]

Hi

Your so right. It does save the reports with a "clean" report.
I'll figure out a solution to the "real" report later.

But as I suspected, it saves the full amount of record numbers in each report, and does not seperate them, so that each report only contains the information from one record... in accordance with the report file name.
(see attached)
I need the seperation, so that each report only contains one record number.

Also I have to click the "OK" button for each record in the table, and I'll have up to several 100 records in final table, so I would really like not to do all the clicking...
Any ideas?

 

[MRose1234]

Hi
I removed the msgbox, so now it works without the clicking.
Then there is just the issue of record numbers in the reports.

 

[Uncle Gizmo]

Can you show the code that Currently changes the record numbers in your reports?

 

[MRose1234]

Hi

There currently is no code that changes record numbers in the report.

I had a report and attached form which worked by running a query choosing a specific record number, and saving the report with the information for this specific record number, with the record number etc in filename.

What I need now is for the form/report to work with a tabel holding several record numbers, instead of only one.
So now I have the tabel holding several records id's, and I need the form to "pick" a record id at a time and save the specific record id in the report layout, with the record number etc. in filename.

So far I'm still where I can get it to run, but all record id's are saved in each and every file. Although I do get the different files saved with correct filenames.

What I was looking for was the counter / do loop to happen in the form, so that just one record id at at time was choosen, the report completed and saved with record id name in filename.

 

[Uncle Gizmo]

>>>which worked by running a query choosing a specific record number<<<

Please show the SQL of this query...

 

[MRose1234]

OK

Is report is based on the query which is bassicaly - Select "different fields" from "different tabels" where [Item number] = [chosen item number]

The form then opens the report (which runs the query) and saves the report with the choosen record number etc.

As I said this works fine, as long as we're talking ONE record id.


Query SQL:

SELECT VS_ML_20_Imagetabel_billede_T.ImagePath1, VS_ML_21_Imagetabel_symbol_T.ImagePath2, AX_03_Data_Vare_T.ABC, VS_ML_02_levopl_og_productsafetyopl_T.[ML_PS-ans], AX_03_Data_Vare_T.[Item number] AS Varenr, AX_03_Data_Vare_T.[Created date], AX_03_Data_Vare_T.[Vendor account], AX_03_Data_Vare_T.[Vendor name], AX_03_Data_Vare_T.[External item number], AX_03_Data_Vare_T.[Product category], AX_03_Data_Vare_T.[Material type], VS_ML_03_Stamdata_T.ML_Billed_tekst, VS_ML_01_Dokumentation_og_datablad_T.[ML_Dato for opd side 2] AS [Dato for opdatering DD-MM-ÅÅÅÅ], VS_ML_01_Dokumentation_og_datablad_T.[ML_Opdateret af], VS_ML_01_Dokumentation_og_datablad_T.[ML_Datablad side 2], VS_ML_07_Tests_T.[ML_sorterings felt], AX_03_Data_Vare_T.[Article name NL] AS Tekst_1, MR_Main_tabel_MaterialType_T.R_Navn_DK, VS_ML_07_Tests_T.ML_lag_for_lag AS [Materiale lag for lag], VS_ML_07_Tests_T.ML_nedre_temp AS [Nedre temp], VS_ML_07_Tests_T.ML_oevre_temp AS [Øvre temp], VS_ML_07_Tests_T.ML_Kontakt AS Fødevarekontakt, VS_ML_07_Tests_T.ML_Kontakt_temp_tid AS [Temp og tid], VS_ML_07_Tests_T.ML_Kontakt_begræns AS [Kontakt begrænsning], VS_ML_07_Tests_T.ML_Kommentar_kontakt AS [Kontakt kommentar], VS_ML_07_Tests_T.ML_frys AS Frys, VS_ML_07_Tests_T.ML_køl AS Køl, VS_ML_07_Tests_T.ML_stue AS Stue, VS_ML_07_Tests_T.ML_varmholdelse AS Varmhold, VS_ML_07_Tests_T.ML_hotfill AS Hotfill, VS_ML_07_Tests_T.ML_ovn AS Ovn, VS_ML_07_Tests_T.ML_ovn_gr AS [Ovn gr], VS_ML_07_Tests_T.ML_micro AS Mikro, VS_ML_05_Specifikke_Stamdata2_T.ML_Lageropbevaring_kommentar AS Lageropbevaring, VS_ML_05_Specifikke_Stamdata2_T.ML_Emballagedirektiv AS [Under emballagedirektivet], VS_ML_05_Specifikke_Stamdata2_T.ML_Kode_materialegenvind_type AS Materialegenvindingskode, VS_ML_05_Specifikke_Stamdata2_T.ML_Tungmetal AS [Overholdelse af Tungmetaller], VS_ML_05_Specifikke_Stamdata2_T.ML_dok_navn_emballagedirektivet AS [Dok navn tungmetaller 94/62/EC], VS_ML_05_Specifikke_Stamdata2_T.ML_Dato_emballagedirektivet AS [Udstedelses dato for tungmetaller 94/62/EC], VS_ML_05_Specifikke_Stamdata2_T.ML_funk_barriere AS [Funktionel barrier], VS_ML_07_Tests_T.ML_Dualuse AS ["Dual use" stoffer], VS_ML_07_Tests_T.ML_Dualuse_hvilke AS [Dualuse Betegnelse], VS_ML_07_Tests_T.ML_mono_krav AS [Monomerer med restriktioner], VS_ML_07_Tests_T.ML_additiv_krav AS [Additiver med restriktioner], VS_ML_07_Tests_T.ML_kemisk_betegn AS [Stof betegnelse], VS_ML_07_Tests_T.ML_Refnr AS [Stof Refnr], VS_ML_07_Tests_T.ML_CASnr AS [Stof CASnr], VS_ML_07_Tests_T.ML_Migrationstest AS [Overall migrationstest udført], VS_ML_07_Tests_T.ML_Dok_navn_OM_test AS [Dok navn på OM test], VS_ML_07_Tests_T.ML_Dato_OM_test AS [Udstedelses dato for OM test], VS_ML_07_Tests_T.ML_Migrationstests_volume_areal AS [Volume og areal ved test], VS_ML_07_Tests_T.ML_mon_add_med_SML_JA AS [SML JA], VS_ML_07_Tests_T.ML_mon_add_med_SML_NEJ AS [SML NEJ], VS_ML_07_Tests_T.ML_spec_migrationtest AS [Specifik migrationstest udført], VS_ML_07_Tests_T.ML_Dok_navn_spec_mig AS [Dok navn på SM test], VS_ML_07_Tests_T.ML_Dato_spec_mig AS [Udstedelses dato for SM test], VS_ML_07_Tests_T.ML_simA_vand AS [Sim A H2O], VS_ML_07_Tests_T.ML_simA_temp_vand AS [A H2O temp/tid], VS_ML_07_Tests_T.[ML_simA_10%_ethanol] AS [Sim A 10%], VS_ML_07_Tests_T.[ML_simA_temp_10%_ethanol] AS [A 10% temp/tid], VS_ML_07_Tests_T.[ML_simB_3%_eddikesyre] AS [Sim B], VS_ML_07_Tests_T.[ML_simB_temp_3%_eddikesyre] AS [B temp/tid], VS_ML_07_Tests_T.[ML_simC_10%_ethanol] AS [Sim C 10%], VS_ML_07_Tests_T.[ML_simC_temp_10%_ethanol] AS [C 10% temp/tid], VS_ML_07_Tests_T.[ML_simC_20%_ethanol] AS [Sim C 20%], VS_ML_07_Tests_T.[ML_simC_temp_20%_ethanol] AS [C 20% temp/tid], VS_ML_07_Tests_T.[ML_simD1_50%_ethanol] AS [Sim D1], VS_ML_07_Tests_T.[ML_simD1_temp_50%_ethanol] AS [D1 temp/tid], VS_ML_07_Tests_T.ML_simD2_Olivenolie AS [Sim D2], VS_ML_07_Tests_T.ML_simD2_temp_Olivenolie AS [D2 temp/tid], VS_ML_07_Tests_T.ML_Isooktan AS Issoktan, VS_ML_07_Tests_T.ML_isooktan_temp AS [Iss temp/tid], VS_ML_07_Tests_T.ML_Ethanol95 AS [95% Ethanol], VS_ML_07_Tests_T.ML_Ethanol95_temp AS [95% temp/tid], VS_ML_07_Tests_T.ML_simE_MPPO AS [Sim E MPPO], VS_ML_07_Tests_T.ML_sinE_MPPO_temp AS [Sim E MPPO temp/tid], VS_ML_07_Tests_T.ML_reduktionsfaktor AS [Fedt reduktion], VS_ML_07_Tests_T.ML_reduktionsfaktor_vaerdi AS Faktor, VS_ML_07_Tests_T.ML_andet AS [Anden test], VS_ML_07_Tests_T.ML_andet_hvad AS Hvilken, VS_ML_07_Tests_T.ML_andet_temp AS [Anden temp/tid], VS_ML_07_Tests_T.ML_kommentar_vedr_test AS [Yderligere kommentar], VS_ML_07_Tests_T.ML_kommentar_vedr_test1 AS [Yderligere kommentar1], VS_ML_04_Specifikke_Stamdata1_T.ML_Import_udenfor_EU AS [Import ex EU], VS_ML_04_Specifikke_Stamdata1_T.ML_Artikel_NEJ AS [Artikel NEJ], VS_ML_04_Specifikke_Stamdata1_T.ML_Artikel_JA AS [Artikel JA], VS_ML_04_Specifikke_Stamdata1_T.ML_dok_navn_REACH_dok AS [Dok navn på Reach], VS_ML_04_Specifikke_Stamdata1_T.ML_Dato_REACH_dok AS [Udstedelses dato for Reach dok], AX_03_Data_Vare_T.ABC AS [Resten er ikke på datablad], VS_ML_05_Specifikke_Stamdata2_T.ML_genanvendt_materiale AS [Genanvendt materiale], VS_ML_05_Specifikke_Stamdata2_T.ML_Dok_navn_genanvendt AS [Dok genenvendt], VS_ML_05_Specifikke_Stamdata2_T.ML_Dato_genanvendt AS [Dato genanvendt], VS_ML_05_Specifikke_Stamdata2_T.ML_bioplast AS [Er det bioplast], VS_ML_05_Specifikke_Stamdata2_T.ML_Tryk_andet AS [Tryk på ikke kontakt flade], VS_ML_05_Specifikke_Stamdata2_T.ML_Tryk AS [Tryk på kontaktflade], VS_ML_05_Specifikke_Stamdata2_T.ML_Dok_navn_tryk AS [Dok navn tryk], VS_ML_05_Specifikke_Stamdata2_T.ML_Dato_tryk AS [Udstedelses dato tryk], VS_ML_05_Specifikke_Stamdata2_T.ML_coat AS [Benyttes coating], VS_ML_05_Specifikke_Stamdata2_T.ML_coat_type AS [Coating type], VS_ML_05_Specifikke_Stamdata2_T.ML_Dok_navn_coat AS [Dok navn på coating], VS_ML_05_Specifikke_Stamdata2_T.ML_Dato_coat AS [Udstedelses dato coat], VS_ML_05_Specifikke_Stamdata2_T.ML_farvestof AS Farvet, VS_ML_05_Specifikke_Stamdata2_T.ML_Dok_navn_farvestof AS [Dok navn farve], VS_ML_05_Specifikke_Stamdata2_T.ML_Dato_farvestof AS [Udstedelses dato for dok farve], VS_ML_05_Specifikke_Stamdata2_T.ML_lim AS Lim, VS_ML_05_Specifikke_Stamdata2_T.ML_lim_hvilke AS [Lim hvilken], VS_ML_05_Specifikke_Stamdata2_T.ML_Dok_navn_lim AS [Dok navn på lim], VS_ML_05_Specifikke_Stamdata2_T.ML_Dato_lim AS [Udstedelses dato for dok lim], VS_ML_05_Specifikke_Stamdata2_T.ML_flerlag AS [Er det flerlags], VS_ML_05_Specifikke_Stamdata2_T.ML_flerlag_andet AS [Lag andet end plast], VS_ML_05_Specifikke_Stamdata2_T.ML_flerlag_materiale AS [Flerlags materialer], VS_ML_07_Tests_T.ML_organoleptisktest AS [Organoleptisk test udført], VS_ML_07_Tests_T.ML_Dok_navn_organoleptisktest AS [Dok navn for organoleptisk test], VS_ML_07_Tests_T.ML_Dato_organoleptisktest AS [Dato for dok organoleptisk test], VS_ML_07_Tests_T.ML_antidug AS [Benyttes Antidug], VS_ML_07_Tests_T.ML_Dok_navn_antidug AS [Dok navn på antidug], VS_ML_07_Tests_T.ML_Dato_antidug AS [Udstedelses dato for antidug dok]
FROM ((((((((MR_Main_tabel_MaterialType_T INNER JOIN AX_03_Data_Vare_T ON MR_Main_tabel_MaterialType_T.Raavare_Id = AX_03_Data_Vare_T.[Material type]) INNER JOIN VS_ML_07_Tests_T ON AX_03_Data_Vare_T.[Item number] = VS_ML_07_Tests_T.ML_varenr) INNER JOIN VS_ML_03_Stamdata_T ON AX_03_Data_Vare_T.[Item number] = VS_ML_03_Stamdata_T.ML_varenr) INNER JOIN VS_ML_01_Dokumentation_og_datablad_T ON AX_03_Data_Vare_T.[Item number] = VS_ML_01_Dokumentation_og_datablad_T.ML_varenr) INNER JOIN VS_ML_05_Specifikke_Stamdata2_T ON AX_03_Data_Vare_T.[Item number] = VS_ML_05_Specifikke_Stamdata2_T.ML_varenr) INNER JOIN VS_ML_02_levopl_og_productsafetyopl_T ON AX_03_Data_Vare_T.[Vendor account] = VS_ML_02_levopl_og_productsafetyopl_T.ML_AX_Levnr) INNER JOIN VS_ML_04_Specifikke_Stamdata1_T ON AX_03_Data_Vare_T.[Item number] = VS_ML_04_Specifikke_Stamdata1_T.ML_varenr) INNER JOIN VS_ML_20_Imagetabel_billede_T ON AX_03_Data_Vare_T.[Item number] = VS_ML_20_Imagetabel_billede_T.[Item number]) INNER JOIN VS_ML_21_Imagetabel_symbol_T ON VS_ML_05_Specifikke_Stamdata2_T.ML_Kode_materialegenvind_type = VS_ML_21_Imagetabel_symbol_T.Genvingingskode
WHERE (((AX_03_Data_Vare_T.[Item number])=[Angiv ML varenr]));

 

[Uncle Gizmo]

WHERE (((AX_03_Data_Vare_T.[Item number])=[Angiv ML varenr]));
#

Is item number the number that needs to change for each report?

 

[MRose1234]

NOTE:
I currently not trying to make my own query/report/form work.
Currently I'm only using the set up of tabel/form/report you setup, and it is there that the reports are still all together.

(although it does work on my own stuff with same result)

 

[MRose1234]

Yes. Item number is the id for all other fields (all linked to this ID)
When the query runs, I choose a item number in the pop up window.

 

[Uncle Gizmo]

I've done another video - Now on my Blog Here:-
Generate Multiple Copies of a Report with different Criteria 2

There's at least one more to come, and a Presentation to show how it works.

Would have been quicker, but had Internet Problems...

 

[Uncle Gizmo]

I've done another video - Now on my Blog Here:-

Generate Multiple Copies of a Report with different Criteria 3