By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

Storing calculated values... Exception!

P: n/a
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

Aug 8 '06 #1
Share this Question
Share on Google+
11 Replies


P: n/a
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.)

Aug 8 '06 #2

P: n/a
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
Aug 8 '06 #3

P: n/a
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.)
Aug 8 '06 #4

P: n/a
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
Aug 8 '06 #5

P: n/a
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
Aug 8 '06 #6

P: n/a
It decided to work when using the following code, cheers for the help!

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

Aug 9 '06 #7

P: n/a
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
Aug 9 '06 #8

P: n/a
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
Aug 9 '06 #9

P: n/a
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
Aug 9 '06 #10

P: n/a
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
Aug 9 '06 #11

P: n/a


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
Aug 9 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.