Changing Report Dynamically

A

Andrew

Hi,

I am creating reports using the following format.

Private Sub Btn_Produce_Report_Byt_Click()
On Error Resume Next
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM Qry_Summary_Of_Departments")
Set qdf = dbs.QueryDefs("Qry_Tbl_Range_By_Season_Data")
With rst
Do Until .EOF
The_Brand = !Company
The_Season = !Season
sSQL = "SELECT Tbl_Range_By_Season_Data.*,
Format(Now(),""dd/mm/yy"") AS As_At,
IIf(Val(Mid([Report_Period],7,2))<7,""S"",""W"") AS Period,
Left([Report_Period],4) & [Period] AS Season "
sSQL = sSQL & "FROM Tbl_Range_By_Season_Data "
sSQL = sSQL & "WHERE (((Tbl_Range_By_Season_Data.Dept) = " &
!Dept & ") And ((Tbl_Range_By_Season_Data.Company) = """ & The_Brand & """)
And ((Tbl_Range_By_Season_Data.Season) = """ & The_Season & """)) "
sSQL = sSQL & "ORDER BY Tbl_Range_By_Season_Data.Dept;"


If IsNull(!Dept) = True Then GoTo MoveNext_Part
qdf.SQL = sSQL
The_Path = "G:\00_CEN\Oth\Inventory Planning Reports\Purchase
Order Listing\" & The_Brand & "\BG" & !Bus_Group & "\" & The_Season & "\"
MkDir "G:\00_CEN\Oth\Inventory Planning Reports\Purchase Order
Listing\" & The_Brand
MkDir "G:\00_CEN\Oth\Inventory Planning Reports\Purchase Order
Listing\" & The_Brand & "\BG" & !Bus_Group
MkDir "G:\00_CEN\Oth\Inventory Planning Reports\Purchase Order
Listing\" & The_Brand & "\BG" & !Bus_Group & "\" & The_Season & "\"

DoCmd.OutputTo acReport, "Rpt_Range_By_Season_Report",
"SnapshotFormat(*.snp)", The_Path & "Dept " & !Dept & ".snp", True
MoveNext_Part:
.MoveNext
Loop
.Close
End With
Set qdf = Nothing
Set rst = Nothing
Set dbs = Nothing

End Sub

This works fine. I do need to make one change though, I need to change a
few lables and text boxes based on the season of the report. (currently the
label's name is Period_Ordered_Month_Label_1 and the caption is 'Feb', I want
it to be 'Aug'. Also text box 'Department_Ordered_Total_1' control source'
is '=Sum([FEB_07_ORD_Retail])' and I need to make it
'=Sum([AUG_07_ORD_Retail])'

What is the code to do this and where do I put it in my code?
 
K

Klatuu

Use the Open event of the report. You will also need to know which you need
it to be so your code will know which to assign. One way would be to have
the value for the season in a text box on the form, or if you are using 2002
or newer, reports now have an OpenArgs argument like forms do. but the basic
is:

If Forms!MyOpeningForm!txtSeason = "W" Then
Me.Period_Ordered_Month_Label_1 = "Feb"
Me.Department_Ordered_Total_1.ControlSource = _
"Sum([FEB_07_ORD_Retail])"
Else
Me.Period_Ordered_Month_Label_1 = "Aug"
Me.Department_Ordered_Total_1.ControlSource = _
"Sum([Aug_07_ORD_Retail])"
End If

You do have one major error in your database design. You should not have
fields with specific time dependant field names. Now, every year you have to
change your code and probably your table design and your forms and queries,
etc. No need to put yourself through that much work. Change the field name
to [ORD_Retail] and add a field to the table to identify the year and season.
--
Dave Hargis, Microsoft Access MVP


Andrew said:
Hi,

I am creating reports using the following format.

Private Sub Btn_Produce_Report_Byt_Click()
On Error Resume Next
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM Qry_Summary_Of_Departments")
Set qdf = dbs.QueryDefs("Qry_Tbl_Range_By_Season_Data")
With rst
Do Until .EOF
The_Brand = !Company
The_Season = !Season
sSQL = "SELECT Tbl_Range_By_Season_Data.*,
Format(Now(),""dd/mm/yy"") AS As_At,
IIf(Val(Mid([Report_Period],7,2))<7,""S"",""W"") AS Period,
Left([Report_Period],4) & [Period] AS Season "
sSQL = sSQL & "FROM Tbl_Range_By_Season_Data "
sSQL = sSQL & "WHERE (((Tbl_Range_By_Season_Data.Dept) = " &
!Dept & ") And ((Tbl_Range_By_Season_Data.Company) = """ & The_Brand & """)
And ((Tbl_Range_By_Season_Data.Season) = """ & The_Season & """)) "
sSQL = sSQL & "ORDER BY Tbl_Range_By_Season_Data.Dept;"


If IsNull(!Dept) = True Then GoTo MoveNext_Part
qdf.SQL = sSQL
The_Path = "G:\00_CEN\Oth\Inventory Planning Reports\Purchase
Order Listing\" & The_Brand & "\BG" & !Bus_Group & "\" & The_Season & "\"
MkDir "G:\00_CEN\Oth\Inventory Planning Reports\Purchase Order
Listing\" & The_Brand
MkDir "G:\00_CEN\Oth\Inventory Planning Reports\Purchase Order
Listing\" & The_Brand & "\BG" & !Bus_Group
MkDir "G:\00_CEN\Oth\Inventory Planning Reports\Purchase Order
Listing\" & The_Brand & "\BG" & !Bus_Group & "\" & The_Season & "\"

DoCmd.OutputTo acReport, "Rpt_Range_By_Season_Report",
"SnapshotFormat(*.snp)", The_Path & "Dept " & !Dept & ".snp", True
MoveNext_Part:
.MoveNext
Loop
.Close
End With
Set qdf = Nothing
Set rst = Nothing
Set dbs = Nothing

End Sub

This works fine. I do need to make one change though, I need to change a
few lables and text boxes based on the season of the report. (currently the
label's name is Period_Ordered_Month_Label_1 and the caption is 'Feb', I want
it to be 'Aug'. Also text box 'Department_Ordered_Total_1' control source'
is '=Sum([FEB_07_ORD_Retail])' and I need to make it
'=Sum([AUG_07_ORD_Retail])'

What is the code to do this and where do I put it in my code?
Click to expand...
 
A

Andrew

Thanks Dave - I'll give it a go
--
Andrew


Klatuu said:
Use the Open event of the report. You will also need to know which you need
it to be so your code will know which to assign. One way would be to have
the value for the season in a text box on the form, or if you are using 2002
or newer, reports now have an OpenArgs argument like forms do. but the basic
is:

If Forms!MyOpeningForm!txtSeason = "W" Then
Me.Period_Ordered_Month_Label_1 = "Feb"
Me.Department_Ordered_Total_1.ControlSource = _
"Sum([FEB_07_ORD_Retail])"
Else
Me.Period_Ordered_Month_Label_1 = "Aug"
Me.Department_Ordered_Total_1.ControlSource = _
"Sum([Aug_07_ORD_Retail])"
End If

You do have one major error in your database design. You should not have
fields with specific time dependant field names. Now, every year you have to
change your code and probably your table design and your forms and queries,
etc. No need to put yourself through that much work. Change the field name
to [ORD_Retail] and add a field to the table to identify the year and season.
--
Dave Hargis, Microsoft Access MVP


Andrew said:
Hi,

I am creating reports using the following format.

Private Sub Btn_Produce_Report_Byt_Click()
On Error Resume Next
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM Qry_Summary_Of_Departments")
Set qdf = dbs.QueryDefs("Qry_Tbl_Range_By_Season_Data")
With rst
Do Until .EOF
The_Brand = !Company
The_Season = !Season
sSQL = "SELECT Tbl_Range_By_Season_Data.*,
Format(Now(),""dd/mm/yy"") AS As_At,
IIf(Val(Mid([Report_Period],7,2))<7,""S"",""W"") AS Period,
Left([Report_Period],4) & [Period] AS Season "
sSQL = sSQL & "FROM Tbl_Range_By_Season_Data "
sSQL = sSQL & "WHERE (((Tbl_Range_By_Season_Data.Dept) = " &
!Dept & ") And ((Tbl_Range_By_Season_Data.Company) = """ & The_Brand & """)
And ((Tbl_Range_By_Season_Data.Season) = """ & The_Season & """)) "
sSQL = sSQL & "ORDER BY Tbl_Range_By_Season_Data.Dept;"


If IsNull(!Dept) = True Then GoTo MoveNext_Part
qdf.SQL = sSQL
The_Path = "G:\00_CEN\Oth\Inventory Planning Reports\Purchase
Order Listing\" & The_Brand & "\BG" & !Bus_Group & "\" & The_Season & "\"
MkDir "G:\00_CEN\Oth\Inventory Planning Reports\Purchase Order
Listing\" & The_Brand
MkDir "G:\00_CEN\Oth\Inventory Planning Reports\Purchase Order
Listing\" & The_Brand & "\BG" & !Bus_Group
MkDir "G:\00_CEN\Oth\Inventory Planning Reports\Purchase Order
Listing\" & The_Brand & "\BG" & !Bus_Group & "\" & The_Season & "\"

DoCmd.OutputTo acReport, "Rpt_Range_By_Season_Report",
"SnapshotFormat(*.snp)", The_Path & "Dept " & !Dept & ".snp", True
MoveNext_Part:
.MoveNext
Loop
.Close
End With
Set qdf = Nothing
Set rst = Nothing
Set dbs = Nothing

End Sub

This works fine. I do need to make one change though, I need to change a
few lables and text boxes based on the season of the report. (currently the
label's name is Period_Ordered_Month_Label_1 and the caption is 'Feb', I want
it to be 'Aug'. Also text box 'Department_Ordered_Total_1' control source'
is '=Sum([FEB_07_ORD_Retail])' and I need to make it
'=Sum([AUG_07_ORD_Retail])'

What is the code to do this and where do I put it in my code?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to change MS Query via VBA 0
Break large table into smaller 4

Top