SaveAs Dialogue box

[jschmidt]

Hello everyone,

I have a button on a form that calls a function to populate some textboxes with where criteria that is used in a parameterized query this is then used as the table source for a macro which is calling the transfertext function.

What I want to be able to do is have a saveas dialog box open when they click the button and populate a textbox with the path and filename the user enters. Then I want to use the variable with the stored path as the filename argument in the transfertext function.

So how do I get the saveas dialogue box to open and also, how do I save the path and filename in a variable?

Any Suggestions? Is this possible?

Thanks to everyone for the help.

[JKing]

I think this is what you're looking for

[code=vb]
Private Sub cmdYourButton_C lick()

Dim dlgSaveAs As FileDialog
Dim strFilePath As String
Dim strFileName As String

Set dlgSaveAs = Application.Fil eDialog(msoFile DialogSaveAs)

dlgSaveAs.Show
strFilePath = dlgSaveAs.Selec tedItems(1)

Me.yourTextBox = strFilePath

strFileName = Right(strFilePa th, Len(strFilePath ) - InStrRev(strFil ePath, "\"))
strFilePath = Left(strFilePat h, InStrRev(strFil ePath, "\"))
End Sub
[/code]
Replace youTextBox with the name of your text box and of course code this for your command button's click() event.

[jschmidt]

I think this is what you're looking for

[code=vb]
Private Sub cmdYourButton_C lick()

Dim dlgSaveAs As FileDialog
Dim strFilePath As String
Dim strFileName As String

Set dlgSaveAs = Application.Fil eDialog(msoFile DialogSaveAs)

dlgSaveAs.Show
strFilePath = dlgSaveAs.Selec tedItems(1)

Me.yourTextBox = strFilePath

strFileName = Right(strFilePa th, Len(strFilePath ) - InStrRev(strFil ePath, "\"))
strFilePath = Left(strFilePat h, InStrRev(strFil ePath, "\"))
End Sub
[/code]
Replace youTextBox with the name of your text box and of course code this for your command button's click() event.

First of all thanks for the prompt response.
There seems to be a problem with the FileDialog type. Is this a user defined type?

[JKing]

No, it is a member of the Microsoft Office 11.0 Object Library. Check to make sure you have a reference to that by going to tools > references. If it's not checked just find it and check that off.

[jschmidt]

Tools > References in what application? I am using MS Access 11.6

[jschmidt]

Tools > References in what application? I am using MS Access 11.6

I found it. It was selected and I still get the same error.

[JKing]

Sorry I should have be more clear. In the Microsft Visual Basic Editor. There should be a menu item Tools from there navigate to references.

[jschmidt]

It says

Comple Error -

User defined type is not declared.

[JKing]

What version of access are you using?

[jschmidt]

What version of access are you using?

Microsoft Access (11.6566.6568) SP2

[JKing]

Check the references once more and make sure you haven't mistaken Microsoft Access 11.0 Object Library for Microsoft Office 11.0 Object Library. Both should be checked.

[jschmidt]

Check the references once more and make sure you haven't mistaken Microsoft Access 11.0 Object Library for Microsoft Office 11.0 Object Library. Both should be checked.

That was the problem. Thanks!!!

[jschmidt]

strFilePath = dlgSaveAs.Selec tedItems(1)

When the user clicks on cancel after the save as dialog box is opened I get an error on this piece of code. Is there a way to avoid this?

Error: "Invalid procedure call or argument"

[JKing]

You can trap this by using the show method. It returns false on cancel.

[code=vb]
Dim dlgSaveAs As FileDialog
Dim strPath As String

Set dlgSaveAs = Application.Fil eDialog(msoFile DialogSaveAs)

If dlgSaveAs.Show = True Then
strPath = dlgSaveAs.Selec tedItems(1)
Else
MsgBox "Save was cancelled"
End If
[/code]

[newwin]

Hi,I am using Microsoft Access 12.0 Object Library and Microsoft Office 12.0 Access Database Engine Object Library then what should i do because its not running. Both are checked. Please reply. I am waiting.