Add pathname to textbox within a form (1 Viewer)

19eNVy86

New member
Joined
Oct 25, 2018
Messages
5
Hi all,

Quite new to the Access scene and wondering if it is possible to double click in a text box and be presented a browse folder function in order to attached a file.
Something like:

C:\Users\Desktop\Access_file.txt

I have four of these text boxes.

One other text box I would like to just provide the location of the file so:

C:\Users\Desktop\

Name of one text box is "Invoice File (TXT)".

Thank you in advance,

N
 

CJ_London

Super Moderator
Staff member
Joined
Feb 19, 2013
Messages
11,595
yes it can be done fairly easily, you use the filedialog method - see this link

https://docs.microsoft.com/en-us/office/vba/api/access.application.filedialog

google 'vba filedialog' to find variations of the code

you can set the filters, to limit only seeing txt files or a specific folder for example

Suggest you use something like the double click event on your textbox control to trigger the code

this might be a more helpful link

https://analystcave.com/vba-application-filedialog-select-file/
 

19eNVy86

New member
Joined
Oct 25, 2018
Messages
5
Thanks CJ,

I tried the latter link and copied

Code:
Dim fDialog As FileDialog, result As Integer
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
     
'Optional: FileDialog properties
fDialog.AllowMultiSelect = False
fDialog.title = "Select a file"
fDialog.InitialFileName = "C:\"
'Optional: Add filters
fDialog.Filters.Clear
fDialog.Filters.Add "Excel files", "*.xlsx"
fDialog.Filters.Add "All files", "*.*"
 
'Show the dialog. -1 means success!
If fDialog.Show = -1 Then
   Debug.Print fDialog.SelectedItems(1)
End If
and amended the .xlsx to .txt for this example.
I go to click in the text box and it errors :
Compile error: User-defined type not defined.

It has fDialog As FileDialog highlighted and my Sub highlighted in yellow :
Private Sub Invoice_File_TXT_DblClick(Cancel As Integer)

Any ideas why?
 

CJ_London

Super Moderator
Staff member
Joined
Feb 19, 2013
Messages
11,595
you need to add a reference (in vba window Tools>References) to the Office library.

For a bit of background, this will do what is called early binding - so intellisense will work and objects such as filedialog will be recognised.

Don't want to complicate the issue, but if this app is to be used across a variety of different access versions, you will need to change to late binding at some stage.

see this link for a more detailed explanation

https://www.devhut.net/2016/10/04/late-binding-the-filedialog/

most developers will use early binding whilst developing, then convert to late binding just before the app is distributed, it's not difficult!
 

19eNVy86

New member
Joined
Oct 25, 2018
Messages
5
Thanks for your swift response CJ,

My organisatio only uses one version of Access so for now, I have just ticked the box in Tools > References.

Going forward, I can now find and select my .txt file and then click Open yet, the pathname does not appear in my text box.

I can see the Debug.Print section of the code. I looked at the Immediate Window and can see the full filepath I selected however, how can that be transfered into the text box?

Again, thank you for the responses, they have been very insightful and I have saved those links.
 

CJ_London

Super Moderator
Staff member
Joined
Feb 19, 2013
Messages
11,595
instead of debug, just assign it to your textbox

Invoice_File_TXT= fDialog.SelectedItems(1)
 

19eNVy86

New member
Joined
Oct 25, 2018
Messages
5
THANK YOU!

I was searching Google but everyone's variation of this had me confused. Massive help. Thank you for your time and efforts.
 

CJ_London

Super Moderator
Staff member
Joined
Feb 19, 2013
Messages
11,595
much of the code you will find on line is either 'air code' i.e. an approximation or for a specific purpose or context which is not always explained. It won't necessarily contain error handling, may use a different naming convention to yours, not follow good development practice etc because essentially it is a demonstration of how to do something. Invariably will not be a simple cut and paste job to get it to work as you found here.

You will learn to be able to pick out the relevant bits and adapt to your needs as you become more experienced.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom