Re: Storing calculated values... Exception!

i tried you code but still no result...

Dim rs As New ADODB.Recordset
Dim sql As String
sql = "SELECT ssDate, ssExpectedDate, ssCompletion, ssAlgorithm
FROM SnapshotTable"
With rs
***---> .Open sql, CurrentProject, adOpenDynamic,
adLockOptimistic <----***
.AddNew
!ssDate = Date
!ssExpectedDate = Me.CompDate
!ssCompletion = Me.Completion
!ssAlgorithm = Algorithm
.Update
.Close
End With


If I included the ".AccessConection" on line 6 it didn't like it, not
recognising object.
The code above it fails on line 6, saying the arguments are in conflict
with one another. I'm sure this code will work... just a minor error
with that one line?
Nick 'The database Guy' wrote:
Hi James,

This is a valid reason, I my opinion, for storing the calculated value.
I also think that you would be better off using VBA write them to the
table.

Dim rs As New ADODB.Recordset
Dim sql As String
sql = _
"SELECT JobID, PercentComplete, ExpectedCompletionDate " & _
"FROM tblJobs"
With rs
.Open sql, CurrentProject.AccessConnection, adOpenDynamic,
adLockOptimistic
.AddNew
!PercentComplete = CalculatedValue1
!ExpectedCompletionDate = CalculatedValue2
.Update
.Close
End With

Your VBA should look something like this.

Good luck

Nick
James Hallam wrote:
I have read through all the past topics and couldn't find what I was
after so...

I am looking to store some calculated values (don't flame just yet,
just read on!). I have an piece of code behind a form which calculates
a percentage completion and an expected completion date of a job. I
would like to store this information in a separate table, along with
the actual date the values were calculated.

Whenever a job is completed I can see how far off my estimated
completion date is off the actual date and make any changes neccessary.

Within the code there are a few different algorithms used to calculate
the estimated completion date, so I would need to store this value as
well.

So Questions...
* Is this a valid exception to the "never store calculted values"
* Would the best idea be to use an append query?
If so could someone post the base code as I am not too hot with SQL!

Cheers,

James Hallam

.

Relevant Pages

  • Re: update table with the word Null
    ... not run a sql statement in a database on its own. ... specifically from an ADO Command object. ... Dim sql ... 'comment out the above line when finished debugging ...
    (microsoft.public.scripting.vbscript)
  • Re: Find record RunSQL in a crosstab query
    ... the results displayed in the typical query result ... Dim SQL As String ... Remember, though, that all this will do, though, is display the results. ... >>> Dim SQL As String ...
    (microsoft.public.access.formscoding)
  • Re: Import Access records to excel (parameter is a called funct)
    ... It querys alright but does not roll back ... > Dim SQL As String ...
    (microsoft.public.excel.programming)
  • RE: ExecuteNonQuery Wierdness
    ... Public Sub Write(ByVal sensor As Integer, ByVal eventId As Integer, ... ByVal eventValue As String) ... Dim sql As String ...
    (microsoft.public.dotnet.languages.vb)
  • ExecuteNonQuery Wierdness
    ... Public Sub Write(ByVal sensor As Integer, ByVal eventId As Integer, ... ByVal eventValue As String) ... Dim sql As String ...
    (microsoft.public.dotnet.languages.vb)