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

Filtering recordsource in VBA

P: n/a
Using MS SQL-server 2000, with Access 2000 adp as frontend

Hi all,

I'm kinda stuck with the code below. What I'm trying to accomplish
here is to extract certain records from a table, based on the names of
folders on my harddisk.
For each project that we have, we make a folder on the fileserver with
the same name and a new record in the table Orders. Projects (and
their respective folders) are numbered from 10000-99999. I now would
like to make a form that contains all projectrecords for which there
is still a folder present on the fileserver. Here's what I have so
far:

Private Sub Form_Open(Cancel As Integer)

Dim FolderName As String
Dim FolderList As String
Dim SQLstr As String

FolderName = Dir$("C:\test\", vbDirectory)

Do While (FolderName <> "")
FolderList = FolderList & FolderName & " "
FolderName = Dir$()
Loop

MsgBox "Folderlist:" & vbCr & FolderList

SQLstr = "SELECT * FROM dbo.tblOrders " & _
"WHERE Projectnr LIKE '" & FolderList & "'"

Me.RecordSource = SQLstr

End Sub

As you might have guessed, this doesn't work and returns an empty
recordsource. I suspect the problem to be in the WHERE clause, and the
fact that the Folderlist string can contain up to 300 foldernames
separated by spaces.
The MsgBox serves just as a tool to show that the actual Folderlist is
assembled correctly. I'd very much appreciate if someone can get this
code to work, or point me to possible solutions.

thanks in advance,
Jerry
Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.