have dynamic report destination/name but want to improve it

[Kim Norgren]

I hope I have followed the posting guidelines; apologies from a neophyte if not, and please correct me. I'm self-taught in VB and have reached my limit, so I'm looking for help. I am dealing with multiple permutations: source report, report name, destination, etc. I've created a form where the user selects options; I have functional code (thanks in part to postings here) but am trying to pull out repetitive portions into a public sub so if there are changes to the main variables I only have to make them once. Here's the relevant functional code:

' creating dynamic report output destination and name
' %O = output drive, %R = responsible team, %T = term, %Y = academic year, %D = date
 
    Dim strReportName As String
 
    strReportName = "%O\Internal_%RByInstructor_%T%Y_%D.pdf"
 
    strReportName = Replace(strReportName, "%O", IIf(Forms!frmMainMenu!optgrpDestination = 1, "H:", "O:"))
    strReportName = Replace(strReportName, "%R", Switch(Forms!frmMainMenu!optgrpResponsibleTeam = 1, "All", Forms!frmMainMenu!optgrpResponsibleTeam = 2, "Education", Forms!frmMainMenu!optgrpResponsibleTeam = 3, "Practice", Forms!frmMainMenu!optgrpResponsibleTeam = 4, "Research"))
 
    strReportName = Replace(strReportName, "%Y", Me.cboYear)
    strReportName = Replace(strReportName, "%T", Me.cboTerm)
    strReportName = Replace(strReportName, "%D", Format(Date, "yy-mm-dd"))

My problem is that I have to repeat this for each of 6 reports (internal/external and by course/by instructor/ by team). The two other variables fall where this snippet has \Internal (following %O) and ByInstructor_ (between %R and %T). I tried making this code (minus the line with those variables) a public sub and calling it from a private sub but I can't get it to work. In "Public Sub DynamicReportName()" I changed the line with variables to read

strReportName = "%O%I%R%N%T%Y_%D.pdf"

but left the rest of the code defining the variables the same. I confirmed that this generates a report name with the appropriate variables for O, R, T, Y, and D, and leaves %I and %N waiting for replacement. Then in the individual sub I tried this:

    Dim strReportName As String
 
    Call DynamicReportName
 
    strReportName = Replace(strReportName, "%I", "\Internal_")
    strReportName = Replace(strReportName, "%N", "ByInstructor_")

When that didn't work (I got the standard Windows dialog box asking for the file destination/name) I tried outputting "strReportName" to a dialog box to see what I have (I haven't figured out how to use the Immediate window, I only know of its existence), and it's blank. I thought perhaps the problem was that I needed to use one name in the public sub (strOutput) and another in the private sub (strReportName), setting strReportName = strOutput, but that didn't work either.

I'm using Access 2007.

Thanks for any help you can provide! Worst case scenario is I modify 6 times if the drive designations or teams change, but I'm hoping to learn something.

[NeoPa]

Kim,

I like your code. It has style. There are a number of tips I can give to someone in your position. Let me drop a few in :

1. When you call the DynamicReportName procedure, it is possible, if set up to do so, to pass values to the procedure. You could pass the name of the form you're calling it from, or even just the %I & %N values so the whole job could be done within the procedure.
2. I see the use of Me. in your procedure code. This is only valid if the procedure code is within a Form Module. Public procedures in Form Modules can only be called from outside their own Form Module if properly qualified.
3. Multiple references to controls found on Forms!frmMainMenu can be recoded by using the With construct in your code. It's more efficient and easier to read generally. More important the more times it is used of course.
4. The Choose() function would be a good one to use in line #9 in place of Switch().
   

   strReportName = Replace(strReportName, "%R", Choose(Forms!frmMainMenu!optgrpResponsibleTeam = 1, "All", "Education", "Practice", "Research"))

In case it helps, here is a function procedure I use very heavily in my code, that does multiple Replace() calls in a single call. You just pass it as many pairs of parameters as you want :

'MultiReplace replaces all occurrences of varParam in strMain with varReplace.
'Using VbBinaryCompare means that case is not ignored.
Public Function MultiReplace(ByRef strMain As String, _
                             ByVal varParam As Variant, _
                             ByVal varReplace As Variant, _
                             ParamArray avarArgs())
    Dim intIdx As Integer
 
    If (UBound(avarArgs) - LBound(avarArgs)) Mod 2 = 0 Then Stop
    MultiReplace = Replace(Expression:=strMain, _
                           Find:=Nz(varParam, ""), _
                           Replace:=Nz(varReplace, ""), _
                           Compare:=vbBinaryCompare)
    For intIdx = LBound(avarArgs) To UBound(avarArgs) Step 2
        MultiReplace = Replace(Expression:=MultiReplace, _
                               Find:=Nz(avarArgs(intIdx), ""), _
                               Replace:=Nz(avarArgs(intIdx + 1), ""), _
                               Compare:=vbBinaryCompare)
    Next intIdx
End Function

It needs to be included in a standard module in your project.

As for why it's not working, can you answer a few questions :

1. You seem to have a procedure called DynamicReportName (contents shown in first code group), but the procedure declaration is missing and we have no info as to where (in which module) it is contained.
2. Without the declaration it's hard to be sure, but the code looks as if it returns the value of strReportName (in which case it's a Function Procedure). That's strange as the call to use it explicitly drops the value it returns. Is this really intentional?

[Kim Norgren]

Thanks so much! This is super, just what I was hoping for. I don't have the code at home, but look forward to incorporating your suggestions next week. As to my errors, I expect my attempt at writing a procedure was all wrong, since your explanation makes sense. The drawback to trying to create databases on an as-needed basis using references and posts; I really need to find a VB class, obviously.

[NeoPa]

My pleasure Kim.

I enjoy helping people who are eager to learn.