Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form Queries (A2000)

    I'm trying to achieve something I'm not quite sure can be done.
    I am working on part of my db which is a form with three fields.

    [Ref1]-number
    [Ref2]-number
    [OlePic]-OleObject.

    As you know, pictures bloat a db out of proportion, so here's what I've done to work around this.

    Created a form which shows an image relative to the two number fields.
    Using a common dialog, I can point to a folder any where on the network & insert it into a linked table.

    The forms control source is a query designed around the linked table.

    I'm thinking, if I can use a query to pull the images from the server on opening the form, into a local table, and then push them back to the server when the form closes, this will increase the speed of opening the pics.

    By this the db doesn't bloat.

    Can this be done on open & close of the form.

    Dave

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form Queries (A2000)

    If you load an image when it is needed, the user will experience some delay (depending on network speed, image size etc.). If you load *all* images into a temporary table when opening the form, the loading time of the form might become unacceptable.

    Say, you have 200 records. Loading each image takes 3 seconds. So, if you load each image as needed, the user has to wait 3 seconds each time. Slightly irritating, but not dramatic. But if you load all images initially, it takes 600 seconds = 10 minutes. If the user closes the form by mistake and reopens it, he/she will to wait for 10 minutes again.

    Are you sure you want to follow this approach?

  3. #3
    Star Lounger
    Join Date
    Feb 2002
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Queries (A2000)

    Hans
    I see what you mean.
    Currently the db has a link to the table which is on the server.
    My local db has a query based on this.
    It seems to work ok.
    It will only load the images which are specified within the quey.

    In the criteria, Forms!frmDetails!EstimateNo

    Am I taking the correct approach by doing it this way.

    Dave

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form Queries (A2000)

    Do you mean that the images are stored in a table on the server?

    Storing images in an Access table is not a good idea. If you do a search on this Forum, you'll find that the common advice is to store the images on a local or server disk, not in the database. Just store the path/filename in a table (or numeric codes you can use to reconstruct the path/filename).

    On forms and reports, don't use the OLE controls, but an unbound image control. You can set the Picture property of this control in the OnCurrent event of a form and in the OnFormat event of the detail section of a report.

    Here is a bit of code (simplified) from one of my databases.

    All images are stored on a network folder P:ProjectImages.
    The file names are of the form Imgnnnn.jpg where nnnn is a unique number.
    The image number is stored in a numeric field ImageID.
    The image is to be displayed in an image control imgPic.
    The form contains a label lblWarning that informs the user of possible problems.

    Private Sub Form_Current()
    Dim strPath As String
    On Error GoTo Err_Current
    lblWarning.Caption = ""
    If IsNull([ImageID]) Then Err.Raise vbObjectError + 1
    strPath = "P:ProjectImagesImg" & Format([ImageID], "0000") & ".jpg"
    [imgPic].Picture = strPath
    Exit Sub
    Err_Current:
    Select Case Err
    Case 2220
    lblWarning.Caption = "Can

  5. #5
    Star Lounger
    Join Date
    Feb 2002
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Queries (A2000)

    Yes the images are on the server.
    I've tried the Microsoft version before, I feel its not very automated.
    What I didn't want was users having to put a file pathname etc, (unless this can be automated)
    You can see from what I have, the user opens the frmImage from frmDetails. A common dialog opens, pointed to a card reader on the local drive. This image is then saved as OLE allong with [EstimateNo]&[Supp], both numeric fields.

    I would really appreciate some kind of demo so I can disect it for the best possible remedy.

    Thanks for your input Hans

    Dave

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form Queries (A2000)

    I'll try to post a demo, but it'll probably be tomorrow.

    Perhaps, other Loungers reading this thread have an example available immediately, or better advice.

    Regards,
    Hans

  7. #7
    Star Lounger
    Join Date
    Feb 2002
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Queries (A2000)

    That would be great Hans
    Thanks
    Dave

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Form Queries (A2000)

    It won't help on the process of selecting and storing the image, but I posted a demo here that shows how the images can be displayed in a form. What is stored is a path. In fact, in this demo, only the file name is stored and the path is assumed to be local to the database since I wanted it to run properly as a demo. If you store your images on a server, you may have to follow a similar plan for the drive letter or server address if not all users have the same drive mappings for your server drive.
    Charlotte

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form Queries (A2000)

    Dave,

    I have attached a zipped Access 97 database that demonstrates basically the same as Charlotte's example. But here, the user can select an image using the standard Open dialog box.

    It also contains a report that demonstrates how to use images in a report.

    Regards,
    Hans
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •