How do I use code to link bitmaps to a table? 1

[Loktar]

Here's a problem for you guys and gals to work on

I have a folder containing about 600mb of bitmap files that correspond with photos of inventory at the company I work for. What I've been unsuccessfully trying to all day is create a table that will link the bitmap files to a record in a table containing a list of the inventory numbers. The files are named the same way as the inv. number (item 1234 is 1234.bmp etc.) This will eventually be used in a report I have that will print the images with a description of each item.

I can do this manually by going to each record in the table and right clicking -> Insert Object, finding the file, and clicking the Link check box, but with over 6000 items this would take forever! And as the inventory is being constantly updated, that method won't work well.

I would like some sort of update query (or something like that) to fill the table, but if anyone knows a shortcut when working with reports to dynamically load images that would work too.

Also, as I tried to specify above, I need the images to be linked, not embedded in the table, or else it would be over 600mb!

Thanks a ton in advance! This one's been giving me a real headache.

 

[Loktar]

Also, I should mention that I have code to search the directory and find a match with an item name and the picture file. I just can't get that picture into the table...

Dim curDB As DAO.Database
Dim rst As DAO.Recordset
[COLOR=green]' not sure if a StdPicture is what I want[/color green]
Dim pic As StdPicture
    
Set curDB = CurrentDb
Set rst = curDB.OpenRecordset("tbl_Images")
    
If rst.EOF And rst.BOF Then
    Exit Sub
End If
    
rst.MoveFirst
While Not rst.EOF
    With Application.FileSearch
        .newsearch
        .FileName = rst.Fields("Item") & ".bmp"
        .MatchTextExactly = False
        .LookIn = "\\server\bmp Images"
        .searchsubfolders = False
        If .Execute > 0 Then [COLOR=green]'if the search is successful[/color green]
            Set pic = LoadPicture(.LookIn & "\" & .FileName)
            rst.Edit
            [COLOR=green]'** this is where I need help
            '** the following line of code doesn't work[/color green]
            [COLOR=red]rst.Fields("Image") = pic[/color red]
            rst.Update
        End If
    End With
    rst.MoveNext
Wend

 

[scriverb]

Are you sure that you want to do it this way? If all you want to do is print the appropriate .bmp when a particular inventory record is printed then the following thread should help you understand how to do this in a report:

thread702-289543

You see since all of the pictures are in the same pathway then you can use the inventory number to build the complete path/. . .filename.bmp that can update a Picture object in a form or report. Read up on this technique in the thread and if you have further questions please post back and I will help you.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Loktar]

Thanks so much!! It worked like a charm and saved me many hours of searching for the .Properties("Picture") variable I couldn't find

 

[Loktar]

scriverb,

Mind if I pick your brain a little more?

Here's the code that I have now, and it works perfectly, except that the FileSearch takes a little time to run, and if someone is pulling up a lot of images to view, it could take over 30sec - 1min to load them all.

If a matching image is found it displays it, and if no match is found it displays a default "no image found" bmp I made.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    On Error GoTo Default_Image
    
    With Application.FileSearch
        .newsearch
        .FileName = Me![Item] & ".bmp"
        .LookIn = "\\server\Images"
        .searchsubfolders = False
        .MatchTextExactly = True
        
        If .Execute > 0 Then 'if the search is successful
            Me![ImageBox].Properties("Picture") = .LookIn & "\" & .FileName
        Else
Default_Image:
            Me![ImageBox].Properties("Picture") = "\\server\Images\NoPictureAvailable.bmp"
        End If
    End With
End Sub

I tried the following code but if an error was found (no matching picture in the Images folder) then it would use the previous picture for all the records until a correct match was found for an item.

    On Error Resume Next

    Me![ImageBox].Properties("Picture") = "\\server\Images\" & Me![Item] & ".bmp"

So I tried this code, but it would just display all images as the default "no image found" bmp I made. This could be because the first item in the list doesn't have a match, but that is something I need to take into consideration anyway.

    On Error GoTo Image_Error

    Me![ImageBox].Properties("Picture") = "\\server\Images\" & Me![Item] & ".bmp"

Image_Error:
    Me![ImageBox].Properties("Picture") = "\\server\Images\NoPictureAvailable.bmp"
    [COLOR=green]' I also tried Resume Next here, didn't work either[/color green]
    Exit Sub

I'm still fairly new to VB code. Is there some trick I'm missing to get this to work using this "simpler" method? Thanks again for all your help.

 

[scriverb]

With just a slight modification I think this should work.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)   
On Error GoTo Image_Error

    Me![ImageBox].Properties("Picture") = "\\server\Images\" & Me![Item] & ".bmp"

Exit_Sub:
    Exit Sub

Image_Error:
    Me![ImageBox].Properties("Picture") = "\\server\Images\NoPictureAvailable.bmp"
    Goto Exit_Sub
End Sub

You see your code was falling through the initial bmp assignment and if successful you were also then executing the Image_error bmp assignment. You must exit after the successful first one and then only do the second one if there is an error.

Good luck

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Loktar]

Perfect! Thanks for all your help. I really appreciate it.