J
Jon22
We have a network filing system which we store jpeg files on under a common
directory with sub folders named according to the year ("2008", "2009" etc).
As we create the jpeg files, we name the files as follows - a two digit
number representing the year it's created (eg. "09"), then a dash ("-") and
then a 3 digit number representing it's order of creation for that year. For
example, the next file we create will be called "09-401.jpg" We call this
string (09-401) the Design Number.
The files are stored in a folder according to the year they're created (ie
their file name prefix), and we create a new folder under the same directory
every year. This means that most of the file path for all of our designs is
common - except for the final directory which can be assumed from the name of
the file itself. This path never changes and this years files are stored in
the following directory:
"\\Server\orion\Artwork\JPEG Files\2009\"
So the file path of the latest design we did is:
"\\Server\orion\Artwork\JPEG Files\2009\09-400.jpg"
And for example the file path of a design we did last year:
"\\Server\orion\Artwork\JPEG Files\2008\08-296.jpg"
The design names are stored in our database (without the file extension
".jpg") along with various other bits of info about them (customer, designer,
date etc). The designers log these designs in before they start creating the
jpeg files because they use the database to tell them what the next Design
Number is to use for the file name.
Unfortunately I have to deal with some of the database users (designers)
being a bit slack with copying their finished jpeg files from their hard
drives to the directory on our Server.
I want my database to do four things which rely on whether or not the jpeg
files reside within their Server directory. They are:
1. On a couple of forms, display a picture of the jpeg file via an image
control based on the value (Design Number) selected in a combobox.
2. Through a subform (in datasheet view), display a list of Design Numbers
which have been recorded in the database, but who's jpeg file has not been
placed on the Server
3. Through a subform, display a list of Design Numbers (based on jpeg file
names) that the database found in the Artwork directory that have not been
recorded in the database.
4. Automatically create a record in the database for Design Number
"revision" files that the database finds in the directory based on the
revision's core Design Number's properties (customer, designer) and the Date
Modified of the revision file. (For example, "09-400R1" is a revision of
"09-400") This saves the designers some data entry as the designs can
sometimes go up to "R7" or more.
I thought the best way to handle this was to somehow import a list of the
jpeg files from all the "Artwork\JPEG Files\..." directories into a table in
my database and extract the info from that. This info would need constant
updating so after getting some great guidance from this forum, (I'm very new
to writing VBA code - as you may have guessed from the way I name things in
my database) I came up with the following code which I set to run every time
the form "Designs" opened:
Private Sub Form_Open(Cancel As Integer)
On Error GoTo ProcError
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMyPath As String
Dim strExt As String
Dim strMyFile As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Jpegs")
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM Jpegs WHERE Left$(File, 2) = Right$(Year(Now()),
2)"
DoCmd.SetWarnings True
'type in the real path in the next line
strMyPath = "\\Server\orion\Artwork\JPEG Files\20" & Format(Now(), "yy")
& "\"
strExt = "*.jpg"
strMyFile = Dir(strMyPath & strExt)
Do While strMyFile <> ""
rs.AddNew
rs("File") = Left(strMyFile, InStr(strMyFile, ".") - 1)
rs("Datemod") = Format(FileDateTime(strMyPath & strMyFile),
"dd/mm/yy")
rs.Update
'get the next file name
strMyFile = Dir
Loop
DoCmd.RunCommand acCmdAppMinimize
DoCmd.OpenForm "Designs", acNormal, "", "", , acNormal
DoCmd.GoToRecord acForm, "Designs", acNewRec
Forms!Designs![Customer Filter] = "All"
Forms!Designs![Year Filter] = "20" & Format(Date, "yy")
Forms!Designs![Design List] = Null
Forms!Designs!Image58.Picture = ""
Forms!Designs![View Button].HyperlinkAddress = ""
Forms!Designs![Open Button].HyperlinkAddress = ""
Forms!Designs![Folder Button].HyperlinkAddress = ""
Forms!Designs![Not Loaded] = Null
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO Designs ( [Design Number], Designer, Customer,
[Date] ) SELECT DISTINCT [Designs Not Entered].File, Designs.Designer,
Designs.Customer, Jpegs.Datemod FROM Designs, [Designs Not Entered] INNER
JOIN Jpegs ON [Designs Not Entered].File = Jpegs.File WHERE (((Left([Designs
Not Entered].[File],6))=Left([Design Number],6)));"
DoCmd.SetWarnings True
DoCmd.RunCommand acCmdRefresh
ExitProc:
'Cleanup
If Not rs Is Nothing Then
rs.Close: Set rs = Nothing
End If
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure GetFilenames..."
Resume ExitProc
End Sub
I feel that this may not be the most efficient way to do the things I want
to do in this database and I was wondering if there was some way of having a
field in a query return a "Yay" or "Nay" via some IfFileExists() expression
or something? And maybe then I need only import a list of files that the
database finds with no matching record somehow?
Apologies for the long winded post but I'd really appreciate hearing some
expert's thoughts on all this and I thought the background info might help.
Thanks for taking the time to read.
directory with sub folders named according to the year ("2008", "2009" etc).
As we create the jpeg files, we name the files as follows - a two digit
number representing the year it's created (eg. "09"), then a dash ("-") and
then a 3 digit number representing it's order of creation for that year. For
example, the next file we create will be called "09-401.jpg" We call this
string (09-401) the Design Number.
The files are stored in a folder according to the year they're created (ie
their file name prefix), and we create a new folder under the same directory
every year. This means that most of the file path for all of our designs is
common - except for the final directory which can be assumed from the name of
the file itself. This path never changes and this years files are stored in
the following directory:
"\\Server\orion\Artwork\JPEG Files\2009\"
So the file path of the latest design we did is:
"\\Server\orion\Artwork\JPEG Files\2009\09-400.jpg"
And for example the file path of a design we did last year:
"\\Server\orion\Artwork\JPEG Files\2008\08-296.jpg"
The design names are stored in our database (without the file extension
".jpg") along with various other bits of info about them (customer, designer,
date etc). The designers log these designs in before they start creating the
jpeg files because they use the database to tell them what the next Design
Number is to use for the file name.
Unfortunately I have to deal with some of the database users (designers)
being a bit slack with copying their finished jpeg files from their hard
drives to the directory on our Server.
I want my database to do four things which rely on whether or not the jpeg
files reside within their Server directory. They are:
1. On a couple of forms, display a picture of the jpeg file via an image
control based on the value (Design Number) selected in a combobox.
2. Through a subform (in datasheet view), display a list of Design Numbers
which have been recorded in the database, but who's jpeg file has not been
placed on the Server
3. Through a subform, display a list of Design Numbers (based on jpeg file
names) that the database found in the Artwork directory that have not been
recorded in the database.
4. Automatically create a record in the database for Design Number
"revision" files that the database finds in the directory based on the
revision's core Design Number's properties (customer, designer) and the Date
Modified of the revision file. (For example, "09-400R1" is a revision of
"09-400") This saves the designers some data entry as the designs can
sometimes go up to "R7" or more.
I thought the best way to handle this was to somehow import a list of the
jpeg files from all the "Artwork\JPEG Files\..." directories into a table in
my database and extract the info from that. This info would need constant
updating so after getting some great guidance from this forum, (I'm very new
to writing VBA code - as you may have guessed from the way I name things in
my database) I came up with the following code which I set to run every time
the form "Designs" opened:
Private Sub Form_Open(Cancel As Integer)
On Error GoTo ProcError
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMyPath As String
Dim strExt As String
Dim strMyFile As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Jpegs")
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM Jpegs WHERE Left$(File, 2) = Right$(Year(Now()),
2)"
DoCmd.SetWarnings True
'type in the real path in the next line
strMyPath = "\\Server\orion\Artwork\JPEG Files\20" & Format(Now(), "yy")
& "\"
strExt = "*.jpg"
strMyFile = Dir(strMyPath & strExt)
Do While strMyFile <> ""
rs.AddNew
rs("File") = Left(strMyFile, InStr(strMyFile, ".") - 1)
rs("Datemod") = Format(FileDateTime(strMyPath & strMyFile),
"dd/mm/yy")
rs.Update
'get the next file name
strMyFile = Dir
Loop
DoCmd.RunCommand acCmdAppMinimize
DoCmd.OpenForm "Designs", acNormal, "", "", , acNormal
DoCmd.GoToRecord acForm, "Designs", acNewRec
Forms!Designs![Customer Filter] = "All"
Forms!Designs![Year Filter] = "20" & Format(Date, "yy")
Forms!Designs![Design List] = Null
Forms!Designs!Image58.Picture = ""
Forms!Designs![View Button].HyperlinkAddress = ""
Forms!Designs![Open Button].HyperlinkAddress = ""
Forms!Designs![Folder Button].HyperlinkAddress = ""
Forms!Designs![Not Loaded] = Null
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO Designs ( [Design Number], Designer, Customer,
[Date] ) SELECT DISTINCT [Designs Not Entered].File, Designs.Designer,
Designs.Customer, Jpegs.Datemod FROM Designs, [Designs Not Entered] INNER
JOIN Jpegs ON [Designs Not Entered].File = Jpegs.File WHERE (((Left([Designs
Not Entered].[File],6))=Left([Design Number],6)));"
DoCmd.SetWarnings True
DoCmd.RunCommand acCmdRefresh
ExitProc:
'Cleanup
If Not rs Is Nothing Then
rs.Close: Set rs = Nothing
End If
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure GetFilenames..."
Resume ExitProc
End Sub
I feel that this may not be the most efficient way to do the things I want
to do in this database and I was wondering if there was some way of having a
field in a query return a "Yay" or "Nay" via some IfFileExists() expression
or something? And maybe then I need only import a list of files that the
database finds with no matching record somehow?
Apologies for the long winded post but I'd really appreciate hearing some
expert's thoughts on all this and I thought the background info might help.
Thanks for taking the time to read.