Generating URL 1

[Domino2]

I have a table of records containing word document names. I know the path where each document is stored (single folder). Is there a way I can generate a query which will convert the current document name into a URL. My folder is "C:\Documents" Thanks

 

[HarleyQuinn]

You want to append "C:\Documents\" to the beginning of the value returned in a query? IS this correct?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[Domino2]

Yes, thanks. The documents are all in this location folder.

 

[HarleyQuinn]

Try this as your query then

SELECT "C:\My Documents\" & [YourField] AS YourNewURLField
FROM YourTable;

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[Domino2]

Many thanks, that generated a list which I now need to put in a query to insert the url address into my table field and I'm away.

 

[HarleyQuinn]

Glad I could help

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[Domino2]

Seeing what the URL syntax was I ran some code:

Dim mysql As String
Set db = CurrentDb()

mysql = "SELECT Table1.PK1, Table1.DocumentName, Table1.DocURL FROM Table1;"
Set Rst = db.OpenRecordset(mysql, dbOpenDynaset)
Do While Not Rst.EOF

Rst.Edit
Rst("DocURL") = "C:\My Documents\" & Rst("DocumentName")
Rst.Update

Rst.MoveNext
Loop

It put the URL address into the DocURL field, however in table view if I click on a URL addrees it does not open the document. Where have I gone wrong? Thanks

 

[HarleyQuinn]

Do you have the Field type of DocURL set to Hyperlink?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[Domino2]

Thanks, yes I do.

 

[Domino2]

Found it. I was stupid, I used C:\My Documents as the folder instead of C:\Documents

All is well, many thanks again

 

[HarleyQuinn]

Glad you got it sorted

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[Domino2]

Spoke too soon. I thought that would have cured it but it didn't!!

I noticed all the table data in the DocURL field were blue. I went into one and right clicked my mouse and selected open hyperlink, but that failed. I did the same and selected edit/modify hyperlink address. I followed it through, the final entry in the field changed to violet colour, but it now opens the document. As I say, the address in the field DocURL is exactly the same as it was in the beginning, but now a violet colour. Just hope there is an easy answer as to manually do each one will be hell. Thanks

 

[HarleyQuinn]

Oops, that was my bad. I forgot you needed to set some of the other propertiers of the Hyperlink field, use this instead:

rst("FileName") = "C:\TestMyCode.doc# C:\TestMyCode.doc## C:\TestMyCode.doc"

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[HarleyQuinn]

Of course, yours would be your filename combination not my testing one...

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[Domino2]

Sorry to be dumb, but I feel as though I have just fallen off a roof!!

Your code:
Rst("FileName") = "C:\TestMyCode.doc# C:\TestMyCode.doc## C:\TestMyCode.doc"

My Code:
Rst("DocURL") = "C:\Documents\" & Rst("DocumentName")

??? Thanks

 

[HarleyQuinn]

If you declare a variable (lets say Dim MyFile as String when you declare the others) then try your code using:

Rst.Edit
MyFile = "C:\My Documents\" & Rst("DocumentName")
Rst("DocURL") = myfile & "# " & myfile & "## " & myfile
Rst.Update

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[Domino2]

Excellent, I'm there. Many thanks for all your help hanging in there. Best regards