Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: MLS Data/Pictures into Report

    I download real estate property data from a server.

    The data is dumped into one folder and the pictures for the corresponding data are dumped into another folder.

    I then import the data to Access 2003.

    I've designed a report which when viewed or printed must link the correct pictures with the correct data.

    I suspect that the MLS number would be used as the linking KEY in the database as there can be no duplicate MLS numbers in the database.

    So - how do I link the pictures into the correct data report page?

    Thanks.

    Rick

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Create a field in the data table to store the location and file name of the MLS picture. Then write some code in vba so when the report opens, it populates an unbound picture frame with the picture location/filename stored in the data field.

    Or

    You can create an OLE Object field and store the picture in the table (usually not preferred if you have a large recordset.)

    There's a couple of other ways but they require a bit of vba coding I won't get into here.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You may want to double check on that. It's not unusual for RETS implementations to use a totally different key for Media items. The only way to know for sure is to ask the MLS about their implementation.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Getting coser to linking

    Thanks Teddy and pkstormy.

    RETS Photo{id} in the download can be easily changed to Photo{ListingID} which renames the photo as follows . . .

    MLS number is Y494872

    The photo names are . . .

    PhotoY494872-1.jpeg
    PhotoY494872-2.jpeg
    PhotoY494872-3.jpeg
    PhotoY494872-4.jpeg
    PhotoY494872-5.jpeg
    PhotoY494872-6.jpeg up to as high as -24.jpeg.

    I have nine unbound frames on my Access 2003 Report.

    One of the fields on the report page is [ListingID]

    So, I need to link the photos to the listingID on each page, as each page is viewed or printed.

    I think I'm close now.

    Any hints?

    Thanks much.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Just as a note, you can also write some vba code to open the website directly.

    ex:
    dim strUrl as variant
    strUrl = "http://uwmadison.qualtrics.com/SE?SID=SV_6VGchtikvd5aIg4&SVID=Prod"
    Application.FollowHyperlink strUrl

    so instead of going the report route and embedding the picture, you could code it (on a form) so it goes to the website itself.

    9 unbound picture frames on a report would most likely make that report open up extremely slow, ESPECIALLY if you're returning multiple MLS records with multiple pictures to the report versus a single MLS record.

    Otherwise what I typically do when it comes to the need to view many pictures for a specific record is put in code (on a form) that opens up the folder (where the pictures are stored) directly versus populating the .Picture of the image files in the report.

    Shell "C:\WINDOWS\explorer.exe """ & Me!FolderName & "", vbNormalFocus

    where I set me!FolderName to...
    me!FolderName = "F:\Data\Databases\Gensub\ScanData\ScanImages\2000 1" (note: no space between 0 and 1 - dbforums added the space.)

    so if a user wants to view all pictures for SubjectID 20002 (a combobox field I have on the form which in your case might be the MLS #), I'll then set me!FolderName (another field on the form) to

    me!FolderName = "F:\Data\Databases\Gensub\ScanData\ScanImages\ " & me!SubjectID & " and then use the shell command above once the user selects a subjectID (or in your case, MLS #) to open the appropriate folder. They then see all the images (ie. jpg files) in that folder (via Explorer) and can then click on a specific picture to view it which will open it in the default picture viewing program.

    You could probably do something similar for a report where you create image objects on the report and use something like the following to change the image:
    Me.Image1.Picture = me!FolderName & "\" & me!JpgImageFileName1
    Me.Image2.Picture = me!FolderName & "\" & me!JpgImageFileName2

    where you might set me!JpgImageFileName1 to the 1st jpg filename, me!JpgImageFileName2 to the 2nd jpg filename, and so on... in the OnOpen event (or OnFormat event of the detail section) of the report.

    (but this typically doesn't work with *.jpg files and only works for *.bmp files. I usually have problems populating an image type control with a jpg versus a bmp.)

    So for example, try (just to test):
    Private Sub Report_Open(Cancel As Integer)
    Me.Image1.Picture = "C:\MyPictureFolder\Pics\PhotoY494872-1.bmp"
    End Sub

    Then you'd need to write some code to set me.Image1.Picture = "the correct folder\picture" in which case it might be easier to save the folder\filename in a field in the table and then set the me.Image1.Picture to the value of the field that stores the folder\picture (ie. You might have a field in the table called: Pic1 which would have a value of: "C:\MyPicFiles\23432\23432.bmp" for the 23432 MLS record. Another field called Pic2 to hold the folder\filename of the 2nd picture for MLS 23432, etc..etc...)

    Again, I'd only return a single MLS record to the report versus all records or the report will most likely take forever to open.

    I hope this helps.
    Last edited by pkstormy; 04-09-10 at 23:13.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    One Record per report page

    Thanks to both of you for your suggestions.

    It appears that I'm not very clear in my descriptions.

    Each report page is limited to ONE MLS record. See Report Page.pdf attached.

    On this page is the MLS number that needs to link to the the MLS Photo numbers as I've shown.

    MLS number is Y494872

    The photo names are . . .

    PhotoY494872-1.jpeg
    PhotoY494872-2.jpeg
    PhotoY494872-3.jpeg
    PhotoY494872-4.jpeg
    PhotoY494872-5.jpeg
    PhotoY494872-6.jpeg

    BTY, Nothing in this has to do with the Internet.

    All of the pictures are downloaded to the same folder on my hard drive at the same time as the one .text file that I import through Access for the data.

    Those fields and the unBound frames are on the Report Page.pdf example.

    So . . . when I view or print a report page, that page needs to auto-populate the pictures from the folder that match the MLS number on the page and then print the page.

    Microsoft Publisher handles pictures far better than Access. Can I use the same VBA or other code to accomplish the same thing?

    I'm going to study in detail what you've sent me.

    Thanks again for your assistance.

    Rick
    Attached Files Attached Files

  7. #7
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Pictures in one folder

    BTW - ALL the pictures (at times easily over 800) are all in one folder!


  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I think there's a misunderstanding.

    After re-reading all the posts, I'm wondering if you're thinking that you want some field in your MSAccess table (ie. ListingID or MLS#) to be able to automatically link to a 'listingID' or 'MLS#' within the jpg picture itself (located in a folder on your harddrive). If so, that's not going to work regardless of the vba code in MSAccess or any other program. You cannot link to a "field" within an actual jpg picture file or 'parse' out a value from the jpg picture regardless of the coding used. JPG pictures don't have "fields". If your data download has a field called 'listingID' or 'MLS#', it's NOT something were you can simply say 'associate this 'listingID' (or MLS#) value to a value within the jpg file which I store in a specific location on my harddrive. Some place in your table (or in code) you MUST tell it where that location AND filename is!

    Hence, you have the following options:
    1. Import the jpg pictures themselves into an OLE Object field in the MSAccess table (which is typically not recommended but an option.)

    2. Create fields in the MSAccess table to tell it where to grab the correct jpgs (ie. you store the foldername/filename of the pictures in fields in the table.) This is most likely the same 'kind' of method the website itself uses to return the correct pictures to the web page. It's typically the method used.

    3. Write code which utilizes a common value (ie. MLS #) to identify that record with part of the jpg filename itself (ie. MLS # = 494872 and you want to grab all pictures which have 494872 as part of the jpg file name in a specific location.) Hence, in this post: http://www.dbforums.com/6455941-post5.html is where I tried to explain some options on how to do this (but in your case, you'd need a bit more coding which can get a little complicated.)

    The internet coding I supplied was another option where instead of opening a report, you could go to the actual correct MLS (or ListingID) URL which has the pictures and other info (using something similar to the method in 3 above (or a field in the download which has the url link) to create the correct URL). The reason I supplied this code was as another option if the ultimate goal was just to see the correct pictures versus trying to embed them into a report which is not going to work with what you're currently trying to do. (if you used this method, you wouldn't necessarily even need to create a report.)

    Now in certain types of picture files there can be 'tags' within that picture file to identify that picture to then be used to link it to a specific record but again, this concept is STILL the same as #2 above and you would not be able to use any kind of 'tag' within your jpg downloads (even if you could identify a 'tag' (such as listingID or MLS#) within a picture, you'd STILL end up storing the folder/filename as described in #2.)
    Last edited by pkstormy; 04-10-10 at 18:27.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Access 2003 Fails In This Regard

    You know - I just don't understand why Access cannot recognize the MLS# on a report page and reach out and grab the associated pictures.

    The Multiple Listing Service does it - thousands of times a day with over 3,000,000 listings on the server.

    I give up - for now.

    Thanks for all of your help.

    Rick

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Rick,

    I sense a bit of frustration. You're blaming MSAccess for something NO program can do without coding! Again, the MLS website most likely uses a method similar to #2 in my previous post. I can guarantee you that it doesn't automatically know where and what picture to grab without some kind of coding to tell it where those jpg pictures are located and which ones to grab!

    The best we can do is give you guidance on how to write code to tell MSAccess where and what pictures to grab. MSAccess is a powerful tool but like every other tool in the world (including whatever they used to designed the MLS), when it comes to jpg pictures, you can't expect something that's simply not possible.

    Before posting something titled such as "Access fails in this regards", you may want to actually talk to someone who has designed the coding for the MLS website if you don't believe me. I'm guessing if you're a realtor and I posted something on a website titled "Realtors fail in this regards" without proof, you might take offense.
    Last edited by pkstormy; 04-11-10 at 22:23.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Filter - Query - LookUp table

    Thanks pkstormy.

    I'm thinking. . . .

    How about when I download if place the pictures in a different folder.

    Right now it's . . .

    C:\PresentationData

    Both the downloaded text file and the pictures (many hundreds of them) are in this folder.

    How about . . .

    C:\PresentationData\Pics and just place the pictures here?

    PhotoY494872-1.jpeg
    PhotoY494872-2.jpeg
    PhotoY494872-3.jpeg
    PhotoY494872-4.jpeg
    PhotoY494872-5.jpeg
    PhotoY494872-6.jpeg etc.

    Could I use a lookup table to get the pictures based on a filter like PhotoY494872* ?

    Could I use a query?

    How about a filter on the report based on the MLS number to populate only the matching
    MLS number in the Pics folder with the pictures?

    Rick

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Rick,

    You would still need to tell MSAccess where and what jpg pictures to grab. Whether you do it programmatically or store the folder/filename of the pictures (or pictures themselves) in the table.

    Your options are still:

    1. Import the jpg pictures themselves into an OLE Object field in the MSAccess table (which is typically not recommended but an option.)

    2. Create fields in the MSAccess table to tell it where to grab the correct jpgs (ie. you store the foldername/filename of the pictures in fields in the table.) This is most likely the same 'kind' of method the website itself uses to return the correct pictures to the web page. It's typically the method used.

    3. Write code which utilizes a common value (ie. MLS #) to identify that record with part of the jpg filename itself (ie. MLS # = 494872 and you want to grab all pictures which have 494872 as part of the jpg file name in a specific location.)

    Your last question is similar to #3 above and requires a bit of coding which probably would not work well with a report. Your best bet is #2 above to work smoothly with a report.
    Last edited by pkstormy; 04-11-10 at 23:09.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Some of the big vendors do this by storing the photo count and just assuming that there will be X number of photos. When photos are requested, it will look for MLS#-1, MLS#-2, MLS#-n, where "n" is the stated photo count according to the rets listing.

    It is also not uncommon to break up the images into folders according to the a piece of the MLS #. For example, one major MLS vendor will create folders 00..99 and store photos according to the last two digits of the MLS # since, as you mention, it's not uncommon for an MLS to have millions of listings when you include non-active statuses and it would be devastating to performance to store all those images in the same folder.

    Note that with this method, it is on you to make sure you have the correct photo counts as well as the correct physical photos throughout the life of a listing. Depending on the RETS implementation, you may have no way to know which photo was changed or added based on the available Media information. You'll want to think that all the way through before deciding how to tackle this.
    Last edited by Teddy; 04-11-10 at 23:28. Reason: trade secrets-- ;o)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by Teddy View Post
    Some of the big vendors do this by storing the photo count and just assuming that there will be X number of photos. When photos are requested, it will look for MLS#-1, MLS#-2, MLS#-n, where "n" is the stated photo count according to the rets listing.

    It is also not uncommon to break up the images into folders according to the a piece of the MLS #. For example, one major MLS vendor will create folders 00..99 and store photos according to the last two digits of the MLS # since, as you mention, it's not uncommon for an MLS to have millions of listings when you include non-active statuses and it would be devastating to performance to store all those images in the same folder.

    Note that with this method, it is on you to make sure you have the correct photo counts as well as the correct physical photos throughout the life of a listing. Depending on the RETS implementation, you may have no way to know which photo was changed or added based on the available Media information. You'll want to think that all the way through before deciding how to tackle this.
    Same method as:

    3. Write code which utilizes a common value (ie. MLS #) to identify that record with part of the jpg filename itself (ie. MLS # = 494872 and you want to grab all pictures which have 494872 as part of the jpg file name in a specific location.)

    or the example I previously posted in this post: http://www.dbforums.com/6455941-post5.html which grabs all photos from a specific folder where the folder name itself has part of the MLS# (or subjectID as in my example).

    Teddy - maybe you can help me here...the point Rick and I have been going round and round on is that there's nothing within the actual jpg picture itself (ie. a 'field') that identifies it with a MLS #. No program exists that can look within a jpg picture to grab information. The foldername/filename where that photo resides must be coded in MSAccess somehow. But I don't think I've convinced Rick or I'm just not relaying it correctly.
    Last edited by pkstormy; 04-12-10 at 21:00.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by Teddy View Post
    Note that with this method, it is on you to make sure you have the correct photo counts as well as the correct physical photos throughout the life of a listing. Depending on the RETS implementation, you may have no way to know which photo was changed or added based on the available Media information. You'll want to think that all the way through before deciding how to tackle this.
    Rick,

    This is why I supplied you with the internet URL website coding.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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