Diana,
Thanks for your post - I know very little about modules and code but I knew
this was possible. I have reviewed your post and understand the logic, the
file names are the same as the ProdID and I am not storing the image name.
I pasted your code into a new module in access made a few tweaks and ran
it - WORKED PERFECT FIRST TIME - VERY COOL.
I realize that it is possible for images to be moved, renamed or delted... I
am going to to start by setting all ImageAvailable to false before doing the
update to stay accurate. I have setup an autoexec macro this macro calls an
update query to reset all to false then calls your code to check and update.
I would guess that there is an easy addition to your code that woudl look
after the reset first... I will see what I can do on my own. If you have an
opportunity to post how you would handle it I'll have a look later on.
Many thanks for your time and consideration providing the direction I
needed!
M
"Diana Criscione" <Diana
(E-Mail Removed)> wrote in
message news:02694B13-C05C-4283-B8BB-(E-Mail Removed)...
> Maureen,
>
> I'm not exactly sure I understand what you want to do but as I understand
it, you just want to update the ImageAvailable field to TRUE if there is an
image in the images folder that matches the product ID. Is that correct?
Are you storing the image name as well? If it is the same as the prodID
(plus the .jpg) there really isn't a need to store is it there? You just
need to know if it's available.
>
> Either way, what you need is a loop that will iterate through the files in
the images folder and match it against the product ID's.
>
> First what I would do is create a recordset of the filenames in the images
folder.
> When you add the filenames, I would "cut off" the extension of the
filename so you just have to worry about the actual name.
>
> Second, using your inventory table, loop through each of the records one
by one and look for a matching filename in the first recordset. When a
match is found, you update Available = True. If you are also needing the
store the image name, do it at the same time.
>
> Try this....
>
> ''
> '' GET FILE NAMES FROM GRAPHICS FOLDER
> ''
> Dim sFileDir As String
> Dim rsFileInfo As ADODB.Recordset
> Set rsFileInfo = New ADODB.Recordset
>
> ' create the filename field -- this is a string data type, length 255
> rsFileInfo.Fields.Append "FileName", adBSTR, 255
>
> ' open the recordset
> rsFileInfo.Open
>
> ' get the files in the correct directory
> sFileDir = Dir("C:\graphics01\images\*.jpg") ' change the path as
necessary
>
> Do While sFileDir <> ""
> If sFileDir <> "." And sFileDir <> ".." Then
> rsFileInfo.AddNew
> rsFileInfo!FileName = sFileDir
> ' alternatively, use the next line instead to get the file
name
> ' minus the last four characters ".jpg"
> ' rsFileInfo!filename = Left(sFileDir, Len(sFileDir) - 4)
> rsFileInfo.Update
> Debug.Print rsFileInfo!FileName
> ' get the next file in the directory
> sFileDir = Dir
> End If
> Loop
> rsFileInfo.MoveFirst
>
> ''
> '' COMPARE FILES TO RECORDS IN INVENTORY TABLE
> ''
> Dim rsInv As ADODB.Recordset
> Set rsInv = New ADODB.Recordset
> rsInv.ActiveConnection = CurrentProject.Connection
> ' open recordset of inventory records that do not already show the
image is available
> rsInv.Open "SELECT * FROM Inventory WHERE
(((Inventory.ImageAvailable)=False));", , adOpenKeyset, adLockOptimistic
>
> ' loop thru recordset to find matches in rsFileInfo
> Do Until rsInv.EOF
> Do Until rsFileInfo.EOF
> ' this next test assumes you have taken out the ".jpg" from
the filename
> ' if not, then create a variable to hold the string combo of
ProdID + .jpg
> If rsInv!ProdID = rsFileInfo!FileName Then
> rsInv!ImageAvailable = True
> rsInv!ImageName = rsFileInfo!FileName
> End If
> rsFileInfo.MoveNext
> Loop
> rsFileInfo.MoveFirst
> rsInv.MoveNext
> Loop
> rsFileInfo.Close
> rsInv.Close
> Set rsFileInfo = Nothing
> Set rsInv = Nothing
>
> "Maureen" wrote:
>
> > I would like to make image files available from access in order to
create a
> > "merged" cataloge. I have my report and query completed and it works
great.
> > Now all I need to do is update my table with the path and file name to
the
> > images. I am working with a single table [Inventory] with:
> >
> > [Inventory].[ProdID] as the key, [Inventory].[PathtoImage],
> > [Inventory].[ImageName] and [Inventory].[ImageAvailable] (there are
other
> > fields that do not affect what I need here. My PathtoImage =
> > \\graphics01\images the ImageName = [Inventory].[ProdID].jpg
> > [ImageAvailble] is a check box that defaults to false.
> >
> > We have a few hundred images available and we are adding more each day,
we
> > have over 20,000 products. I am hoping that there is a way to create an
> > update query with some code that will scour \\graphics01\images and when
it
> > finds an image for a record have it set ImageAvailble to True
> >
> > I know very very little about using VB in queries but I am quite sure
that
> > this can be done. Any suggestions would be appreciated!
> >
> > M
> >
> >
> >
> >
Similar Threads
