Code under report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to avoid writing 288 queries, so I wrote this VB code under the
report

strsql = "Select * from TempAverage where Month = 10 and Year = 2004"
Set Oct2004 = CurrentDb.OpenRecordset(strsql)
Text582.Text = Oct2004!Average

On the third line, I get an error:

You can't reference a property or method for a control unless the control
has the focus.

How do I fix this?

Thanks in Advance,
Elena
 
I am trying to avoid writing 288 queries, so I wrote this VB code under the
report

strsql = "Select * from TempAverage where Month = 10 and Year = 2004"
Set Oct2004 = CurrentDb.OpenRecordset(strsql)
Text582.Text = Oct2004!Average

On the third line, I get an error:
Click to expand...
if you talk about access then use the Value property.
And further on it is the default property of a textbox, therefore you
can leave it out
 
I changed the code to:

strsql = "Select * from TempAverage where Month = 10 and Year = 2004"
Set Oct2004 = CurrentDb.OpenRecordset(strsql)
Text585.Value = Oct2004!Average

I got an error that says:

You can't assign a value to this object.

Is there another method?

~Elena
 
Hi Elena,

You can't extract a single value from a query in this manner - you either
need to declare and open a recordset or use the Dlookup function. If you
only need a single value, Dlookup is easier to use:

Presumably, there is a field named 'Average' in the table TempAverage.

Me.Text582=dlookup("TempAverage","Average","Month=" & me.txtMonth & " And
Year=" & me.txtYear)

For flexibility, I wrote the above to get the comparison values from
controls on the current form (or report). However, there are some problems
here. You have several fields that have names that should be avoided since
they conflict with the names of built in functions- "Month" and "Year".

You may also want to look at the DAvg function - instead of calculating and
storing averages, you could use this function to compute the average for a
domain using the same criteria.
 
I changed the code to:

strsql = "Select * from TempAverage where Month = 10 and Year = 2004"
Set Oct2004 = CurrentDb.OpenRecordset(strsql)
Text585.Value = Oct2004!Average

I got an error that says:

You can't assign a value to this object.
Click to expand...
what is text585?

i believe (if it is a textbox) the rowsource is not a field
 
Back
Top