Problem updating record in table . . .

[Jim Wood]

I am having a problem updating the "Encoding" field in the "Tapelist" table.
I have a group of mp3 files on my hard drive in the "h:sermons\" folder and
I want to update a Yes/No field ("Encoded") in my table using VBA. This is
the code that I have:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Update_Encoding_List_Click()
DoCmd.SetWarnings False

'Update Outlines field
Dim objDbs As DAO.Database
Dim objRst As DAO.Recordset
Set objDbs = CurrentDb
Set objRst = objDbs.OpenRecordset("Missing Encodes qry")
Set objFso = CreateObject("Scripting.FileSystemObject")

'Set path to outline directory
fpath = "h:\sermons" & "/"

Do While Not objRst.EOF

'Set "Encoded" to True if mp3 version exists for tape "ID"
objRst.Edit
objRst.Fields("Encoded") = objFso.FileExists(fpath &
objRst.Fields("ID") & "*.mp3")
objRst.Update
objRst.MoveNext
Loop

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

As an example one of the files is named: 1018 - Study of Romans (part
1).mp3. Note: In every case, the "ID" field is a 4 digit number that matches
the first 4 characters of the filename.

If I change line 18 to read:

objRst.Fields("Encoded") = objFso.FileExists(fpath &
objRst.Fields("ID") & " - Study of Romans (part 1).mp3")
it will correctly update that record to show that the file exists.

But, if I try to use a wildcard in line 18 like:
objRst.Fields("Encoded") = objFso.FileExists(fpath &
objRst.Fields("ID") & "*.mp3")
or
objRst.Fields("Encoded") = objFso.FileExists(fpath &
objRst.Fields("ID") & "%.mp3")

no records are updated.

Can anyone tell me what I'm doing wrong here? Using Access 2003

Thanks!

Jim

 

[Douglas J. Steele]

You appear to have the wrong slash when you're assigning the value to fpath.
I'd suggest

fpath = "h:\sermons\"

If the above didn't solve the problem, it's probably due to the spaces in
the file name: you'll need to enclose them in quotes. Try:

objRst.Fields("Encoded") = objFso.FileExists(Chr$(34) & fpath &
objRst.Fields("ID") & "*.mp3" & Chr$(34))

To be honest, though, I don't understand why you're invoking the unnecessary
overhead of FSO just to determine file existence.

objRst.Fields("Encoded") = Len(Dir(Chr$(34) & fpath &
objRst.Fields("ID") & "*.mp3" & Chr$(34))) > 0

works just as well.

 

[Jim Wood]

Thanks, I'll try your suggestions. As far as the slash, that was the only
way I was able to get it to access the directory in another module. I'll
also try the backslash again as you suggested.