Add Calc Field into Table from Form

[d1004]

Hi,
I'm like a very very beginner at this, so any inputs are greatly appreciated.

What I am trying to do is add my calculated field from the form into my table. What I've done was create a query with my calculated field, then I used that query for my form. After I enter all the information, ONLY my calculated field are not stored into the table. So what I did was create a Save button with an event procedure to occur when the user click on it. When I run it, it gave me the following errors:

The database has been placed in a state by user "Admin" on machine "PC..." that prevents it from being opened or locked.

Another error is:

Item cannot be found in the collection corresponding to
the requested name or ordinal.


Here are my code:

Private Sub txtSave_Click()
Dim Conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim intID As Integer

Set dbs = CurrentDb
Set Conn = New ADODB.Connection
With Conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source= " & dbs.Name
.Open
End With
Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open "tblTransactions", Conn, , , adCmdTable

rs.MoveLast
intID = rs![ID] + 1
rs.AddNew
rs![ID] = intID
rs![Reporting Date] = Me.[Reporting Period]
rs![Transaction Date] = Me.[Transaction Date]
rs![Maturity Date] = Me.[Maturity Date]
rs![Noon Day Rate] = Me.[Noon Day Rate]
rs![Spot Rate] = Me.[Spot Rate]
rs![Forward Rate] = Me.[Forward Rate]
rs![Contract Date] = Me.["Contract Rate"]
rs![Canadian Dollars] = Me.[Canadian Dollars]
rs![US Dollars] = Me.["US Dollars"]
rs![BankName] = Me.BankName
rs![TransactionType] = Me.[TransactionType]
rs![Confirmation] = Me.Confirmation
rs.Update
rs.Close

End Sub

My calculated field are Contract Rate, and US Dollars

Thank you so much

 

[scking]

Normally a calculated field is not stored in a table because it can be calculated in a form or report each time. There are reasons it should be done but you need to consider the impacts. If you want to update it with a query then why are you trying to update it from within a form. To update it from within a form simply bind your control to the field name in the table and, from within the OnCurrent event you calculate the value and set the control to the calculated value. This will update only the record that has been navigated to using the form. As an alternative method you could run the query to update the fields before the form is loaded, maybe when the database is opened. For non-volatile information this is satisfactory but maybe your calculation is based on currency exchange rates which change daily.

If, as you are trying to do, you update the table from within the form you are trying to update the record the form has already locked. -------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------