Clear Fields

[capogna1]

Good morning!

I have this code to personalize my error message pertaining duplicate record:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Const ERR_DUPLICATE_INDEX_VALUE = 3022
Dim strMsg As String

If DataErr = ERR_DUPLICATE_INDEX_VALUE Then
strMsg = "This record cannot be added to the database." & vbCrLf
strMsg = strMsg & "It would create a duplicate record." & vbCrLf & vbCrLf
strMsg = strMsg & "Changes were unsuccessful."

MsgBox "Error Number: " & DataErr & vbCrLf & vbCrLf & strMsg, _
vbCritical + vbOKOnly, "Please search by SSN and add encounter to existing Veteran."

'Set focus to the offending control
Me.Patient_SSN_Entry.SetFocus

Response = acDataErrContinue 'prevent Access from displaying its own error message
Else
Response = acDataErrDisplay 'cause Access to display its own error message
End If

End Sub

I would need a code to clear my fields after the message, the fields are:

so the user can search from the combo box below and not worry about clearing the fields manually.

Thanks in advance.

 

[Gasman]

Me.ControlName = Null or Me.Controlname = "" ?

 

[capogna1]

Me.ControlName = Null or Me.Controlname = "" ?

Thanks much!

 

[capogna1]

Private Sub Form_Error(DataErr As Integer, Response As Integer)

    Const ERR_DUPLICATE_INDEX_VALUE = 3022
    Dim strMsg As String

    If DataErr = ERR_DUPLICATE_INDEX_VALUE Then
        strMsg = "This record cannot be added to the database." & vbCrLf
        strMsg = strMsg & "It would create a duplicate record." & vbCrLf & vbCrLf
        strMsg = strMsg & "Changes were unsuccessful."

        MsgBox "Error Number: " & DataErr & vbCrLf & vbCrLf & strMsg, _
            vbCritical + vbOKOnly, "Please search by SSN and add encounter to existing Veteran."

        'Set focus to the offending control
        Me.Patient_SSN_Entry.SetFocus

        Response = acDataErrContinue 'prevent Access from displaying its own error message
    Else
        Response = acDataErrDisplay 'cause Access to display its own error message

    End If

End Sub

 

[capogna1]

Thanks much!

It didn't work

 

[Gasman]

It didn't work

That really does not say much.
Please show your code where you clear the controls, plus the code around it.

 

[moke123]

I think gasman meant

Me.ControlName = Null

or

Me.Controlname = ""

One or the other, not both.

 

[capogna1]

Private Sub Form_Error(DataErr As Integer, Response As Integer)

    Const ERR_DUPLICATE_INDEX_VALUE = 3022
    Dim strMsg As String

    If DataErr = ERR_DUPLICATE_INDEX_VALUE Then
        strMsg = "This record cannot be added to the database." & vbCrLf
        strMsg = strMsg & "It would create a duplicate record." & vbCrLf & vbCrLf
        strMsg = strMsg & "Changes were unsuccessful."

        MsgBox "Error Number: " & DataErr & vbCrLf & vbCrLf & strMsg, _
            vbCritical + vbOKOnly, "Please search by SSN and add encounter to existing Veteran."

        'Set focus to the offending control
        Me.Patient_SSN_Entry.SetFocus

        Response = acDataErrContinue 'prevent Access from displaying its own error message
    Else
        Response = acDataErrDisplay 'cause Access to display its own error message
        
        Me.Patient_SSN_Entry = Null
        

    End If

End Sub


Private Sub Patient_SSN_Entry_Click()

End Sub

 

[capogna1]

Do I replace Me.ControlName = Null
with
Me.Patient_SSN_Entry = Null

???

 

[Gasman]

Yes, or use "" as already mentioned.
I was going to ask if that is the name of the control, but you seem to set focus to it without issues?

 

[Gasman]

Don't you want to reset it on your error message?

 

[capogna1]

Private Sub Form_Error(DataErr As Integer, Response As Integer)

    Const ERR_DUPLICATE_INDEX_VALUE = 3022
    Dim strMsg As String

    If DataErr = ERR_DUPLICATE_INDEX_VALUE Then
        strMsg = "This record cannot be added to the database." & vbCrLf
        strMsg = strMsg & "It would create a duplicate record." & vbCrLf & vbCrLf
        strMsg = strMsg & "Changes were unsuccessful."

        MsgBox "Error Number: " & DataErr & vbCrLf & vbCrLf & strMsg, _
            vbCritical + vbOKOnly, "Please search by SSN and add encounter to existing Veteran."

        'Set focus to the offending control
        Me.Patient_SSN_Entry.SetFocus

        Response = acDataErrContinue 'prevent Access from displaying its own error message
    Else
        Response = acDataErrDisplay 'cause Access to display its own error message
       
        Me.Patient_SSN_Entry = Null
       

    End If

End Sub


Private Sub Patient_SSN_Entry_Click()

End Sub

This doesn't clear my field

 

[Gasman]

You said you wanted to clear if you displyed your dupe message, yet you have the clear in the else block of the code?
Walk through your code line by line.

 

[moke123]

Me.Patient_SSN_Entry = Null is in the wrong place.

 

[capogna1]

Don't you want to reset it on your error message?

Private Sub Form_Error(DataErr As Integer, Response As Integer)

    Const ERR_DUPLICATE_INDEX_VALUE = 3022
    Dim strMsg As String

    If DataErr = ERR_DUPLICATE_INDEX_VALUE Then
        strMsg = "This record cannot be added to the database." & vbCrLf
        strMsg = strMsg & "It would create a duplicate record." & vbCrLf & vbCrLf
        strMsg = strMsg & "Changes were unsuccessful."

        MsgBox "Error Number: " & DataErr & vbCrLf & vbCrLf & strMsg, _
            vbCritical + vbOKOnly, "Please search by SSN and add encounter to existing Veteran."

        'Set focus to the offending control
        Me.Patient_SSN_Entry.SetFocus
        Me.Patient_SSN_Entry = Null
            Me.Patient_Last_Name = Null
            Me.Patient_First_Name = Null

        Response = acDataErrContinue 'prevent Access from displaying its own error message
    Else
        Response = acDataErrDisplay 'cause Access to display its own error message
        
        


    End If

End Sub

 

[capogna1]

You said you wanted to clear if you displyed your dupe message, yet you have the clear in the else block of the code?
Walk through your code line by line.

Thanks it worked that is exactly what I needed to do. Thanks so much!