Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Location
    Houston, TX
    Posts
    29

    Unanswered: Test for Control Value deletion

    Ok here is my problem:

    In a form I have control txtcphs, and I want to question the user when he/she changes or deletes the value in this control. Here is the code that I have thus far. All scenarios work ok except for when the control value is deleted completely.

    Thanks for the help!

    '*************************************
    'SET CURRENT CPHS # FOR ERROR CHECKING
    '*************************************
    Private Sub txtcphs_Enter()
    If Not (IsNull(Me.cphs)) Then
    strCPHS = Trim(Me.cphs)
    MsgBox "strCPHS = " & strCPHS
    Else
    strCPHS = ""
    MsgBox "It's Blank"
    End If
    End Sub


    '*************************************
    'WARNING FOR CPHS# CHANGE
    '*************************************
    Private Sub txtcphs_Exit(Cancel As Integer)

    On Error GoTo ErrorHandler

    Dim db As Database
    Dim rec As Recordset
    Dim intMsgResult As Integer
    Dim intDeleteMsg As Integer
    Dim lngProtocolIDValue As Long

    MsgBox "Me.CPHS = " & Me.cphs
    MsgBox "strCPHS = " & strCPHS

    If strCPHS <> Me.cphs Then
    MsgBox "They do not equal. " & Chr(10) & _
    " Me.CPHS = " & Me.cphs & Chr(10) & _
    " strCPHS = " & strCPHS

    '** They do not equal, someone changed the CPHS number.
    intMsgResult = MsgBox("You are attempting to change an existing CPHS#. " & Chr(13) & Chr(10) & "Do you want to continue?", 276)
    If intMsgResult = 6 Then 'Answer is no
    strCPHS = ""
    MsgBox "Me.CPHS = " & Me.cphs & Chr(10) & _
    "strCPHS = " & strCPHS
    'do nothing

    Else ' Answer is yes, I want to change the number. So we track it.
    Set db = CurrentDb()
    Set rec = db.OpenRecordset("tblCPHSLOG", dbOpenDynaset)
    lngProtocolIDValue = Me.txtProtocolID
    With rec
    .AddNew
    .Fields("protocolID") = lngProtocolIDValue
    .Fields("cphs") = Me.cphs
    .Fields("DateModified") = Now()
    .Fields("UserName") = fOSUserName
    .Update
    .Close
    End With
    Me.cphs = strCPHS
    strCPHS = ""
    MsgBox "Me.cphs = " & Me.cphs & Chr(10) & _
    "strCPHS has been reset to: " & strCPHS
    End If
    End If

    '*****THIS IS WHERE THE TROUBLE BEGINS******

    If IsNull(Me.cphs) And Not IsNull(strCPHS) Then
    intDeleteMsg = MsgBox("You deleted the cphs number." & Chr(10) & _
    "Are you sure you want to do this?", vbYesNo)

    Select Case intDeleteMsg

    Case vbYes
    MsgBox "Done, the CPHS number has been deleted."

    Case vbNo
    Me.cphs = strCPHS
    MsgBox " The CPHS number has been restored."

    Case Else
    End Select
    End If

    End Sub

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You need to test for both Null and the Empty String (""). The easiest way I have found to test for both at the same time is:

    If txtcphs.Value & "" = "" Then

    So in your case change:

    If IsNull(Me.cphs) And Not IsNull(strCPHS) Then

    To:

    If IsNull(Me.cphs) And strCPHS & "" <> "" Then

Posting Permissions

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