hyperlink fields

[Guest]

Hi

I have some code that is a attached to a button on a form which creates a
folder in a directory and then writes the location as a hyperlink to field.
Which works very well. See code below. What I want to know is how can I
change \\server1 to a value held in a field eg to field sgbPath held in a
table call tblSysGlobals. The idea is if i change to location of the folders
to a another server eg server2 I would only need to change the value held in
the sgbPath field which would then point the hyperlink fields to the new
server. Hope that makes sense

Thanks
Matt

Dim myFolder, myPath
Dim myHyperPath, myHyper1, myHyper2

myFolder = Me![JobNumber] & "_" & Me![CustomerName]
myPath = "\\server1\Files"

MkDir myPath & "\" & myFolder
myHyper1 = HyperlinkPart(myFolder, acDisplayText)
myHyper2 = HyperlinkPart(myPath & "\" & myFolder, acDisplayText)
Me.wpPath = myHyper1 & "#" & myHyper2

 

[Guest]

Matt,

I can think of 2 ways to handle this.
1- Use the dlookup to retrieve the value from the value. equate a vraiable
to = dlookup() then simply use the variable within your sub or function.
somthing like
Since there will most probably be only 1 entry in the table I use the
condition "[ID] = 1") but you way set it up differently so you may have to
adjust your condition statement within the dlookup.

Dim myFolder, myPath
Dim myHyperPath, myHyper1, myHyper2
Dim strServerName as String

strServerName=varX = DLookup("[FieldName]", "TableName", _
"[ID] = 1")

myPath = strServerName & "\Files"
myFolder = Me![JobNumber] & "_" & Me![CustomerName]

MkDir myPath & "\" & myFolder
myHyper1 = HyperlinkPart(myFolder, acDisplayText)
myHyper2 = HyperlinkPart(myPath & "\" & myFolder, acDisplayText)
Me.wpPath = myHyper1 & "#" & myHyper2


2- You could use a looping function to run through the recordset within the
table something like

Dim db As Database
Dim rst As Recordset
Dim strTable As String
Dim intRecCount As Integer
Dim strServerName as String

Set db = CurrentDb()
strTable = "YourTableName"
Set rst = db.OpenRecordset(strTable)

With rst
.MoveFirst 'use the 1st recordset in the table to get your value
strServerName= ![YourFieldName]
.Close
End With

Hope this helps,

Daniel