Run macro in embedded worksheet in Access Report

>I've got a report and have inserted an excel spreadsheet into it.
Just out of curiosity, why?

An Access report is non-editable, with no user interface allowed, and is intended only to be printed on paper or to a file.
An Excel Spreadsheet, even as an embededed (i.e. not bound) OLE Object, is editable, with a user interface.

It would be far better to make a 'links to a query from another database' into the Access database you're using, then use this query in your report.

i would be careful doing this though,.. if you can run the same operation from the MSAccess side fo the operation try to do so.  Fewer namespaces involved means less likelyhood of errors that will be difficult to diagnose.

wouldn't a datasheet view of a table work just as well for this report??  
I'm with jimhorn that there is alot of complexities in what your are trying to do that are going to plague this operation with possible errors

Hi
Thanks for your replies
Why is a really good question. I've been asked to build a risk management database - and I can get by in access to a degree but this one I'm struggling with. I need to come up with a report that maps risk codes - this can be a spreadsheet - doesn't matter so long as it can be run from within access. Here's an idea of what it needs to look like.
http://www.ghost-watch.co.uk/map.html
Basically this charts the results of a query in a spreadsheet. Charts won't work for this as you'll see there can be more than one data label for each point and I can't get the access charts to do what I need. So with some help from here - here's the link
http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21989474.html
I've got it to programatically fill in cells with a vb macro that looks at the contents of Risk Code, Impact and Probability and fills in the relevant cells on the spreadsheet . However I also need to be able to list underneath the chart the Description field in the query. This again is pulled from the same access query - but importing the text into excel causes the column width to extend to accommodate the text - and this destroys what until then looks like a chart. The data will change as the query updates and I can't get a range of columns to stay the same fixed size in excel, whilst other columns shift to accommodate the text. So now becoming increasingly frustrated I thought I'd try embedding the spreadsheet "chart" in an access report and then bringing in the query details in text form into the report in the usual way. Any ideas to produce what I need would be very gratefully recieved.
Thanks
Deb

Oh - and 7 is plotted in the wrong place on the jpg map file.

oh baby, do I feel your pain!!!  but that is a very cool map.html!  it is very descriptive of your purpose here.

Do the numbers on the axes stay fixed at 6 each way??  (i think I'm having an epiphany...)

I'm thinking Crystal Reports, as you can embed graphs into reports much easier than Access reports can.  JD, you agree?

yes, presuming the process owner will throw for CR.

I'm having a bitch of a time now with a graph on an access form... does anyone here want to take this whiney ass client off my hands?  I'm tired of hearing "Can't ya just....."

sorry,... i was gone for a moment,...but I'm back now

Yep - the axes values are fixed at 1-6 (or there'll be violence if someone tells me otherwise). Crystal Reports? Sounds expensive and my budget is not great - the process owner is my boss and I'm in charge of the budget and as we're a charity it isn't huge. Any way I can do this using what I have?

>>there'll be violence<<  OH YEAH,... been there !!!
..i'll be back after the laughter subsides and I can think..

if that macro isn't huge,... can you post it here or summarize what it does...
My thought is that with a form layout of flat textboxes you could simulate that map.html  a subform could provide the data layout below it.

<offtopicremarks>
    Deb:  you seem to have a penchant for coming up with some very interesting and unique requirements.
</offtopicremarks>

"" Deb:  you seem to have a penchant for coming up with some very interesting and unique requirements. ""
- Nope that'd be my boss, bless her. My requirements are to go into work, have a pleasant stress free day, get paid lots of money and then go home and have fun. It never happens.

The macro is in the link to the PAQ I posted ie
Sub EnterRisk()
    Dim O As Range
    For Each cell In Range("PTable")
        P = cell
        I = cell.Offset(0, 1)
        R = cell.Offset(0, -2)
        Set O = WorksheetFunction.Index(Range("Results"), 7 - I, P)
        If O = "" Then
            O = R
        Else
            O = O & "," & R
        End If
    Next cell
End Sub

It needs a bit of tweaking ie to set the cells to nothing before running but otherwise it works - apart from the column resizing issue. I could do it in a chart fine - but the data label problem ruins that nicely.
           
Hmm - using text boxes - interesting idea. Can you do conditional formatting on them? - what I didn't add is that the query rows need to be in various colours depending on the value of "Score" - again easy in excel - Any cunning plans?

Ah yes you can do conditional formatting - never tried it in access before. So if I could get 36 text boxes, loop through each query row and set the control source on each one to check the values of let's say [probability] and [impact] and return the value of [riskcode] or nothing. Then if the box is already populated to add a , and the next value of [riskcode].  Again - way out of my league. If I could award 20000 points for this I would.

...thinking "out loud"...

  a grid of textboxes 6 by 6
  all butted up together nice and tight
  shaped the way that would provide good visibility and text reading
  each text box acting as a cell, and possibly being name as such (ie txtX1Y1,... to txtX6Y6)
 
  here my unfamiliarity with the evaluation of the data is hurting me...
  i see that you are using an iterative process to set the cell,... I just have difficulty making the transition

  colors and be easilty applied by having an array of color (longints) set aside for use in variables.

  in that the cute little grid of text boxes is not tied to the width of the fields in the data display grid, they could remain a fixed but optimal size
  a subreport can supply the data below the grid with it designed to fit any eventuality, sized independently of the cute graph.

  phew... brain was getting a cramp...

i think we were having similar thoughts, but yours was conditional formating  mine was to just set them outright in a case statement or the like

Yes - a case statement should do it - it's just my programming skills stopped and started with C about 5 years ago and I struggle badly with syntax in VBA (and any other language for that matter). The colours of the text boxes that make up the "chart" would be fixed so no problem there. It's just the query listed underneath that requires CF and that's the least of my worries. Please post your ideas because right now you're the only person helping - thanks!

jimhorn is listening too... he's just hanging around waiting for me to reveal the miracle code that pulls it all together.

in your map.html,.. what is the value of the cell in the how do the colors associate to the underlying values?

i may have a bazillion more questions before we are done here, but I think we're all going to learn something out of this, and it will be a very cool exercise in control manipulation

assignment of the number found in Risk Code can be done by scanning the textfield in given pattern ("N" or "Z") and dropping the value in as

for x = 1 to 6
  for y = 1 to 6
      targctrl = "txt" & x & "X" & y & "Y"
      me(targctrl) = me(targctrl) & iif(not isnull(me(targctrl),",","") & rs("RiskCode")
  next y
next x

Thanks Jadedata
Great to see that you're up for the challenge and if you ever have any W2k server/2kPro/XP Client/network problems give me a shout. Am in the UK and so it's 11pm here and I'll be putting this down for the weekend as I need some fun - so I'll get back to you on Monday am GMT in more detail.

Basically the value ranges for the columns on the html chart are based on the score total and are within fixed ranges, so all we need to do is assign a background colour to a specific text box as it's value range and position will stay constant if you know what I mean. In case you haven't noticed (and I'm sure you have) we're developing a risk management tool but it's based on a specific UK risk model hence the tight requirements. If you look at the html chart - the colours of the cells stay totally static - it's the value displayed for each Risk code that matters and that depends on the values of Impact and Probability - in terms of the chart these are plotted by y and x values respectively ( I should have labelled the axes - tut tut - bad me). For example if you look at the chart any value that has x(probability)=6 AND (in a boolean sense) y(impact)= or >5 is going to be the vivid orange colour which means in risk terms, Critical. I don't have the exact ranges right now but you can work it out by just looking at the colours in the chart and how they relate to the values of Impact(Y axis) and Probability(x axis)  (am too tired to work it out and post it but will do so Monday). As each text box has it's own value in terms of x,y  I thought that testing for the x and y values and displaying risk code (or not) for each text box would be the way to go with your idea - although I haven't a clue how to do that. I'm a network admin who's forced to be a Jack of all Trades - but I DO love the charity (non-profit if you're US) that I work for.
As for what you posted - it looks impressive but I don't have a clue (or in Uk rhyming slang "Scooby-Doo") what to do with it, so assume I'm an idiot and please spell it out like I'm a five-year-old.

I'll catch up with you Monday and have a good weekend,
Thanks so very much
Deb :))

P.S I'll explain fully what I'd like ideally and if you think you can help with the design issue (as I might have it wrong) then we can split the parts into separate questions as that may be the best way to go - I'm not sure because I'm not a DB expert but  I've earned enough points!

some of what I posted I still don't have a clue really... just whistling in the dark so far...  see you Monday

Hello - any ideas as I'm stuck
Thanks
Deb

?? - where'd you come from?

sorry wrong question number.

Hi
Sorry but my time is running out on this. I haven't had a response to my original question or a solution so I'll let this hang for a few days in case I get any replies and then I'll ask for it to be closed.
Thanks
Deb :))

still here...
  trying to devine how to popuate the 6x6 grid, but with only your one sample of possible outcomes i'd need advanced calculus to predict a different outcome.  I cut that class.  Actually I cut that course.

  Do the colors in the grid remain in the same place or can they move/change.
  Is there logic to which cell a riskcode appears in or is it sufficient to be posted in the proper color range....
  and oh yeah,... what are the ranges?

  Could this report be prepared for distribution entirely in MS Excel and simply driven by output from msaccess? (eliminate the report thing all together.)  Furballs like this I usually find myself setting up a "template" book and populating it from MSAccess and saving as a new name in context with the report data.

Sorry - getting worried but I really appreciate you sticking with this. I've now figured out an alternative way round using an excel worksheet that can be pulled up via access, but this would be preferable if we can do it.
see the following

http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21989474.html
http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21993098.html

So - the grid stays the same colour - it's basically a simple x,y plot and the axes values stay the same. The colours are purely based on the value of x*y within a fixed range and so they stay constant. So if we have the following as the top 2 rows of this chart the plot values used for the top row are based on x axis - max value=6 and y axis - max value=6
Textbox1    textbox2   textbox3   textbox4   textbox5   textbox6    
 x=1, y=6   x=2,y=6    x=3,y=6   x=4,y=6  x=5,y=6   x=6,y=6
Textbox7    textbox8   textbox9   textbox10   textbox11   textbox12    
 x=1, y=5   x=2,y=5    x=3,y=5   x=4,y=5  x=5,y=5   x=6,y=5

etc.
So if the query produces
RiskCode     Probability(x)   Impact(y)    Score (x*y)
2                         5                6                30

Then the risk code (ie 2 in this case) needs to be displayed in textbox5 as this has the corresponding x,y co-ordinates. The colours of the text boxes are determined by score so any scores over 27 ie textbox5, testbox6 and textbox12 will be red in background ie textbox5 = 5*6 =30 which is >27. The other colour ranges are for scores = x*y equals
1-9 - pale yellow
10-18 - pale orange
19-27 - pale blue
27+  - bright orange/red
So the textbox positions and colours have to stay fixed - it's just the value that are displayed that change - the value being the risk codes.

I can sort out conditional formatting for the text boxes - my problem is looping through the query to populate them in the first instance as this can't go from record to record with just one text box populated. Of course where two scores both have the same coordinates of x,y then the relevant text box needs to display all corresponding risk codes for those coordinates so that could be 3,5,7,12 if all these risk codes have say coordinates x(prob)=4, y (imp)=4.

I really hope that makes this clearer - but we can let this go if it's going to eat too much of our time as the links I posted show how I sorted this out in excel and whilst that solution isn't ideal both me and my boss can live with it.

Ouch - my brain hurts - let me know what you want to do
Thanks again and bet you didn't bargain for this lot
Deb :))

no problem to go with what you have that works on this one...  Post to CS for a close/refund, or you can leave this open until we put up or give up.


range1 = 1-9 - pale yellow  - post riskid to txtRange1
range2 = 10-18 - pale orange - post riskid to txtRange2
range3 = 19-27 - pale blue - post riskid to txtRange3
range4 = 27+  - bright orange/red - post riskid to txtRange4


dim rs
set rs = currentdb.openrecordset({sql that these records come from},dbopensnapshot)
do until rs.eof
    if rs("score")>=1 and rs("score")<=9 then
        me("txtRange1") = iif(not isnull(me("txtRange1"),", ","") & rs("RiskCode")
    elseif rs("score")>=10 and rs("score")<=18 then
        me("txtRange2") = iif(not isnull(me("txtRange2"),", ","") & rs("RiskCode")
    elseif rs("score")>=19 and rs("score")<=27 then
        me("txtRange3") = iif(not isnull(me("txtRange3"),", ","") & rs("RiskCode")  
    elseif rs("score")>=28 and rs("score")<=30 then
        me("txtRange4") = iif(not isnull(me("txtRange4"),", ","") & rs("RiskCode")
    end if
    rs.movenext
loop

 if two risk have the same outcome in range1 (1-9), they concant into the same text box.

I'm probably still really fuzzy on the process that is in play here, so sorry about the kludgish nature of the above.  more thinking out loud

this issue would give even the most ardent logic puzzlers a headache,  but I feel we might invent something really cool here.

Hi
Really in terms of time I need to leave this - sorry but this is not my usual approach to questions. I can basically achieve exactly what I want by opening up the spreadsheet from within access. The macros and data refresh that I needed the spreadsheet to do are now set to happen automatically when the spreadsheet opens in excel and I've found a compromise with the cell width problem by lowering the cell widths and setting the column size not to change in the properties of the datarange in excel when data is imported. Enabling word wrap in formatting on these cells has enabled it to work how I needed.

Thanks for your help
Deb :))

cool... ask CS to wrap it up then.