Page 1 of 3 123 LastLast
Results 1 to 15 of 40
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Handling Report Recordsets with a For loop (2000)

    This may be just me, but I won't open any attached files unless I know exactly who is sending it and why; not even from my father! I'd advise you to copy the relevant portion of your code into your message.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Handling Report Recordsets with a For loop (2000)

    Giorgio,

    You sometimes get that message for other reasons. Try putting a breakpoint in your code, or hit the Debug button if it's offered, or do a Ctrl+Break before clicking OK on the message box and try to troubleshoot what's actually happening. Part of learning to program is learning to figure out what you did wrong. We're glad to give you our assistance, but you also need to start learning how to work through some of these problems yourself .

    I agree with Mark in that you need to post the code that is throwing the error instead of expecting anyone to figure it out by trying to make your database work.
    Charlotte

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Handling Report Recordsets with a For loop (2000)

    When I try and open the Durate Report(put 2.4 in the text25 text box on the Mask1 form before opening it) of the attached file I get the following:
    <font color=red>Run-time error '2147217904 (80040e10)': No value given for one or more required parameters.</font color=red>
    I don't see which parameter(s) is missing, do you?

    Private Sub Report_Activate()
    Dim a
    Set cnn = CurrentProject.Connection
    For a = 0 To Forms!Mask1!Text25 * 1000 Step Forms!Mask1!Passo
    rst.Open "SELECT MEDIEGIO.Mese, MEDIEGIO.Giorno, Avg(MEDIEGIO.Cassiglio) AS selectedfield, Nz(selectedfield)/24 AS AvgOfPowerday" _
    & " FROM MEDIEGIO INNER JOIN tblMonthOrder ON MEDIEGIO.Mese = tblMonthOrder.Month" _
    & " WHERE MEDIEGIO.Anno Between 1997 And 2000 and AvgOfPowerday >=" & a _
    & " GROUP BY MEDIEGIO.Mese, MEDIEGIO.Giorno, tblMonthOrder.OrderOfMonth" _
    & " ORDER BY tblMonthOrder.OrderOfMonth;", cnn, adOpenKeyset, adLockOptimistic
    durate = rst.RecordCount
    Reports!durate!Passo = a
    Medie = (a * durate) / 365
    rst.Close
    Next
    End Sub
    Attached Files Attached Files

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Handling Report Recordsets with a For loop (2000)

    I've stepped through and the code stops, right at the first cycle of the loop, at the rst.Open line of Report_Activate giving the error message so it's obvious there's something wrong with the Select statement but I can't figure out what. Help, Charlotte! <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Handling Report Recordsets with a For loop (2000)

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

    I've followed your advice and edited the first post.
    Could the mdb files I attach be prone to carrying viruses? I use the corporate edition of Norton AntiVirus installed on my notebook plus a corporate firewall and I've never had any problems, passing scot-free through Love Bug, Melissa and similar amenities.
    Ciao

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Handling Report Recordsets with a For loop (2000)

    It is possible for a .mdb file to contain a virus, though it is unlikely. Primarily because few people distribute .mdb files, so it has no appeal to virus makers. I just take all the precautions I can, and I've gone unscathed also (except for some years ago, when I got a Word macro virus).

    Additionally, for those of us who see ALOT of messages, downloading a file and trying to find the exact spot you are talking about is bothersome. We (at least me) tend to skip over them, thinking we will possibly get to it later. This is not to say we don't download databases, just not routinely.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Handling Report Recordsets with a For loop (2000)

    Are you sure both of those forms are opened? Also, I would use "Dim a As Long". Using just "Dim a" declares it has a variant. Access can process Longs much faster than Variants. Oh, and that part of the WHERE clause that is Avgof Powerday=a, shouldn't that be part of a Having statement. Not sure here, as I always have trouble with WHERE vs HAVING.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Handling Report Recordsets with a For loop (2000)

    At the risk of repeating myself, don't try and do it all in one step! And for goodness sake, set Option Explicit at the top of the module and declare all your variables. That will save you all the problems you've had with misspelling names. And declare any module level variables with an m at the front of the name to make it obvious they don't need to be declared in the current routine.

    As far as the rest goes, you're having problems because you aren't clearly thinking through what you want to do. You don't open a recordset over and over in a loop, you open the recordset once and then loop through the records. What exactly are you trying to do here?

    What does "durate = rst.RecordCount" mean? You hae a control or field on the report named duarter--is that what you're referring to? Or are you referring to a variable with the same name? RecordCount in ADO recordsets normally returns a -1 or 0, meaning there are records or there aren't. It doesn't work quite like RecordCount in DAO.

    By coding it like the following, you can put a break in at the rst.Open line and print the SQL string to the immediate window. Then copy that SQL and paste it into the SQL window of a new query. That will allow you to see exactly what might be wrong with the query.

    <pre>Private Sub Report_Activate()
    Dim a As Long
    Dim cnn As ADODB.Connection
    Dim lngSteps as Long
    Dim strSQL As String

    Set cnn = CurrentProject.Connection
    lngSteps = Forms!Mask1!Text25 * 1000 Step Forms!Mask1!Passo
    strSQL = "SELECT MEDIEGIO.Mese, MEDIEGIO.Giorno, Avg(MEDIEGIO.Cassiglio) " _
    & "AS selectedfield, Nz(selectedfield)/24 " _
    & " AS AvgOfPowerday " _
    & "FROM MEDIEGIO INNER JOIN tblMonthOrder ON " _
    & "MEDIEGIO.Mese = tblMonthOrder.Month" _
    & " WHERE MEDIEGIO.Anno Between 1997 And 2000 and AvgOfPowerday >=" & a _
    & " GROUP BY MEDIEGIO.Mese, MEDIEGIO.Giorno, tblMonthOrder.OrderOfMonth" _
    & " ORDER BY tblMonthOrder.OrderOfMonthSELECT MEDIEGIO.Mese, " _
    & "MEDIEGIO.Giorno, Avg(MEDIEGIO.Cassiglio) AS " _
    & "selectedfield, Nz(selectedfield)/24 AS AvgOfPowerday" _
    & " FROM MEDIEGIO INNER JOIN tblMonthOrder ON MEDIEGIO.Mese = " _
    & "tblMonthOrder.Month" _
    & " WHERE MEDIEGIO.Anno Between 1997 And 2000 and AvgOfPowerday >=" & a _
    & " GROUP BY MEDIEGIO.Mese, MEDIEGIO.Giorno, " _
    & "tblMonthOrder.OrderOfMonth" _
    & " ORDER BY tblMonthOrder.OrderOfMonth"

    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
    rst.MoveLast
    durate = rst.AbsolutePostion
    rst.MoveFirst
    For a = 0 To lngSteps

    Reports!durate!Passo = a
    Medie = (a * durate) / 365
    rst.MoveNext
    Next
    rst.Close
    End Sub </pre>
    Charlotte

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Ahhh, what a relief...

    hearing you say Access files are inherently safe and that you do download databases(your post #61733).
    Actually, if you decide to open the attached mdb, you'll see there's only one form.
    Ciao

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Ahhh, what a relief...

    He didn't say they were inherently safe. He said they were unlikely targets.
    Charlotte

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Handling Report Recordsets with a For loop (2000)

    >>& " WHERE MEDIEGIO.Anno Between 1997 And 2000 and AvgOfPowerday >=" & a _<<

    Here is your problem, you can't use AvgOfPowerday within the SQL statement. You must use the expression that created AvgOfPowerday.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Handling Report Recordsets with a For loop (2000)

    I've substituted Nz(selectedfield)/24 for AvgOfPowerday in the Where clause as you told me to (please see attached file) but I still get the same error message.
    Ciao
    Attached Files Attached Files

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Handling Report Recordsets with a For loop (2000)

    <img src=/w3timages/purpleline.gif width=33% height=2><img src=/w3timages/purpleline.gif width=33% height=2><img src=/w3timages/purpleline.gif width=33% height=2>
    What does "durate = rst.RecordCount" mean? You have a control or field on the report named durate--is that what you're referring to?
    <img src=/w3timages/purpleline.gif width=33% height=2><img src=/w3timages/purpleline.gif width=33% height=2><img src=/w3timages/purpleline.gif width=33% height=2>
    Yes, it's a text box in the Durate Report.

    <img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2>
    As far as the rest goes, you're having problems because you aren't clearly thinking through what you want to do. You don't open a recordset over and over in a loop, you open the recordset once and then loop through the records. What exactly are you trying to do here?
    <img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2>
    I'm trying to obtain the report depicted in the attached text file.

    <img src=/w3timages/greenline.gif width=33% height=2><img src=/w3timages/greenline.gif width=33% height=2><img src=/w3timages/greenline.gif width=33% height=2>
    set Option Explicit at the top of the module
    <img src=/w3timages/greenline.gif width=33% height=2><img src=/w3timages/greenline.gif width=33% height=2><img src=/w3timages/greenline.gif width=33% height=2>
    If you look at the attachment of the first post, I did set Option Explicit in the Declaration section at the top of the Report_Durate module.

    <img src=/w3timages/blueline.gif width=33% height=2><img src=/w3timages/blueline.gif width=33% height=2><img src=/w3timages/blueline.gif width=33% height=2>
    RecordCount in ADO recordsets normally returns a -1 or 0, meaning there are records or there aren't. It doesn't work quite like RecordCount in DAO.
    <img src=/w3timages/blueline.gif width=33% height=2><img src=/w3timages/blueline.gif width=33% height=2><img src=/w3timages/blueline.gif width=33% height=2>
    Actually the RecordCount Property in ADO does indicate the current number of records in a Recordset object. I know because I've used it before, see <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=41571&page=& view=&sb=&o=&vc=1>this</A>.
    (How I suffer when I have to correct you <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>.)

    I've created a query(Query1, see attached mdb) using the same Select statement contained in the rst.Open command of the Sub Report_Activate and another query(Query2) which is the same as Query1 except I took out AvgOfPowerday from the Where clause.
    How come Query2 works while Query1 doesn't?
    Ciao
    Attached Files Attached Files

  14. #14
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Handling Report Recordsets with a For loop (2000)

    You will have to convert your database to Access97 for me to look at it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Presto Changeo!

    Ciao
    Attached Files Attached Files

Page 1 of 3 123 LastLast

Posting Permissions

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