Help with updating hyperlinks

[caj]

HELP!!! I have been working on this came up with several ways to do this and
this is what i settled on.

Below is my code. I am trying to save files from one directory to another
directory and update the hyperlinks in the form. The saving part works. But
it only updates the first link in the form. How do I get it to update all the
hyperlinks for each file.


Private Sub SaveDBFiles_Click()

Dim strSQL As String
Dim strCurrFile As String
Dim strCurrFolder As String
Dim strNewFolder As String

strCurrFolder = "C:\Post\STAGING\"
strNewFolder = "C:\Post\ARCHIVE\"
strCurrFile = Dir(strCurrFolder & "*.*")

Do While Len(strCurrFile) > 0

Name strCurrFolder & strCurrFile As strNewFolder & strCurrFile
strCurrFile = Dir()
Me.PathFileName = strNewFolder

Loop


End Sub

 

[Ken Snell \(MVP\)]

Is PathFileName the control that you want to update? If yes, you need to
"loop" through the form's RecordsetClone to update each record's control's
value.

Tell us more about your form's design/structure, and whether the records on
the form are in the same order as the data that will come from the Dir
function (note: I personally would never rely on an expected order of data
from Dir function, so you likely will need a way to identify the specific
record that goes with the specific filename being returned by the Dir
function).

 

[caj]

Ok that make sense to save the . But how do I do that. I have set up a
commandbutton that enters hyperlinks for all the files located in the STAGING
folder into the PathFileName control. Then the user can enter information
about the file and save the files. As the files are saved they are moved to
the ARCHIVE folder and the new hyperlinks will be saved in the table. I hope
that make sense.

 

[Ken Snell \(MVP\)]

Sounds as if you need to save the record into which you write the hyperlink
information, and then either move to a new record or move to an existing
record to save the next hyperlink information.

It's important that you give us information about the form -- is the 'first
record' that is saving the value a new record that you create in the form,
or is it an existing record that you "found" or moved to because it has
information that matches the file in the folder?

 

[caj]

This is how it would work. Files are moved to a folder called STAGING by the
user. In Access the user opens a form called frmMain. Then clicks a button
called Staging -- links to all the files in the STAGING folder are entered as
new records in frmMain. User then enters information into the other fields in
the record which pertain to each file (works til this point). When the user
saves the file it should move the files to a folder called ARCHIVE (this
works) but does not update the hyperlink in the records to the ARCHIVE
folder.

 

[Ken Snell \(MVP\)]

OK. You need to change the path of the hyperlink in the records that are
currently in the frmMain form's recordset. I would use this code to update
the path of the records in the frmMain:



Private Sub SaveDBFiles_Click()

Const strCurrFolder As String = "C:\Post\STAGING\"
Const strNewFolder As String = "C:\Post\ARCHIVE\"

With Me.RecordsetClone
If .RecordCount <> 0 Then
.MoveFirst
Do While .EOF = False
PathFileName.Value = Replace(.PathFileName.Value, _
strCurrFolder, strNewFolder )
.MoveNext
Loop
.MoveFirst
End With
End If
End Sub

 

[Ken Snell \(MVP\)]

Sorry... left out two steps (by the way, this code assumes that you have a
field named PathFileName in your form's RecordSource query):

Private Sub SaveDBFiles_Click()

Const strCurrFolder As String = "C:\Post\STAGING\"
Const strNewFolder As String = "C:\Post\ARCHIVE\"

With Me.RecordsetClone
If .RecordCount <> 0 Then
.MoveFirst
Do While .EOF = False
.Edit
.PathFileName.Value = Replace(.PathFileName.Value, _
strCurrFolder, strNewFolder )
.Update
.MoveNext
Loop
.MoveFirst
End With
End If
End Sub