Storing calculated values... Exception!

[James Hallam]

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

[James Hallam]

I tried this code but it doesn't seem to work, or have any effect
what-so-ever!

With CurrentDb.OpenRecordset("SnapshotTable")
.AddNew
!ssstgKey = Me.stgKey
!ssDate = Date
!ssExpectedDate = Me.CompDate
!ssCompletion = Me.Completion
!ssAlgorithm = Algorithm
End With

(Where stg, CompDate, Completion are textboxs on the form; Date is
(hopefully) the current date; and algorithm is a variable defined in
the form.)

[Nick 'The database Guy']

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

[Nick 'The database Guy']

You have to update a recordset for the values to be saved, please see
my earlier example.

Good luck

Nick

James Hallam wrote:

I tried this code but it doesn't seem to work, or have any effect
what-so-ever!

With CurrentDb.OpenRecordset("SnapshotTable")
.AddNew
!ssstgKey = Me.stgKey
!ssDate = Date
!ssExpectedDate = Me.CompDate
!ssCompletion = Me.Completion
!ssAlgorithm = Algorithm
End With

(Where stg, CompDate, Completion are textboxs on the form; Date is
(hopefully) the current date; and algorithm is a variable defined in
the form.)

[James Hallam]

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

[Nick 'The database Guy']

This is the right code to use.

rs.Open sql, CurrentProject.AccessConnection, adOpenDynamic,
adLockOptimistic

However you must already have declared rs as a new adodb recordset, you
do so with the line of code:

Dim rs As New adodb.Recordset

It works for me every time!

Nick

James Hallam wrote:

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

[James Hallam]

It decided to work when using the following code, cheers for the help!

rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

[Nick 'The database Guy']

What is your backend database?

Nick
James Hallam wrote:

It decided to work when using the following code, cheers for the help!

rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

[James Hallam]

my frontend is called prototypev4.mdb, backend is called DataV1.mdb
Nick 'The database Guy' wrote:

What is your backend database?

Nick
James Hallam wrote:

It decided to work when using the following code, cheers for the help!

rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

[Nick 'The database Guy']

I was wondering why the CurrentProject.AccessConection usn't working.
Which version of Access are you using?

Nick

James Hallam wrote:

my frontend is called prototypev4.mdb, backend is called DataV1.mdb
Nick 'The database Guy' wrote:

What is your backend database?

Nick
James Hallam wrote:

It decided to work when using the following code, cheers for the help!
>
rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

[James Hallam]

2000

Nick 'The database Guy' wrote:

I was wondering why the CurrentProject.AccessConection usn't working.
Which version of Access are you using?

Nick

James Hallam wrote:

my frontend is called prototypev4.mdb, backend is called DataV1.mdb
Nick 'The database Guy' wrote:

What is your backend database?
>
Nick
>
>
James Hallam wrote:
It decided to work when using the following code, cheers for the help!

rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

[Nick 'The database Guy']

Yes, in that case the syntax is slightly different. This syntax that I
gave you will only work on 2002 or better. Sorry.

James Hallam wrote:

2000

Nick 'The database Guy' wrote:

I was wondering why the CurrentProject.AccessConection usn't working.
Which version of Access are you using?

Nick

James Hallam wrote:

my frontend is called prototypev4.mdb, backend is called DataV1.mdb
>
>
Nick 'The database Guy' wrote:
What is your backend database?

Nick


James Hallam wrote:
It decided to work when using the following code, cheers for the help!
>
rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic