updating a recordset with hyperlinkpart 1

[Scoty]

Hello all,
I have a table with about 1000 hyperlinks stored in it. I need to change the address to the relative drive on each one (right now they are pointed to the network drive). So what I have tried to do is create a record set and use the edit and update features (throuh a loop). But I keep getting an error stating Item Not Found In Collect. I know this means that the field name does not exist in the recordset. My problem is I dont know how to get the the acAddress of the hyperlink. Here is my code:

Dim lrs As Recordset
Dim lsSQL As String
Dim lsHyperLink as Variant

lsSQL = "SELECT * FROM Search"
Set lrs = CurrentDb.OpenRecordset(lsSQL)
lrs.MoveLast
lrs.MoveFirst
Do Until lrs.EOF = True
If Left(CStr(HyperlinkPart(lrs.Fields(0), acAddress)), 18) = "\\MyNetwork\Server\" Then
    lsHyperLink = "j:" & _
    Mid(CStr(HyperlinkPart(lrs.Fields(0), acAddress)), 17, Len(HyperlinkPart(lrs.Fields(0), acAddress)))
    With lrs
        .Edit
        !HyperlinkPart(lrs.Fields(0), acAddress) = CVar(lsHyperLink)

'this is where I get my
'error (of course because there is no field call Hyperlinkpart

        .Update
    End With
End If

'this was added because I have 2 network drives with the
'same info except this is one (unlike the above) is not read
'only..so I figured I would kill two birds with one stone

If Left(CStr(HyperlinkPart(lrs.Fields(0), acAddress)), 16) = "\\MyOtherNetwork\Server\" Then
    lsHyperLink = "j:" & _
    Mid(CStr(HyperlinkPart(lrs.Fields(0), acAddress)), 15, Len(HyperlinkPart(lrs.Fields(0), acAddress)))
    With lrs
        .Edit
        !link(HyperlinkPart(lrs.Fields(0), acAddress)) = CVar(lsHyperLink)
        .Update
    End With
here:
End If
lrs.MoveNext
Loop
End Sub

Any Help would be greatly appreciated
Thanks
Scotty :
Learn from others' mistakes. You could not live long enough to make them all yourself."
-- Hyman George Rickover (1900-86),

 

[930driver]

I think your line !HyperlinkPart(lrs.Fields(0), acAddress) = CVar(lsHyperLink) should probably read something like

lrs.Fields(0) = lsHyperLink

because you want to write the change back to the same field and lsHyperLink is where you stored the new hyperlink. I'm not sure if you need the CVar function around lsHyperLink but if you do you can add it in.

A note that won't really affect your code but might interest you: You really don't need to do a movelast with your recordset unless you need to update the recordsetcount property.

 

[Databaseguy]

Make a new table. Make one field, datatype Hyperlink. Name it whatever you want. Paste this as a new record:
ClickHere#

http://www.msn.Com#

That'll give you an idea how hyperlinks are stored. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[Scoty]

Tyrone,
Thank you of reminding me of the first rule of programming. Keep it simple, stupid. I had complete ignored the fact that I could build a hyperlink with concantination using the # sign. I have made the corrections to my code and with a little experimenting have gotten the correct order
Thanks again
Scoty : Learn from others' mistakes. You could not live long enough to make them all yourself."
-- Hyman George Rickover (1900-86),