Before update event

J

Junior

I placed the following code in before update event on a form -for
the[txtPssn]
The other txt controls are those that were entered before reaching
[txtPssn] - i tried setting them to null
however, txtLname and txtFname are required fields in the table design so
access won't let them be null values...
Question - how can i cancel the record being created when vbNo is
selected.(without closing the Db)
Thanks


Private Sub txtPssn_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_HandleErr_Click
'added 12/15/03 to handle duplicate SSN and application dates

Dim strName As String, strPos As String, dtApp As Date
Dim intAns As Integer
strName =Nz(DLast("Name", "QChkSSN"),"z")
dtApp = Nz(DLast("AppDte", "QChkSSN"), 99)
strPos = Nz(DLast("PosDesc", "QChkSSN"), "ZZ")
'MsgBox "strssn = " & strSSNChk
'MsgBox "txtPssn= " & txtPssn
If strName <> "z" Then
intAns = MsgBox(" An application for " & strName & " with this " _
& vbCrLf & "SSN and an application date of " & dtApp & "" _
& vbCrLf & "for " & strPos & " is already entered." _
& vbCrLf & "Do you want to continue recording this application? " _
, vbYesNo + vbQuestion, "Application Warning")
If intAns = vbNo Then
Me!txtLname = Null
Me!TxtFname = Null
Me.txtMI = Null
Me!cboSal = Null
Me!cboGender = Null
Cancel = True
Exit Sub
End If
End If

End Sub
 
T

TC

If you want to stop the record being saved (but leave the unsaved values on
the form), just set Cancel=True before you exit the sub.

If you want to erase the data & reset the record to empty as well, say:
Me.Undo before you set Cancel & exit the sub.

HTH,
TC
 
J

Junior

TC Thanks that worked great
TC said:
If you want to stop the record being saved (but leave the unsaved values on
the form), just set Cancel=True before you exit the sub.

If you want to erase the data & reset the record to empty as well, say:
Me.Undo before you set Cancel & exit the sub.

HTH,
TC


Junior said:
I placed the following code in before update event on a form -for
the[txtPssn]
The other txt controls are those that were entered before reaching
[txtPssn] - i tried setting them to null
however, txtLname and txtFname are required fields in the table design so
access won't let them be null values...
Question - how can i cancel the record being created when vbNo is
selected.(without closing the Db)
Thanks


Private Sub txtPssn_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_HandleErr_Click
'added 12/15/03 to handle duplicate SSN and application dates

Dim strName As String, strPos As String, dtApp As Date
Dim intAns As Integer
strName =Nz(DLast("Name", "QChkSSN"),"z")
dtApp = Nz(DLast("AppDte", "QChkSSN"), 99)
strPos = Nz(DLast("PosDesc", "QChkSSN"), "ZZ")
'MsgBox "strssn = " & strSSNChk
'MsgBox "txtPssn= " & txtPssn
If strName <> "z" Then
intAns = MsgBox(" An application for " & strName & " with this " _
& vbCrLf & "SSN and an application date of " & dtApp & "" _
& vbCrLf & "for " & strPos & " is already entered." _
& vbCrLf & "Do you want to continue recording this application? " _
, vbYesNo + vbQuestion, "Application Warning")
If intAns = vbNo Then
Me!txtLname = Null
Me!TxtFname = Null
Me.txtMI = Null
Me!cboSal = Null
Me!cboGender = Null
Cancel = True
Exit Sub
End If
End If

End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Before Update event 3
VBA question 1
Before-update reference on a field 5

Top