Variable Procedure Name 1

[pluto1415]

I've got a procedure that calls another procedure - currently called OpenMergedDocs()

OpenMergedDocs() calls individually named merge functions for specific documents - ie OpenDoc1, OpenDoc2, OpenDoc3, (up to 25).

It works perfectly.

Now what I need to do is make a separate OpenMergedDocs() procedure for each State - ie OpenMergedDocsAL, OpenMergedDocsAK, etc... That will call different combinations of documents. (we need documents 1,2,3 in AL and 1,3 in AK)

The state is determined by a field value in a recordset. I need a way to basically call OpenMergedDocsXX() where XX is the state. I've tried everything I can think of, but it doesn't seem to like a variable in a call statement.

Any ideas?

 

[Andrzejek]

If you want to have one precudure for each State, you will need 50 procedures. That's crazy. Unless different States require different procedure for some reason.

Can you just pass the State into one procedure:

Call OpenMergedDocs("AL")
or
Call OpenMergedDocs("AK")
or[blue]
Call OpenMergedDocs(rst.Fields("StateABR").Value)[/blue]

Private Sub OpenMergedDocs(strState As String)
[green]   'Do your magic here for a given State[/green]
End Sub

Have fun.

---- Andy

 

[PHV]

Why not simply use the Application.Run method ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pluto1415]

I was thinking I need to have a different procedure for each state for a couple reasons.
1 - each state has state approved documents (and they are stored in sub-folders on our network by state) you cannot use AL#1 if the state is AK, you have to use AK#1.
2 - There are 25 possible documents, but not all documents are used by all states (AL uses 1,2,3 and AK uses 1,3,4)
3 - Each document has it's own SUB as different pieces of data need to be merged. (1 = insured name and address, 2=policy owner name, 3 = insured name and address AND policy owner name)

If I try to have OpenMergedDocs(AL) and OpenMergedDocs(AK) I get an error about duplicate procedure names - so I thought I need to have OpenMergedDocsAL() and OpenMergedDocsAK()

 

[Andrzejek]

A lot of assumptions here on my side, but if you have 25 combinations depending on the State from your db, and you have some folders per state on your drive (server),
[tt]
\\ntsvr2\SomeFolder\AL\
\\ntsvr2\SomeFolder\AK\
\\ntsvr2\SomeFolder\TX\
\\ntsvr2\SomeFolder\CA\[/tt]

how about:

[green]'Loop thru your rst here and pass the State [/green]
Call OpenMergedDocs(rst.Fields("StateABR").Value)

Private Sub OpenMergedDocs(strState As String)
    [green]'Set the Folder on your drive:[/green]
    Folder = "\\ntsvr2\SomeFolder\" & strState & "\"

    Select Cace strState
        Case "AL", "TX", "CA"
            [green]'Proccess docs 1, 2, 3[/green]
        Case "AK", "IA", "IL"
            [green]'Proccess docs 1, 3, 4[/green]
        Case ...
            [green]nother 23 cases here[/reen]
    End Select
End Sub

Have fun.

---- Andy

 

[pluto1415]

I like what you're thinking. I'm going to give it a shot this weekend. Thanks for the idea!

I have no formal VBA training, just what I've learned from working my way through things, so what I'm thinking frequently isn't the easiest/best way to do things. Everyone here has been extremely helpful.

 

[pluto1415]

I got it working! I still have some work to do, I'm sure it's not very clean code, but it's doing what I need it to for now.

 

[Andrzejek]

In many cases that's the scenario - you just make it work, no matter how 'pretty' or 'ugly' the code or logic is, make sure it works right, and then - usually Friday or Saturday night - it comes to you: better, more efficient code and simpler logic.

The trick is to remember your new solution until Monday morning….


Have fun.

---- Andy