Does anybody see anything wrong with this code, it gives me error messages which differ day to day, PLEASE tell me if you thing something is wrong with this code and what it is.
PLEASE?????
Sub SFM()
Dim strFileName As String, strMsg As String, vResult As Variant
‘On Error GoTo ExportSFMReport_Err
Dim rst As DAO.Recordset, db As DAO.Database
‘Turn System warnings off
DoCmd.SetWarnings False
‘Delete contents of the table
DoCmd.RunSQL “DELETE [tblSFMReportSource].* FROM [tblSFMReportSource] WITH OWNERACCESS OPTION;”, 0
‘Run Append query to add SFM records to the table
DoCmd.OpenQuery “AppendToSFMReportSource”, acNormal, acEdit
‘Turn System warnings on.
DoCmd.SetWarnings True
Set db = CurrentDb
Set rst = db.OpenRecordset(“tblSFMReportSource”)
If rst.BOF And rst.EOF Then
MsgBox “There are no records”, vbOKOnly
Set rst = Nothing
Set db = Nothing
‘Open fax cover
Set objWord = CreateObject(“Word.Basic”)
objWord.AppShow
‘objWord.AppMaximize “”, 1 (optional)
objWord.FileOpen “S:SRI_WO~1TRADEA~1Fax.doc”
Exit Sub
Else
‘Export records to spreadsheet and open it
strFileName = “S:SRI_WORK_AREADOCUME~1” & “BCP” & Format(Now, “DDMMYY”) & “.xls”
vResult = Dir(strFileName)
If vResult “” Then
vResult = MsgBox(“File ” & strFileName & _
” already exists, Would you like to overwrite that file?”, vbYesNo)
If vResult = vbYes Then
DoCmd.OutputTo acOutputQuery, “SFMTradeReport”, acFormatXLS, strFileName, True
Else
strFileName = “S:SRI_WORK_AREADOCUME~1” _
& InputBox(“File ” & strFileName & ” already exists,” _
& Chr(10) & “Please enter another filename not including ” _
& Chr(34) & “.xls” & Chr(34) & “: “) & “.xls”
DoCmd.OutputTo acOutputQuery, “SFMTradeReport”, acFormatXLS, strFileName, True
End If
End If
DoCmd.OutputTo acOutputQuery, “SFMTradeReport”, acFormatXLS, strFileName, True
‘Display message
Beep
MsgBox “Data has been exported successfully.”, vbInformation, “Export Confirmation”
‘Delete contents of the table
DoCmd.RunSQL “DELETE [tblSFMReportSource].* FROM [tblSFMReportSource] WITH OWNERACCESS OPTION;”, 0
Set rst = Nothing
Set db = Nothing
‘AppActivate “Microsoft Excel”
End If
End Sub
