Saving unbound text box in Table for current record

[benny1983]

Hi All,

I have an audit form which has a calculating unbound text box which displays a result at the end of the audit. I understand it's not best practice to save these in the table however I need to secure what the result was at the time of the audit.

I have managed to compile the code to place within the event used when I click the save button. The command button does a number of functions so this is just one of them. It saves in the table just fine except it always saves as a new entry. I want it to save in the field of the current entry. Is there any way to do this?

Dim sql As String
Dim QAResult As String
Dim db as DAO.Database
 
set db = CurrentDb
 
QAResult = Me.txtQAResult.Value

 
If Not IsNull(QAResult) Then
sql = "INSERT INTO tblQAAuditRecords2014 ([QAResult]) VALUES ('" & QAResult & "')"

CurrentDb.Execute (sql)
 
End If

[TheSmileyCoder]

I just want to point out that there are certainly cases where storing a calculation IS considered best practice. For example the final result of a invoice. You wouldn't want the invoice total to change if the tax percentage changed one day.

I think the easiest way is to store the value when the record is saved. If you have the field in the same table as your record what could do is simply copy the value as the record is saved, using the forms BeforeUpdate event. Example posted below (Aircode)

Add a textbox, e.g. txtNameOfBoundF ield, and sets its visibility to False

Private Sub Form_BeforeUpdate(Cancel as Integer)
  Me.txtNameOfBoundField=Me.txtQAResult
End Sub

[benny1983]

I just want to point out that there are certainly cases where storing a calculation IS considered best practice. For example the final result of a invoice. You wouldn't want the invoice total to change if the tax percentage changed one day.

I think the easiest way is to store the value when the record is saved. If you have the field in the same table as your record what could do is simply copy the value as the record is saved, using the forms BeforeUpdate event. Example posted below (Aircode)

Add a textbox, e.g. txtNameOfBoundF ield, and sets its visibility to False

Private Sub Form_BeforeUpdate(Cancel as Integer)
  Me.txtNameOfBoundField=Me.txtQAResult
End Sub

Thanks for the code. I tried exactly what you said however I got an error : "Object doesn't support this property or method."

Is there something else I need to do to make this work?

Appreciate your assistance.

[benny1983]

Scrap that - there is no error with the code (there was an error with my typing).

The only thing is that now, although the action runs smoothly, there is nothing saved in that field of the table.

[benny1983]

Tried again and seems to be all saving now.

Thanks a million for your help!

[TheSmileyCoder]

Your welcome :)