Split DB

Topic

New Reply

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: [INDENT]

Code:

    DoCmd.OutputTo acOutputReport, str_Report_Name, acFormatPDF, MyPath & MyFilename, False

[/INDENT]
I’ve also tried this code and it fails also:

[INDENT]

Code:

    ‘DoCmd.OpenReport str_Report_Name, acViewPreview, “”, str_Where, acWindowNormal, “”   ‘Opens the report

[/INDENT]

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 PriceDrive1Drive1_PandoraRe_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?

Reply | Quote

Replies

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.

Reply | Quote

Two thoughts:
1. Use UNC addressing: \servernameRoberta PriceDrive1Drive1_PandoraRe_Faxes
2. Did you enforce Record Locking and if so at what level? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote