MS Access - VBA - Take file name from Source to Destination path in File Dialog Picker

Hey,

I have a form that have two text boxes as Source and Target. The Source take the source path and the destination textbox xopy the file to the destination path. Copying file is working well, i want that when the user copy the file from Source to destination in file dialog picker it also take the file name when the destination is open from source with it's extension. Is there a way to achieve this?

Private Sub txtSource_Click()
 Dim Dialog      As FileDialog
    
    Dim Selected    As Long
    
    Set Dialog = FileDialog(msoFileDialogFilePicker)
    With Dialog
        .AllowMultiSelect = False
        .InitialFileName = Nz(Me!txtSource.Value)
        .Title = "Select file to copy"
        Selected = .Show
        If Selected <> 0 Then
            Me!txtSource.Value = .SelectedItems.Item(1)
        End If
    End With
    
    Cancel = True
End Sub

Private Sub txtTarget_Click()
Dim Dialog      As FileDialog
    
    Dim Selected    As Long
    Set Dialog = FileDialog(msoFileDialogSaveAs)
    With Dialog
        .AllowMultiSelect = False
        .InitialFileName = Nz(Me!txtTarget.Value)
        .Title = "Name saved file"
         
        Selected = .Show
        If Selected <> 0 Then
         Me!txtTarget.Value = .SelectedItems.Item(1)
         TargetFile = .SelectedItems.Item(1)
            If Not IsNull(Me!txtSource.Value) Then
                 FileCopy Me!txtSource.Value, Me!txtTarget.Value
                 Me.txtTarget = Split(TargetFile, ":")(1)
            End If
           ' Application.FollowHyperlink Me.txtTarget
            
        End If
    End With
    
    Cancel = True
   
End Sub

Open in new window


Also as shown in first image to copy file the file name is test, i want that when the other target location open there file name test should come automatically with extension also.

Thank you.
getfilename1.png
getfilename2.png
LVL 1
WSStudentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
You are close. Change to:

With Dialog
    .AllowMultiSelect = False
    .InitialFileName = Nz(Me!txtSource.Value)

Open in new window

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WSStudentAuthor Commented:
it works.

Any error pop up if you can guide me in this: it's run time error says "Permission Denied contact Administrator", i am the administrator of the server and the write permission is set to yes. When i manually copy the file it's working but when i try to copy using this VBA code it's giving this error. Although considering the file gets copied but still it's poping up this error, do you have any idea about this?

Thank you Gustav.
Máté FarkasDatabase Developer and AdministratorCommented:
I don't exactly understand your problem because your code works fine.
You did not ask any question or problem.
In SaveAs dialog you have to set InitialFileName property to show a default filename but while your txtTarget.Value is empty it will show an empty filename. You probably have to replace this:
.InitialFileName = Nz(Me!txtTarget.Value)

Open in new window

to this:
.InitialFileName = Nz(Me!txtTarget.Value, "Test.xlsx")

Open in new window

and see what happens.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

WSStudentAuthor Commented:
As you can see in the image the file is copied as i have write permission. Also when i manually tried to copy its working without any error.Why is this error coming? error.png
WSStudentAuthor Commented:
@Mate, the issue was that the initial file name was empty and i wanted the file name to be the one from source in the file dialog, the solution which Gustav mention start working as in the target location i was taking the target name file, it should be the initial filename from Source.

I asked a question, i was close just a bit problem which Gustav solution solved.

Sorry for not explaining well.
Gustav BrockCIOCommented:
OK.
The file permission error is completely separate issue - so don't blame your code for this.

/gustav
WSStudentAuthor Commented:
yeah but why is this coming just in Access ? like if i copy manually it doesn't appear. The code which i mention is the only code im using to copy. Is there anything other than copying also?
Gustav BrockCIOCommented:
Perhaps the path is listed (or not listed) in:

Settings, Center for Security etc., Settings for Center for Security etc., Trusted Paths

/gustav
WSStudentAuthor Commented:
Thank you.

For the other error i think i should ask another question as that is something different i guess.

Thank you.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
WS, for the permissions, if you are sure you have sufficient Windows privileges, perhaps:

File, Options, Trust Center, Trust Center Settings, Trusted Locations --> check: Allow Trusted Locations on my network

?

please let us know if this works, thanks

have an awesome day,
crystal
WSStudentAuthor Commented:
Thank you Crystal, Allow Trusted Locations on my network is set to true like the it's checked but still the same. Anything else to check?
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome. I notice you are writing to N:\ ... did you also do as Gustav suggested and set N:\ as a trusted location? same dialog box -- "Add new location" -- and when you set that, be sure to check "Subfolders of this location are also trusted", if you want to be able to use any file on the drive
WSStudentAuthor Commented:
yes the server is mapped to N drive. i try setting as Gustav said and it show this, why is this not getting trusted location here??? Kindly Look at the attach image.
error1.png
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
apparently you are blocked from using the root of N:

make a directory and use that instead.  Perhaps something like N:\temp
then the file would be N:\temp\test3.pdf

... or maybe you already have a folder you can use?
WSStudentAuthor Commented:
I am the Administrator of this server so how could i be blocked from using root? i tries with directory also as N:\GLO which is there and still it gives the same error. Is there anything i should change in server ? The permission for this N:\ are set to Read,Write and Execute. Yes it's not allowed to modify because in that case user can delete the file also which is a huge mess. But as the write permission and other are enable for this folder it should work and mark as trusted.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hard to guess without being there and exploring ... what about using a different drive letter? What is the N: drive mapped to?
WSStudentAuthor Commented:
N drive is mapped to Server Address like \\163.xx.xx.x\D, first is the server address and in the server there is a folder D which is mapped to my PC.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
>>  folder D which is mapped to my PC <<

can you not use a local location instead then?  do you have any local drives or paths that automatically synchronize to the server?
Gustav BrockCIOCommented:
Even if you are administrator, you should normally not run as such. Thus, the shared folder for this purpose should be granted rights for "Users" (or "Domain Users" if on a domain).

/gustav
WSStudentAuthor Commented:
@Crystal, As it is mapped to my local PC as N:\ so it's a local path which is synchronize as i add something here it is there also on server, isn't it?

@Gustav,
the shared folder for this purpose should be granted rights for "Users" (or "Domain Users" if on a domain).
how can i do that? how can i grant right?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.