Results 1 to 3 of 3

Thread: Split DB

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Louisville, Kentucky, USA
    Thanked 0 Times in 0 Posts

    Unhappy Split DB

    I have a front end and back end Access 2010 db. Both sit on a network and 7 users can access it. I put the following code in so the user can open a report convert it to pdf, save to a network drive that everyone has access to then fax and finally a transmission page is sent to the user's email.

    On my PC the process works as it should, however, it crashes on everyone else's . I'm totally at a loss at to why, the front end is the same one I and everyone else use, so it can't be an issue with a library reference.

    Below is the code I'm having an issue with and this is the line where the code fails:
        DoCmd.OutputTo acOutputReport, str_Report_Name, acFormatPDF, MyPath & MyFilename, False
    I've also tried this code and it fails also:

        'DoCmd.OpenReport str_Report_Name, acViewPreview, "", str_Where, acWindowNormal, ""   'Opens the report
    Public Sub SendFax()
    Dim Cancel As Integer
    Dim rsp As String
    Dim str_MyPath As String
    Dim str_MyFilename As String
    Dim str_Report_Name As String
    Dim str_Where As String
    Dim str_FaxNum As String
    Dim str_ToFaxName As String
    Dim oLook As Object
    Dim oMail As Object
    str_MyPath = "s:\Roberta Price\Drive1\Drive1_Pandora\Re_Faxes\"
    str_MyFilename = sToFaxName & "_MedRec_Request.pdf"
    str_Report_Name = "rpt_Cvr_PS_Mailing_User_Select"
    str_FaxNum = "[FAX: " & str_ToFaxName & "@" & Me.PCP_ProviderFAX & "]"
    str_ToFaxName = Me.PCP_ProviderName
    'We need to verify if we have a FAX number for the PVD--if we don't exit the sub
        If IsNull(Me.PCP_ProviderFAX) Then
            rsp = InputBox("You must enter a FAX #!")
                If IsNull(rsp) Or False Then
                    Me.PCP_ProviderFAX = rsp
                    GoTo Err_Need_FaxNum
                End If
        End If
            Exit Sub
     'We need a form to open while the conversion and faxing takes place  
        Call MESS("Processing Report to PDF and sending Fax.....")
    'Open the report and save it as PDF
        'DoCmd.OpenReport str_Report_Name, acViewPreview
        DoCmd.OutputTo acOutputReport, str_Report_Name, acFormatPDF, MyPath & MyFilename, False
        'DoCmd.OpenReport str_Report_Name, acViewPreview, "", str_Where, acWindowNormal, ""   'Opens the report
        Reports!str_Report_Name.Caption = "Medical Record Request:" & MyFilename    'Renames the Report
        'Let's close our previewed report
        'DoCmd.Close acReport, str_Report_Name
    'Now that we have a FAX NUM and saved the report in PDF to "Drive1), we need to send the fax to the PVD    
        Set oLook = CreateObject("Outlook.Application")
            Set oMail = oLook.createitem(0)
                With oMail
                    .To = str_FaxNum
                    .Body = "See attached"
                    .subject = "Medical Record Request"
                    .Attachments.Add str_MyPath & str_MyFilename
                End With
                    MsgBox "Fax was sent to " & Me.PCP_ProviderName, vbOKOnly
                    'Status = ""
        Set oMail = Nothing
        Set oLook = Nothing
    DoCmd.Close acForm, "frm_Please_Wait", acSaveNo

    And lastly I can't understand why a subform changes poistion on the main form and I have the form properties set to NO on resize.

    Any suggestions would be wonderful. PLEASE?
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Crystal Beach, FL, Florida, USA
    Thanked 40 Times in 39 Posts
    My first suggestion is that you don't have everyone share the frontend. Each user should have a copy of the frontend on their local drive.

    You've hardcoded the path to "S:\roberta price\....". If other users don't have the S: drive mapped the same way, there will be an error. I personally don't like to use mapped drives for this reason.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Manning, South Carolina
    Thanked 1,605 Times in 1,449 Posts
    Two thoughts:
    1. Use UNC addressing: \\servername\Roberta Price\Drive1\Drive1_Pandora\Re_Faxes\
    2. Did you enforce Record Locking and if so at what level?
    May the Forces of good computing be with you!


    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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