Handling Report Recordsets with a For loop (2000)

Topic

New Reply

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.

Reply | Quote

Replies

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:
Run-time error ‘2147217904 (80040e10)’: No value given for one or more required parameters.
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

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

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.

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 

Reply | Quote

What does “durate = rst.RecordCount” mean? You have a control or field on the report named durate–is that what you’re referring to?

Yes, it’s a text box in the Durate Report.

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?

I’m trying to obtain the report depicted in the attached text file.

set Option Explicit at the top of the module

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.

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.

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 this.
(How I suffer when I have to correct you .)

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

Reply | Quote

Hi,
I think your problem is that the select statement is effectively opening a recordset which calculates the averages and then trying to restrict that recordset based on one of the values it has calculated. This is really 2 queries and you can’t combine them into one, as I think has already been mentioned. What you could do is open the recordset and then apply a filter to it – e.g.:
rst.Filter = “[selectedfield] >= ” & a *24
but this still doesn’t get around the fact that you are repeatedly opening the recordset rather than looping through the values in it, so your report will only actually show the values for the last pass. If you can explain what exactly you’re trying to achieve, perhaps we can help. (My impression though is that your current method won’t do what you’re after.)
Hope that helps.

Reply | Quote

Now I’m sure to wade the waters.
Following your advice I’ve moved the Where clause of Sub Report_Activate into
the Filter method for the recorset(see attached file) but this time the code stops at the rst.Filter line with the message: Run-time error ‘3001’: the application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another.
You came in just at the right time(how could it be any different?) because something very similar happened in the and you came through with flying colors.
What’s wrong with
rst.Filter = “MEDIEGIO.Anno Between 1997 And 2000 and AvgOfPowerday >=” & a
?
Ciao

Reply | Quote

Hi,
You didn’t return Anno in your SELECT statement so you can’t filter on it. Leave the WHERE Anno Between 1997 And 2000 in the original SELECT statement, and just filter on AvgOfPowerday >= a * 24 and you should be OK.
Hope that helps.

Reply | Quote

I’ve done what you said and this time the code stops at the rst.MoveLast line (see attachment) with the message: Run-time error ‘3021’: Either BOF or EOF is true, or the current record has been deleted; the operation requested by the application requires a current record.
Ciao

Reply | Quote

Hi,
I imagine that’s because there are no records in the recordset at some point, due to the filter, so you can’t move to the last one! You can either add an If Not rst.EOF then rst.MoveLast or simply skip the movelast altogether – because you’re using a keyset cursor, Recordcount should return the actual number of records in the recordset correctly. In theory anyway!
Hope that helps.

Reply | Quote

O.k., now that I’ve commented out the rst.MoveLast line, the For loop loops nicely but your prediction(“but this still doesn’t get around the fact that you are repeatedly opening the recordset rather than looping through the values in it, so your report will only actually show the values for the last pass.”) has come true(never doubted it, I knew it would come, at least now the rest of the work is done ).
I think to enable the report to show all the 50 lines(see text file attached to this), one should conjugate a “Do until rst.EOF” with the existing For loop but I don’t know how to do that.

Reply | Quote

As for the second part of your post, please check this.
Ciao

Reply | Quote

[indent]

---

I’m trying to obtain the report depicted in the attached text file.

---

[/indent]In programming, a picture may *not* be worth a thousand words. You need to verbalize what all that business with the loop and opening and closing the recordset is supposed to accomplish within the report.[indent]

---

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.

---

[/indent]In that case, where did you declare the variables that weren’t declared in the routine? It’s customary to give module level variable names a one letter prefix to denote where they were declared. If it was declared at a module level, a variable might be called mVariable. If it was declared public in a standard module, it might be called gVariable. Only local variables (those declared in a Sub or Function) don’t have a special character.[indent]

---

Actually the RecordCount Property in ADO does indicate the current number of records

---

[/indent]Not without a movelast and movefirst, which you had left out. Otherwise all you get is a zero or a minus one indicating whether or not there are records.

The second query works because you took AvgOfPowerday out of the Where clause. I believe Rory explained that in his reply.

Reply | Quote

You’re always a step ahead because you can put things in perspective so me knowing in advance that you’re eventually bound to rest your case makes me suffer when I’m about to correct something you said.

You need to verbalize what all that business with the loop and opening and closing the recordset is supposed to accomplish within the report.

All right, here we go:
Starting with the qryProdGiox of the attached file, I’m trying to obtain the report depicted in the attached text file and here are the house-keeping details:
I begin by setting to 0 the start value of the variable “a as Long” declared in Sub Report_Activate and ask myself, “How many times the value in the qryProdGiox.AvgOfPowerday field is equal to or greater than a”? And that’s the value that goes into the Duration column you see in the text file. The Step column in the text file contains the value assumed by “a” during each cycle and the Average column is equal to (Duration*a)/365.
Afterwards I increase the value of “a” by the amount contained in the Passo text box of the Mask1 form and repeat the cycle.
All of this I tried to accomplish in Sub Report_Activate.
Ciao

Reply | Quote

[indent]

---

makes me suffer when I’m about to correct something you said.

---

[/indent]If there was any correction in there, I failed to notice it. But feel free to suffer if it makes you happy.

My question should have included a “why are you doing this” as well. I can see *what* you’re doing, but the reason for it escapes me. Is this a one-time calculation to populate an unbound control in your report, or are you attempting to populate a field for each record?

Reply | Quote

quote]makes me suffer when I’m about to correct something you said.

If there was any correction in there, I failed to notice it.

Ouch, I was still referring to this

Is this a one-time calculation to populate an unbound control in your report, or are you attempting to populate a field for each record?

The former.
I’m trying to populate the three unbound controls you see in the report according to the goals set in my explanation, each column(Duration, Step, Average) mentioned in said explanation corresponds to an unbound text box of the report.
Ciao

Reply | Quote

Not without a movelast and movefirst, which you had left out. Otherwise all you get is a zero or a minus one indicating whether or not there are records.

Hi Charlotte, did you now that if you use a keyset cursor, you can skip using the MoveLast method for recorsets?(Rory taught me that)

If I give a default value to the text25 text box in the Mask1 form using the properties sheet instead of typing it in, then the default value is not accepted. Even if, after opening the Mask1 form, I tab out of text25, it still behaves as if I hadn’t left text25. Is it because of this?
Ciao

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

>>& ” 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.

Reply | Quote

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

Reply | Quote

You will have to convert your database to Access97 for me to look at it.

Reply | Quote

Ciao

Reply | Quote

Okay, let’s look at your code again. Your problem stems from this part of the code:

Avg(MEDIEGIO.Cassiglio) AS selectedfield,
Nz(selectedfield)/24 AS AvgOfPowerday

You can’t use “selectedfield” or “avgOfPowerday” anywhere else in the select statement. The AS clause for a field merely define how this field is named in the resultant recordset, so you can use “rs!selectedfield”, etc. Therefore, you must always use the expression that created it. So the 2nd line above should read:

NZ(Avg(mdiegio.cassiglio))/24 as AvgOfPowerDay

Similarly, when you use AvgOfPowerDay in your WHERE statement, you must use the expression above instead. Also, this should appear in the Having statement, not the Where statement.

Try use the QBE grid to create this query. Then look at the SQL code it creates.

Reply | Quote

I’ve done what you said, using the originating expressions instead of the named fields and Having instead of Where(see attached mdb) and now the error message says, “Run-time error’-2147217887 (80040e21)’:You tried to execute a query that doesn’t include the specified expression ‘MEDIEGIO.Anno Between 1997 and 2000 And Nz(Avg(MEDIEGIO.Cassiglio))/24>=0’ as part of an aggregate function.”
Ciao

Reply | Quote

>>’MEDIEGIO.Anno Between 1997 and 2000 <<

This part needs to be in the WHERE section. Look at it this way. Use the WHERE section to specify conditions that determine which of the underlying records are to be included/excluded. Use the HAVING section to determine which of the Groups are to be included/excluded. So you want to include records with Anno between 1997 and 2000; but you want to exclude groups based on that Avg/24.

Reply | Quote

Thanks Mark, but Rory already covered .
Now">this.
Can you please lend a hand?
Ciao

Reply | Quote

Actually, I don’t understand what your code is doing. Perhaps you could give a description. You are opening a recordset with each iteration of the For…Next loop. To get the recordcount of the recordset, you must to a rs.MoveLast, which your initial code didn’t include. Why are you then looping through the records in the recordset? You initial code doesn’t show anything like that.

Reply | Quote

pristine beaches of Sardinia…
I’ve just arrived for my fortnight vacation but I just can

Reply | Quote

I’m sorry, but this has gone beyond the time I can devote to it. Perhaps someone else can pick this up. There is a rather fuzzy line which separates providing help and providing consulting assistance (which is what I do for a living), and one of the factors is time.

Reply | Quote

You asked for a description so I was bound to give it to you. Thanks anyway.
I

Reply | Quote

Hi,
I think the easiest way for you to get what you want will involve writing the values you want to a table after opening each recordset (given that you’re not actually interested in the individual values in the recordset but rather the recordcount). You can then base your report on this table and you should get a row for each returned recordcount and not just the last one.
Hope that helps.

Reply | Quote

Sorry for my slowness in responding but I

Reply | Quote

Scrap my last message. I realized I inadvertently typed rst.id instead of rst!id.
Yet something still doesn

Reply | Quote

I

Reply | Quote

Hi,
As far as I know, the open event of a report fires before the report retrieves its data (unlike a form) whereas the Activate event does not. Effectively I guess the report was obtaining a value for its 3 bound controls, then the recordsource was being changed, so when you go to print or preview, it then has to go back and retrieve the new data, from the first record. Hence you end up with the first row duplicated. There’s a fair bit of speculation in there though!
As for the default value question, yes it is related to the other post. The value of the textbox isn’t set to its default value until the record is saved – otherwise if you had default values in a form, as soon as you moved to a new record, it would be created even if you didn’t actually enter anything or manually save it.
Hope that helps.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote