Dailog Box in Access DB

[pedie]

Hi I need a function, that will open Dailog Box in Access DB and let me select the file of any type and then make textbox value of the select file.

Thanks,
Pedie

 

[boblarson]

That is a built-in thing now paste this function into a standard module and make sure you don't name anything else, including the module, the name of this function:

Function GetFileName(TextBoxName As TextBox, strInitialDirectory As String)
    Dim fd As Object
Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Title = "Make Selection"
        .InitialFileName = strInitialDirectory 
        .Show
    End With
    TextBoxName.Value = fd.SelectedItems(1)
 
 
End Function

With this you just call the function and pass it the text box name on the form to put the data into. For example:
GetFileName Me.Text32, "C:\Temp"

The C:\Temp part is my initial directory I want it to open to when I do the dialog.

 

[pedie]

Thanks Bob, how do i run this code? or call this fuction?

I thought we call function this way...

[/FONT]
[FONT=Courier New]Sub testrytyu()
Call GetFileName
End Sub

 

[boblarson]

Thanks Bob, how do i run this code? or call this fuction?

I thought we call function this way...

[FONT=Courier New]Sub testrytyu()[/FONT]
[FONT=Courier New]Call GetFileName[/FONT]
[FONT=Courier New]End Sub[/FONT]

You don't need to use the Call keyword. But if you do you need the parens. You also need to pass the parameters which in this function that I've given you is the Text box name where you want the file name put and then the initial directory to go to.

So:

Sub testrytyu()
Call GetFileName(Me.TextBoxNameHere, "C:\")

would be one example

 

[pedie]

Bob, it tried that and it errored...at
msoFileDialogFilePicker this line....compile error variable not defined....

So i removed

[/FONT]
[FONT=Courier New]Option Compare Database
Option Explicit
[/LEFT]
[/FONT][LEFT][FONT=Courier New]

and got error again at
Set fd = Application.FileDialog(msoFileDialogFilePicker)


in the form the code is entered as

[/FONT]
[FONT=Courier New]Private Sub Command8_Click()
Call GetFileName(Me.TextBoxNameHere, "C:\")
End Sub

Thanks again​

 

[boblarson]

two things -

One, go to the VBA Window and TOOLS > REFERENCES and make sure that the appropriate library is selected:

If on Access 2007:
Microsoft Office 12.0 Object Library

If on Access 2010
Microsoft Office 14.0 Object Library

and you need to change the name of the text box in your call to the exact name of YOUR text box, not using TextBoxNameHere as that is just my sample name. You need to use the actual name of your text box there.

 

[pedie]

Bob, 'm sorry i think 'm not troubling you too much...
error at TextBoxName.Value = fd.SelectedItems(1)
it allowed me to select the file then errors "rutime error 2448", you cant assign value to this object

'm i doing something wrong?



Sorry it is working now....
it was the texbox...
I was earlier referring to something else caption....


Thanks Bob!!!

Regards
Pedie

 

[boblarson]

So you are fine with it now?

 

[pedie]

Yes Sir

Bob, thank you soo much! works Perfect now!