By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,762 Members | 1,838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,762 IT Pros & Developers. It's quick & easy.

ADO Recordset as a Form Recordset?

twinnyfo
Expert Mod 2.5K+
P: 3,283
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.


Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_Open(Cancel As Integer)
  5. On Error GoTo EH
  6.     Dim strPath     As String
  7.     Dim rst         As ADODB.Recordset
  8.     Dim strFile     As String
  9.  
  10.     strPath = "C:\Folder\"
  11.  
  12.     Set rst = New ADODB.Recordset
  13.     With rst
  14.         Call .Fields.Append("Filename", adChar, 255)
  15.         Call .Open
  16.         strFile = Dir(strPath & "IDNum*.*")
  17.         Do While Not strFile = vbNullString
  18.             Call .AddNew
  19.             .Fields(0).Value = strFile
  20.             Call .Update
  21.             strFile = Dir
  22.         Loop
  23.         Set Me.Recordset = rst
  24.         Me.txtFilename.ControlSource = .Fields(0).Name
  25.     End With
  26.  
  27.     Set rst = Nothing
  28.  
  29.     Exit Sub
  30. EH:
  31.     MsgBox "There was an error listing the Files!  " & _
  32.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  33.     Exit Sub
  34. End Sub
Any hepp any o' all y'all can provide would be greatly appreciated. This one has Goofy scratching his head profusely!

:-)
Aug 22 '17 #1

✓ answered by 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!

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_Open(Cancel As Integer)
  5. On Error GoTo EH
  6.     Dim strPath     As String
  7.     Dim rst         As ADODB.Recordset
  8.     Dim strFile     As String
  9.  
  10.     strPath = "C:\Folder\"
  11.  
  12.     Set rst = New ADODB.Recordset
  13.     With rst
  14.         Call .Fields.Append("Filename", adChar, 255)
  15.         .LockType = adLockOptimistic
  16.         Call .Open
  17.         strFile = Dir(strPath & "IDNum*.*")
  18.         Do While Not strFile = vbNullString
  19.             Call .AddNew
  20.             .Fields(0).Value = strFile
  21.             Call .Update
  22.             strFile = Dir
  23.         Loop
  24.         Set Me.Recordset = rst
  25. '        Me.txtFilename.ControlSource = .Fields(0).Name
  26.     End With
  27.  
  28.     Set rst = Nothing
  29.  
  30.     Exit Sub
  31. EH:
  32.     MsgBox "There was an error listing the Files!  " & _
  33.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  34.     Exit Sub
  35. End Sub

Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,489
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.
Aug 22 '17 #2

twinnyfo
Expert Mod 2.5K+
P: 3,283
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....
Aug 22 '17 #3

twinnyfo
Expert Mod 2.5K+
P: 3,283
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!

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_Open(Cancel As Integer)
  5. On Error GoTo EH
  6.     Dim strPath     As String
  7.     Dim rst         As ADODB.Recordset
  8.     Dim strFile     As String
  9.  
  10.     strPath = "C:\Folder\"
  11.  
  12.     Set rst = New ADODB.Recordset
  13.     With rst
  14.         Call .Fields.Append("Filename", adChar, 255)
  15.         .LockType = adLockOptimistic
  16.         Call .Open
  17.         strFile = Dir(strPath & "IDNum*.*")
  18.         Do While Not strFile = vbNullString
  19.             Call .AddNew
  20.             .Fields(0).Value = strFile
  21.             Call .Update
  22.             strFile = Dir
  23.         Loop
  24.         Set Me.Recordset = rst
  25. '        Me.txtFilename.ControlSource = .Fields(0).Name
  26.     End With
  27.  
  28.     Set rst = Nothing
  29.  
  30.     Exit Sub
  31. EH:
  32.     MsgBox "There was an error listing the Files!  " & _
  33.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  34.     Exit Sub
  35. End Sub
Aug 22 '17 #4

twinnyfo
Expert Mod 2.5K+
P: 3,283
According to ADezii, DAO does not support Disconnected Recordsets.
Aug 22 '17 #5

NeoPa
Expert Mod 15k+
P: 31,489
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.
Aug 23 '17 #6

twinnyfo
Expert Mod 2.5K+
P: 3,283
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.
Aug 23 '17 #7

NeoPa
Expert Mod 15k+
P: 31,489
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.
Aug 23 '17 #8

Post your reply

Sign in to post your reply or Sign up for a free account.