Display directory contents in a list box 2

[vm079186]

I'm working on an access database using access 97. I want to be able to display a list of files contained within a directory inside a list box. I already have a variable within the form that holds the path that I want to look in, this is stored within the database and is different for each record.

The aim of this exercise is to then be able to filter the files by type (.jpg) and then click on a file from the list to preview it on the form.

Any ideas?

Cheers

 

[TimKThailand]

Check this

http://support.microsoft.com/default.aspx?scid=kb;en-us;308634

out.

 

[BillPower]

If you were using Access 2000, this would be quite simple using FSO. As you're not, would you consider using a dialog box?

An example can be found at

http://www.wpower.fsbusiness.co.uk/

file: CommonDialogAccess97.zip

Regards

Bill

 

[vm079186]

Er, some good ideas there but...

This form is used to display a collection of photos related to the various records in the database. One set of photos per record.

The photos associated with a specific record are stored together in a directory. Each set of photos is stored in a different directory.

I don't want to store the photos as a blob within the database, instead I want to have their location stored in the database and then point an image control's image source at that location.

At the moment The user can select the directory they wish to retrieve the photos from using a dialog box. There is a field that stores the directory the photos are in (this is populated by the dialog box).

What I want to do is display the photos in that directory when this form is opened.

I was going to try to allow the user to select which photo from the directory they wish to view, the idea is that the user can click on the photo name from a list of available photos in the list box and then use a command button to display it in an image control....Any Ideas? (please remember this is access 97!)

Cheers

vm079186

 

[GGleason]

Here is a function to place files from a folder in a list box. There are a couple of functions that you will need to either comment or change to suit your computing environment. Function GetEV returns the environment variable for the argument and YesTableExist returns a Boolean result of whether or not the table in the argument exists. There may be other functions I didn’t see. If so let me know. You may need to add your own error trapping.

Example call: Call ListFiles("lstFolder", txtFolderPath.Value, "*.log", "lblInfoLog&quot

Argument 1: the List Box name (e.g., lstFolder)
Argument 2: the File Path (UNC or mapped drives ok)
Argument 3: the file limiter (e.g., "*.jpg", "*.*&quot
Argument 4: the label on the form to display information from the function

[tt]
Private Sub ListFiles(strControl As String, strPath As String, strFileLim As String, strLabel As String)
Dim strCheckDir As String, strCheckFyle As String, strCheckFile As String, strTemp As String, i As Integer
Dim dbsPDMScata As Database, strSQL As String, strTable As String, rstQuery As Recordset
Dim strFyle As String, strFile As String
Dim rst As ADODB.Recordset, lngMax As Long, strFiles As String, strTbl As String, rstNew As Recordset

strFyle = strPath + strFileLim

strFile = Dir(strFyle)

'===
Set rst = New ADODB.Recordset
rst.Fields.Append "FileName", adVarChar, 255
rst.Open

If Not strFile = "" Then
Do While Not strFile = ""
rst.AddNew Array("FileName&quot, strFile
strFile = Dir
Loop
End If

If rst.EOF = False Then
rst.Sort = "Filename"
rst.MoveLast
lngMax = rst.RecordCount
rst.MoveFirst
Else
End If

strFiles = ""

For i = 1 To lngMax
' MsgBox rst!FileName
strFiles = strFiles + IIf(strFiles = "", "", ";&quot + rst!FileName
rst.MoveNext
Next i

If Len(strFiles) > 2048 Then
strTbl = "tblSpecWorksRS_" + GetEV("USERNAME&quot

If YesTableExist(strTbl) = False Then
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT q0.Name AS FileName INTO " + strTbl + " FROM MSysObjects AS q0 WHERE q0.Type = 123456789;"
DoCmd.SetWarnings True

If YesTableExist(strTbl) = False Then
strMsg = "Could not create the following table:" + vbCrLf + vbCrLf + _
strTbl
MsgBox strMsg, vbExclamation

rst.Close
Set rst = Nothing

Exit Sub
End If
Else
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM " + strTbl + ";"
DoCmd.SetWarnings True
End If

rst.MoveFirst

Set rstNew = OpenDatabase(CurrentDb.Name).OpenRecordset(strTbl)

For i = 1 To lngMax
' MsgBox rst!FileName
With rstNew
.AddNew
.Fields(0) = rst!FileName
.Update
.Bookmark = .LastModified
End With
rst.MoveNext
Next i

rst.Close
Set rst = Nothing

Me(strControl).RowSourceType = "Table/Query"
Me(strControl).RowSource = strTbl
Else
Me(strControl).RowSourceType = "Value List"
Me(strControl).RowSource = strFiles
End If
'===

If Me.Controls(strControl).ListCount > 0 Then
' Me.Controls(strControl).ListIndex = 0
strTemp = IIf(Me.Controls(strControl).ListCount = 1, "file", "files&quot
Me(strLabel).Caption = "A total of " + Format(Me.Controls(strControl).ListCount, "#,##0&quot + " " + strTemp + " found"
Else
Me(strLabel).Caption = "No files found"
End If

Me.SetFocus
Me.Repaint
Me.Controls(strControl).TAG = lblInfoLog.Caption
End Sub

[/tt]

hth,
GGleason

 

[vm079186]

That would be a good solution, but it uses ado...unfortunately this database is using dao and forms bound to the data by access - not my work btw (

If I used the dialog box code provided by Bill (see above), how can I get the module to set the target path of the dialog box to be the same as the directory stored on the photopath field and then get it to display the file it opens in the image holder on the form when the user click the open button on the dialog box?

Cheers

vm079186

 

[GGleason]

Just out of curiosity, did you try the function? I am curious as to what errors you would get.

I use is in Access 97 SR-2 with no problems.

GGleason

 

[vm079186]

I've tried the code (was well written and easy to understand which is always good!)

Access threw up a the following error:

"user defined type not defined"

that was referring to the line where the recordset is declared:

Dim rstQuery As Recordset

Dim strFyle As String
Dim strFile As String

==> Dim rst As ADODB.Recordset <==== THIS LINE
Dim lngMax As Long
Dim strFiles As String

If I go into tools > references, MS DAO 3.6 object library is selected, there are no ADO references do they need to be checked? If so which ones? and will the DAO stuff still work?

Cheers

vm079186

 

[GGleason]

Here are my references for the form that contains the function:

Visual Basic For Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.5 Object Library
Microsoft Excel 8.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft Word 8.0 Object Library
Microsoft Windows Common Controls 6.0 (SP3)
Microsoft ActiveX Data Objects 2.1 Library

My guess is try adding the Microsoft ActiveX Data Objects 2.1 Library to your form and see if that does it.

hth,
GGleason

 

[vm079186]

Ok have set up the references, it's bought up an error:

sub or function not defined:

strTbl = &quot;tblSpecWorksRS_&quot; + GetEV(&quot;USERNAME&quot

it seemed not to like the GetEV function.

Is this one of functions that needs comments / changing to suit the computing environment? Please could you explain the purpose of Function GetEV and what changes are needed? (not sure what need to be done)

Function GetEV returns the environment variable for the argument and YesTableExist returns a Boolean result of whether or not the table in the argument exists.
(could you explain this bit again? - whats an environment variable?)

Cheers

vm079186

 

[jebry]

Hi!

Try this:

Dim f, fc
Set fc = CreateObject(&quot;Scripting.FileSystemObject&quot.GetFolder(&quot;YourCompleteDirectoryPath&quot.Files
cboFilename.RowSource = &quot;&quot;
For Each f In fc
cboFilename.RowSource = cboFilename.RowSource & &quot;&quot;&quot;&quot; & f.Name & &quot;&quot;&quot;;&quot;
Next f

This will add all of the file names from the directory to the list box. Note, in A97 the RowSource of a list box could not exceed 255 characters or something close to that.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[GGleason]

I am running NT 4.0. If you open a command prompt and type in &quot;SET&quot;, it returns the environment variables for the users compurter. The &quot;USERNAME&quot; environment variable is our login name. The above funcrion makes a table unique to the user. You don't have to do that.

You can do this:
[tt]
strTbl = &quot;tblMyTempFolder&quot;
[/tt]
hth,
GGleason

 

[BillPower]

Slightly different approach using a table to store the file names. Access 97 limits a value list to around 2000 characters, so if there are a lot of files in a folder it won't work.

I really haven't time to explain this, this morning, but have posted the DB I tested this in at:

http://www.wpower.fsbusiness.co.uk/

the zip file to download is: Add List of Image Files to Combo - Then Display Selected Image - Access 97.zip

The code is below, just paste it into a new Form:

Option Compare Database
Option Explicit
Dim strPath As String 'change this to your existing variable

Private Sub comFileNames_Click()
Dim i, fs, strSQL As String, intPathLen
Set fs = Application.FileSearch
strPath = &quot;C:\My Documents\My Pictures&quot;
intPathLen = Len(strPath) + 1
DoCmd.SetWarnings False
strSQL = &quot;DELETE FileName FROM tblFileNames;&quot;
DoCmd.RunSQL (strSQL)
With fs
.LookIn = strPath
.FileName = &quot;.jpg|.jpeg|.bmp&quot; 'edit this to suit yourself
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
strSQL = &quot;INSERT INTO tblFileNames ( FileName ) &quot; & _
&quot;SELECT '&quot; & Right(.FoundFiles(i), Len(.FoundFiles(i)) - intPathLen) & &quot;' AS File;&quot;
DoCmd.RunSQL (strSQL)
Next i
End If
End With
DoCmd.SetWarnings True
Me!YourCombo = &quot;&quot;
Me!YourCombo.RowSource = &quot;tblFileNames&quot;
Me!YourCombo.SetFocus 'remove this if you want
Me!YourCombo.Dropdown 'remove this if you want
End Sub

Private Sub YourCombo_AfterUpdate()
On Error Resume Next
Me!imgLinked.Picture = strPath & &quot;\&quot; & Me!YourCombo
End Sub

Requirements to test this if you don't trust downloads are:

Table called &quot;tblFileNames&quot; with &quot;Text&quot; Field called &quot;FileName&quot; Length &quot;255&quot;.

Form called anything

Combo called YourCombo

Image called imgLinked

Command Button called comFileNames

Sorry, not usually so rushed.

Bill

 

[GGleason]

Bill,

My function addresses the &quot;lot of files&quot; issue by building a temporary table and adding those files to the table and then having the list box read the contents of that table. My goal was to make it a function for all cases. It does assume the user has rights to make a table in the database.

GGleason

 

[BillPower]

GGleason,

I'm simply giving vm079186 an answer to their 1st (which I embarassingly misread) and 3rd posting which came after your initial posting. The DB on the web address above answers this fully: &quot;If I used the dialog box code provided by Bill (see above), how can I get the module to set the target path of the dialog box to be the same as the directory stored on the photopath field and then get it to display the file it opens in the image holder on the form when the user click the open button on the dialog box?&quot; I was in a rush this morning to go to an interview, otherwise I would have explained more fully. Also I have just posted an update, in my haste I posted the wrong DB.

The &quot;lot of files&quot; issue referred to (sorry Jebry) Jebry's suggestion.

Incidentally, I think you need to post the code for the &quot;YesTableExist&quot; function for the overall function to work.

Finally, I think both your's and Jebry's are excellent suggestions.

Bill

 

[GGleason]

Here is the YesTableExist function:
[tt]
Public Function YesTableExist(strTableName As String) As Boolean
' Last modified on 6/13/01 10:59AM GCG
Dim i As Integer

YesTableExist = False

i = DCount(&quot;[Name]&quot;, &quot;MSysObjects&quot;, &quot;[Name] = &quot;&quot;&quot; + strTableName + &quot;&quot;&quot; And Type = 1&quot

If i = 1 Then
YesTableExist = True
End If
End Function
[/tt]
Thanks,
GGleason

 

[vm079186]

Hi Guys,

Thanks for all the advice (learning more about vba by reading your posts than at uni!)

I've found this database which suits my purposes pretty well, with a bit of modification to the forms and the class module, it works well.

http://www.mvps.org/access/modules/mdl0013.htm

I've added an image control to the database whose .picture property is set to the correct photo when the photo is clicked in the lbxFiles listbox.

I've also managed to get it to store the path and then if there is no path in the database, open to a default directory else open to the directory containing the photos for that record.

One question - I have a command button which I want to print the photo displayed in the image when clicked but don't want to create a report with a kodak image control which is opened / printed etc but just print the image straight from this form I already havethe path and filename stored in variables on the form (At the moment It launches the kodak imaging s/w shipped with windows).

Is there a way to print the photo strainght from the form by clicking the command button?

Thanks for the advice

vm079186

 

[GGleason]

You have to use the Shell Function in the click event of the command button. Look at the Access Help and that should guide you through it.
[tt]
' In Microsoft Windows:
' Specifying 1 as the second argument opens the application in
' normal size and gives it the focus.
Dim RetVal
RetVal = Shell(&quot;C:\WINDOWS\CALC.EXE&quot;, 1) ' Run Calculator.
[/tt]
hth,
GGleason