Problem updating record in table . . .

  • Thread starter Thread starter Jim Wood
  • Start date Start date
J

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
 
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.
 
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.
 
Back
Top