store calculate values

[jalverson]

I searched the archive for how to store a calculated value and found a lot of controversial dialogue about the pros/cons but not really a solution on how to do it.

I have a form based on a query. The database behind the form and query is our ERP database and is connected though an odbc connection. The form allows the user to enter some shipping dimensions and freight rates. The data is automatically stored in the ERP database and any user can view the data from the ERP software.

Here is the problem. There are also some freight calculations that occur on the form that our business teams want the results stored in the ERP database. They can view the results from the calculations using the ERP software instead having to open a 2nd application (Access form) to view the calculated data.

I have determined which fields in the ERP database will hold the data. I only need the vb code or other suggestions on how to update the calculated values into the database.

I apologize for the long message. Thanks for your help,

Jeff

 

[RuralGuy]

Without knowing more about your tables and forms how about this? In the BeforeUpdate event of the form you could put something like:

Me.FreightCalcControl = (Me.Weight * Me.CostPerPound) * Me.Discount

Just to remind you that storing calculated values means the field will probably be incorrect in the future without any method of correcting it. (among other things)

 

[phoenicks7]

I found that I used to run into this problem quite frequently as well with linking to external databases.

The work around I use is typically to load the form with VB Code INSTEAD of using a query. Simply build the query as you would normally, switch to SQL view... then copy / paste the Select statement to your code window for the form. That way, your form is based on the table data instead of the query data.

I'm not sure what the exact parameters for your specific database connection and string would be, however.. I will give you an example of what I'm using in my current project.....

Private Sub Form_Load()
Dim con As ADODB.Connection, rstEvents As ADODB.Recordset

Set con = CurrentProject.Connection
Set rstEvents = New ADODB.Recordset

strSQL = "SELECT * FROM [Events Table] WHERE [EventsTable].Week
= #" & dteCurrentDate & "#"

With rstEvents
.Open strSQL, con, adOpenKeyset, adLockOptimistic, adCmdTableDirect
intCount = rstEvents.RecordCount
.Close
End With

If Not intCount = 0 Then
strSQL = "SELECT [Operators Table].oper_id, [Events Table].date, " _
& " ----/ rest of SELECT Statement here /----" _
& "ORDER BY [Operators Table].Name"

Me.RecordSource = strSQL
Me.Requery
Else
MsgBox "There are no records for " & dteCurrentDate
Me.Close
End If

End Sub


The select statement here was quite long.... so I got rid of most of the middle info for clarity..... but.... essentially, the table information is updated as the data controls on the form are updated automatically.

I've found through experience, that basing a form on a query when it is unnecessary to do so, leads to future problems.. mostly I try to keep the number of queries and macros to a minimum and keep everything on the code level. That is a personal preference, though.