update the hyperlink address at same time as appending data in to table

[Dharmesh Patel]

I have created excel file susing macro in access and updated an Access table with the location of the files. The table column data type is hyperlink. How can I set the address of teh hyperlink at the sametime as I update the table.

e.g the location column in the table "Created files" is a hyperlink data type, once a file is created the location column gets updated with "C:\DHARMESH\PRODUCT\AMERICAS.XLS". How can I set the address of the hyperlink at the same time as updateing the column.

I use the DOCmd.run sql to update the table.
dpSQL = "INSERT INTO TPGCreated(File,Centre,Date_Completed,Pivotal_Reference,TourName,TourDate,TourTime,ASDASM,SaleEmail,Location,TourCentre) VALUES (""" & FileName.Value & """,""" & CentreNumber.Value & """,""" & Now() & """, """ & PivRef.Value & """, """ & TourName.Value & """, """ & Tour_Date.Value & """, """ & Tour_Time.Value & """, """ & ASDASM.Value & """, """ & SaleEmail.Value & """, """ & xlSaveFile & """, """ & TourCentre.Value & """)"
DoCmd.RunSQL dpSQL

 

[Ziggy]

I don't understand you issue, why can't you update the other field if you know the location

 

[Dharmesh Patel]

Hi,
I can update the field, but the hyperlink dose not work. For example, I am able to update the "Location" filed (Hyperlink datatype) with "C:\DHARMESH\PRODUCT\AMERICAS.XLS". However when I click on the link nothing happens. I have to manually go and set the address of the hyperlink in each record currently.

Is thera away to loop through each record in Loaction field and set the hyperlink address to the data in the Location Field?


Hope that explains the problem.
Regards
Dharmesh

 

[Dharmesh Patel]

Silly me,
Recorded macro to find the solution.

.Hyperlinks.Add Anchor:=Selection, Address:= _
"C:\Desktop\Tour price guides\new", TextToDisplay:= "C:\Documents and Settings\dharmesh.patel\Desktop\Tour price guides\new"

Regards
Dharmesh

 

[Ziggy]

lol, I know, it sometimes just helps to talk about it to get your head straight