Objects:
Form Named: FormMaster
Combo Box in Form named: PortfolioSelection
I want to refer to this in VBA how do I write it out?
Works Fine here
But when I call MasterReportBuilder it it either undefined (or if I Dim it a null value)
Form Named: FormMaster
Combo Box in Form named: PortfolioSelection
I want to refer to this in VBA how do I write it out?
Works Fine here
Code:
Private Sub RunReports_Click()
Dim frmPortfolioSelectionNumber As Double
Dim frmPerilNumber As Double
Dim frmAnalysisID As Long
frmPerilNumber = DLookup("[Peril_Number]", "Desc_Perils", "[Peril] = '" & PerilSelection & "'")
frmPortSelectionNumber = DLookup("[PORTINFOID]", "dbo_Portinfo", "[PortName] = '" & PortfolioSelection & "'")
frmAnalysisID = DLookup("[ID]", "dbo_rdm_analysis", "[Name] = '" & frmAnalysisName & "'")
' Delete The current Contents of Th eFilter Table
DoCmd.RunSQL "DELETE * FROM FiltersForReport"
' Create the new Table
DoCmd.RunSQL "Insert Into FiltersForReport (" & _
"IsValid, PortinfoID, Peril, ReportName, Anlsid)" & _
"Values(-1," & frmPortSelectionNumber & "," & frmPerilNumber & ", '" & frmReportName & "' ," & frmAnalysisID & " )"
Call MasterReportBuilderAc
End Sub
But when I call MasterReportBuilder it it either undefined (or if I Dim it a null value)
Code:
Public Sub MasterReportBuilderAc()
' Get the Path Name
strFullPath = CurrentDb().Name
sXL_Path = Left(strFullPath, InStrRev(strFullPath, "\"))
NewReportName = DLookup("[ReportName]", "FiltersForReport", "[Isvalid] = -1")
MsgBox (sXL_Path)
Set XLapp = CreateObject("excel.application")
Set XLwb = Workbooks.Open(sXL_Path & TargetXLFile)
Set CurDB = CurrentDb()
XLapp.Visible = True
XLwb.SaveAs (sXL_Path & NewReportName)
sDB_Path = CurrentDb().Name
With XLwb.Worksheets("Primary")
.Range("F2") = PortfolioSelection
.Range("G2") = frmAnalysisName
End With
'CLEAN UP
Set rst = Nothing