Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2008
    Posts
    22

    Cool Unanswered: SourceDoc does not work for reports - workaround?

    I have an external excel chart stored for every record.

    In a form this works great, I just use the following code:
    Code:
    '        excelpath = "\\ccri-srv01\M2600\Labs\Keller\Images\" & [Mouse UID] & "\TumorSize.xls"
    '
    '        If Dir(excelpath) = "" Then
    '            Me.TumorChart.SourceDoc = "\\ccri-srv01\M2600\Labs\Keller\Necropsy Manager\Blank.xls"
    '            Me.TumorChart.Action = acOLECreateLink
    '        Else
    '            Me.TumorChart.SourceDoc = excelpath
    '            Me.TumorChart.Action = acOLECreateLink
    '        End If
    Which, naturally, does not work for a report.

    Bloat is not an option - I can't afford to embed these. I've googled a bit, and it seems the only course of action is to make a temporary table or hidden form, linking the .xls there. Can someone please let me know how to do this workaround?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    dont use excel for data collection
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    healdem, it must be wonderful not to be subordinate to higher powers or legacy systems!

    hunterw, I think that you need to create a temporary table that mimics the structure of the Excel files. (If the Excel files are arranged so as to make the data look pretty, you might need to add another worksheet to them that will grab the ranges that you require into a single row.) Then you can import the file to the table, use the data and delete it at the end of the process.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by weejas
    healdem, it must be wonderful not to be subordinate to higher powers or legacy systems!

    hunterw, I think that you need to create a temporary table that mimics the structure of the Excel files. (If the Excel files are arranged so as to make the data look pretty, you might need to add another worksheet to them that will grab the ranges that you require into a single row.) Then you can import the file to the table, use the data and delete it at the end of the process.
    If people want to maintain legacy systems thats fine, but too often those legacy system (espcecially ones using spreadsheets were designed for publication not data analaysis)
    if you are worth your salary you advise and act in a professional manner, the employer can agree or disagree.
    if someone asks me to use Excel as a data capture mechanism my answer is to demonstrate precisely why Excel (or any other spreadsheet) is inappropriate for use, but if they wish to proceed with a spreadsheet solution that is what they get once they sign off on the understanding that they are accepting the limitatiosn and problems of spreadhseets

    ultimately I do what the customer wants, but I will always advise that spreadsheets are a fantastic analysis tool they are pants as a data capture and data validation tools, and NOT TO BE TRUSTED in that role.
    they are too easily broken they are too easily damaged, but worst of all once broken there is no warning that there is a problem. and when you are using them for medical research and (I guess pharamcology research that is a big issue)

    importing the spreadsheet as is is frankly a mess.. the data needs to be imported into a properly designed db storage mechanism and then if needs be re analysed and spat out as required, that reformatting can be in a spreadsheet.. I've done that before because its what the customer wanted, what they were familiar with.. BUT the data source was in a db.

    merely importing the spreadsheet doesn't advance the cause at all, it probably takes you back a few steps...... potentially the worst of both worlds

    doing the data capture in a db isn't going to be too big an issue.
    doing a consolidation or aggregation in a properly designed db will (should) be a doddle
    combining all results from numerous trials
    spitting out hat data into a format such as SPSS so you can do you statistical analysis probably easy depending on your knowledge of SQL

    Ive not done any medial research systems, but I have cross consolidated exam results, assessments by course, student, module/unit/ year over time.
    prior to that the department used excel spreadsheets and didn't have a scooby on comparatives (short of having lots of admin staff going through each report scorecard/excel spreadsheet by hand and doing the calculations manually)
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2008
    Posts
    22
    These concerns about Excel aren't too terribly worrisome to us. Our data is not something you would amass a huge statistical analysis of, as the amount of variables we have is huge.

    We like the flexibility of the Excel chart to create graphs of things unique to certain mice, and there's tons of externally stored data files anyway.

    The master table used for each record already has 255 columns, so I'd have to generate a new table for each record, something I've no clue how to do. I really dislike this solution, since everything is in place for excel to be used other than the chart showing up on the report.


    The best lead I've found on google is this:

    an alternative is to use an Unbound OLE Frame control on a hidden
    form.
    Update the OLE control on the form during the format event of the Detail
    section, then copy the VALUE prop of the Frame control on the Form to that
    of the report. This method does not always work but depends on the OLE Server app.
    The core issue is that many OLE Server apps do not update the contents of
    the Frame control properly under the Access report writer's OLE Host
    interface. That's why so many control's will work on an Access form but
    fail
    on a report, or work with a Bound OLE Frame control but not an Unbound
    one.
    Looks promising, but I have no idea what the "VALUE prop" is, or the syntax of how to set it.

    One way or another I'll get this working! Thanks so much guys.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    255 columns!
    there is nothing stopping you using Excel to create whatever charts or wahtever else you want.

    255 columns.... sounds like seriously flaky db design
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2008
    Posts
    22
    I guess. Here's an example of one record. If you can think of a way to fit all that in to less than 255 fields I'd love to hear it.

    The only thing seriously flaky seems to be Access. Why the hell would I be able to set SourceDoc in a form and not a report?
    Attached Files Attached Files

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Woah.

    That would take rather a long time to normalise... Also, according to the help file, SourceDoc works for charts in reports as well as forms.

  9. #9
    Join Date
    Jun 2008
    Posts
    22
    Quote Originally Posted by weejas
    Woah.

    That would take rather a long time to normalise... Also, according to the help file, SourceDoc works for charts in reports as well as forms.
    Interesting!!

    Is there simply something I need to use instead of:

    Me.TumorChart.Action = acOLECreateLink

    ?

Posting Permissions

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