Can somebody please help me with this:
highlighted during error: Set Rsdb = DB.OpenRecordset("qry_bscactivity", dbOpenDynaset)
heres the code:
Private Sub Command0_Click()
Dim DB As Database
Dim Rsdb As Recordset
Dim i As Integer, j As Integer
Dim RsSql As Variant
Dim CurrentValue As Variant
Dim CurrentField As Variant
Dim Workbook As Object
Dim xlApp As Object
Dim Sheet As Object
Set DB = DBEngine.Workspaces(0).Databases(0)
Set Rsdb = DB.OpenRecordset("qry_bscactivity", dbOpenDynaset)
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Add
Set Sheet = xlApp.ActiveWorkbook.Sheets(1)
j = 1
' Loop through the Microsoft Access field names and create
' the Microsoft Excel labels.
For i = 0 To Rsdb.Fields.Count - 1
CurrentValue = Rsdb.Fields(i).Name
Sheet.Cells(j, i + 1).Value = CurrentValue
Next i
j = 2
' Loop through the Microsoft Access records and copy the records
' to the Microsoft Excel spreadsheet.
Do Until Rsdb.EOF
For i = 0 To Rsdb.Fields.Count - 1
CurrentField = Rsdb(i)
Sheet.Cells(j, i + 1).Value = CurrentField
Next i
Rsdb.MoveNext
j = j + 1
Loop
' Print the Microsoft Excel spreadsheet.
'Sheet.PrintOut
' Close workbook without saving.
'xlApp.ActiveWorkbook.Saved = True
Set Sheet = Nothing
'xlApp.Quit
Set xlApp = Nothing
End Sub
here's the query referred to:
SELECT [tbl_bscactivity].[network_element] AS [Network Element], [tbl_bscactivity].[implementation_date] AS [Implementation Date], [tbl_bscactivity].activity AS Activity, [tbl_bscactivity].[mar_reference] AS [MAR Reference], [tbl_bscactivity].[service_affecting] AS Effect, [tbl_bscactivity].[site_name] AS [Site Name], [tbl_bscactivity].[start_time] AS [Start Time], [tbl_bscactivity].[end_time] AS [End Time], [tbl_bscactivity].remarks AS Remarks, [tbl_bscactivity].[contact_person] AS [Contact Person], [tbl_bscactivity].[bsc_engr] AS [BSC Engr], *
FROM tbl_bscactivity
WHERE ((([tbl_bscactivity].[network_element])=IIf([forms]![frm_reports]![frmnetworkelement].[value] Like "BSS Iligan",[network_element],[forms]![frm_reports]![frmnetworkelement].[value])) AND (([tbl_bscactivity].[implementation_date]) Between [forms]![frm_reports]![beginningdate] And [forms]![frm_reports]![enddate]))
ORDER BY [tbl_bscactivity].[network_element] DESC;
highlighted during error: Set Rsdb = DB.OpenRecordset("qry_bscactivity", dbOpenDynaset)
heres the code:
Private Sub Command0_Click()
Dim DB As Database
Dim Rsdb As Recordset
Dim i As Integer, j As Integer
Dim RsSql As Variant
Dim CurrentValue As Variant
Dim CurrentField As Variant
Dim Workbook As Object
Dim xlApp As Object
Dim Sheet As Object
Set DB = DBEngine.Workspaces(0).Databases(0)
Set Rsdb = DB.OpenRecordset("qry_bscactivity", dbOpenDynaset)
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Add
Set Sheet = xlApp.ActiveWorkbook.Sheets(1)
j = 1
' Loop through the Microsoft Access field names and create
' the Microsoft Excel labels.
For i = 0 To Rsdb.Fields.Count - 1
CurrentValue = Rsdb.Fields(i).Name
Sheet.Cells(j, i + 1).Value = CurrentValue
Next i
j = 2
' Loop through the Microsoft Access records and copy the records
' to the Microsoft Excel spreadsheet.
Do Until Rsdb.EOF
For i = 0 To Rsdb.Fields.Count - 1
CurrentField = Rsdb(i)
Sheet.Cells(j, i + 1).Value = CurrentField
Next i
Rsdb.MoveNext
j = j + 1
Loop
' Print the Microsoft Excel spreadsheet.
'Sheet.PrintOut
' Close workbook without saving.
'xlApp.ActiveWorkbook.Saved = True
Set Sheet = Nothing
'xlApp.Quit
Set xlApp = Nothing
End Sub
here's the query referred to:
SELECT [tbl_bscactivity].[network_element] AS [Network Element], [tbl_bscactivity].[implementation_date] AS [Implementation Date], [tbl_bscactivity].activity AS Activity, [tbl_bscactivity].[mar_reference] AS [MAR Reference], [tbl_bscactivity].[service_affecting] AS Effect, [tbl_bscactivity].[site_name] AS [Site Name], [tbl_bscactivity].[start_time] AS [Start Time], [tbl_bscactivity].[end_time] AS [End Time], [tbl_bscactivity].remarks AS Remarks, [tbl_bscactivity].[contact_person] AS [Contact Person], [tbl_bscactivity].[bsc_engr] AS [BSC Engr], *
FROM tbl_bscactivity
WHERE ((([tbl_bscactivity].[network_element])=IIf([forms]![frm_reports]![frmnetworkelement].[value] Like "BSS Iligan",[network_element],[forms]![frm_reports]![frmnetworkelement].[value])) AND (([tbl_bscactivity].[implementation_date]) Between [forms]![frm_reports]![beginningdate] And [forms]![frm_reports]![enddate]))
ORDER BY [tbl_bscactivity].[network_element] DESC;
thanks Tim,