Report to Table

T

TheGerman

I have another question.

I have a report in which i calculate a running sum. I want to be able to
copy that into a Table. The Report will only result in one line always. I
have a "Temp" Global Variable setup which saves it.

On a form i can just use Tcost.value = temp and it updates it, but it does
not work in the form. Since i save it in a global variable i can use it
anywhere.

What would be the best way to insert that into the table? I am thinking
maybe and SQL statment?

~Daniel
 
R

Rick B

Please do not post in multiple groups. You only need one correct answer to
your question.
 
D

Douglas J. Steele

You shouldn't store it in a table. You can recalculate it whenever you need
it.

One of the biggest problems with storing calculated values is that you can't
be sure which is correct if there's a discrepency between the calculated
value and the underlying data.
 
T

TheGerman

I know, but i already took care of that. The way the database is setup i
cannot recalculate it. It's rather complex, but do you how i can store it?
 
J

John Vinson

I know, but i already took care of that. The way the database is setup i
cannot recalculate it. It's rather complex, but do you how i can store it?
Click to expand...

If you can calculate it on a Report, then you can calculate it in a
Form or a Query, right? An Append query would probably be the simplest
solution; just recapitulate the Report's operations in a query (which
might need to be several queries nested).

I've never tried it but you might be able to open a Recordset in a
Report's Close event and capture the value of a textbox.

John W. Vinson[MVP]
 
T

TheGerman

I guess i'm looking for something like this:

strsql = "Update assemblies set tcost = temp Where Parentid = myvalue"
CurrentDb.Execute strsql, dbFailOnError

where assemblies is my table and tcost is the field in the table and temp is
a global variable i want to assign tcost to and parentid is a field in the
table and myvalue is another varible which i am comparing parentid to.
instead of my value i could use rst.Fields("Parentid").Value to get the
right answer

I get an error when trying to run it

"Run-time error '3061':

To few paramters. expected 2.

Hope u can help i'm guessing i'm just using wrong syntax
 
D

Douglas J Steele

Assuming temp and myvalue are variables, you need to put them outside of the
quotes:

strsql = "Update assemblies set tcost = " & temp & " Where Parentid = " &
myvalue
CurrentDb.Execute strsql, dbFailOnError

If they're text values, you need to put quotes around them:

strsql = "Update assemblies set tcost = " & Chr$(34) & temp & Chr$(34) & _
" Where Parentid = " & Chr$(34) & myvalue & Chr$(34)
CurrentDb.Execute strsql, dbFailOnError
 
T

TheGerman

I got it working now i used

strsql = "Update assemblies set tcost = " & temp & " Where Parentid = '" &
myvalue & "'"
CurrentDb.Execute strsql, dbFailOnError

Thank you very much Everyone!!
 
Top