Results 1 to 3 of 3

Thread: Split DB

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    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:
    Code:
        DoCmd.OutputTo acOutputReport, str_Report_Name, acFormatPDF, MyPath & MyFilename, False
    I've also tried this code and it fails also:

    Code:
        'DoCmd.OpenReport str_Report_Name, acViewPreview, "", str_Where, acWindowNormal, ""   'Opens the report
    Code:
    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
                    Me.PCP_ProviderFAX.SetFocus
                    GoTo Err_Need_FaxNum
                End If
        End If
        
    Err_Need_FaxNum:
            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
                    .Send
                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
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 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
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 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!

    RG

    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
  •