enginerd477 wrote:
I am having trouble getting my database to do what i want. I have a
form with autonumbers which are going to be the numbers of some
inventory. the inventory has reports in word or other program. The
file name is going to be the number generated by access, but every file
will be saved in the same folder. i was wondering if there was a way
to click on the button and then have the comput search the correct
folder linking the number in access to the file name and have that file
come up on the screen for viewing.
If you're a newbie, this may be a bit hard to follow, so I'll try to
explain clearly. If the files already exist in the folder and the
records already exist in a table, but you don't have the full path
stored, you could use some code to get all this for you. (I did that
part for you. Hopefully it's what you wanted.)
All the code was cobbled from this NG or taken from The Access Web.
'---put all this code in a new code module.
Option Compare Database
'************** Code Start **************
'This code was originally written by Terry Kreft.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code courtesy of
'Terry Kreft
Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _
"SHGetPathFromIDListA" (ByVal pidl As Long, _
ByVal pszPath As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _
"SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) _
As Long
Private Const BIF_RETURNONLYFSDIRS = &H1
Public Function BrowseFolder(szDialogTitle As String) As String
Dim X As Long, bi As BROWSEINFO, dwIList As Long
Dim szPath As String, wPos As Integer
With bi
.hOwner = hWndAccessApp
.lpszTitle = szDialogTitle
.ulFlags = BIF_RETURNONLYFSDIRS
End With
dwIList = SHBrowseForFolder(bi)
szPath = Space$(512)
X = SHGetPathFromIDList(ByVal dwIList, ByVal szPath)
If X Then
wPos = InStr(szPath, Chr(0))
BrowseFolder = Left$(szPath, wPos - 1)
Else
BrowseFolder = vbNullString
End If
End Function
'*********** Code End *****************
Then in the form that you want to use to launch the cataloging for you,
'********** Code Start ****************
Option Compare Database
Private Sub Command0_Click()
Dim strDirectory As String
Dim blnFileExists As Boolean
Dim rsFiles As DAO.Recordset
'---prompt the user for the folder to catalog.
strDirectory = BrowseFolder("Select a folder to catalog")
'---open the table containing all the FileNumbers
Set rsFiles = DBEngine(0)(0).OpenRecordset("tblFiles", dbOpenTable)
'---loop over all the records, look for the file (Dir), and if it
exists,
'---mark it as found/existing
'---and write the directory.
Do Until rsFiles.EOF
blnFileExists = (Len(Dir(strDirectory & "\" &
rsFiles.Fields("RecordID") & ".txt")) > 0)
With rsFiles
.Edit
!FileExists = blnFileExists
If blnFileExists Then
!FullPath = strDirectory & "\" &
rsFiles.Fields("RecordID") & ".txt"
End If
.Update
End With
rsFiles.MoveNext
Loop
rsFiles.Close
Set rsFiles = Nothing
End Sub
'*************Code End*************************
I was thinking that you might only want to ignore the files you had
already catalogued, but what if someone deletes one... then you'll get
a Null in your FullPath field.
Hope this makes sense. If you have questions, fire away.
Pieter
