Change of fields in Array Module

[Wayne Cramer]

I have an array module which opens a report and sends vendor report cards (245) as RTF files to a folder. The reports are based on the vendor name. All was going well qwhen the client decided that the file name needed to use the vendor number vs the vendor name. The vendor number is a field in the report "Report Card YTD" called Vendor No (Yes, I know it should not have spaces but I inherited this). Is there a way to include this Vendor No field to make the file anme use the Vendor No instead of the Vendor Name? Everything I try I get an External Name Not Defined error.

Function SendRTFAutoMonthYear()
On Error GoTo Err_Command0_Click
Dim strSQL As String
Dim strFileName As String
Dim cVendorArray(241)
Dim cVendorName

DoCmd.OpenReport "Report Card YTD", acViewPreview, , strSQL


strFileName = cVendorName & "_" & Year(Date) & Format((Month(Date) - 1) & "/1", "MM") & " _ " & "Sourcing" & ".doc"
DoCmd.OutputTo acOutputReport, "Report Card YTD", acFormatRTF, "H:\SUPPLY CHAIN\Report Cards\4 Report Card 2008\RTF Report Cards\" & strFileName, False
DoCmd.Close acReport, "Report Card YTD"

 

[boblarson]

I see you starting to use cVendorName but nowhere do you instantiate or assign a value.

 

[Wayne Cramer]

Bob, I only included part of the code. Right now the module does exactly what it is instructed to do - sends the report page by page as individual reports in RTF format to a specific folder with a file name of Vendor Name + Year Month.RTF. The client now wants the file name to be Vendor Number+ Year Month.DOC. The Vendor Number is a field in the report (and query) the module is based on but I don't know how to (if possible) make it part of the module where it recognizes Ford Motor company is vendor number 884471263 for example. Would it help if I provided the entire module or entire DB?

 

[Wayne Cramer]

Bob. Where is your post on uploading a DB to the site?

 

[ErikSnoek]

Bob, I only included part of the code. Right now the module does exactly what it is instructed to do - sends the report page by page as individual reports in RTF format to a specific folder with a file name of Vendor Name + Year Month.RTF. The client now wants the file name to be Vendor Number+ Year Month.DOC. The Vendor Number is a field in the report (and query) the module is based on but I don't know how to (if possible) make it part of the module where it recognizes Ford Motor company is vendor number 884471263 for example. Would it help if I provided the entire module or entire DB?

Show us the part where you assign a value to "cVendorName". It seems a bit weird that you can get the vendor name but have trouble getting the vendor number.

 

[Wayne Cramer]

Here is the entire module. Note that I did not include the entire 241 vendor array but did include the first two and the last two so you can see how it is conctructed. Thanks for any help.


Function SendRTFFiles()
On Error GoTo Err_Command0_Click
Dim strSQL As String
Dim strFileName As String
Dim cVendorArray(241)
Dim cVendorName
cVendorArray(0) = "3M ABRASIVES"
cVendorArray(1) = "3M ADHESIVES DIVISION"
...
cVendorArray(240) = "YORK UNITARY PRODUCT GROUP"
cVendorArray(241) = "ZOELLER COMPANY"
For Each cVendorName In cVendorArray
strSQL = "[Vendor Name] = '" & cVendorName & "'"

'The following line previews the report... To PRINT the report change acViewPreview to acViewNormal
DoCmd.OpenReport "Report Card YTD", acViewPreview, , strSQL

'Remove ' prior to following line to also allow for printout of reports
'DoCmd.PrintOut

'Needed to remove extra apostrophe from filename e.g. LISTA INT''L CORP
cVendorName = Replace(cVendorName, "''", "'")
'Needed to remove slash from filename, e.g. CUMMIN/ONAN
cVendorName = Replace(cVendorName, "/", "-")


strFileName = Format((Month(Date) - 1) & "/1", "MMMM") & " " & Year(Date) & " - " & cVendorName & ".rtf"
DoCmd.OutputTo acOutputReport, "Report Card YTD", acFormatRTF, "H:\SUPPLY CHAIN\Report Cards\4 Report Card 2008\RTF Report Cards\" & strFileName, False
DoCmd.Close acReport, "Report Card YTD"

Next
Exit_Command0_Click:
Exit Function
Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Function

 

[boblarson]

Bob. Where is your post on uploading a DB to the site?

A sticky in the Access FAQ section:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[ErikSnoek]

Aren't these vendors stored in a table?

 

[ErikSnoek]

If they are, then couldn't you just use something like this?

Function SendRTFFiles()
On Error GoTo Err_Command0_Click
    Dim strSQL As String
    Dim strFileName As String
    Dim rstVendors As DAO.Recordset
    
    Set rstVendors = CurrentDb.OpenRecordset("SELECT Vendor_No, Vendor_Name FROM tblVendors")
    
    If Not rstVendors.EOF Then
        rstVendors.MoveFirst
        Do While Not rstVendors.EOF
            strSQL = "[Vendor Name] = '" & cVendorName & "'"
            
            'The following line previews the report... To PRINT the report change acViewPreview to acViewNormal
            DoCmd.OpenReport "Report Card YTD", acViewPreview, , strSQL
            
            'Remove ' prior to following line to also allow for printout of reports
            'DoCmd.PrintOut
            
            'Needed to remove extra apostrophe from filename e.g. LISTA INT''L CORP
            cVendorName = Replace(cVendorName, "''", "'")
            'Needed to remove slash from filename, e.g. CUMMIN/ONAN
            cVendorName = Replace(cVendorName, "/", "-")
            
            strFileName = Format((Month(Date) - 1) & "/1", "MMMM") & " " & Year(Date) & " - " & rstVendors!Vendor_Name & "(" & rstVendors!Vendor_No & ").rtf"
            DoCmd.OutputTo acOutputReport, "Report Card YTD", acFormatRTF, "H:\SUPPLY CHAIN\Report Cards\4 Report Card 2008\RTF Report Cards\" & strFileName, False
            DoCmd.Close acReport, "Report Card YTD"
            
            rstVendors.MoveNext
        Loop
        
        rstVendors.Close
        Set rstVendors = Nothing
Exit_Command0_Click:
    Exit Function
    
Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click
End Function

 

[Wayne Cramer]

I tried this and got an If without End If error. When i corrected that I got a "Too Few Parmeters - Expected 2" error. Can I direct the OpenRecordset to a query vs a table?

 

[ErikSnoek]

Woops sorry about the missing "End If". Did you place it under "Loop"? That's where it should be. On what line are you getting that error?
Also, yes you can use a query in OpenRecordset. Just use

CurrentDb.OpenRecordset("yourQueryName")

 

[Wayne Cramer]

When I step into the module it goes through with no prioblem then pops up the error "Too few parameters. Expected 2." error. I tried to compress the application to upload but even after compact and repair and compression it is just too large. Thanks for the help so far.

 

[Wayne Cramer]

Erik and Bob,
I think you are saying that the array module I inherited may not be the best way to handle this operation. Basically I have one report of 245 vendors, each vendor with it's own page copntrolled by one query. I need to send a RTF (or more ideally a doc) file for each page of the report and the client wants a specific file name convention of Vendor No_Date as 200804_Sourcing.doc (for example: 20001097_200804_Sourcing.doc). Vendor Name and Vendor No are both fields in the controlling query. Maybe my thinking has been stuck on modifying the existing array module when there is a better way of handling this. The array is a pain because each time there is a change in vendors the module needs to be modified. Help.