Directory Search Error (1 Viewer)

[ChronicFear]

Hello,

I downloaded Ghudson's file here in order to allow my users to view documents relevant to the record they are viewing in the database.

I've made some slight modifications so that the code pulls all the file links from the directory into a table. The code then scans the entire title to grab a unique ID number that corresponds with the record the user is viewing in the database. A query then limits the file links shown to those that correspond with the record, and the user views this in a form.

This works fine in the demo that I downloaded from Ghudson (I imported some of the other files from my db into his for testing). My problem occurs when I try to import the modified table and form from Ghudson into my db. I've made sure that everything is identical between the two, but for some reason it only works in one. In my db it says "13 - Type mismatch error".

I appreciate your help.

Thanks,
CF

My modifications:

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

    DoCmd.Restore

    Call FilesAndDetails

    Me.Caption = "Double click on a file to view it."

Exit_Form_Open:
    Exit Sub
    
Err_Form_Open:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Form_Open

End Sub

Public Function FilesAndDetails()
On Error GoTo Err_FilesAndDetails

    Dim rs As Recordset
    Dim vDir As Variant
    Dim sTransID As Variant
    Dim sTransID2 As Variant
    
    Const sPath = "\\server\file\file2\file3\" 'sPath must end with a back slash, sPath = "C:\Windows\"
    
    CurrentDb.Execute "Delete tTempFiles.* from tFiles;"
    
    Set rs = CurrentDb.OpenRecordset("tFiles")
    
    vDir = Dir(sPath & "*.*")
    Do Until vDir = ""

                rs.AddNew
                rs!FilePathName = sPath & vDir
                rs!FilePath = sPath
                rs!FileName = vDir
                rs!ModifiedDate = FileDateTime(sPath & vDir)
                
                sTransID = ParseWord(vDir, -1)
                sTransID2 = ParseWord(sTransID, -2, ".")
                
                If IsNumeric(sTransID2) Then
                    rs!TransactionID = sTransID2
                End If
                
                rs.Update
                vDir = Dir
    Loop
    
    rs.Close
    Set rs = Nothing

    DoCmd.Requery

Exit_FilesAndDetails:
    Exit Function
    
Err_FilesAndDetails:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_FilesAndDetails

End Function

Private Sub tbFileName_DblClick(Cancel As Integer)
On Error GoTo Err_tbFileName_DblClick

    OpenFile tbFilePathName

Exit_tbFileName_DblClick:
    Exit Sub
    
Err_tbFileName_DblClick:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_tbFileName_DblClick

End Sub

 

[boblarson]

See if making this change works:

Dim rs As Recordset

change to:

Dim rs As DAO.Recordset

 

[GaryPanic]

this one has had me thrown for quite a while -- I'll keep my eyes on this thread..

 

[ChronicFear]

Yep, making that little change seems to have solved the problem. Weird.

Would you mind explaining to me exactly what the issue could have been? Obviously I wasn't being specific enough for Access, but what was Access likely assuming I meant by simply "recordset"?

Thank you so much for your help.

 

[gemma-the-husky]

i think bob correctly surmmised that your version of access (A2000?) uses ADO, rather than DAO as the default recordset handler - some commands are the same, but others arent

- so either explicitly tell access to use DAO, or alternatively set DAO above ADO in your references, then it will automatically use DAO (but that might mess up other code if you had any other similar uses)

 

[ChronicFear]

Ahh, I see. Yes, that must have been the problem. Ghudson's version was written in A97, and while I converted it up to A2002 (my version), I forgot about those little changes in the coding.

Thank you again for all your help, Bob and Gemma. It is sincerely appreciated.

 

[boblarson]

i think bob correctly surmmised that your version of access (A2000?) uses ADO, rather than DAO as the default recordset handler - some commands are the same, but others arent

- so either explicitly tell access to use DAO, or alternatively set DAO above ADO in your references, then it will automatically use DAO (but that might mess up other code if you had any other similar uses)

That is it exactly. It would be a good thing to get into the habit of explicitly referencing your ADO and DAO objects because they can co-exist if you do and someone coming in later might mess something up if they don't see the explicit referencing (or Access might get thrown into a tizzy if they all of a sudden start using ADO when most of the code is DAO and the explicit referencing hasn't been used.