Results 1 to 12 of 12
  1. #1
    Jonaripp is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2019
    Posts
    12

    Code for presence or size of an image box on a report, conditional to null value of a field

    I have a table called "AncestryDetails" which has several fields for photos, imbedded JPG files which generally are pulling into my report the way I want them to. Two of them are essentially thumbnails and they look fine with fixed image boxes holding the space if there is no picture.



    The third photo is a field called "Photo" which prints as the last field in each record in an image box called "image23". Only a few selected records will have a photo in "image23", these images are typically bigger, so I would like "image23" to not be visible (and not take up the space on the page) when there is no embedded image in the "photo" field.

    So my plan was to write some code within the report in the "details" container in the "On Format" line to say:

    If the value of the field called "Photo" is null or "" or blank, then
    Image23.height = .1
    image23.visible = false
    Image23.border=transparent
    Else
    Image23,height = 2.5
    image23.visible = true
    Image23.border=solid
    End if

    I am typing this out in layman's terms because I can't get the syntax right. I am self-taught so either a bad student or a bad teacher at this point, not sure which. Probably both. I dearly wish I had taken some proper coursework when younger to be able to speak VBA better. I am guessing there is a simple painfully obvious answer to someone other than I, who knows what they're doing, and which can be offered up in 3 minutes.

    Thanks in advance, Jonathan

    P.S. Even better would be to set the height of the image box to be equal to the height of the JPG image, but if that is too elaborate, I will deal with a fixed height and work around the image sizes, select different pictures, or whatever, to make it work visually.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So you are embedding images in attachment field? Most experienced developers advise against this.

    Setting Height property with VBA must be done with TWIPS units, not inches. 1 inch = 1440 TWIPS.

    Set Detail section CanGrow and CanShrink properties to Yes. It should adjust when Image control Height is changed.

    Scaling Image control Height and Width to image dimensions would not be simple.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Jonaripp is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2019
    Posts
    12
    Quote Originally Posted by June7 View Post
    So you are embedding images in attachment field? Most experienced developers advise against this.

    Setting Height property with VBA must be done with TWIPS units, not inches. 1 inch = 1440 TWIPS.

    Set Detail section CanGrow and CanShrink properties to Yes. It should adjust when Image control Height is changed.

    Scaling Image control Height and Width to image dimensions would not be simple.


    Thank you for your response. Good to know about the TWIPS.

    What is the alternative to embedding an image? I figured out the embedding process by searching through forums. Is there a better way ? I am a novice, and my attempt at 'developing' something here is probably a one-time use. I am OK with embedding if the reason to advise against it is a matter of efficiency. My database doesn't need to be overly efficient, but I do need it to be reliable, so if the reason for advising against is a matter of unexpected results, that would be a problem for me.

    Also, part of my problem is simply using the wrong syntax to define the field in my condition: if the field is null, then . . .

    Thanks.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Embedding uses up Access file size limit and can slow queries in large datasets.
    Alternative to embedding is to leave files in external folder and save path in text field. Review https://www.accessforums.net/showthread.php?t=73766 especially post #17.

    One way to check if attachment field has object:

    If Me.Photo.FileName = "" Then







    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    P.S. Even better would be to set the height of the image box to be equal to the height of the JPG image, but if that is too elaborate, I will deal with a fixed height and work around the image sizes, select different pictures, or whatever, to make it work visually.
    here's a helpful procedure to redimension image files to max height and width.

    Code:
    
    
    ''****************************************************************************
    '***Author: Emilio Sancha MSAccess VIP   http://www.mvp-access.es/emilio/Index.htm
    '' * ESH 08/16/09
    ''** google translated from spanish to english 9/2019
    ''* sRedimension
    ''* re-scale the last image as a parameter
    
    
    '>>>You must include a reference to Microsoft Windows Image Acquisition Library vX.X <<<<<<<<<<<
    
    
    ''* Arguments: strImage => path of the file to resize
    ''* lngHeight=> height in pixels to apply
    '' * lngWidth => width in pixels to apply
    '' * use: sRedimension "C:\Temp\test.PNG"
    '' * If you use this code, respect authorship and credits
    ''****************************************************************************
    
    
    Public Function sRedimension(strImage As String, lngHeight As Long, lngWidth As Long) As String
    
    
        Dim wImage As WIA.ImageFile
        Dim IP As WIA.ImageProcess
        Dim strScale As String
    
    
        On Error GoTo sRedimension_Error
    
    
        Set wImage = CreateObject("WIA.ImageFile")
        Set IP = CreateObject("WIA.ImageProcess")
    
    
        wImage.LoadFile (strImage)
    
    
        IP.Filters.Add (IP.FilterInfos("Scale").FilterID)
        IP.Filters(1).Properties("MaximumWidth").Value = lngWidth
        IP.Filters(1).Properties("MaximumHeight").Value = lngHeight
    
    
        Set wImage = IP.Apply(wImage)
    
    
        strScale = Replace$(strImage, ".", ".redim.")
    
    
        'if the file already exists I delete it
        If Not Dir$(strScale) = vbNullString Then Kill strScale
    
    
        wImage.SaveFile (strScale)
        
        sRedimension = strScale
    
    
    Resize_Exit:
        If Not wImage Is Nothing Then Set wImage = Nothing
        If Not IP Is Nothing Then Set IP = Nothing
        On Error GoTo 0
        Exit Function
    
    
    sRedimension_Error:
        MsgBox "Error " & Err & " in proc.: sRedimension :  (" & Err.Description & ")", vbCritical + vbOKOnly, "Attention"
        Resume Resize_Exit
    
    
    End Function

  6. #6
    Jonaripp is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2019
    Posts
    12
    Thank you again for your help, however, I am still stalled on my problem. I have pored through all the responses and related links. Probably something I haven't explained properly. I used to do some programming with Paradox years ago, but I just don't understand this well enough.

    My data has no photos in the first five records, so this code (below) helped to generate report which had no extra space for non-existent photos for five records, then, in record six the image box expands to print the first photo. Fine. But record seven has a null value in the PHOTO field, and the image box for record seven remains a 2880 TWIPS in height. In fact, all the rest of the records in the (now) 400 page report have the enlarged image box. It seems like I should I wish I had an "ON CURRENT" basis for an event for the code rather than the "ON FORMAT".

    I really need a code which will format each record.

    I tried the Me.Photo.FileName property and came back with an error. "Name" was an available property which tried also, to no avail. The IsNull function is being read, but, as mentioned, the result is still not what I need.

    My code as it is now:

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

    If IsNull(Me.[PHOTO]) = True Then
    Image23.Height = 0
    Image23.BorderStyle = Transparent

    Else
    Image23.Height = 2880
    Image23.BorderStyle = Solid

    End If

    End Sub

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Embedded images are in Attachment type field and using Image control to display?

    Simple reference to Photo.Filename worked in my initial test, however, not remembering exactly what I did.

    So this worked today:

    Include Photo.Filename in report RecordSource: SELECT tablename.*, tablename.Photo.Filename FROM tablename;

    Bind a textbox to tablename.Photo.Filename.

    Code references textbox.

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Jonaripp is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2019
    Posts
    12
    Quote Originally Posted by June7 View Post
    Embedded images are in Attachment type field and using Image control to display?

    Simple reference to Photo.Filename worked in my initial test, however, not remembering exactly what I did.

    So this worked today:

    Include Photo.Filename in report RecordSource: SELECT tablename.*, tablename.Photo.Filename FROM tablename;

    Bind a textbox to tablename.Photo.Filename.

    Code references textbox.


    Thank you again.

    In answer to your post, no, I was not using an "Attachment type control object", I was using an "Image type control object". After your latest post, I tried using the Attachment type control object, but I couldn’t get that to pull an image into my report, whatsoever, perhaps not understanding your meaning of "using Image control to display". Control Source is =[PHOTO], Display As Image. With this configuration, I didn't see any images in my report. The Attachment object displayed as blank for each record in the report.

    Having said that, I am getting partial success using the "Image type control object". Let me try to clarify because it seems like I am close to a solution in this direction.

    I am able to pull my images from the PHOTO field in each record into my report. The PHOTO data type is Short Text and consists of the path to a JPG file (e.g. C:\USERS\ . . . \Frank circa 1955.jpg). I don't know if this is called "embedding" or the alternative where photo files a held in a separate folder. My photos are contained in a separated folder.

    The images for each record are being pulled into the report, however, if there is no image, then there is a big empty space. I need to eliminate that empty space on each record. I have 3,000 + records and only a couple hundred of those records have images. If I had the options of "Can Shrink" or "Can Expand" that would work, but those specifications don't seem to exist in either the Image objects, or the Attachment objects. Therefore, I turned to a coding option.

    With this code (below) entered in the "Details - On Format" property. I am getting another partial success. I wish I could put that code in a "Details - On Current" property.

    ---------------------------------------------------------------------------------------------
    Private Sub Detail Format(Cancel As Integer, FormatCount As Integer)

    If IsNull(Me.[PHOTO]) = True Then
    Image35.Visible = False
    Image35.Height = 0
    Image35.BorderStyle = Transparent

    Else
    Image35.Visible = True
    Image35.Height = 2880
    Image35.BorderStyle = Solid

    End If

    End Sub
    -----------------------------------------------------------------------------------------------

    This code worked for the first five records which had no data in the PHOTO field. No Image control was displayed. There was no empty space for the Image35 control. That is the desired result.

    The sixth record had data in the PHOTO field, a path to the JPG image, and the report displayed the proper image, which was 2 inches high (2880 TWIPS). That is the desired result.

    From the seventh record on, if there were data in the PHOTO field, the photos displayed properly in the report, but for all records thereafter without data in the PHOTO field, there was a 2 inch blank at the end of every record. That is not the desired result for records without PHOTO data.

    The Detail specifics are set with "Auto height = yes", "Can shrink = yes", "Can expand = yes".

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I did not refer to 'Attachment type control object', I referred to Attachment type field and Image control.

    So you are not using Attachment field, just a text field with image file path. This is not 'embedding'. Embedding is saving file into Attachment or OLEObject field in table.

    Instruction was to set 'CanShrink' and 'CanGrow' properties for Detail section. However, they are not working with this approach.

    Following code works:
    Code:
    With Me
    
    If IsNull(!PHOTO) Then
    .Image35.Visible = False .Image35.Height = 0 .Detail.Height = 0 Else .Image35.Visible = True .Image35.Height = 2880 End If End With
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Jonaripp is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2019
    Posts
    12
    Quote Originally Posted by June7 View Post
    I did not refer to 'Attachment type control object', I referred to Attachment type field and Image control.

    So you are not using Attachment field, just a text field with image file path. This is not 'embedding'. Embedding is saving file into Attachment or OLEObject field in table.

    Instruction was to set 'CanShrink' and 'CanGrow' properties for Detail section. However, they are not working with this approach.

    Following code works:
    Code:
    With Me
    
    If IsNull(!PHOTO) Then
    .Image35.Visible = False .Image35.Height = 0 .Detail.Height = 0 Else .Image35.Visible = True .Image35.Height = 2880 End If End With



    Thank you. I appreciate your patience. I am struggling with my own impatience. Good grief! This stuff makes me feel so stupid. So I guess it's good news that I am not embedding images (despite the fact the Picture Type property is called "Embedded").



    The code you gave me resulted in the following error message:


    Compile error:

    With object must be user-defined type, Object, or Variant


    Is there some sort of declaration I need to make?

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No.

    With is intrinsic object in one of the 4 standard libraries automatically set (not sure which one but probably Visual Basic for Applications). However, can remove With Me and End With and use Me qualifier in front of each field and control reference. You can exclude as in your original code but best to use. It along with dot will provoke intellisense popup tips.

    Note use of bang (!) in front of PHOTO as this is referencing field, not control.

    I think Picture Type property is irrelevant when using ControlSource.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Jonaripp is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2019
    Posts
    12
    Quote Originally Posted by June7 View Post
    No.

    With is intrinsic object in one of the 4 standard libraries automatically set (not sure which one but probably Visual Basic for Applications). However, can remove With Me and End With and use Me qualifier in front of each field and control reference. You can exclude as in your original code but best to use. It along with dot will provoke intellisense popup tips.

    Note use of bang (!) in front of PHOTO as this is referencing field, not control.

    I think Picture Type property is irrelevant when using ControlSource.

    If you want to provide db for analysis, follow instructions at bottom of my post.



    Thank you so much for your patience. The last adjustment to the code did indeed work once I did my part accurately. I am sorry for the shortfall in my facilities.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 03-22-2018, 12:02 PM
  2. Replies: 1
    Last Post: 01-05-2017, 05:14 PM
  3. Replies: 7
    Last Post: 11-07-2016, 09:24 AM
  4. image size
    By pattrickcolin in forum Forms
    Replies: 2
    Last Post: 09-12-2015, 03:37 AM
  5. need assistance on image size in access
    By paulkimball in forum Reports
    Replies: 0
    Last Post: 05-16-2012, 10:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums