Form saving several reports in one go (1 Viewer)

MRose1234

Registered User.
Local time
Today, 17:53
Joined
Aug 6, 2014
Messages
17
Thanks

I won't be able to check this out before wednesday though.
I get back to you.

Thanks for your assistance :)
 

MRose1234

Registered User.
Local time
Today, 17:53
Joined
Aug 6, 2014
Messages
17
Hi again

Been through the videos and document.
It's been really helpfull, thanks a lot for taking the time.

Unfortunately I 'm still missing out on something.
I've set the test up as explained, but keep getting the Error massage
of there being no data for the report.

But the form does contain data?

I set the Query to select only the references with "1", and the collected data is present in the form. But for some reason the report won't run.
I've attached the test database, as I though it might be easier for you to detect the error.
 

Attachments

  • TEST VBA for Report.accdb
    1 MB · Views: 394

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:53
Joined
Jul 9, 2003
Messages
16,244
Your command button is operating a Macro which opens the report, the report is expecting the name of the form attempting to open it to be passed through via OpenArgs. You should actually be using the command button to run the function:- "fRSL_MakeRptWithNumbers"

Go to the command buttons onClick event and change it from [Embedded Macro]
to [Event Procedure].

Find the code stub:-
Private Sub Commando0_Click()

End Sub

and Paste:-
Call fRSL_MakeRptWithNumbers
in it.
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:53
Joined
Jul 9, 2003
Messages
16,244
BTW .. There are some typos in your code. Message box is spelt wrong, and there are several other similar type mistakes. Also the table you are drawing the numbers from, the field with the numbers is a text field, would be a good idea to change it to a number format field.
 

MRose1234

Registered User.
Local time
Today, 17:53
Joined
Aug 6, 2014
Messages
17
I could just Hug you :)

I cleaned up my little mishaps (sorry stressed) and changed the field to numeric... and it's working.

However I do need the Item number field (FieldRptNumb) to be a text field,
as my item numbers are made up by both numbers and letters. So a numeric field won't work for the actual report.

So I'll have to figure that one out.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:53
Joined
Jul 9, 2003
Messages
16,244
With regard to it being text, I think all you need to do is change any variables that handle the value from integer or long to string.
 

MRose1234

Registered User.
Local time
Today, 17:53
Joined
Aug 6, 2014
Messages
17
Thanks for lot for your assistance.
I'll try just that.

Hope I don't need to get back to you on this one :)
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:53
Joined
Jul 9, 2003
Messages
16,244
It might be interesting to add a title in the table that gets added to the report.
 

MRose1234

Registered User.
Local time
Today, 17:53
Joined
Aug 6, 2014
Messages
17
Hi again

I had to tweak the code and set up a bit to make it workable with my needs. Also I created an additional query where the range of item numbers needed is extracted into a table, and a query with the needed SQL is based on this table. Here I have a row counter as a new numeric count which I use for “Do Until” code (Field: DataLinkId).

This all works just fine. The reports are saved, currently with the counter ID (Field: DataLinkID) so the file name reads;
1 databladMG.pdf
2 databladMG.pdf
3 databladMG.pdf
Etc.

My issue is that I stil need the files to be saved with the “Item-Number” field information instead of the “DataLinkId”. So I tried to change the it to contain “Item_Number” instead of “DataLinkId” in several different ways. But all I get is that the Last report is saved with the file name of the first “item_number”.

I need the filename to be following syntax: Item_number & strSufix

Since the filename set up can be just about anything, I can’t seem to locate why I can’t make it work with the “item_number” instead of the “DataLinkID” number…..

Hope you can put your finger on it……

Here’s the code, still only in trail mode

#
Dim strsql_rsl As String
strsql_rsl = "SELECT tblrowcounter_testA.Data_Lev_Number, tblrowcounter_testA.DataLinkId, tblrowcounter_testA.FieldRptNumb, tblrowcounter_testA.id, tblrowcounter_testA.Item_number, tblrowcounter_testA.DataID, tblrowcounter_testA.Datasets, tblrowcounter_testA.DataItems, tblrowcounter_testA.datatickedoff, tblrowcounter_testA.text1, tblrowcounter_testA.text2, tblrowcounter_testA.text3, tblrowcounter_testA.text4 from tblrowcounter_testA order by tblrowcounter_testA.DataLinkId"

Dim intDataLinkId As Integer
Dim strRptname As String
Dim strPrefix As String
Dim strSufix As String
Dim strURL As String
Dim strFrmName As String

strRptname = "RptData_testA"
strPrefix = "C:\A_Transfer\"
strSufix = " data sheet MG.pdf"
strFrmName = "Form1_testA"

Set rsrptnumb = curdb.OpenRecordset(strsql_rsl, dbOpenForwardOnly)

Do Until rsrptnumb.EOF
intDataLinkId = rsrptnumb!DatalinkID

prpSQL_ForRpt = fSQL_ForRpt(intDataLinkId)
DoCmd.OpenReport strRptname, acViewReport, , , , Me.Name
strURL = strPrefix & intDataLinkId & strSufix
'MsgBox " >>> " & strURL
DoCmd.OutputTo acOutputReport, strRptname, acFormatPDF, strURL
DoCmd.Close acReport, strRptname

rsrptnumb.MoveNext
Loop
#
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:53
Joined
Jul 9, 2003
Messages
16,244
Try changing this line:-
intDataLinkId = rsrptnumb!DatalinkID

To:-
intDataLinkId = rsrptnumb!Item_number

It don't help when you talk about Item-number when you mean Item_number...
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:53
Joined
Jul 9, 2003
Messages
16,244
You may need to change the Order By statement.

Make sure the field is the same data type.
 

vbaInet

AWF VIP
Local time
Today, 17:53
Joined
Jan 22, 2010
Messages
26,374
By the way MRose, why all this?
Code:
    DoCmd.[COLOR="Red"]OpenReport[/COLOR] strRptname, acViewReport, , , , Me.Name
    DoCmd.[COLOR="red"]OutputTo [/COLOR]acOutputReport, strRptname, acFormatPDF, strURL
    DoCmd.[COLOR="red"]Close [/COLOR]acReport, strRptname
 

Users who are viewing this thread

Top Bottom