File List

[Jon22]

Is it possible to create a table with a field that has a list of file names
from a particular directory on our small office network? Ideally I would like
the records to be refreshed every time the database is opened. The file path
would always remain the same with the only variation being the file name. The
file extension would also be constant (always being ".jpg").

Path would be:

"\\Server\orion\Artwork\JPEG Files\2009\[filename].jpg"

In a nut shell, I'd like to create a field in a table which has the list of
"[filenames]" from the above path. If it has to return the full file path
then I think I can work around this using text functions.

Please bear in mind I have virtually zero exp with VBA and have built our
databsae using the inbuilt functions.

 

[kc-mass]

Create a lets say tblJPGFiles with one field "FileName".

Copy this code and past it into a new module and save the module.
Place a command button on a form. In the onclick event just
type in GetFileNames. When you click the button this will run
and fill the table with names of jpg files.

Sub GetFilenames()
Dim db As Database
Dim rs As Recordset
Dim strMyPath As String
Dim strMyFile As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tblJPGFiles")
type your real path in the next line
strMyPath = "c:\*.jpg"
' get the first matching file name
strMyFile = Dir(strMyPath)
Do While strMyFile <> ""
' add the filename to the table
rs.AddNew
rs!FileName = strMyFile
rs.Update
'get the next file name
strMyFile = Dir
Loop
' clean up
Set db = Nothing
Set rs = Nothing
End Sub
RegardsKevin

 

[Jon22]

Thank you so much. It worked.

Create a lets say tblJPGFiles with one field "FileName".

Copy this code and past it into a new module and save the module.
Place a command button on a form. In the onclick event just
type in GetFileNames. When you click the button this will run
and fill the table with names of jpg files.

Sub GetFilenames()
Dim db As Database
Dim rs As Recordset
Dim strMyPath As String
Dim strMyFile As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tblJPGFiles")
type your real path in the next line
strMyPath = "c:\*.jpg"
' get the first matching file name
strMyFile = Dir(strMyPath)
Do While strMyFile <> ""
' add the filename to the table
rs.AddNew
rs!FileName = strMyFile
rs.Update
'get the next file name
strMyFile = Dir
Loop
' clean up
Set db = Nothing
Set rs = Nothing
End Sub
RegardsKevin

Is it possible to create a table with a field that has a list of file
names
from a particular directory on our small office network? Ideally I would
like
the records to be refreshed every time the database is opened. The file
path
would always remain the same with the only variation being the file name.
The
file extension would also be constant (always being ".jpg").

Path would be:

"\\Server\orion\Artwork\JPEG Files\2009\[filename].jpg"

In a nut shell, I'd like to create a field in a table which has the list
of
"[filenames]" from the above path. If it has to return the full file path
then I think I can work around this using text functions.

Please bear in mind I have virtually zero exp with VBA and have built our
databsae using the inbuilt functions.

 

[Jon22]

Just one more thing, is it possible to add into this script something that
filters out and excludes any file names that already exist in the field
"FileName"? I noticed if I run it again it just appends another copy of the
list of file names and I would like to run this code each time a particular
form opens so that the information is always current.

Thank you so much. It worked.

Create a lets say tblJPGFiles with one field "FileName".

Copy this code and past it into a new module and save the module.
Place a command button on a form. In the onclick event just
type in GetFileNames. When you click the button this will run
and fill the table with names of jpg files.

Sub GetFilenames()
Dim db As Database
Dim rs As Recordset
Dim strMyPath As String
Dim strMyFile As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tblJPGFiles")
type your real path in the next line
strMyPath = "c:\*.jpg"
' get the first matching file name
strMyFile = Dir(strMyPath)
Do While strMyFile <> ""
' add the filename to the table
rs.AddNew
rs!FileName = strMyFile
rs.Update
'get the next file name
strMyFile = Dir
Loop
' clean up
Set db = Nothing
Set rs = Nothing
End Sub
RegardsKevin

Is it possible to create a table with a field that has a list of file
names
from a particular directory on our small office network? Ideally I would
like
the records to be refreshed every time the database is opened. The file
path
would always remain the same with the only variation being the file name.
The
file extension would also be constant (always being ".jpg").

Path would be:

"\\Server\orion\Artwork\JPEG Files\2009\[filename].jpg"

In a nut shell, I'd like to create a field in a table which has the list
of
"[filenames]" from the above path. If it has to return the full file path
then I think I can work around this using text functions.

Please bear in mind I have virtually zero exp with VBA and have built our
databsae using the inbuilt functions.

 

[kc-mass]

The simple option would be:

If you only ever want to have in the table the then current file names
then you could run a delete query to empty the table before
repopulating it with the module.

You could do that within the code with :

Sub GetFilenames()
Dim db As Database
Dim rs As Recordset
Dim strMyPath As String
Dim strMyFile As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tblJPGFiles")
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tbljpgfiles"
DoCmd.SetWarnings True
'type in the real path in the next line
strMyPath = "c:\*.jpg"
strMyFile = Dir(strMyPath)
Do While strMyFile <> ""
rs.AddNew
rs!FileName = strMyFile
rs.Update
'get the next file name
strMyFile = Dir
Loop
MsgBox ("File Name Load Completed")
Set db = Nothing
Set rs = Nothing
End Sub


Regards
Kevin

Just one more thing, is it possible to add into this script something that
filters out and excludes any file names that already exist in the field
"FileName"? I noticed if I run it again it just appends another copy of
the
list of file names and I would like to run this code each time a
particular
form opens so that the information is always current.

Thank you so much. It worked.

Create a lets say tblJPGFiles with one field "FileName".

Copy this code and past it into a new module and save the module.
Place a command button on a form. In the onclick event just
type in GetFileNames. When you click the button this will run
and fill the table with names of jpg files.

Sub GetFilenames()
Dim db As Database
Dim rs As Recordset
Dim strMyPath As String
Dim strMyFile As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tblJPGFiles")
type your real path in the next line
strMyPath = "c:\*.jpg"
' get the first matching file name
strMyFile = Dir(strMyPath)
Do While strMyFile <> ""
' add the filename to the table
rs.AddNew
rs!FileName = strMyFile
rs.Update
'get the next file name
strMyFile = Dir
Loop
' clean up
Set db = Nothing
Set rs = Nothing
End Sub
RegardsKevin
Is it possible to create a table with a field that has a list of file
names
from a particular directory on our small office network? Ideally I
would
like
the records to be refreshed every time the database is opened. The
file
path
would always remain the same with the only variation being the file
name.
The
file extension would also be constant (always being ".jpg").

Path would be:

"\\Server\orion\Artwork\JPEG Files\2009\[filename].jpg"

In a nut shell, I'd like to create a field in a table which has the
list
of
"[filenames]" from the above path. If it has to return the full file
path
then I think I can work around this using text functions.

Please bear in mind I have virtually zero exp with VBA and have built
our
databsae using the inbuilt functions.

 

[Jon22]

Great, thanks again kc, another little thing - is it possible to have the
file names brought in without the file extension (".jpg") in the name?

Just one more thing, is it possible to add into this script something that
filters out and excludes any file names that already exist in the field
"FileName"? I noticed if I run it again it just appends another copy of the
list of file names and I would like to run this code each time a particular
form opens so that the information is always current.

Thank you so much. It worked.

Create a lets say tblJPGFiles with one field "FileName".

Copy this code and past it into a new module and save the module.
Place a command button on a form. In the onclick event just
type in GetFileNames. When you click the button this will run
and fill the table with names of jpg files.

Sub GetFilenames()
Dim db As Database
Dim rs As Recordset
Dim strMyPath As String
Dim strMyFile As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tblJPGFiles")
type your real path in the next line
strMyPath = "c:\*.jpg"
' get the first matching file name
strMyFile = Dir(strMyPath)
Do While strMyFile <> ""
' add the filename to the table
rs.AddNew
rs!FileName = strMyFile
rs.Update
'get the next file name
strMyFile = Dir
Loop
' clean up
Set db = Nothing
Set rs = Nothing
End Sub
RegardsKevin
Is it possible to create a table with a field that has a list of file
names
from a particular directory on our small office network? Ideally I would
like
the records to be refreshed every time the database is opened. The file
path
would always remain the same with the only variation being the file name.
The
file extension would also be constant (always being ".jpg").

Path would be:

"\\Server\orion\Artwork\JPEG Files\2009\[filename].jpg"

In a nut shell, I'd like to create a field in a table which has the list
of
"[filenames]" from the above path. If it has to return the full file path
then I think I can work around this using text functions.

Please bear in mind I have virtually zero exp with VBA and have built our
databsae using the inbuilt functions.

 

[kc-mass]

Change: rs!FileName = strMyFile
To: rs!FileName = Left(strMyFile, instr(strMyFile,".")-1)

Great, thanks again kc, another little thing - is it possible to have the
file names brought in without the file extension (".jpg") in the name?

Just one more thing, is it possible to add into this script something
that
filters out and excludes any file names that already exist in the field
"FileName"? I noticed if I run it again it just appends another copy of
the
list of file names and I would like to run this code each time a
particular
form opens so that the information is always current.

Thank you so much. It worked.

:

Create a lets say tblJPGFiles with one field "FileName".

Copy this code and past it into a new module and save the module.
Place a command button on a form. In the onclick event just
type in GetFileNames. When you click the button this will run
and fill the table with names of jpg files.

Sub GetFilenames()
Dim db As Database
Dim rs As Recordset
Dim strMyPath As String
Dim strMyFile As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tblJPGFiles")
type your real path in the next line
strMyPath = "c:\*.jpg"
' get the first matching file name
strMyFile = Dir(strMyPath)
Do While strMyFile <> ""
' add the filename to the table
rs.AddNew
rs!FileName = strMyFile
rs.Update
'get the next file name
strMyFile = Dir
Loop
' clean up
Set db = Nothing
Set rs = Nothing
End Sub
RegardsKevin
Is it possible to create a table with a field that has a list of
file
names
from a particular directory on our small office network? Ideally I
would
like
the records to be refreshed every time the database is opened. The
file
path
would always remain the same with the only variation being the file
name.
The
file extension would also be constant (always being ".jpg").

Path would be:

"\\Server\orion\Artwork\JPEG Files\2009\[filename].jpg"

In a nut shell, I'd like to create a field in a table which has the
list
of
"[filenames]" from the above path. If it has to return the full
file path
then I think I can work around this using text functions.

Please bear in mind I have virtually zero exp with VBA and have
built our
databsae using the inbuilt functions.

 

[Jon22]

Wow, great,. I think after this 4 years of building databases for my little
company it might be time for me to start learning VB.

Is it possible to combine macros and VB code? For example, I have a macro
that runs various functions on a form during the "On Open" event (functions
that I wouldn't even begin to know how to right in VB) but would like to run
your code as well. I was playing around with adding a RunCode Action to this
macro but I can't seem to get it to work. Is that the right way to go about
it if I want to run your code coupled with a macro on the same event?

(I apologise if I am stretching the friendship a bit with all these
additional requests - but I'm learning lots!)

Cheers.

Change: rs!FileName = strMyFile
To: rs!FileName = Left(strMyFile, instr(strMyFile,".")-1)

Great, thanks again kc, another little thing - is it possible to have the
file names brought in without the file extension (".jpg") in the name?

Just one more thing, is it possible to add into this script something
that
filters out and excludes any file names that already exist in the field
"FileName"? I noticed if I run it again it just appends another copy of
the
list of file names and I would like to run this code each time a
particular
form opens so that the information is always current.

:

Thank you so much. It worked.

:

Create a lets say tblJPGFiles with one field "FileName".

Copy this code and past it into a new module and save the module.
Place a command button on a form. In the onclick event just
type in GetFileNames. When you click the button this will run
and fill the table with names of jpg files.

Sub GetFilenames()
Dim db As Database
Dim rs As Recordset
Dim strMyPath As String
Dim strMyFile As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tblJPGFiles")
type your real path in the next line
strMyPath = "c:\*.jpg"
' get the first matching file name
strMyFile = Dir(strMyPath)
Do While strMyFile <> ""
' add the filename to the table
rs.AddNew
rs!FileName = strMyFile
rs.Update
'get the next file name
strMyFile = Dir
Loop
' clean up
Set db = Nothing
Set rs = Nothing
End Sub
RegardsKevin
Is it possible to create a table with a field that has a list of
file
names
from a particular directory on our small office network? Ideally I
would
like
the records to be refreshed every time the database is opened. The
file
path
would always remain the same with the only variation being the file
name.
The
file extension would also be constant (always being ".jpg").

Path would be:

"\\Server\orion\Artwork\JPEG Files\2009\[filename].jpg"

In a nut shell, I'd like to create a field in a table which has the
list
of
"[filenames]" from the above path. If it has to return the full
file path
then I think I can work around this using text functions.

Please bear in mind I have virtually zero exp with VBA and have
built our
databsae using the inbuilt functions.

 

[kc-mass]

I haven't dealt with macros in any meaningful way in 10 or so years. My
recollection of the runcode cmd was in Access 97 I think it could run a
Function but not a sub procedure. I may be wrong on that.

I also think there is (maybe "was") a built in utility to convert access
macros to VBA code module.

Maybe you should post a new question geared at this and someone can help.

Regards

Kevin

Wow, great,. I think after this 4 years of building databases for my
little
company it might be time for me to start learning VB.

Is it possible to combine macros and VB code? For example, I have a macro
that runs various functions on a form during the "On Open" event
(functions
that I wouldn't even begin to know how to right in VB) but would like to
run
your code as well. I was playing around with adding a RunCode Action to
this
macro but I can't seem to get it to work. Is that the right way to go
about
it if I want to run your code coupled with a macro on the same event?

(I apologise if I am stretching the friendship a bit with all these
additional requests - but I'm learning lots!)

Cheers.

Change: rs!FileName = strMyFile
To: rs!FileName = Left(strMyFile, instr(strMyFile,".")-1)

Great, thanks again kc, another little thing - is it possible to have
the
file names brought in without the file extension (".jpg") in the name?

:

Just one more thing, is it possible to add into this script something
that
filters out and excludes any file names that already exist in the
field
"FileName"? I noticed if I run it again it just appends another copy
of
the
list of file names and I would like to run this code each time a
particular
form opens so that the information is always current.

:

Thank you so much. It worked.

:

Create a lets say tblJPGFiles with one field "FileName".

Copy this code and past it into a new module and save the module.
Place a command button on a form. In the onclick event just
type in GetFileNames. When you click the button this will run
and fill the table with names of jpg files.

Sub GetFilenames()
Dim db As Database
Dim rs As Recordset
Dim strMyPath As String
Dim strMyFile As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tblJPGFiles")
type your real path in the next line
strMyPath = "c:\*.jpg"
' get the first matching file name
strMyFile = Dir(strMyPath)
Do While strMyFile <> ""
' add the filename to the table
rs.AddNew
rs!FileName = strMyFile
rs.Update
'get the next file name
strMyFile = Dir
Loop
' clean up
Set db = Nothing
Set rs = Nothing
End Sub
RegardsKevin
Is it possible to create a table with a field that has a list of
file
names
from a particular directory on our small office network? Ideally
I
would
like
the records to be refreshed every time the database is opened.
The
file
path
would always remain the same with the only variation being the
file
name.
The
file extension would also be constant (always being ".jpg").

Path would be:

"\\Server\orion\Artwork\JPEG Files\2009\[filename].jpg"

In a nut shell, I'd like to create a field in a table which has
the
list
of
"[filenames]" from the above path. If it has to return the full
file path
then I think I can work around this using text functions.

Please bear in mind I have virtually zero exp with VBA and have
built our
databsae using the inbuilt functions.

 

[Jon22]

No worries. I did it the other way around and added a "DoCmd.RunMacro..."
line to your code. Thanks very much for all your help.

I haven't dealt with macros in any meaningful way in 10 or so years. My
recollection of the runcode cmd was in Access 97 I think it could run a
Function but not a sub procedure. I may be wrong on that.

I also think there is (maybe "was") a built in utility to convert access
macros to VBA code module.

Maybe you should post a new question geared at this and someone can help.

Regards

Kevin

Wow, great,. I think after this 4 years of building databases for my
little
company it might be time for me to start learning VB.

Is it possible to combine macros and VB code? For example, I have a macro
that runs various functions on a form during the "On Open" event
(functions
that I wouldn't even begin to know how to right in VB) but would like to
run
your code as well. I was playing around with adding a RunCode Action to
this
macro but I can't seem to get it to work. Is that the right way to go
about
it if I want to run your code coupled with a macro on the same event?

(I apologise if I am stretching the friendship a bit with all these
additional requests - but I'm learning lots!)

Cheers.

Change: rs!FileName = strMyFile
To: rs!FileName = Left(strMyFile, instr(strMyFile,".")-1)


Great, thanks again kc, another little thing - is it possible to have
the
file names brought in without the file extension (".jpg") in the name?

:

Just one more thing, is it possible to add into this script something
that
filters out and excludes any file names that already exist in the
field
"FileName"? I noticed if I run it again it just appends another copy
of
the
list of file names and I would like to run this code each time a
particular
form opens so that the information is always current.

:

Thank you so much. It worked.

:

Create a lets say tblJPGFiles with one field "FileName".

Copy this code and past it into a new module and save the module.
Place a command button on a form. In the onclick event just
type in GetFileNames. When you click the button this will run
and fill the table with names of jpg files.

Sub GetFilenames()
Dim db As Database
Dim rs As Recordset
Dim strMyPath As String
Dim strMyFile As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tblJPGFiles")
type your real path in the next line
strMyPath = "c:\*.jpg"
' get the first matching file name
strMyFile = Dir(strMyPath)
Do While strMyFile <> ""
' add the filename to the table
rs.AddNew
rs!FileName = strMyFile
rs.Update
'get the next file name
strMyFile = Dir
Loop
' clean up
Set db = Nothing
Set rs = Nothing
End Sub
RegardsKevin
Is it possible to create a table with a field that has a list of
file
names
from a particular directory on our small office network? Ideally
I
would
like
the records to be refreshed every time the database is opened.
The
file
path
would always remain the same with the only variation being the
file
name.
The
file extension would also be constant (always being ".jpg").

Path would be:

"\\Server\orion\Artwork\JPEG Files\2009\[filename].jpg"

In a nut shell, I'd like to create a field in a table which has
the
list
of
"[filenames]" from the above path. If it has to return the full
file path
then I think I can work around this using text functions.

Please bear in mind I have virtually zero exp with VBA and have
built our
databsae using the inbuilt functions.

 

[kc-mass]

That's great that you worked it out.

Kevin

No worries. I did it the other way around and added a "DoCmd.RunMacro..."
line to your code. Thanks very much for all your help.

I haven't dealt with macros in any meaningful way in 10 or so years. My
recollection of the runcode cmd was in Access 97 I think it could run a
Function but not a sub procedure. I may be wrong on that.

I also think there is (maybe "was") a built in utility to convert access
macros to VBA code module.

Maybe you should post a new question geared at this and someone can help.

Regards

Kevin

Wow, great,. I think after this 4 years of building databases for my
little
company it might be time for me to start learning VB.

Is it possible to combine macros and VB code? For example, I have a
macro
that runs various functions on a form during the "On Open" event
(functions
that I wouldn't even begin to know how to right in VB) but would like
to
run
your code as well. I was playing around with adding a RunCode Action to
this
macro but I can't seem to get it to work. Is that the right way to go
about
it if I want to run your code coupled with a macro on the same event?

(I apologise if I am stretching the friendship a bit with all these
additional requests - but I'm learning lots!)

Cheers.

:

Change: rs!FileName = strMyFile
To: rs!FileName = Left(strMyFile, instr(strMyFile,".")-1)


Great, thanks again kc, another little thing - is it possible to
have
the
file names brought in without the file extension (".jpg") in the
name?

:

Just one more thing, is it possible to add into this script
something
that
filters out and excludes any file names that already exist in the
field
"FileName"? I noticed if I run it again it just appends another
copy
of
the
list of file names and I would like to run this code each time a
particular
form opens so that the information is always current.

:

Thank you so much. It worked.

:

Create a lets say tblJPGFiles with one field "FileName".

Copy this code and past it into a new module and save the
module.
Place a command button on a form. In the onclick event just
type in GetFileNames. When you click the button this will run
and fill the table with names of jpg files.

Sub GetFilenames()
Dim db As Database
Dim rs As Recordset
Dim strMyPath As String
Dim strMyFile As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tblJPGFiles")
type your real path in the next line
strMyPath = "c:\*.jpg"
' get the first matching file name
strMyFile = Dir(strMyPath)
Do While strMyFile <> ""
' add the filename to the table
rs.AddNew
rs!FileName = strMyFile
rs.Update
'get the next file name
strMyFile = Dir
Loop
' clean up
Set db = Nothing
Set rs = Nothing
End Sub
RegardsKevin
Is it possible to create a table with a field that has a list
of
file
names
from a particular directory on our small office network?
Ideally
I
would
like
the records to be refreshed every time the database is
opened.
The
file
path
would always remain the same with the only variation being
the
file
name.
The
file extension would also be constant (always being ".jpg").

Path would be:

"\\Server\orion\Artwork\JPEG Files\2009\[filename].jpg"

In a nut shell, I'd like to create a field in a table which
has
the
list
of
"[filenames]" from the above path. If it has to return the
full
file path
then I think I can work around this using text functions.

Please bear in mind I have virtually zero exp with VBA and
have
built our
databsae using the inbuilt functions.