FileDialog

[PSSMargaret]

I found the below code at http://stackoverflow.com/questions/14915179/ms-access-browse-for-file-and-get-file-name-and-path. and it works except if a folder or file name has an "#" in the name it won't enter the full path and file name in the textbox. Any idea how to alter this so it enters the entire path to a file no matter what is in the file path or name?

Private Sub Command7_Click()
    Dim f As Object
    Dim strFile As String
    Dim strFolder As String
    Dim varItem As Variant

    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = True
    If f.Show Then
        For Each varItem In f.SelectedItems
            strFile = Dir(varItem)
            strFolder = Left(varItem, Len(varItem) - Len(strFile))
            MsgBox "Folder: " & strFolder & vbCrLf & _
                "File: " & strFile
        Next
    End If
    Set f = Nothing
End Sub

 

[MarkK]

The way I read your post you are saying the code works, that it assigns a value to a textbox, except if there is a "#" in the filename. But I don't see in the code where a textbox is the target of an assignment. This can't be the code that is failing the way you describe.

 

[PSSMargaret]

Sorry, I posted the wrong code. File textbox name is Me.Location below.

Private Sub cmdAddLink_Click()

    Dim f As Object
    Dim strFile As String
    Dim strFolder As String
    Dim varItem As Variant

    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = False
    
    If f.Show Then
        For Each varItem In f.SelectedItems
            strFile = Dir(varItem)
            strFolder = Left(varItem, Len(varItem) - Len(strFile))
            'MsgBox strFolder & vbCrLf & strFile
            Me.Location = strFolder & strFile
        Next
    End If
    Set f = Nothing
    
    DoCmd.RefreshRecord
 
    Forms!frmRescNew!Type.SetFocus

End Sub

 

[MarkK]

I tested this code...

    Dim f As Object
    
    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = False
    
    If f.Show Then Me.Text0 = f.SelectedItems(1)
    Set f = Nothing

Since you are setting AllowMultiSelect = False, there is no need to loop through the SelectedItems, since there will only be one.

It looks like you may have the Textbox.IsHyperlink property = True. If I make that setting in my test I can reproduce the problem you describe, so it looks like it may solve it to set that property to false.

hth
Mark

 

[PSSMargaret]

If I'm understanding you correctly, you're saying that I only need the code shortened code you responded with, correct? Also, changing the "Is Hyperlink" property for the Location textbox did not fix the issue. I still cannot insert a file name if there is a "#" in the folder path or the file name.

 

[NauticalGent]

Nothing to do with your question, but is your screen name by chance related to a certain Ray Stevens song...?

 

[PSSMargaret]

No, I've never heard the song

 

[NauticalGent]

https://youtu.be/4Wb2nZR6qbE

Some humor while you and MarkK sort this out!

 

[PSSMargaret]

Funny! At least something made me laugh today. Been dealing with one issue after another. Thanks.

 

[MarkK]

More troubleshooting ....

I still cannot insert a file name if there is a "#" in the folder path or the file name.

Can you describe this failure in greater detail. Do you get an error? Does the assignment fail silently? Does it fail partially? What happens if you try to display the contents of the textbox after the assignment, like...

    Me.Location = strFolder & strFile
    MsgBox Me.Location

Does the message box show the correct value? Maybe we are just dealing with a display issue, but the value of the textbox is correct?

And holy crap, that guy in the video should get arrested. That's creepy. #IMO (But I didn't watch it to the end).

Cheers NGent!

 

[PSSMargaret]

Here are two examples of the file paths and names with a "#" and what occurs when each is clicked.

C:\Users\muggs\Documents\Test File\Test Document #1

When the textbox with the above is clicked, you first receive a Microsoft Security Warning Notice, which is another hurdle to overcome. The message states "This location may be unsafe" and lists the file name "1.docx". When I click Yes to continue, you get the message "Unable to open 1.docx. Cannot open the specified file."

C:\Users\muggs\Documents\Test File #1\Test Document 1

When the textbox with the above is clicked, you first receive a Microsoft Security Warning Notice. The message states "This location may be unsafe" and lists the file name "1\Test Document 1.docx", but it continues on and opens the folder "C:\Users\muggs\Documents\Test File" anyway. I click Yes to continue and receive the error message "Unable to open "1\Test Document 1.docx". Cannot open the specified file".

When I alter the code to the below that you provided, a message displays listing the entire file name correctly.

Me.Location = strFolder & strFile
MsgBox Me.Location

I probably should have thought the video was creepy but needed a good laugh.

Thanks for your assistance.

 

[MarkK]

1) So it looks like you need to find the "Trust Center" in Access, and go in there and declare certain disks and/or folders as "Trusted Locations."

2) Then, let's back up and you try this code....

Private Sub cmdAddLink_Click()
    With Application.FileDialog(3)
        .AllowMultiSelect = False
        If .Show Then Me.Location = .SelectedItems(1)
    End With
End Sub

...which simplifies things to what I think you are trying to do. Change any names if you need to, because I'm just guessing, OK? And let's see what goes wrong after that...

hth

 

[PSSMargaret]

I don't understand why I would have to declare my own hard drive as a Trusted Location, however, I went ahead and added my C:\ drive and C:\Users\Muggs\Documents\ and then tried your simplified code and it did the same thing truncating everything after the "#" symbol.

This data will be used by ~70 users so for all of them to have to declare folders in the trust center would not work but to be thorough I did want to check it.

 

[MarkK]

But you previously reported a "Microsoft Security Warning Notice" and you are not reporting this now. Did that change? Is that solved? That was the object of the trusted locations exercise. Also, the trusted locations warning is a registry setting that you can programmatically set if you have multiple machines to configure. Check into .reg files, but there are countless ways to edit the registry.

 

[PSSMargaret]

Yes, still get the Microsoft Security Warning Notice. To change the registry on ~70 machines so they don't get a warning notice doesn't sound feasible but that's a different issue. Got to resolve this issue first.

 

[MarkK]

To further troubleshoot the only partial appearance of the path, can you amend the code as follows...

Private Sub cmdAddLink_Click()
    With Application.FileDialog(3)
        .AllowMultiSelect = False
        If .Show Then 
           Me.Location = .SelectedItems(1)
           MsgBox "Loc: " & Me.Location
           MsgBox "IsLink: " & Me.Location.IsHyperlink
        End If
    End With
End Sub

... so we can test whether the value is getting properly assigned, but just isn't visible in the control.
Cheers,

 

[PSSMargaret]

First pop up message states . . .

"Loc:C:\Users\muggs\Documents\Test File\Test Document #1.docx"

I click OK

Second pop up message states . . .
IsLInk: True

I click ok

But the file name is still inserted into the text box Location truncated.

 

[MarkK]

No. I believe all the text is assigned, but the textbox only shows the text up to the "#" when Textbox.IsHyperlink = True. To test further, what happens when you do...

Private Sub cmdAddLink_Click()
    Me.Location.IsHyperlink = False
    With Application.FileDialog(3)
        .AllowMultiSelect = False
        If .Show Then Me.Location = .SelectedItems(1)
    End With
End Sub

Also, you can add a button to your form and toggle the state of the control, and watch it show and hide the text after the first "#"...

private sub cmdToggleIsHyperlink_CLick()
   me.location.ishyperlink = not me.location.ishyperlink
end sub

See how that just toggles the property on and off with each click, and see how it changes the display of the textbox?
hth

 

[PSSMargaret]

If I change the properties of the textbox to False, it still is not showing the whole path and file name. It's not just occurring when "Is Hyperlink" = True.

I tried the new code with no change; the entire file name is still not showing. Also, the toggle button didn't change anything when clicked.

 

[MarkK]

Margaret, I am not able to replicate your result, so I am not able to troubleshoot any further. If you post a database that demonstrates this problem I will take a look at it, but I am out of bullets.