Calculated field on a Form into a Table 1

[sdlewis]

I have a field that is the end results of several calculations. The form has 30 or so inputs, then does non-visible calculations and returns a value. I want to write this value into the originating table. I tried the technique I found in a simiar request to this on of setting the default value on another field, but the field remains blank. Is there another way to do this?

 

[Databaseguy]

On click (or another event).

Dim DBS as database
Dim rst as recordset
Dim strVar as string
Set DBS = currentDB
Set rst = dbs.openrecordset("YourTable&quot
strVar = me.yourcalculatedfieldorvariable
With recordset
.fields("YourField&quot = strVar
.update
End With
Set dbs = nothing
Set rst = nothing

However, it is not good normalization practice to store a calculated variable..... Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[sdlewis]

I can't get the coding to work, but I'm open to not puting the calculated field in the table. My understanding is to shoe it in a query, but how do I go about doing that? As far as I can tell, I can only use fields from another table or query in a query.

 

[Databaseguy]

Post the code you're using & lets have a look see... Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[Windshpere]

dbguy is write on but it may not work in Access 2000. Because of libraries.
Try this. If MS Office 2000

On click (or another event).
'_____________________code start_______________________

Dim rst as object: Set rst = currentdb.openrecordset ("YourTable&quot

Dim strVar as string: strVar = me.yourcalculatedfieldorvariable

With rst
.addnew ' this adds a new record dbguy missed this
!"YourField" = strVar '(example: !calc = strVar)
.update 'this updates the new record
End With

Set rst = nothing
'_____________________code end________________________

'If you still have problems with this look up DAO in the help files.

 

[sdlewis]

Let me try to define the problem better. The user enters several numbers (radius lengths)and gets returned a 'high point' value. The calculations are really just vector addition. So, I end up with a degree value, I'm calling it FinalDeg, that says how many degrees from the start point the high point is. All the calculations are hidden, and the end result is shown. I also want to keep record of this end result for future reference. It need not be part of the table (that field called Final Angle)if I can make it show up elsewhere in a query or other display. As far as the code goes, I have been using exactly what you have been writing, substituting my field names. Thank you for bearing with me.

 

[Sawatzky]

dear sdlewis,

calculating in a query could be done like this (very simple one)
having to number field a and b

select a + b as sum from sourcetable

having two strings c and d

select a & " is concatenated with " & d as coolthing from sourcetable

have a look into the acces/sql forum on this site, you will find a lot of really cool things that can be done with queries

regards astrid

 

[sdlewis]

This is the code I'm using to try to write the data to the table:

Private Sub Text412_Click()
Dim rst As Object: Set rst = CurrentDb.OpenRecordset(tblLongShaftFront)

Dim strVar As String: strVar = Me.FinalDeg

With rst
.AddNew
!FinalAngle = strVar
.Update
End With

Set rst = Nothing
End Sub

FinalDeg is my calculated value, FinalAngle is the field name in the table, tblLongShaftFront is the table. Please advise.