Save As command with variable file name

[bikermo]

asked something similar a while back, got no replies, now i think maybe i've found the right terms to explain myself better.

i'm using a form with an unbound text box to temporarily hold data that i want to use in several other places (all those functions work great). last step is to save the newly created table with a different name (will be used for a mail merge later so must be a separate table or else we'd have to filter thousands of records during each mail merge) and i want to use the data i've temporarily held in my unbound text box within my form as the name in the save as function.

can i specify a variable as a file name within the save as function? is there a better way?

 

[willknapp]

If you're using a Save As File Dialog, you can specify the file name as the InitialFileName in the file dialog prior to displaying it.

    Dim fdlg As FileDialog
    Dim FileName As String
 
    Set fdlg = Application.FileDialog(msoFileDialogSaveAs)
 
[COLOR=seagreen]   ' Set the value in the unbound textbox as the default filename[/COLOR]
    fdlg.InitialFileName = Me.UnboundTextBox
    If fdlg.Show <> "" Then
        FileName = fdlg.SelectedItems(1)   [COLOR=seagreen]' This is the filename ultimately selected by the user[/COLOR]
       [COLOR=seagreen]' Place Code to save file here, using the FileName variable as the new file's name.[/COLOR]
    End If

 

[bikermo]

that seems to do the trick if i want to save the table as a file outside the database, but i just want to save one table with another name within the same database.

starting to switch gears on this and thinking maybe the better idea is to query my table containing all the records (to "filter" for the text in the box on the form) and then programmatically mail merge it with a word doc, rather than create a table for each notification, which will then be used as the merge data source.

of course i can't quite figure out how to do that either, but i believe i'm at least moving forward.

 

[willknapp]

That's really funny - my first response was precisely how to rename the table, but I reread your original post and thought you wanted to save it as a separate file, so I deleted everything and rewrote that!

Anyway, here's how to rename a table, assuming you've created a second table that has the data you want:

    Docmd.Rename me.UnboundTextBox, acTable, "Existing Table"

Likewise, you can also copy an existing table with a new name, assuming you want to existing table to stick around:

    Docmd.CopyObject , me.UnboundTextBox, acTable, "Existing Table"

Ultimately, though, creating a query based on your source table, and using that new query as the source for your mail merge is the way to go, particularly if the query will have the same parameters every time you run it.

 

[bikermo]

WOO HOO!

DoCmdCopyObject did it! you're my hero. not the final solution, but that gets me a step closer to full automation of this part of the process anyway, and that is a BIG help.

thanks a MILLION!