check if file exist in a folder and make a list (1 Viewer)

eshai

Registered User.
Local time
Today, 16:29
Joined
Jul 14, 2015
Messages
193
hi:
i have db with students table and a form that show picture of a student and author details
all the pictures are in a folder and i have a code that called the picture by file name etc'


now what i need is to which student is missing a picture
1. im getting error in half way
2 i want the results of the missing picture well go to a table
3 the result that i'm getting Looks like a mess

best regards


Code:
Private Sub btn_miss_Click()
   Dim a As New FileSystemObject
Dim f As Folder
Dim s As String
Dim strFirstName As String
Dim strLastName As String
Dim strID As String
Dim strCity As String
Dim strStudentName As String

txtMiss.SetFocus
txtMiss.Text = ""
 For i = 0 To lstNames.ListCount - 1
   
    strID = lstNames.Column(0, i)
    strFirstName = lstNames.Column(2, i)
    strLastName = lstNames.Column(1, i)
    strCity = lstNames.Column(3, i)
    strStudentName = "D:\StudentsManager2\Pictures\" & strLastName & " " & strFirstName & " " & strCity & ".gif"
    
    IsFileExsist = a.FileExists(strStudentName)
    If Not IsFileExsist Then
        strStudentName = "D:\StudentsManager2\Pictures\" & strLastName & " " & strFirstName & " " & strCity & ".gif"
        IsFileExsist = a.FileExists(strStudentName)
        If (Not IsFileExsist) Then
           txtMiss = txtMiss.Text + strLastName & " " & strFirstName & " " & strCity + ", "
        End If
    End If
    
 Next i
End Sub
 

isladogs

MVP / VIP
Local time
Today, 14:29
Joined
Jan 14, 2017
Messages
18,186
now what i need is to which student is missing a picture
1. im getting error in half way
2 i want the results of the missing picture well go to a table
3 the result that i'm getting Looks like a mess

Can you help us to help you
1. Which line has an error & what is the error
2. See below
3. What exactly does 'looks like a mess' mean?

For info, I have the following setup for student photos though it works for any image linked to a number of records
Table - tblStudentPhotos
Fields - PupilID (PK), PhotoPath (text), ImageCheck (boolean)

I use the PupilID field for the image filenames e.g. 13124.png as that's guaranteed to be unique whereas JaneSmith.png may not be
I keep all photos in a specific folder and the PhotoPath field states what that full file path should be
Code similar to yours loops through all records & checks if the file exists in the specified folder. If yes, the boolean field is marked true & vice versa

The code I use is FileOrDirExists([PhotoPath]):

Code:
Function FileOrDirExists(PathName As String) As Boolean

'Used to test for photos
'Code from http://www.vbaexpress.com/kb/getarticle.php?kb_id=559
    'Macro Purpose: Function returns TRUE if the specified file
    '               or folder exists, false if not.
    'PathName     : Supports Windows mapped drives or UNC
    '             : Supports Macintosh paths
    'File usage   : Provide full file path and extension
    'Folder usage : Provide full folder path
    '               Accepts with/without trailing "\" (Windows)
    '               Accepts with/without trailing ":" (Macintosh)
     
    Dim iTemp As Integer
     
     'Ignore errors to allow for error evaluation
    On Error Resume Next
    iTemp = GetAttr(PathName)
     
     'Check if error exists and set response appropriately
    Select Case Err.Number
    Case Is = 0
        FileOrDirExists = True
    Case Else
        FileOrDirExists = False
    End Select
     
     'Resume error checking
    On Error GoTo 0
End Function
 

eshai

Registered User.
Local time
Today, 16:29
Joined
Jul 14, 2015
Messages
193
Can you help us to help you
1. Which line has an error & what is the error
2. See below
3. What exactly does 'looks like a mess' mean?

hi:
my form build different than yours
the image is linked by "student id" and the code is defrent
now i did a debag test and it was missing a little ","
a mess is that that i'm getting the names in one row instead of a column

my code for sowing images

Code:
Private Sub Comboboxid_Change()
On Error GoTo ErrorHandler
    
    Dim str
    Dim strLastName As String
    Dim strFirstName As String
    
    str = Split(Me![Comboboxid])
    Me.RecordsetClone.FindFirst "[student id]='" & str(0) & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
    
    strLastName = Me![lastname]
    strFirstName = Me![firstname]
    
    ' init image
    StudentPicture.Visible = False
    On Error GoTo PictureHandler
        
    Dim a As New FileSystemObject
    Dim strStudentName As String
    Dim fl As File
    Dim IsFileExsist As Boolean
    Dim strPlace As String
    
    
    strStudentName = "my folder path\" & strLastName & " " & strFirstName & ".gif"
       If (Not IsFileExsist) Then
    strStudentName = "my folder path\" & strLastName & " " & strFirstName & ".jpg"
    IsFileExsist = a.FileExists(strStudentName)
End If
    'Set fl = a.GetFile(strStudentName)
    
    IsFileExsist = a.FileExists(strStudentName)
    If IsFileExsist Then
        StudentPicture.Picture = strStudentName
        StudentPicture.Visible = True
    Else
   
        strPlace = [city].OldValue
        strStudentName = "my folder path\" & strLastName & " " & strFirstName & " " & strPlace & ".gif"
  
        IsFileExsist = a.FileExists(strStudentName)
        If (IsFileExsist) Then
            StudentPicture.Picture = strStudentName
            StudentPicture.Visible = True
        End If
    End If
    
    Exit Sub
PictureHandler:
    Exit Sub
ErrorHandler:
    MsgBox Err.Description & " " & Err.Number
    Resume Next

End Sub
 

isladogs

MVP / VIP
Local time
Today, 14:29
Joined
Jan 14, 2017
Messages
18,186
I'm confused by your answer but that may be because English doesn't appear to be your first language

the image is linked by "student id"

How can that be true when you have code like this

Code:
strStudentName = "my folder path\" & strLastName & " " & strFirstName & ".gif"
...
StudentPicture.Picture = strStudentName

i'm getting the names in one row instead of a column
Not sure which part of your code is being used to create a list
Are you trying to populate a listbox using a value list?
If so, suggest you instead use a query which creates records where the image doesn't exist

Also I suggest you move the code shown in your second post to the After Update event of your combo
 

eshai

Registered User.
Local time
Today, 16:29
Joined
Jul 14, 2015
Messages
193
I'm confused by your answer but that may be because English doesn't appear to be your first language



How can that be true when you have code like this

Code:
strStudentName = "my folder path\" & strLastName & " " & strFirstName & ".gif"
...
StudentPicture.Picture = strStudentName


Not sure which part of your code is being used to create a list
Are you trying to populate a listbox using a value list?
If so, suggest you instead use a query which creates records where the image doesn't exist

Also I suggest you move the code shown in your second post to the After Update event of your combo

1.the combo box is linked to the student table by id not the perfect way but its working
2. i need to break the string to a rows now i get "lastname firstname city.lastname firstname city.lastname firstname city."and so on
i need to replace the dot with a break that well make a new row below
Code:
txtMiss = txtMiss.Text + strLastName & " " & strFirstName & " " & strCity & "." [COLOR="SeaGreen"]'here instead of a dot i need a new column[/COLOR]
 

isladogs

MVP / VIP
Local time
Today, 14:29
Joined
Jan 14, 2017
Messages
18,186
Try this:

Code:
Me.txtMiss = Me.txtMiss & vbCrLf & strLastName & " " & strFirstName & " " & strCity

NOTE:
1. vbCrLf will add a line return
2. I've replaced use of '+' with '&' in your code
 

eshai

Registered User.
Local time
Today, 16:29
Joined
Jul 14, 2015
Messages
193
Try this:

Code:
Me.txtMiss = Me.txtMiss & vbCrLf & strLastName & " " & strFirstName & " " & strCity

NOTE:
1. vbCrLf will add a line return
2. I've replaced use of '+' with '&' in your code

thank you very much its working prefect
now i can print the list and run after the students to take a picture:)
 

Users who are viewing this thread

Top Bottom