Capturing a Value in a Text box before update

  • Thread starter Thread starter Nadine
  • Start date Start date
N

Nadine

Ok so I created a form using the form wizard and it populates the form
for me. Now what I need to do is when the user changes a value on the
form to update another table (not the table the value originaly comes
from) with the new value. My problem is capturing what value is being
changed. I created a global varible and in the before update assigned
the value of the text box to this varible. But when I use the varible
in my on change event all I get is the value that it has been changed
to not the original value. I have attached the code to help see what I
am doing!

Option Explicit
Dim intCIHIIDUpdate As Integer

Private Sub Form_BeforeUpdate(Cancel As Integer)

intCIHIIDUpdate = txt_cihi_provider_type_id

End Sub

Private Sub Form_AfterUpdate()

'Create a connection to the current Database
Dim cnHospList As ADODB.Connection
Set cnHospList = CurrentProject.Connection

Dim strSQL As String

strSQL = "Update [tblHospitalNames] Set
[tblHospitalNames].[cihi_provider_type_id] = " &
txt_cihi_provider_type_id & " Where
[tblHospitalNames].[cihi_provider_type_id] = " & intCIHIIDUpdate & " "

cnHospList.BeginTrans
cnHospList.Execute (strSQL)
cnHospList.CommitTrans

End Sub
 
If this is a bound control, and you want to know what the value was before
it was changed, its OldValue property should give you that:
intCIHIIDUpdate = txt_cihi_provider_type_id.OldValue

BTW, you may need to declare a Variant rather than an Integer. The OldValue
may have been Null (e.g. in the case of a new record).
 
Back
Top