Tweaking gHudson's browse database (1 Viewer)

[NascarBaritone]

I am using the Browse... sample database posted by gHudson and and stuck in one place.

I have the form with the file directory as a subform to my main form. I am not getting any errors, but the files don't update based on the variable path I have spelled out. Any suggestions based on my code?

Private Function FilesAndDetails()
On Error GoTo Err_FilesAndDetails
    Dim rs As DAO.Recordset
    Dim vDir As Variant
    Dim Y As String
    Dim vardoc As String
    vardoc = Forms![Reps]!LastName.Value & ", " & Forms![Reps]!FirstName.Value
    Y = Left(Forms![Reps]!LastName.Value, 1)
    If Y >= "A" And Y <= "G" Then
        vardoc = "K:\Corporate Compliance\Rep Appointments\Sent Appointment Applications\Last Name A-G\" & vardoc
    ElseIf Y >= "H" And Y <= "N" Then
        vardoc = "K:\Corporate Compliance\Rep Appointments\Sent Appointment Applications\Last Name H-N\" & vardoc
    ElseIf Y >= "O" And Y <= "Z" Then
        vardoc = "K:\Corporate Compliance\Rep Appointments\Sent Appointment Applications\Last Name O-Z\" & vardoc
    
    Const sPath = "vardoc\" '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)
        rs!FileSize = FileLen(sPath & vDir)
        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 If
End Function

 

[Uncle Gizmo]

If you place a message box as shown:

Const sPath = "vardoc\" 'sPath must end with a back slash, sPath = "C:\Windows\"
MsgBox " >>> " & sPath

what do you get in the message box?

(You can copy the msgbox contents with CTRL C and paste them here with CTRL V)

 

[NascarBaritone]

Nothing. In the form when I select a record no message box appears.

 

[Uncle Gizmo]

Put another msgbox here:
vardoc = Forms![Reps]!LastName.Value & ", " & Forms![Reps]!FirstName.Value
MsgBox " >>> " & vardoc

 

[NascarBaritone]

This gave me a message box with the name of the first record in the database. However, my subform is not visible when my database loads (on purpose). When I click the tab that gives me the subform, the directory is blank meaning it isn't pointing to the variable location.

 

[Uncle Gizmo]

So from your answer I take it you have discovered the problem?

 

[NascarBaritone]

Not exactly. As best I can tell, the correct file information is being loaded into the tFiles table, but is not showing up in the subform. And nothing happens when I try and switch records.

I know the paths won't work for you, but see if you can make sense of the database.

 

[Uncle Gizmo]

Sorry, I don't have the time to get deeply involved.