Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Serious code help wanted. (Office 2000)

    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

  2. #2
    Lounger
    Join Date
    Jun 2001
    Location
    Syracuse, NY
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Serious code help wanted. (Office 2000)

    And those errors would be what?

    It would help when you posted questions if you did a little more work and supplied some detail.

    Jim.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Serious code help wanted. (Office 2000)

    Please explain what kind of errors you're getting rather than inviting us to guess. Have you inserted breakpoints into the code to see which line or lines are causing you problems? That's the usual approach when you get errors.
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Serious code help wanted. (Office 2000)

    If a file with the same name exists then the user is prompted to make a choice of overwrite the file or giving it another name, so when I give a different name to the file, an error message is displayed stating that the file can't be saved with the name I gave but it saves it with that name. (This a todays error message)

    When I try to overwrite the file, it overwrites the file and outputs a message: Main Switchboard can't save the output data to the file you've selected.

    Why does it give me the error message when there is no need for it coz it does what its supposed to do.

    No charlotte, I did not insert any break points coz I don't know how.

    PLEASE help me......

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Serious code help wanted. (Office 2000)

    NO WORRIES!
    I've done it myself. It was the DoCmd.Output after the last EndIf statement which caused it.

    Thanx 2 everyone who tried to help me.
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •