Need help eliminating charactors in MS naming rules when saving a report to a file 2

[Johnnycat1]

I have been using the following code to email a report after changing its name. The key is that it takes the first two words in the vendor company's name and truncates the rest. The issue is that if the vendor has as a comma in their name the name violates the MS naming rules and cannot be saved. The blue line in the code is the specific code for the vendor name.

How can I modify the code to perform the same renaming function and to delete any commas in the vendor name or to replace the comma with another item that doesn't violate the naming rules?

As always your help is very appreciated! This function is used extremely frequently and helps us standardize our file naming formats (on purchase orders).

    If Nz(Me![Project Name], 9) = 9 Then Exit Sub

Refresh

    Dim stDocName As String
    Dim stReportName As String
    Dim StrDblSpace As String
    Dim StrVendor As String
        
    stReportName = "RptPurchaseOrder"
[COLOR=#204A87]    If InStr(Replace(Me.Vendor, " ", Chr(255), 1, 1), " ") < 2 Then StrVendor = Me.Vendor Else StrVendor = Left(Me.Vendor, InStr(Replace(Me.Vendor, " ", Chr(255), 1, 1), " ") - 1)[/color]    
    StrDblSpace = Chr(13) & Chr(10) & Chr(13) & Chr(10)
    stDocName = Me.Abbreviation & " PO " & Me.Text286 & " " & StrVendor & " " & Me.year & IIf(Me.month < 10, "0" & Me.month, Me.month) & IIf(Me.Day < 10, "0" & Me.Day, Me.Day)
    
    DoCmd.Rename stDocName, acReport, "RptPurchaseOrder"
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    DoCmd.SendObject acReport, stDocName, acFormatPDF, , , , ([Forms]![FrmPurchaseOrders]![Project Name]) & " PO#: " & ([Forms]![FrmPurchaseOrders]![Text286])DoCmd.Rename stReportName, acReport, stDocName
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    Exit Sub

Exit_PDFPurshaseOrderRpt_Click:
    Exit Sub

Err_PDFPurshaseOrderRpt_Click:
    MsgBox Err.Description
    DoCmd.Rename stReportName, acReport, stDocName
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    Resume Exit_PDFPurshaseOrderRpt_Click

nanos gigantum humeris insidentes

A dwarf on the shoulders of giants...

 

[dhookom]

You are already using the replace function so it should be simple to replace a comma with an underscore or some other character.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Yes, what Duane said:

If InStr(Replace(Me.Vendor, " ", Chr(255), 1, 1), " ") < 2 Then 
    StrVendor = Me.Vendor 
Else 
    StrVendor = Left(Me.Vendor, InStr(Replace(Me.Vendor, " ", Chr(255), 1, 1), " ") - 1)    
End If
[blue]
StrVendor = Replace(StrVendor, ",", "_")
[/blue]
StrDblSpace = Chr(13) & Chr(10) & Chr(13) & Chr(10)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.