Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Report Value (97 SR2)

    Well, (I am not a guru!) but -- I wonder if the width can't be reset unless you are running in design mode, so after you calculate the track1, you'll have to close, open in design mode, set the width, then reopen. Of course, you can see this turning into a never-ending "happening" .. Also, there is the continual problem of referring to the actual fieldname vs the text control name -- code in Access will sometimes not work at all unless your name is different from the field -- (but this won't relate to you, because your text control data source is a dlookup, so ignore that last!) Hope you get some help -- Pat

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Report Value (97 SR2)

    Did you do it in the OnFormat event code? Checking the builtin help, it looks like that's the only place it might work.

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Report Value (97 SR2)

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    There are seven arenas for code entry in a report:

    On Open
    On Close
    On Activate
    On Deactivate
    On No Data
    On Page
    On Error

    How might OnFormat be of help? (This is 97 SR2..)

    I just want to grab the value and re-size my box.. seems simple.. anyone have any insight?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Report Value (97 SR2)

    This should work with no problems. Where are you trying to execute the code? Assuming it is in the detail section of the report the following will work.

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me!track1box.Width = track1 * 0.0925
    End Sub

    Are you getting an error message? or just not seeing the control? if an error what is it? If no error just no apparent control, try setting the back colour to red and see if it is a fine line, then try

    Me!track1box.Width = track1 * 0.0925

    Dont forget that the width setting here is in twips.

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Report Value (97 SR2)

    Thanks!

    Strange behavior now though, it seems that my format is stuck in a loop...

    I've adjusted this line to represent 6 track text boxes, and 6 boxes. track1 through track6, and track1box through track6box. Now, it's taking a good 15-20 minutes to generate the report, and when it comes out, the width of all my boxes is Zero.

    Track1-Track6 contain a DCount formula which returns the number of entries on a specific day:

    =DCount("*","tblMain","[date]=Date()-1)

    Does your solution here retain the result of this formula? or is it trying to multiply the formula itself by .0925?

    Thanks for all your help!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Report Value (97 SR2)

    Here's a thought:

    Assign the value of your calculation to a variable and then calculate and assign the width of your box in the OnOpen event of the report.

    That should prevent the code running multiple times in the OnFormat event of the detail section.

    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Report Value (97 SR2)

    "Does your solution here retain the result of this formula? or is it trying to multiply the formula itself by .0925?"

    it multiplies the value contained in the text box by .0925. When you say stuck in a loop have you actually stepped through the code to determine that is what is happening or are you making an assumption.

    The problem with domain lookups is simply that they are incredibly slow. Depending on what you are doing I'd be tempted to base your report on a query where the resultant records provide the value for Track[n] ie

    SELECT tblMain.DATE, Count(tblMain.DATE) AS Track
    FROM tblMain
    GROUP BY tblMain.DATE
    HAVING (((tblMain.DATE) Between Date()-1 And Date()-7));

    will provide a recordset like

    DATE Track
    12/08/2001 4
    13/08/2001 1
    14/08/2001 2
    15/08/2001 2

    This will be a lot quicker.

    Put a break point on the first line of the relevant report section format event and determine if the problem is the code being called multiple times or just the speed of the domain lookups.

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Report Value (97 SR2)

    It's stuck in a loop.. I didn't put a breakpoint, but I did put, simply, 'msgbox track1' after the first assignment, the message box appears over and over, and doesn't seem to match anything, (e.g. the number of objects in Detail, etc... ) as far as the number of times it repeats.. .

    I will try out your solution to this, wish me luck, and many many thanks!

    Regards,
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calling Report Value (97 SR2)

    I've got a text box on my report, which i've named track1. I've also got a box which i've named track1box..

    The statement:
    track1box.width = track1.value * .0925 in vba, is not working, I assume it's because i'm trying to call the value of track1, which contains a dcount statement.

    How can I retreive the value from track1, and use it to determine the width of my box? This is for creating a graph of sorts... Any ideas?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Report Value (97 SR2)

    Last question, I hope.. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I've got my returned record set from my SQL statement, how can I extract the values in this data set for use on my report? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  11. #11
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Report Value (97 SR2)

    I assumed that the report would be based on the new query. Oh well.

    If the report is only designed to return the values from the new recordset change the report datasource to the new query. Update the controlsource where appropriate to get it to use any data field names that are different.

    If the count is associated with a different recordset, modify the source for the report by adding the new query to the query the report is based on. Linking on the date field (presumably) to make the count figure available as just another field. Then change the dcount controlsource to be the field name from the query.

    Make any sense or just more confusing?

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Report Value (97 SR2)

    Something like this:<pre> Dim db as DAO.Database
    Dim rst as DAO.Recordset
    Dim fld as DAO.Field
    Dim lngAnswer as Long 'Or whatever data type your field will return
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("<Your SQL>",dbOpenSnapshot)
    Set fld = rst.Fields("<Your Fieldname>")
    'This assumes your SQL Statement will only return one calculated record
    lngAnswer = fld.Value
    rst.Close
    Set fld = nothing
    Set rst = nothing
    Set db = nothing</pre>

    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  13. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Report Value (97 SR2)

    The query we made generates 5 lines. Each line has two items, Date, and Track.

    How can I extract ALL of the data? If I use a field for track, I only get one value, The next page has the next information, but I want it ALL to display together without having to rely on multiple instances of the details section. any ideas?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  14. #14
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Report Value (97 SR2)

    Now i'm confused, I assumed that the primary purpose of this was to create a graphical represenation of the sumary data. Now I'm starting to think that you are using this as a supporting represenation within another report. Is this correct?

    If you are looking to show the 5 lines as a primative bar chart in the report header or footer, why not create the report based on the summary graph and just insert it as a subreport? This has the advantage that you don't need to add new text boxes or anything else via vba if the number of records reurned by the query increases.

Posting Permissions

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