If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Passing footer to Access reports from VB
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/ |
#3
|
|||
|
|||
Passing footer to Access reports from VB
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/ |
#4
|
|||
|
|||
Passing footer to Access reports from VB
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/ |
Thread Tools | |
Display Modes | |
|
|