MS Access - VBA - Split the path of file and save in textbox

Hi ,

I have a form that have a field as "txtTarget" which save the destination path of file. I want to split the path and just save the require as For example: file path is (W:\GLO\ALL\...) , it should be save in table as (\GLO\ALL\EXMP1.pdf...) OR (\ABC.pdf), the drive name which is c:\ or D:\ should not be saved. In my code i have written code as
Me.txtTarget = Split(TargetFile, "\", 1)(0)

Open in new window

for splitting but it's not working as required.

Any help?

Also if anyone can also guide how to concatenate that back afterwards when require to open this file would be helpful.
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.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
You are taking the first element of the split (0 based) and should use the 2nd element based on your description. Try
Me.txtTarget = Split(TargetFile, "\", 1)(1)

Open in new window

0
NorieAnalyst Assistant Commented:
Try splitting on ':' instead of '\'.

Me.txtTarget = Split(TargetFile, ":")(1)

Open in new window

2
WSStudentAuthor Commented:
@Anders, This give error message as "Scripting out of range"
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
 Split("W:\GLO\ALL\EXMP1.pdf", ":", 2)(1)

Open in new window

0
WSStudentAuthor Commented:
@Norie, it works.

Can you guys me how to concatenate that back also? the user want to search for file afterwards so in that case , this target should concatenate with the server path for example as "\\189.30.20.1\GLO\..." , show how to do that?
0
Fabrice LambertFabrice LambertCommented:
Hi,

If you are sure your path always start with a letter, and has been validated, just strip the 1st and 2nd characters:
1:Me.txtTarget = right(TargetFile, len(TargetFile) - 2)

Open in new window


With UNC path, you might want to use something more elaborated:
0
Fabrice LambertFabrice LambertCommented:
Just use the & operator to concatenate strings
path = "\\189.30.20.1" & Me.txtTarget

Open in new window

0
WSStudentAuthor Commented:
@Fabrice,

I'm trying the concatenate part from yesterday but i am not able to make that work, can you help me a bit in that. In the subform which is a datasheet in that on field link i put this code at event On Click but it's not working, any idea why?

Private Sub Link_Path_Click()
Dim path As String
path = "\\xxx.xx.xx.xx\" & Me.Link_Path
Application.FollowHyperlink Me.path

End Sub

Open in new window

0
Fabrice LambertFabrice LambertCommented:
You misunderstand the meaning of the "Me" keyword:
It is  a reference to the current object, and it should be used only to remove ambiguities, as it give an alternative access to the current object's methods and properties.

path is a local variable and for sure have nothing to do with properties.
So the following will be enough to achieve your goal:
Application.FollowHyperlink path

Open in new window

0

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:
I got it but this is also not working.

Private Sub Link_path_Click()

Dim path As String
path = "\\xxx.xx.xx.xx" & Me.Link_path

Application.FollowHyperlink path

End Sub

Open in new window

0
Fabrice LambertFabrice LambertCommented:
Are you sure that your path variable hold valid data ?
0
WSStudentAuthor Commented:
yes there is the specific file at the path which is mention. i got the link when i copy file to this location and then i split the link into root and path for relative addressing , after that i want to concatenate it back to open that file.

Also note one thing the field link_path data type in hyperlink , does that create any problem?
0
WSStudentAuthor Commented:
i found a problem while breaking it and i think this is the problem : path= "\\xxx.xx.xx.xx\#GLO\ALL\test.pdf# , so these "#" are not letting it opening this but i haven't add these "#" , from where these "#" signs are coming?? is this something due to data type or from where?
0
Fabrice LambertFabrice LambertCommented:
Use breakpoints,
Execute your code step by step (F8 hotkey),
Track your variables values in the spy window ect .... the usual debugging stuffs.
0
WSStudentAuthor Commented:
Thank you Fabrice the code above started working when i change hyperlink field to text.

Thank you again.
0
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.