Replace part of a string with another string

[gamisens]

I have a hyperlink field in a record in my table that links to a specific word or excel file (something like #description#x:\directory\subdirectory\filemane). Another field of the same record specifies the subdirectory name where the word or excel file is found. I would like the subdirectory part of the hyperlink field to change automaticaly according to changes made to the subdirectory field of that record (i.e. respond to the subdirectory field's OnChange or AfterUpdate events). Can someone help me with the right VB code for this task? Thank you in advance.

 

[kathryn]

You're correct, the after update event would be good.

You are going to have to do some fancy string manipulations, I think. Is the subdirectory always the text immediately after the second "\"? Any chance the "directory" is always the same???

Kathryn

 

[gamisens]

Yes Kathryn, the directory is always the same. I even have the old subdirectory name as the old value of the subdirectory field. Actually all I need is to replace whatever is between the second and third "/" (which is the old value of the subdirectory field) with the new value of the subdirectory field.

Thank you.

 

[kathryn]

OK you need to get the position of the second "\" and third "\" characters.

The position of the first character is given by

x = Instr("#description#x:\directory\subdirectory\filemane", "\directory\&quot

so the position of the second "\" is just

y = x + 10

Now if we use the first position after the second "\" as a starting point, we can find the value of the third "\"

z=Instr(y+1,"#description#x:\directory\subdirectory\filemane", "\&quot


So if we put that into code, this is what we have:

In the OnChange event of the Subdirectory field on the form, put the following

dim strHyperlink as string
dim x as int
dim y as int

strHyperlink = me!HyperlinkFieldName

'get position of second "\"

x = Instr(strhyperlink, "\directory\&quot + 10

'get position of third "\"

y = Instr(x+1,strhyperlink, "\&quot


me!HyperlinkFieldName = left(strHyperlink,x) & me!subdirectory & mid(strhyperlink,y)



This does not have any error checking and it is off the top of my head, so you will probably have to tinker with it. Check out the help files on InStr, Left and Mid.

Hope this helps.
Kathryn

 

[gamisens]

Thank you for all your help, but I found out Acc2000 comes with a handy replace function for doing what I need so my code ended up looking like that:

Private Sub folder_ID_AfterUpdate()
Dim strHyperlink As String
Dim strOldSubDirectory As String
Dim strNewSubDirectory As String
strHyperlink = Me.HyperlinkFieldName
strOldSubDirectory = DLookup("[SubDirectory]", "SubDirectories", "[SubDirectory_ID]=" & Me.SubDirectory_ID.OldValue)
strNewSubDirectory = Me.SubDirectory_ID.Column(1)
Me.HyperlinkFieldName = Replace(strHyperlink, strOldSubDirectory, strNewSubDirectory)
DoCmd.RunCommand acCmdSaveRecord
End Sub