Saving Record

[mwabbe]

i want this to be able to save the encrypted data in the table but then when the form is opened have the data decrypted to be shown in the form.

well i here is what i have on the form that the data is typed in:

     Private Sub Form_BeforeUpdate(Cancel As Integer)
bWasNewRecord = Me.NewRecord
Call AuditEditBegin("EmpInfo", "audTmpEmpInfo", "ID", Nz(Me.ID, 0), bWasNewRecord)
If Not IsNull(SSN) Then
Call Enc("SSN")
End If
End Sub


Private Sub Form_Current()
If Not IsNull(SSN) Then
Call Enc("SSN")
End If
End Sub

and here is the enc/dec modual:

     
Dim sSecretData
Dim sCipherText
Dim capEData
Dim sPlainText
Dim key


Function Enc(ByVal SSN As String) As String
sSecretData = "SSN"

key = "Super duper password"


' Build up the key

Set capEData = CreateObject("CAPICOM.EncryptedData.1")
capEData.Algorithm = 3 'Use 3DES
capEData.SetSecret key
capEData.Content = sSecretData

sCipherText = capEData.Encrypt

Beep
MsgBox "Original data:" & sSecretData, vbOKOnly, ""
MsgBox "Encrypted data: " & sCipherText, vbOKOnly, ""

End Function

Function Dec(ByVal SSN As String) As String

key = "Super duper password"

capEData.Algorithm = 3
capEData.SetSecret key
capEData.Decrypt sCipherText

sPlainText = capEData.Content

MsgBox "Recoverd data: " & sPlainText, vbOKOnly, ""

End Function

after it saves it tells me from the msgbox that i has been encrypted and what it has been encrypted too but when i look in the table part it is not encrypted it still shows the original data

[RuralGuy]

Try describing in words what you think each step in the two form events (BeforeUpdate and Current) are doing.

[mwabbe]

if the SSN is not null then the function enc/dec will be called

[RuralGuy]

Why are you doing this in the Current event of the Form?

[mwabbe]

honestly.. no idea

[RuralGuy]

You get a CurrentEvent *every* time you move to a different record in the RecordSource of the form. That is *not* the time to write to the table. Who developed this db? Was it someone else?

[mwabbe]

kind of some one else told me to put that there

[mwabbe]

So Basically all i want to do is, have the user enter the SSN have the SSN be Encrypted and have the encrypted value save to the table so that when i open the form back up the data is decrypted and shown as the original SSN on the form. Right now I have it working except for the saving part. It will show me what is going to be encrypted then what it is encrypted too then the next time i open the form it tells me that the SSN has been recovered from, shows the encrypted value then shows the decrypted value but it still does not save the encrypted value

[RuralGuy]

The BeforeUpdate event will work for encripting and saving an SSN. The decript should be done in the CurrentEvent of the form. You want the SSN control to be UNBOUND (no control source). The Current Event code will look something like:
Me.YourSSNcontrolName = Dec([SSN_Field])
Your BeforeUpdate event will look something like:
[SSN_Field] = Enc(Me.YourSSNcontrolName)
...after verifying valid data of course.
You *only* get a BeforeUpdate event when something on the form has been dirtied and unbound controls do *not* dirty a form so you will need some other mechanism to dirty the form or use the AfterUpdate event of the SSNcontrol to encript the field into the table.

[mwabbe]

since its unbound it doesnt know where to save it because there is not data in the ssn feild on the table

[RuralGuy]

You need to specify what field to use in your code.

[mwabbe]

i put in [tblEmpInfo]![SSN] = enc(me.SSN) it does not work it errors and says cannot find the field

[RuralGuy]

You would use: [SSN] = enc(Me.SSN)
...and the [SSN] field *must* be in the RecordSet of the form's RecordSource. You do *not* specify the table in the syntax.