Passing footer to Access reports from VB

HI,

We are using VB 2005(Visual Basic.net) with SQL server as database and MS
Access for the Reports. Right now we are passing the footer label from our
VB app to MS Access for a selected report. If the user closes that report(not
closing access) and opens another report the footer label is not being
displayed. Is there any way I could send the footer label to all the reports
in Access as soon as I open the database.

Right now in below code we are sending the footer label just for the report
selected. Is there a way to send the footer label to all the reports that are
existing in the Access database.
PLEASE HELP!!!.

Thanks A Lot!!
Hima...

Here is the code we are using right now.

Public Sub printReports(ByRef trn As ITTransaction, ByVal blnPreview As
Boolean)

Dim intSelectRptCnt As Short
Dim intI As Short
Dim strRpt As String
Dim strWhere As String
Dim strDate As String
Dim strSQL As String
Dim dtFromDate As Date
Dim dtToDate As Date
Dim blnFCTables As Boolean
Dim footerLabel As Access.Label
Dim ctlTemp As Access.Control

Try

intSelectRptCnt = lstAvailRpts.Items.Count

If mCategory = ModReports.enReportCategory.rcForecastingReports
Then
'do it with forecast tables
blnFCTables = True
Else
'do it with production tables
blnFCTables = False
End If

mtrnCurrent.Reports = GetAccessObject(trn, intFrequencyCode)

mtrnCurrent.Reports.OpenCurrentDatabase(mtrnCurren t.ReportFileName(intFrequencyCode), True)

If intSelectRptCnt 0 Then

mtrnCurrent.Reports.Application.Visible = True

For intI = 0 To (intSelectRptCnt - 1)

If lstAvailRpts.GetSelected(intI) = True Then
strRpt = VB.Format(CInt(mCategory), "00") &
lstAvailRpts.Items.Item(intI)
strWhere = ""
If blnFCTables = True Then
'only user for forecast reports
If mprcCurrent Is Nothing Then
strWhere = "[Forecast ID]=[Forecast_ID]"
Else
strWhere = "[Forecast ID]=" & mprcCurrent.Id
End If
End If

'get dates
dtFromDate = dtpFromDate.Value
dtToDate = dtpToDate.Value

If blnFCTables = True Then
'only need and of a forecasting report
strDate = " and"
Else
strDate = ""
End If

If Not mprcCurrent Is Nothing Then
strDate = strDate & " [CurrentDate] Between " &
"#" & dtFromDate & "#" & " and " & "#" & dtToDate & "#"
Else
If dtpFromDate.Value.ToString "" And
dtpToDate.Value.ToString "" Then
strDate = strDate & " [CurrentDate] Between
" & "#" & dtFromDate & "#" & " and " & "#" & dtToDate & "#"
End If
End If

'used if one of the dates was not entered.
If dtpFromDate.Value.ToString = "" Or
dtpToDate.Value.ToString = "" Then
strDate = strDate & " [CurrentDate] between
[Current_DateFrom] and [Current_DateTo] "
End If

'put it all together
strSQL = strWhere & strDate

mtrnCurrent.Reports.DoCmd.OpenReport(strRpt,
Access.AcView.acViewDesign, , strSQL)

' search the page footer for the report status label
footerLabel = Nothing
For Each ctlTemp In
mtrnCurrent.Reports.Reports(strRpt).Section(Access .AcSection.acPageFooter).Controls
If TypeOf ctlTemp Is Access.Label Then
If CType(ctlTemp, Access.Label).Name =
"FooterLabel" Then
footerLabel = ctlTemp
End If
End If
Next


If footerLabel Is Nothing Then
' control does not exist, create it
footerLabel =
mtrnCurrent.Reports.CreateReportControl(strRpt, Access.AcControlType.acLabel,
Access.AcSection.acPageFooter, , , 50, 50, 1440, 200)
footerLabel.Name = "FooterLabel"
footerLabel.SizeToFit()
footerLabel.Caption = ""
footerLabel.FontSize = 8
footerLabel.Width = 3000
footerLabel.Height = 200
footerLabel.BackStyle = 1
footerLabel.Visible = False
Else
' control was found, reset the caption and make
it invisible
footerLabel.Caption = ""
footerLabel.Visible = False
End If

If mtrnCurrent.status =
enTransactionStatus.stDevelopment Then
footerLabel.Caption = "Printed in Development
Status"
footerLabel.Visible = True
ElseIf mtrnCurrent.status =
enTransactionStatus.stRepair Then
footerLabel.Caption = "Printed in Repair Status"
footerLabel.Visible = True
End If

' Access loses our SQL command while manipulating
the footer label
' Close and then reopen the report to ensure Access
prints the report for the correct date range.

mtrnCurrent.Reports.DoCmd.Close(Access.AcObjectTyp e.acReport, strRpt,
Access.AcCloseSave.acSaveYes)

' display preview, or send to printer
If blnPreview Then
mtrnCurrent.Reports.DoCmd.OpenReport(strRpt,
Access.AcView.acViewPreview, , strSQL)
mtrnCurrent.Reports.DoCmd.Maximize()
' wait for the user to exit access
While (IsProjectLoaded(mtrnCurrent.Reports) =
True)
' wait while the user is working in MS Access
Sleep(500)
End While
Else
' send to printer
mtrnCurrent.Reports.DoCmd.OpenReport(strRpt,
Access.AcView.acViewNormal, , strSQL)

mtrnCurrent.Reports.DoCmd.Close(Access.AcObjectTyp e.acReport, strRpt,
Access.AcCloseSave.acSaveNo)

mtrnCurrent.Reports.Quit(Access.AcQuitOption.acQui tSaveNone)
End If
End If
Next intI
Application.DoEvents()
End If

Catch ex As System.Runtime.InteropServices.COMException

' This error will occur when the mapping of components to fields
on the investor reports is broken.
' This situation happens when a transaction is migrated from
production to development, and the
' securization analyst has not yet fixed these mappings.
If Err.Number = 2501 Then
showMsg("Preview report cancelled because there is no data
to display." & vbNewLine & _
"This is most likely due to not having report fields
mapped to components." & vbNewLine & vbNewLine & _
"To remap components, use the change button when the
transaction is in" & vbNewLine & _
"Development or Repair status to edit the report." &
vbNewLine & vbNewLine & _
"Any changes made in Preview mode will not be
saved!", MsgBoxStyle.Critical, "Access Report Print Error")
Else
' Generic COM error handler
showMsg(Err.Number & " " & ex.Message, MsgBoxStyle.Critical,
"Access COM Error")
handleAccessError("previewReports()")
End If

Catch ex As Exception
handleError(Err.Number, Err.Description, "previewReports()")

Finally
footerLabel = Nothing
TerminateAccess(mtrnCurrent.Reports)
End Try
End Sub

Hima wrote:

We are using VB 2005(Visual Basic.net) with SQL server as database and MS
Access for the Reports. Right now we are passing the footer label from our
VB app to MS Access for a selected report. If the user closes that report(not
closing access) and opens another report the footer label is not being
displayed. Is there any way I could send the footer label to all the reports
in Access as soon as I open the database.

The best approach would be to store that data in another location.
One common thing I do within Access is to store such variables on a
hidden GlobalOptions form.

So setup a bunch of controls on a hidden form, open it, update those
from your app, and then reference those controls from within the
reports.

Forms!GlobalOptionsHidden!txtFooter

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/

Thanks a Lot for the reply Tony.

I am really dumb stuck here. Could you please send me a code sample as to
how I would incorporate this in the code below.

Thanks a million for all the help in advance.

Thanks,
Hima Kalidindi.

"Tony Toews [MVP]" wrote:

Hima wrote:

We are using VB 2005(Visual Basic.net) with SQL server as database and MS
Access for the Reports. Right now we are passing the footer label from our
VB app to MS Access for a selected report. If the user closes that report(not
closing access) and opens another report the footer label is not being
displayed. Is there any way I could send the footer label to all the reports
in Access as soon as I open the database.

The best approach would be to store that data in another location.
One common thing I do within Access is to store such variables on a
hidden GlobalOptions form.

So setup a bunch of controls on a hidden form, open it, update those
from your app, and then reference those controls from within the
reports.

Forms!GlobalOptionsHidden!txtFooter

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/

Hima wrote:

I am really dumb stuck here. Could you please send me a code sample as to
how I would incorporate this in the code below.

Let's use the following

footerLabel.Caption = "Printed in Repair Status"

so at the top of the code module you'd have

Docmd.openform "HiddenForm"

That form has a control call footerLabel

forms!HiddenForm!footerlabel = footerLabel.Caption = "Printed in
Repair Status"

Then you'd open the report.

In the reports OnOpen event you'd have

footerLabel.Caption = forms!HiddenForm!footerlabel

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/