why wouldnt the strNetwDesc not be passing thru in the following code?
the data for the strNetwork is correct and is used in an append qry but i'm not able to save the file name using the strNetwDesc.
here is hte skinny.....i have a qry Net list that has an id and a desc....i want to use the id as a parameter for the qry and then the desc for that associate id in the file name.
the data for the strNetwork is correct and is used in an append qry but i'm not able to save the file name using the strNetwDesc.
here is hte skinny.....i have a qry Net list that has an id and a desc....i want to use the id as a parameter for the qry and then the desc for that associate id in the file name.
Code:
Private Sub GenerateReports()
DoCmd.SetWarnings False
Dim rsNetwork As Recordset
Dim X As Integer
Set rsNetwork = CurrentDb.OpenRecordset("qry_Net_List")
Dim flds As DAO.Fields
Dim fld As DAO.Field
Dim strNetwork As String
Set flds = rsNetwork.Fields
Set fld = flds("NETWID")
Dim fldsDesc As DAO.Fields
Dim fldDesc As DAO.Field
Dim strNetworkDesc As String
Set fldsDesc = rsNetwork.Fields
Set fldDesc = fldsDesc("NETDES")
With rsNetwork
.MoveFirst
Do While Not .EOF
strNetwork = fld
strNeworkDesc = fldDesc
DoCmd.OpenQuery ("DMA_Delete")
Const QRY_APPEND = "qry_DMA_Append"
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs("qry_DMA_Append")
qd.Parameters(0) = strNetwork
qd.Execute
'save the template as strRegion
Const FILE_PATH As String = "G:\MyPath\CreationTools\"
Dim FullPath As String
strFullPath = FILE_PATH
Const FILE_PATH_TWO As String = "G:\MyPath\"
Dim SavePath As String
strSavePath = FILE_PATH_TWO
Set objXl = GetObject("Book1").Application
With objXl.Application
.Visible = True
.Workbooks.Open "G:\MyPath\NetworkSummaryTemplate.xls"
.Worksheets("DMAQuery").Hide
.Worksheets("DMA Detail").Activate
objXl.Cells(1, 1).Value = strNetworkDesc
.ActiveWorkbook.SaveAs (strSavePath & "Network Detail for " & strNetworkDesc & ".xls")
.ActiveWorkbook.Close
End With
.MoveNext
Loop
DoCmd.SetWarnings True
End With
End Sub