search files 1

[at51178]

Hey guys

I don't know if this can be done with access but what I would like to do is create a form that displays all the files that I have in a particular folder like documents or mp3 files and then bring it up in a contiuous form.

Is that possible with access or do I have to type in manually in to a table all the names of the files and then link them.

Please let me know if this is possible or if this can not be done thankyou.

 

[danvlas]

1. Use Dir function to populate a list box (RowsourceType = ValueList). The problem is that such a listbox is limited to 2044 characters.

Private Sub Command6_Click()
Dim MyFolder$, myfile
On Error GoTo finderror
MyFolder = "C:\Path\"
myfile = Dir(MyFolder)
While myfile <> &quot;&quot;
MyListBox.RowSource = MyListBox.RowSource & myfile & &quot;; &quot;
myfile = Dir
Wend
Exit Sub
finderror:
Debug.Print MyListBox.RowSource
Debug.Print Len(MyListBox.RowSource)
Exit Sub
End Sub

2. use Dir to insert filenames in a temporary table. Base your form on the temporary table:

Private Sub Form_Open(Cancel As Integer)
Dim MyFolder$, myfile
Dim rst as DAO.Recordset
MyFolder = &quot;C:\Path\&quot;
Set rst=CurrentDb.OpenRecordset(&quot;TableName&quot
myfile = Dir(MyFolder)
With rst
While myfile <> &quot;&quot;
.AddNew
![FieldName] = myfile
.Update
myfile = Dir
Wend
.Close
End With
Set rst = Nothing
Me.Requery
End Sub

Sub Form_Close()
DoCmd.SetWarnings False
DoCmd.RunSQL(&quot;Delete * From TableName;&quot
DoCmd.SetWarnings True
End Sub

HTH
Dan

 

[at51178]

Dan
first things first thanks for responding to my question.

Thanks a lot the first method it worked great
but just three things I failed to mention which I didn't think of.
1)Even though I see it in the list box I can not open it is there a way so that when I click on it the file opens
2)is there a way to filter it so that it only looks for any file that is ending with an extention like .doc or .xls
3) is there a way to do this using a text box and then view the information in a continous form.

 

[danvlas]

Create a label on the form and name it MyHyperlink.
Then paste the following code for the DblClick event of the listbox:

Private Sub MyListBox_DblClick(Cancel As Integer)
MyHyperlink.HyperlinkAddress = &quot;C:\Path\&quot; & MyListBox
MyHyperlink.Hyperlink.Follow
End Sub

When you doubleclick the listbox, it will open the file.

Regards,

Dan

 

[at51178]

Dan it worked thanks

Can you explain to me how the label box let me double click on the file and open it.

and I take it this can only be done in the list box only and not in the text box.


Thanks

 

[danvlas]

To display only the doc and xls files in the list box:

Private Sub Command6_Click()
Dim MyFolder$, myfile
On Error GoTo finderror
MyFolder = &quot;C:\Path\&quot;
myfile = Dir(MyFolder)
While myfile <> &quot;&quot;
If right(myfile,4)=&quot;.xls&quot; Or right(myfile,4)=&quot;.doc&quot; Then
MyListBox.RowSource = MyListBox.RowSource & myfile & &quot;; &quot;
End If
myfile = Dir
Wend
Exit Sub
finderror:
Debug.Print MyListBox.RowSource
Debug.Print Len(MyListBox.RowSource)
Exit Sub
End Sub

3. To display the info in a continuous form, you have to use the temporary table and base your form on it:
Private Sub Form_Open(Cancel As Integer)
Dim MyFolder$, myfile
Dim rst as DAO.Recordset
MyFolder = &quot;C:\Path\&quot;
Set rst=CurrentDb.OpenRecordset(&quot;TableName&quot
myfile = Dir(MyFolder)
With rst
While myfile <> &quot;&quot;
If right(myfile,4)=&quot;.xls&quot; Or right(myfile,4)=&quot;.doc&quot; Then
.AddNew
![FieldName] = myfile
.Update
End If
myfile = Dir
Wend
.Close
End With
Set rst = Nothing
Me.Requery
End Sub

Sub Form_Close()
DoCmd.SetWarnings False
DoCmd.RunSQL(&quot;Delete * From TableName;&quot
DoCmd.SetWarnings True
End Sub

The label box has the only purpose to store the hyperlink to the file you want to open in its Hyperlink property.
Then Hyperlink.Follow actually opens the file with the associated program.
And you can use the DblClick event for a text box using the same syntax.

Dan

 

[at51178]

Hi Dan

Thanks for all the info that you are giving me I am still new to VB and only been working with access for 6 months.


1)I tried the second method that you gave me where you create a temp table but for some reason when I put the code in the on open part of the form I get an error message and the &quot;Dim rst as DAO.Recordset&quot; statement is greyed out.
I created a table called &quot;table1&quot; and a column called &quot;MYFILE&quot; I Changed the path to &quot;c:\&quot; I then went to the form and added a text box that pointed to the &quot;myfile&quot; column am I doing anything wrong.


2)On the first method is there any way I could use a network drive to look up information I tried this on one network drive at work and it worked with no prob but when I tried this with my home network it didn't work. I even tred it on the server where I have two seperate physical harddrives and that didn't even work. I even tried reading my cd rom drive and that didn't work but when I change it back to the c:\ it worked with no prob.


Sorry to trouble you with these kinds of questions problems.

 

[Rolliee]

Check out microsoft.com's letter Q303066. It has a great browser. Someone in this forum pointed me to it.

rollie@bwsys.net

 

[icupat]

Just for your info. If you go to that website (the microsoft one) it doesn't give you the code for a multi-selectable explorer window. You have to add the following to make it multi-selectable:

Const OFN_ALLOWMULTISELECT = &H200&
Const OFN_EXPLORER = &H80000

'put that at the top of the code, with any other declaring

'then you should have something like this for the tags..

OFName.flags = OFN_ALLOWMULTISELECT Or OFN_EXPLORER

note: when multiselecting a NULL seperates each file name in this mode, and the patname is the first stored name.

 

[at51178]

Hey DanVlas

You had helped me once before and I was hoping you can help me again.
This is in reference to the above situation that you had helped me with. I was able to use your code to look up extentions with no problem and then click on the file and have it open.

I have since modified the script so that a user can click on a combo box (cboPath) and pick the path. I also have set up a text box where the user can type in the extention that they are looking for. Everything works fine.

I would like to now change the way it is searching for files what I mean is instead of looking for an extention I would like to have it view filenames so if I had a file called calendar for example if I typed in cal or calendar then then it would search for any word containing cal or calendar.

Here is the code that I use now to look up files by extention I treid to put &quot;LIKE&quot; where Right(myfile,4) is in stead of &quot;=&quot; but that didn't work. Can you let me know what I was doing wrong.

Thanks for any help you can provide

MyListBox.RowSource = &quot;&quot;
Dim MyFolder$, myfile
On Error GoTo finderror
MyFolder = &quot;M:\&quot; & Me.cboPath & &quot;\&quot;
myfile = Dir(MyFolder)
While myfile <> &quot;&quot;
If Right(myfile, 4) = Me.txtExtention Then
MyListBox.RowSource = MyListBox.RowSource & myfile & &quot;; &quot;
End If
myfile = Dir
Wend
Exit Sub
finderror:
Debug.Print MyListBox.RowSource
Debug.Print Len(MyListBox.RowSource)
Exit Sub

 

[danvlas]

If InStr(myfile, txtExtention ) > 0 Then
blah blah blah

Good luck


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[at51178]

Thanks it worked

 

[at51178]

Sorry I forgot

I was trying to use the code that you had previously entered where you can create a temp table to populate the fields with the filenames and then view them in a continuous form.

I was trying to use that code as well just the way you have written it and I get an error saying user-defined type not defined on the
Dim rst as DAO.Recordset



Here is the code you had written originally

. To display the info in a continuous form, you have to use the temporary table and base your form on it:
Private Sub Form_Open(Cancel As Integer)
Dim MyFolder$, myfile
Dim rst as DAO.Recordset
MyFolder = &quot;C:\Path\&quot;
Set rst=CurrentDb.OpenRecordset(&quot;TableName&quot
myfile = Dir(MyFolder)
With rst
While myfile <> &quot;&quot;
If right(myfile,4)=&quot;.xls&quot; Or right(myfile,4)=&quot;.doc&quot; Then
.AddNew
![FieldName] = myfile
.Update
End If
myfile = Dir
Wend
.Close
End With
Set rst = Nothing
Me.Requery
End Sub

 

[danvlas]

You're using Access 2K/Xp... just set a reference to DAO 3.6 and it will work just fine. Otherwise, change the syntax from DAO to ADO...:

Dim rst as New ADODB.Recordset
rst.Open &quot;Select * From TableName&quot;, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Good luck...




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[at51178]

Thanks a lot your the man

 

[at51178]

Sorry to bother you again

Everything works fine
but when I am searching for a file the word that I am search has to be case sensative. SO if I am looking for CALENDAR it would only look for CALENDAR NOT calendar Here is the code that I have how would I change this.


Me.LowerBox.Visible = True
DoCmd.SetWarnings False
DoCmd.RunSQL (&quot;DELETE * From tblTemp;&quot
Dim MyFolder$, myfile
Dim rst As New ADODB.Recordset
MyFolder = &quot;M:\&quot;
rst.Open &quot;Select * From tblTemp&quot;, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
myfile = Dir(MyFolder)
With rst
While myfile <> &quot;&quot;
If InStr(myfile, Me.txtSearch) > 0 Then
.AddNew
![filename] = myfile
.Update
End If
myfile = Dir
Wend
.Close
End With
Set rst = Nothing
Me.Requery

 

[danvlas]

Replace
Option Compare Database
with
Option Compare Binary

and you're set...

Good luck,




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[at51178]

I tried putting
Option Compare Binary
Option Explicit
In the General Declaration section of the code but it still did not work here is my entire code.

Option Compare Binary
Option Explicit

Private Sub cmdSearch_Click()
Me.LowerBox.Visible = True
DoCmd.SetWarnings False
DoCmd.RunSQL (&quot;DELETE * From tblTemp;&quot
Dim MyFolder$, myfile
Dim rst As New ADODB.Recordset
MyFolder = Me.cboPath & &quot;\&quot;
rst.Open &quot;Select * From tblTemp&quot;, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
myfile = Dir(MyFolder)
With rst
While myfile <> &quot;&quot;
If InStr(myfile, Me.txtSearch) > 0 Then
.AddNew
![filename] = myfile
.Update
End If
myfile = Dir
Wend
.Close
End With
Set rst = Nothing
Me.Requery
End Sub

Private Sub txtFileName_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub txtFileName_DblClick(Cancel As Integer)
lblMyHyperLink.HyperlinkAddress = cboPath & &quot;\&quot; & Me.filename
lblMyHyperLink.Hyperlink.Follow


End Sub

 

[danvlas]

You have Option Compare Database on top of the module (not procedure).

The button is on a form. The form has a module. The module has a declarations section on top, just use PageUp until you hit the ceiling .
You will see the Option thing... If it's not there, just type it...but make sure it's the first thing written.

This will make your entire form case sensitive...

Good luck




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[at51178]

I created a module and did what you told me and it worked fine.


Thanks a lot sorry for the trouble