ADO Recordset as a Form Recordset?

[twinnyfo]

Hello, Friends!

It's been a while since I've had the chance to be on here, but I've run across a snag, which I think should have a relatively easy solution, but I am at a loss.

Background:
I have a bunch of files saved in folders on our network. They are all saved in individual folders. I want users to be able to select which folder and see a list of files in that folder, then either open or print one of those files.

What I want:
I want a Continuous Form with a Command Button for each record allowing the user to view or print. There will also be a Text Box which lists the file name the user has access to.

What I've done so far:
The Form itself, is easy-breezy, Command Buttons are no problem for this type of thing. I've also been able to create an ADO Recordset that loops through the designated folder and creates the list of files in the Recordset. Looping through and Debug.Print gives me exactly the list of files that I want. No problems at all.

What I have been unsuccessful at:
Although I have been successfully able to assign the Recordset to the Recordset Property of the Form, AND I have ben able to successfully assign the Field Name to the Control Source of the Text Box in question, every time I open the Form, the Text Box only lists "#Error".

Listed below is the scaled down code.

Any ideas what I am doing wrong? I think this must be a simple oversight.

Please note: both the Form itself and the Text Box are Unbound until the Form opens.

Option Compare Database
Option Explicit
 
Private Sub Form_Open(Cancel As Integer)
On Error GoTo EH
    Dim strPath     As String
    Dim rst         As ADODB.Recordset
    Dim strFile     As String
 
    strPath = "C:\Folder\"
 
    Set rst = New ADODB.Recordset
    With rst
        Call .Fields.Append("Filename", adChar, 255)
        Call .Open
        strFile = Dir(strPath & "IDNum*.*")
        Do While Not strFile = vbNullString
            Call .AddNew
            .Fields(0).Value = strFile
            Call .Update
            strFile = Dir
        Loop
        Set Me.Recordset = rst
        Me.txtFilename.ControlSource = .Fields(0).Name
    End With
 
    Set rst = Nothing
 
    Exit Sub
EH:
    MsgBox "There was an error listing the Files!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub

Any hepp any o' all y'all can provide would be greatly appreciated. This one has Goofy scratching his head profusely!

:-)

[NeoPa]

Unusual to see a recordset with no SQL associated. A first for me.

I would say only two things :

1. Is it necessary to use ADODB? Would DAO work better?
2. If you'd like to send me a copy of the file to look at I'd happily do so for you. An intriguing situation I may learn from. PM for email details if you're interested.

[twinnyfo]

NeoPa,

I could only find info on creating a Recordset like this (some folks call them "disconnected" recordsets, and I've seen another name for them thrown around, but I can't remember what it was) using ADO.

I'd be glad to try to create one via DAO. These disconnected RSs are new to me, also. I have a whopping three hours experience on them!

And.... the whole thing is what was posted. Before I got too deep into building the entire automobile, I figured I'd make sure I could get the wheels spinning first....

[twinnyfo]

Notice Line 15 below added; AND Line 25 commented out, with the Text Box using "FileName" as its bound Control Source. For whatever reason, Line 15 must be included. It works like a charm. I hope others may benefit from my head-scratching (and hair-pulling-out).

This is a good way to use a Recordset as the RecordSource for a Form (apparently when you set the Recordset Property, you are also setting the RecordSource Property). And now I'm apparently one of the few people who've been able to do it successfully.

I am sure there are numerous uses for this type of thing, but one could use it for listing files for the User, listing objects in a DB. I'm not sure I have much use beyond my initial concerns, but there may be applications for this in the future.

Hope this hepps!

Option Compare Database
Option Explicit
 
Private Sub Form_Open(Cancel As Integer)
On Error GoTo EH
    Dim strPath     As String
    Dim rst         As ADODB.Recordset
    Dim strFile     As String
 
    strPath = "C:\Folder\"
 
    Set rst = New ADODB.Recordset
    With rst
        Call .Fields.Append("Filename", adChar, 255)
        .LockType = adLockOptimistic
        Call .Open
        strFile = Dir(strPath & "IDNum*.*")
        Do While Not strFile = vbNullString
            Call .AddNew
            .Fields(0).Value = strFile
            Call .Update
            strFile = Dir
        Loop
        Set Me.Recordset = rst
'        Me.txtFilename.ControlSource = .Fields(0).Name
    End With
 
    Set rst = Nothing
 
    Exit Sub
EH:
    MsgBox "There was an error listing the Files!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub

[twinnyfo]

According to ADezii, DAO does not support Disconnected Recordsets.

[NeoPa]

If ADezii says that I expect it's true. He's quite a bright lad if truth be told.

Thank you TwinnyFo for this example, and particularly the solution. Very interesting work :-)

Unusually, I've selected your solution post as Best Answer. This seems appropriate in these circumstances.

[twinnyfo]

NeoPa,

I guess there are times when I do something right.... And I didn't want to presume and select my post as an answer. :-O

Miss you guys! Might be back on here soon for some other quirky questions.

[NeoPa]

Twinnyfo:
And I didn't want to presume and select my post as an answer.

Generally best avoided. Moderators have that responsibility so they may do so if and when they believe their post is instructive and generally helpful for anyone searching for the same question. That was true in this case but rarely so for those who come back to answer their own questions.

Not that we want to discourage any member from doing that. Far from it. We love it when they do. Rarely though are they candidates for Best Answer.