Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2009
    Location
    New York, New York
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    BeforeUpdate form ctl event trickery? (2003/SP2)

    Is there any way to "smoothly" trick the BeforeUpdate event procedure in Access forms to programmatically do an update?

    Let me explain what I'm trying to do here. I have an Access front end linked to a SQL Server 2005 database. One of these tables in the database has employee social security numbers, so I encrypted the SocSecNum field in SQL Server using a certificate. The social security number is actually stored as binary data, and I use a SQL Server view to present the decrypted social security numbers to the user in the Access front-end, so the social security number the user is looking at is actually a derived field.

    But let's say the user needs to edit a social security number in the Access form for whatever reason. Well, since the social security number he sees is a derived field in my SQL view, obviously he can't edit it. So, what I thought I'd do was to have the SocSecNum textbox's BeforeUpdate event call a SQL Server stored procedure (via a pass-through query in Access) to do the update. I've included my code below -- it actually works! The SQL stored procedure gets called, and the social security number gets changed. The only problem is because I've canceled the event, the change doesn't show up seamlessly for the user. Instead, he has to press Esc to get out of the textbox, then refresh the form to see the changed social security number.

    Has anyone else here ever have to work with encrypted back-end data, and tried to do something like this and got it to work?

    Thanks.

    '************************************************* ****************
    Private Sub SocSecNum_BeforeUpdate(Cancel As Integer)
    'On Error Resume Next

    Dim rst As Recordset
    Dim intEmplID%
    Dim strSocSecNum$

    Dim strSQL As String
    Dim strQueryName As String
    Dim strConnection As String
    Dim blnReturnsRecords As Boolean

    'Get EmplID for currently selected record
    Set rst = Form.RecordsetClone
    With rst
    intEmplID% = ![EmplID]
    End With
    Set rst = Nothing

    strSocSecNum$ = SocSecNum.Value

    'Use the pass-through query to call the stored procedure to do the update
    strSQL = "Exec dbo.usp_Employees_UpdateSSN " & intEmplID% & ", '" & strSocSecNum$ & "'"
    strQueryName = "qsptEmployees_UpdateSSN"
    strConnection = "ODBC;DSN=ADPUnionPayments;DATABASE=ADPUnionPaymen ts;Trusted_Connection=Yes"
    blnReturnsRecords = False

    Call CreateSPT(strQueryName, strSQL, strConnection, blnReturnsRecords)
    DoCmd.OpenQuery strQueryName

    MsgBox "Social Security Number changed.", vbInformation, gstrMsgBoxTitle

    'Cancel the update event since it was actually handled by the pass-through query...
    Cancel = True

    End Sub
    [pre]Stephan Ip
    www.CustomOfficeDev.com
    [/pre]

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: BeforeUpdate form ctl event trickery? (2003/SP2)

    I'm not sure I follow the whole situation. Is this a bound form? If so, I'm assuming the SSN control is unbound? Why do you need to update the SQL record immediately when the SSN is changed? Can't you do it in the form's AfterUpdate event?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Feb 2009
    Location
    New York, New York
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: BeforeUpdate form ctl event trickery? (2003/SP2)

    Hey Mark, thanks for reading my post and getting back to me.

    I know this is a little confusing, so let me explain further. Yes, this is a bound form -- bound to a linked SQL view -- and all the controls, including the text box for the SSN, are bound as well.

    Here's the SQL view it's bound to:

    /************************************************** ************************************************** ************************************************** ********************************/
    CREATE VIEW [dbo].[vwEmployees]
    AS
    SELECT TOP (100) PERCENT EmplID, CONVERT(VARCHAR(9), DECRYPTBYCERT(CERT_ID('certADPUnionPayments'), SocSecNum)) AS SocSecNum, LastName,
    FirstName, HireDate, RehireDate, TermDate, Union_CD
    FROM dbo.Employees
    ORDER BY EmplID
    /************************************************** ************************************************** ************************************************** ********************************/

    Note that the SSN in the view, because the actual SSN data is encrypted, is a *derived* field -- and that's the crux of problem. Because the SSN in the view is derived, you *can't* actually change it in the Access form, and if you do try, you'll get an error, make sense?

    Because of the encryption, the only way of changing the SSN is in SQL itself via a stored procedure:

    /************************************************** ************************************************** ************************************************** ********************************/
    ALTER PROCEDURE [dbo].[usp_Employees_UpdateSSN]
    (
    @EmplID SMALLINT,
    @SocSecNum VARCHAR(9)
    )

    AS

    SET NOCOUNT ON

    UPDATE dbo.Employees
    SET SocSecNum = ENCRYPTBYCERT(Cert_ID('certADPUnionPayments'), @SocSecNum)
    WHERE EmplID = @EmplID
    /************************************************** ************************************************** ************************************************** ********************************/

    So what I do (or attempt to do anyway) in the BeforeUpdate event is to trick Access into updating the SSN by calling the above SQL stored procedure via a pass-through query. Like I said, it does work, but it's kludgy -- the user has to Esc out of the textbox and refresh the form in order to see the changed SSN value. And I can't do this in the AfterUpdate event, since the real update will never fire.

    I was just hoping that someone else in this forum has worked with encrypted data in the SQL Server back end, and the associated problems of changing that data in the Access front end.
    [pre]Stephan Ip
    www.CustomOfficeDev.com
    [/pre]

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: BeforeUpdate form ctl event trickery? (2003/SP2)

    OK, I understand that. But why must the SSN control be bound also? Can't you make it unbound, and fill it from the recordsource during the form's Current event? Then in the form's AfterUpdate event, you could check to see if the SSN has been changed, which would trigger your code.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    New Lounger
    Join Date
    Feb 2009
    Location
    New York, New York
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: BeforeUpdate form ctl event trickery? (2003/SP2)

    OK, I'll try that. Thank you so very much!

    I owe you one.
    [pre]Stephan Ip
    www.CustomOfficeDev.com
    [/pre]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •