DoCmd.GoToRecord , , acNewRec

[Rick A]

I have a button on a bound form that adds a new record. It runs the code - DoCmd.GoToRecord , , acNewRec

I also have a cancel button in case the person changes their mind. In testing I press the Add button and then the Cancel button and a new blank record is added. I want to stop this behavior.

I've tried if me.dirty but since nothing has been typed on the form it's not dirty. I've tried me.undo but that's not working either.

How do I trap for this and stop the insert of a blank row into the database?

Thanks,

 

[Ken Snell \(MVP\)]

Tell us more about the form's setup and record source. If no data are
entered by the user or by programming, then a Me.Undo action should avoid
the saving of the new record.

However, if you're using a subform in the form, and the focus moves from the
form in which you've added the new record to the "other" form (main form or
subform), then the newly added record will be saved as part of the focus
move.
--

Ken Snell
<MS ACCESS MVP>


I have a button on a bound form that adds a new record. It runs the code -
DoCmd.GoToRecord , , acNewRec

I also have a cancel button in case the person changes their mind. In
testing I press the Add button and then the Cancel button and a new blank
record is added. I want to stop this behavior.

I've tried if me.dirty but since nothing has been typed on the form it's not
dirty. I've tried me.undo but that's not working either.

How do I trap for this and stop the insert of a blank row into the database?

Thanks,

 

[Rick Allison]

Ken,

The form is bound to a query and is a single form with subforms. However,
when adding a new record focus is set to the first textbox on the main form,
never switching to the subform.

What's strange is this. If I click the add button then cancel then close
the form nothing is added. However, if I click the add button, the cancel
button, the add button, the cancel button, close the form, and open the
form, one blank row is added to the database. Go figure. I guess that's
why I'm pulling my hair out. I guess the user will probably never do this
but all it takes is once and I'll be asked to fix it. Why does it do this?

Here's the add owner code:

Private Sub cmdAddOwner_Click()
On Error GoTo HandleError

EnableControls Me, acDetail, True
Select Case ObtainOrganization
Case "CKC"
Me!grpCountry = 1
Case "AKC"
Me!grpCountry = 2
End Select
Call grpCountry_AfterUpdate
Me!cmdApply.Enabled = True
Me.cmdCancel.Enabled = True
Me!cboSelectOwner.Enabled = False
Me!cmdDogEntry.Enabled = False
Me!cmdEditDog.Enabled = False
Me!cmdDeleteThisOwner.Enabled = False
Me!cmdAgilityEntryForm.Enabled = False
Me!cmdAddDog.Enabled = False
Me!btnMailingLabel.Enabled = False
Me!cmdDeleteSelectedDog.Enabled = False
Me!fsubOwnerLastEntered.Enabled = False
Me!fsubDogList.Enabled = False
DoCmd.GoToRecord , , acNewRec
Me!txtActualOwnerFirstName.SetFocus
Me!cmdAddOwner.Enabled = False
blnAddOwner = True

ExitHere:
Exit Sub

HandleError:
Select Case Err.Number
Case 2448
Resume Next
Case Else
Call Handle_Err(Err.Number, Err.Description,
"OwnerMaintenance-cmdAddOwner_Click")
Resume ExitHere
End Select
End Sub

Here's the cancel code:

Private Sub cmdCancel_Click()
Dim strAddUpdate As String

On Error GoTo HandleError
EnableControls Me, acDetail, False
Me!cboSelectOwner.Enabled = True
Me!cmdAddOwner.Enabled = True
Me!cboSelectOwner.Enabled = True
Me!cboSelectOwner.SetFocus
Me!cmdApply.Enabled = False
Me!cmdCancel.Enabled = False

ExitHere:
Exit Sub
HandleError:
Call Handle_Err(Err.Number, Err.Description,
"OwnerMaintenance-cmdCancel_Click")
Resume ExitHere
End Sub

I've tired using me.undo and me.dirty. If I click add then cancel me.dirty
is not set. If I check for null values in key fields I can trip the me.undo
but that does not seem to work.

 

[Ken Snell \(MVP\)]

Are the "add" and "cancel" buttons in the same section of the main form?

Is the "grpCountry" control bound to a field in the form's RecordSource?
What is the code that runs on the AfterUpdate event for the "grpCountry"
control? I don't see any "Me.Undo" code step in the cancel button's code?

Your description of what works and what doesn't work suggests that an error
is occurring in the Cancel button when you click it during the second cycle.
I see you have an error handler in that code; does it get called at all
during your sequence? What is the code for the Handle_Err subroutine?

I have found that uncleared errors from a called function or subroutine can
cause an error for the calling code that does not trip the calling code's
error handler, and the code silently fails. This may be what you're seeing
as well.

Another source of a silent failure has been that code on another control
raises an error that prevents the code from continuing -- e.g., trying to
change the value of a control during its BeforeUpdate event, trying to
change the focus from a control and code in its LostFocus or Exit event
fails, trying to change focus to a control and code in its GotFocus or Enter
event fails, etc.

So, it may be necessary for you to post all the code in the form's module
and the external subs/functions that are called. Let's see what's being
done.
--

Ken Snell
<MS ACCESS MVP>

Ken,

The form is bound to a query and is a single form with subforms. However,
when adding a new record focus is set to the first textbox on the main
form,
never switching to the subform.

What's strange is this. If I click the add button then cancel then close
the form nothing is added. However, if I click the add button, the cancel
button, the add button, the cancel button, close the form, and open the
form, one blank row is added to the database. Go figure. I guess that's
why I'm pulling my hair out. I guess the user will probably never do this
but all it takes is once and I'll be asked to fix it. Why does it do
this?

Here's the add owner code:

Private Sub cmdAddOwner_Click()
On Error GoTo HandleError

EnableControls Me, acDetail, True
Select Case ObtainOrganization
Case "CKC"
Me!grpCountry = 1
Case "AKC"
Me!grpCountry = 2
End Select
Call grpCountry_AfterUpdate
Me!cmdApply.Enabled = True
Me.cmdCancel.Enabled = True
Me!cboSelectOwner.Enabled = False
Me!cmdDogEntry.Enabled = False
Me!cmdEditDog.Enabled = False
Me!cmdDeleteThisOwner.Enabled = False
Me!cmdAgilityEntryForm.Enabled = False
Me!cmdAddDog.Enabled = False
Me!btnMailingLabel.Enabled = False
Me!cmdDeleteSelectedDog.Enabled = False
Me!fsubOwnerLastEntered.Enabled = False
Me!fsubDogList.Enabled = False
DoCmd.GoToRecord , , acNewRec
Me!txtActualOwnerFirstName.SetFocus
Me!cmdAddOwner.Enabled = False
blnAddOwner = True

ExitHere:
Exit Sub

HandleError:
Select Case Err.Number
Case 2448
Resume Next
Case Else
Call Handle_Err(Err.Number, Err.Description,
"OwnerMaintenance-cmdAddOwner_Click")
Resume ExitHere
End Select
End Sub

Here's the cancel code:

Private Sub cmdCancel_Click()
Dim strAddUpdate As String

On Error GoTo HandleError
EnableControls Me, acDetail, False
Me!cboSelectOwner.Enabled = True
Me!cmdAddOwner.Enabled = True
Me!cboSelectOwner.Enabled = True
Me!cboSelectOwner.SetFocus
Me!cmdApply.Enabled = False
Me!cmdCancel.Enabled = False

ExitHere:
Exit Sub
HandleError:
Call Handle_Err(Err.Number, Err.Description,
"OwnerMaintenance-cmdCancel_Click")
Resume ExitHere
End Sub

I've tired using me.undo and me.dirty. If I click add then cancel
me.dirty
is not set. If I check for null values in key fields I can trip the
me.undo
but that does not seem to work.

 

[Rick A]

Here's the code...

Option Compare Database
Option Explicit
Dim blnAddOwner As Boolean
Dim strOpeningArgument As String
Dim strFieldName As String
Dim strmsg As String

Private Sub cboOwnerCountryID_AfterUpdate()
Me!cboOwnerStateCD.Requery
If Me!cboOwnerCountryID = 1 Then
Me!txtOwnerZip.InputMask = "&&&\ &&&"
Else
Me!txtOwnerZip.InputMask = "00000\-9999"
End If
End Sub

Private Sub btnMailingLabel_Click()
DoCmd.OpenReport "rptMailingLabels", acViewNormal
End Sub

Private Sub cboSelectOwner_AfterUpdate()
On Error GoTo err_cboSelectOwner_AfterUpdate

Me.RecordSource = "Select * from qryOwner where OwnerID = " &
Me!cboSelectOwner
Me!grpCountry = DLookup("OwnerCountryID", "tblOwner", "ownerID=" &
Me!cboSelectOwner)
Call grpCountry_AfterUpdate
EnableControls Me, acDetail, True
Me!cmdApply.Enabled = True
Me!cmdCancel.Enabled = True
Me!cmdAddOwner.Enabled = False
Me!txtActualOwnerFirstName.SetFocus
Me!cboSelectOwner.Enabled = False

exit_cboSelectOwner_AfterUpdate:
Exit Sub
err_cboSelectOwner_AfterUpdate:
Call Handle_Err(Err.Number, Err.Description,
"OwnerMaintenance-cboSelectOwner_AfterUpdate")
Resume exit_cboSelectOwner_AfterUpdate
End Sub

Private Sub cmdAddDog_Click()
If IsNull(Me!OwnerID) Then
MsgBox "Please enter an owner first.", vbOKOnly + vbInformation, "Owner
Missing"
Me!txtActualOwnerFirstName.SetFocus
Else
DoCmd.OpenForm "frmDogMaintenanceNew", , , , , ,
"OwnerMaintenanceNewDog"
End If
End Sub

Private Sub cmdAddOwner_Click()
On Error GoTo HandleError

DoCmd.Echo False
DoCmd.Hourglass True
EnableControls Me, acDetail, True
Select Case ObtainOrganization
Case "CKC"
Me!grpCountry = 1
Case "AKC"
Me!grpCountry = 2
End Select
Call grpCountry_AfterUpdate
Me!cmdApply.Enabled = True
Me.cmdCancel.Enabled = True
Me!cboSelectOwner.Enabled = False
Me!cmdDogEntry.Enabled = False
Me!cmdEditDog.Enabled = False
Me!cmdDeleteThisOwner.Enabled = False
Me!cmdAgilityEntryForm.Enabled = False
Me!cmdAddDog.Enabled = False
Me!btnMailingLabel.Enabled = False
Me!cmdDeleteSelectedDog.Enabled = False
Me!fsubOwnerLastEntered.Enabled = False
Me!fsubDogList.Enabled = False
Me!cmdEntryReceived.Enabled = False
Me!cmdPreliminaryConfirmation.Enabled = False
Me!cmdEmailOwner.Enabled = False
Me!grpEmail.Enabled = False
DoCmd.GoToRecord , , acNewRec
Me!txtActualOwnerFirstName.SetFocus
Me!cmdAddOwner.Enabled = False
blnAddOwner = True

DoCmd.Hourglass False
DoCmd.Echo True

ExitHere:
Exit Sub

HandleError:
Select Case Err.Number
Case 2448
Resume Next
Case Else
MsgBox Err.Description
Resume ExitHere
End Select
End Sub

Private Sub cmdAgilityEntryForm_Click()
On Error Resume Next
Select Case ObtainApplicationType
Case gcAgility
Me!txtNewExisting = "New"
DoCmd.OpenForm "frmAgilityEntry"
' DoCmd.OpenForm "frmUniversalGenericEntry", , , , , ,
"OwnerMaintenance"
Case gcObedience
Me!txtNewExisting = "New"
DoCmd.OpenForm "frmObedienceEntry"
Case gcHerding
Me!txtNewExisting = "New"
DoCmd.OpenForm "frmGenericEntry"
End Select
End Sub

Private Sub cmdApply_Click()
Dim strAddUpdate As String

On Error GoTo Err_cmdApply_Click
If MissingFields Then
MsgBox strmsg, vbOKOnly, "Missing Data"
Me.Controls(strFieldName).SetFocus
Else
If blnAddOwner Then
strAddUpdate = "Added"
Else
strAddUpdate = "Updated"
End If
Select Case strOpeningArgument
Case "UniversalGenericEntry"
strmsg = Me!txtActualOwnerFirstName & " " &
Me!txtActualOwnerLastName & _
" Has been " & strAddUpdate & " in the database. " & _
" Do you want to return to the Entry Form?"
If MsgBox(strmsg, vbYesNo + vbInformation, "Changed") = vbYes Then
DoCmd.Close acForm, "frmOwnerMaintenance", acSaveNo
Forms!frmuniversalgenericentry.Visible = True
End If
Case "DogMaintenance"
strmsg = Me!txtActualOwnerFirstName & " " &
Me!txtActualOwnerLastName & _
" Has been " & strAddUpdate & " in the database. " & _
" Returning to Dog Maintenance"
MsgBox strmsg, vbOKOnly, "Added"
DoCmd.Close acForm, "frmOwnerMaintenance", acSaveNo
Case Else
'save the record
DoCmd.RunCommand acCmdSaveRecord
' DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
strmsg = Me!txtActualOwnerFirstName & " " &
Me!txtActualOwnerLastName & _
" Has been " & strAddUpdate & " in the database. " & _
"Do you want to add or update another Owner?"


If MsgBox(strmsg, vbYesNo + vbInformation, "Changed") = vbYes Then
EnableControls Me, acDetail, False
Me!cboSelectOwner.Enabled = True
Me!cmdAddOwner.Enabled = True
blnAddOwner = False
Me.RecordSource = "qryOwner"
Me!cboSelectOwner.Enabled = True
Me!cboSelectOwner.SetFocus
Me!cboSelectOwner.Value = Null
Me!cmdApply.Enabled = False
Me!cmdCancel.Enabled = False
Me!cboSelectOwner.Requery
Else
DoCmd.Close acForm, "frmOwnerMaintenance"
End If
End Select
End If
Exit_cmdApply_Click:
Exit Sub
Err_cmdApply_Click:
Call Handle_Err(Err.Number, Err.Description,
"OwnerMaintenance-cmdApply_Click")
Resume Exit_cmdApply_Click
End Sub

Private Sub cmdCancel_Click()
Dim strAddUpdate As String

On Error GoTo HandleError
If IsNull(Me!txtActualOwnerFirstName) And
IsNull(Me!txtActualOwnerLastName) Then
Me.Undo
End If
EnableControls Me, acDetail, False
Me!cboSelectOwner.Enabled = True
Me!cmdAddOwner.Enabled = True
Me!cboSelectOwner.Enabled = True
Me!cboSelectOwner.SetFocus
Me!cmdApply.Enabled = False
Me!cmdCancel.Enabled = False
' Me!cboSelectOwner.Requery ' no don't do this

ExitHere:
Exit Sub
HandleError:
Call Handle_Err(Err.Number, Err.Description,
"OwnerMaintenance-cmdCancel_Click")
Resume ExitHere
End Sub

Private Sub cmdClose_Click()
DoCmd.Close
End Sub

Private Sub cmdDeleteThisOwner_Click()
Dim dbs As DAO.Database
Dim strmsg As String
On Error GoTo HandleError
strmsg = "Are you sure? You will delete this Owner and " & _
"all dogs associated with this owner"
If MsgBox(strmsg, vbYesNo + vbExclamation, "About to Delete") = vbYes Then
' Format the message befor the dog is delete. This is required because
' the name is lost once the dog record is deleted.
strmsg = Me!cboSelectOwner.Column(1) & _
" Has been DELETED from the database. " & _
"Do you want to delete another Owner?"
' Delete the owner requested
' cascading deletes in the database will take care of all dogs.
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
' Set dbs = CurrentDb()
' dbs.Execute "DELETE * FROM tblOwner " _
' & "WHERE OwnerID = " & Me!cboSelectOwner.Column(0)
' dbs.Close
' Set dbs = Nothing
Else
MsgBox "Owner not deleted!", vbOKOnly + vbInformation, "That was Close"
End If

Me!cboSelectOwner.Enabled = True
Me!cboSelectOwner.Requery
Me!cboSelectOwner.SetFocus
Me!cmdAddOwner.Enabled = True
EnableControls Me, acDetail, False

ExitHere:
Exit Sub
HandleError:
Call Handle_Err(Err.Number, Err.Description,
"OwnerMaintenance-cmdDelete_Click")
Resume ExitHere
End Sub

Private Sub cmdDeleteSelectedDog_Click()
Dim dbs As DAO.Database
Dim strmsg As String
Dim strcboSelectOwner As String
On Error GoTo HandleError
strmsg = Me!fsubDogList!txtRegisteredName & _
" will be deleted" & vbCrLf & vbCrLf & _
"Are you sure?"
If MsgBox(strmsg, vbYesNo + vbExclamation, "About to Delete") = vbYes Then
' Delete the dog requested
Set dbs = CurrentDb()
dbs.Execute "DELETE * FROM tblDog " _
& "WHERE DogID = " & Me!txtDogID, dbFailOnError
dbs.Close
Set dbs = Nothing
' Format the message befor the dog is delete. This is required because
' the name is lost once the dog record is deleted.
strmsg = Me!fsubDogList!txtRegisteredName & _
" Has been DELETED from the database. " & _
"Do you want to delete another Dog?"
strcboSelectOwner = Me!cboSelectOwner
Me.RecordSource = "qryOwner"
DoCmd.ApplyFilter , "OwnerID = " & strcboSelectOwner
' Me!cboSelectOwner.SetFocus
End If
ExitHere:
Exit Sub
HandleError:
Select Case Err.Number
Case 2427
MsgBox "No dog exists to be deleted", vbOKOnly, "Nothing to delete"
Resume ExitHere
Case Else
Call Handle_Err(Err.Number, Err.Description,
"frmOwnerMaintenance-cmdDeleteSelectedDog_Click")
Resume ExitHere
End Select
End Sub

Private Sub cmdDogEntry_Click()
On Error Resume Next
Select Case ObtainApplicationType
Case gcAgility
Me!txtNewExisting = "Existing"
' DoCmd.OpenForm "frmAgilityEntry"
DoCmd.OpenForm "frmUniversalGenericEntry", , , , , ,
"OwnerMaintenance"
Me.Visible = False
Case gcObedience
Me!txtNewExisting = "Existing"
DoCmd.OpenForm "frmObedienceEntry"
Case gcHerding
Me!txtNewExisting = "Existing"
DoCmd.OpenForm "frmGenericEntry"
End Select
End Sub

Private Sub cmdEditDog_Click()
On Error GoTo HandleError
If Me!txtDogID <> "" Then
' DoCmd.OpenForm "frmDogMaintenance"
DoCmd.OpenForm "frmDogMaintenanceNew", , , "DogID = " & Me!txtDogID, , ,
"OwnerMaintenance"
End If
ExitHere:
Exit Sub
HandleError:
Select Case Err.Number
Case 2427
MsgBox "No Dog Selected" & vbCrLf & vbCrLf & "Please select one", _
vbInformation + vbOKOnly, "No Dog Selected"
Case Else
Call Handle_Err(Err.Number, Err.Description,
"OwnerMaintenance-cboSelectOwner_AfterUpdate")
Resume ExitHere
End Select
End Sub

Private Sub cmdEmailOwner_Click()
Call EmailOwner
End Sub

Private Sub cmdEntryReceived_Click()
Call SendEmailMessage("OwnerMaintenance", "EntryReceived")
End Sub

Private Sub cmdPreliminaryConfirmation_Click()
Call SendEmailMessage("OwnerMaintenance", "PreliminaryConfirmation")
End Sub

Private Sub Form_Open(Cancel As Integer)
' Standard code on open of each form to capture the Help file information
Me.HelpFile = SetHelpFile
Me.HelpContextId = SetHelpContextID(Me)
' end of help file capture


Select Case Me.OpenArgs
Case "UniversalGenericEntry"
strOpeningArgument = "UniversalGenericEntry" ' used to control what
happens when adding a new dog from the entry form
blnAddOwner = True
Me!cmdDeleteThisOwner.Visible = False
Me!cmdDeleteSelectedDog.Visible = False
Me!btnMailingLabel.Visible = False
Me!cmdEditDog.Visible = False
Me!cmdAddOwner.Visible = False
Me!cmdDogEntry.Visible = False
Me!cmdAgilityEntryForm.Visible = False
Me!cmdEntryReceived.Visible = False
Me!cmdPreliminaryConfirmation.Visible = False
Me!cmdEmailOwner.Visible = False
Me!grpEmail.Visible = False
Me!lblEmail.Visible = False
Me!lblSelectOwner.Visible = False
Me!cboSelectOwner.Visible = False
Me!lblOr.Visible = False
Me!cmdApply.Enabled = True
Me!cmdCancel.Visible = True
EnableControls Me, acDetail, True
Me!txtActualOwnerFirstName.SetFocus
DoCmd.GoToRecord , , acNewRec
Me.Caption = "Owner Maintenance - Add"
Case "DogMaintenance"
strOpeningArgument = "DogMaintenance" ' used to control what happens
when adding a new dog from the entry form
blnAddOwner = False
EnableControls Me, acDetail, True
' header
Me!lblSelectOwner.Visible = False
Me!cboSelectOwner.Visible = False
Me!lblOr.Visible = False
Me!cmdAddOwner.Visible = False
Me!cmdApply.Enabled = True
Me!cmdCancel.Visible = False
' detailed
Me!fsubOwnerLastEntered.Visible = False
Me!fsubDogList.Visible = False
Me!cmdDeleteThisOwner.Visible = False
Me!cmdDeleteSelectedDog.Visible = False
Me!btnMailingLabel.Visible = False
Me!cmdEditDog.Visible = False
Me!cmdAddDog.Visible = False
Me!cmdDogEntry.Visible = False
Me!cmdAgilityEntryForm.Visible = False
Me!cmdEntryReceived.Visible = False
Me!cmdPreliminaryConfirmation.Visible = False
Me!cmdEmailOwner.Visible = False
Me!grpEmail.Visible = False
Me!lblEmail.Visible = False
' grpCountry_AfterUpdate
Me!txtActualOwnerFirstName.SetFocus
Select Case Get_Priv_Property("DogMaintenanceNew")
Case "SubForm"
Me.RecordSource = "Select * from tblOwner where OwnerID = " & _
Forms!frmuniversalgenericentry!frmDogMaintenanceNew!cboOwnerID.Value
If Get_Priv_Property("NotInListOwner") = "On" Then
Me!txtOwnerStreet.SetFocus ' reset focus if adding a brand new
owner. I know that
' because the form is a subform and
the edit button is not
' visible
Else
Me!txtActualOwnerFirstName.SetFocus
End If
Case "Form"
Me.RecordSource = "Select * from tblOwner where OwnerID = " & _
Forms!frmDogMaintenanceNew!cboOwnerID.Value
End Select
If Me!grpCountry = 0 Then
Select Case ObtainOrganization
Case "CKC"
Me!grpCountry.Value = 1
Case "AKC"
Me!grpCountry.Value = 2
End Select
End If

Case Else
strOpeningArgument = "" ' used to control what happens when adding a
new dog from the entry form
Me!cmdDeleteThisOwner.Visible = True
Me!cmdDeleteSelectedDog.Visible = True
Me!btnMailingLabel.Visible = True
Me!cmdEditDog.Visible = True
Me!cmdAddOwner.Visible = True
Me!cmdDogEntry.Visible = True
Me!cmdAddDog.Visible = True
Me!cmdAgilityEntryForm.Visible = False ' no longer show this button
Me!cmdEntryReceived.Visible = True
Me!cmdPreliminaryConfirmation.Visible = True
Me!cmdEmailOwner.Visible = True
Me!grpEmail.Visible = True
Me!lblEmail.Visible = True
Me!cmdAddOwner.Visible = True
Me!lblSelectOwner.Visible = True
Me!cboSelectOwner.Visible = True
Me!lblOr.Visible = True
Me!fsubDogList.Visible = True
Me!fsubOwnerLastEntered.Visible = True
Me!cmdApply.Enabled = False
Me!cmdCancel.Enabled = False
blnAddOwner = False
Me!cboSelectOwner.SetFocus
EnableControls Me, acDetail, False
End Select

Call grpCountry_AfterUpdate

' Set status bar to say nothing
SysCmd acSysCmdSetStatus, " "

End Sub

Private Sub grpCountry_AfterUpdate()
' converted to 5.0
' This code is activated after a change to the country USA or Canada
' The code changes the properties for state/province and zip/postal
' and requeries both based on the request.
Select Case Me!grpCountry
Case 1
Me!lblState.Caption = "Province"
Me!cboOwnerStateCD.RowSource = "qryProvinces"
Me!lblZip.Caption = "Postal"
Me!txtOwnerZip.InputMask = "&&&\ &&&"
Me!grpCountry.DefaultValue = 1
Case 2
Me!lblState.Caption = "State"
Me!cboOwnerStateCD.RowSource = "qryStates"
Me!lblZip.Caption = "Zip"
Me!txtOwnerZip.InputMask = "00000\-9999"
Me!grpCountry.DefaultValue = 2
End Select
End Sub

Private Sub txtActualOwnerFirstName_AfterUpdate()
If blnAddOwner Then
Me!fsubDogList.Enabled = True
Me!cmdAddDog.Visible = True
Me!cmdAddDog.Enabled = True
Me!cmdDeleteSelectedDog.Enabled = True
Me!btnMailingLabel.Enabled = True
Me!cmdDogEntry.Enabled = True
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

Private Sub txtActualOwnerLastName_AfterUpdate()
If blnAddOwner Then
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

Function MissingFields() As Boolean
MissingFields = False
strmsg = ""
strFieldName = ""
If IsNull(Me!txtActualOwnerFirstName) Then
strmsg = " First Name is Required" & vbCrLf
strFieldName = "txtActualOwnerFirstName"
MissingFields = True
End If
If IsNull(Me!txtActualOwnerLastName) Then
strmsg = strmsg & " Last Name is Required" & vbCrLf
If strFieldName = "" Then
strFieldName = "txtActualOwnerLastName"
MissingFields = True
End If
End If
End Function

Here's the error handler

Option Compare Database
Option Explicit

'standard error handler that all procedures call.
'Specific error handling is done by each procedure to trap expected errors.
'MCK
'10/19/98
Public Sub Handle_Err(Err_Num As Integer, _
Err_Descript As String, _
Err_Procedure As String, _
Optional err_line As String)
Dim str_Message As String
Dim str_Title As String
Dim rst As DAO.Recordset

On Error GoTo HandleError

Set rst = CurrentDb.OpenRecordset("tblLogError")
rst.MoveLast
Do While rst.RecordCount >= 1000
rst.Delete
rst.MoveLast
Loop
rst.MoveFirst
With rst
.AddNew
!ErrNumber = Err_Num
!ErrDescription = Left(Err_Descript, 255)
!ErrDate = Now()
!CallingProc = Err_Procedure
!UserName = CurrentUser()
.Update
End With
rst.Close

str_Message = "In Procedure: " & Err_Procedure & vbCrLf & vbCrLf
str_Message = str_Message & "The following Error has Occurred: " &
vbCrLf
str_Message = str_Message & Err_Descript & "(" & Err_Num & ")"
str_Message = str_Message & vbCrLf & vbCrLf
If err_line <> "" Then
str_Message = str_Message & "after line number " & err_line
End If
str_Title = gcProgramTitle & " Error"
MsgBox str_Message, vbCritical + vbOKOnly, str_Title

ExitHere:
On Error Resume Next
rst.Close
Set rst = Nothing
Exit Sub

HandleError:
Select Case Err.Number
Case 3021
Resume Next
Case Else
str_Message = "An unexpected situation arose in the program." & vbCrLf
& _
"Please write down the following details:" & vbCrLf &
vbCrLf & _
"Calling Proc: " & Err_Procedure & vbCrLf & _
"Error Number " & Err_Num & vbCrLf & " " &
Err_Descript & _
vbCrLf & vbCrLf & "Unable to record error because
Error " & Err.Number & _
" " & Err.Description
End Select
MsgBox str_Message, vbCritical, "Handle Error"
Resume ExitHere
End Sub



--
Rick Allison

Are the "add" and "cancel" buttons in the same section of the main form?

Is the "grpCountry" control bound to a field in the form's RecordSource? -
Yes
What is the code that runs on the AfterUpdate event for the "grpCountry"
control? I don't see any "Me.Undo" code step in the cancel button's code?
I took the me.undo off.

Your description of what works and what doesn't work suggests that an
error is occurring in the Cancel button when you click it during the
second cycle. I see you have an error handler in that code; does it get
called at all during your sequence?

No - error routine never fires

What is the code for the Handle_Err subroutine?

I have found that uncleared errors from a called function or subroutine
can cause an error for the calling code that does not trip the calling
code's error handler, and the code silently fails. This may be what you're
seeing as well.

Another source of a silent failure has been that code on another control
raises an error that prevents the code from continuing -- e.g., trying to
change the value of a control during its BeforeUpdate event, trying to
change the focus from a control and code in its LostFocus or Exit event
fails, trying to change focus to a control and code in its GotFocus or
Enter event fails, etc.

So, it may be necessary for you to post all the code in the form's module
and the external subs/functions that are called. Let's see what's being
done.

 

[Ken Snell \(MVP\)]

I've clipped the code that you posted in order to shorten this reply.....

(1) I'm not understanding this part of your code in the "cancel" button's
procedure:

' code excerpt start
Private Sub cmdCancel_Click()
Dim strAddUpdate As String

On Error GoTo HandleError
If IsNull(Me!txtActualOwnerFirstName) And
IsNull(Me!txtActualOwnerLastName) Then
Me.Undo
End If
' code excerpt end


If you want to cancel the record's addition, why would you undo the record
only if no entries have been made in txtActualOwnerFirstName and in
txtActualOwnerLastName? Would you not want to undo in every situation --
isn't that why you're clicking the cancel button?


(2) I don't think it affects what's happening here, but it's good coding
practice to always explicitly identify the object that you're closing when
doing a DoCmd.Close. If the focus changes for whatever reason before the
code executes, the active object will be closed. So it's best to change your
"close" button's code to this:

Private Sub cmdClose_Click()
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub


(3) Although you may not be having problems, the form's Open event often
occurs too early for using it to set focus to controls and to manipulate the
form's data (e.g., moving to a new record). Better to use the form's Load
event, which occurs after the Open event.


(4) You're starting a new record in the form's Open event when OpenArgs is
the "UniversalGenericEntry" case. If you then click the Add button, you'll
save that new record and move to another new record. That first record will
not be undone by the cancel button because it was already saved in order to
move to the new record by the add button.


(5) In the Open event for the form, you also have code that resets the
RecordSource of the form.


(6) Your "cancel" button's code needs to reset blnAddOwner variable back to
False. It currently does not do that.


(7) In the txtActualOwnerFirstName_AfterUpdate and
txtActualOwnerLastName_AfterUpdate procedures, you explicitly save the
record if blnAddOwner is True. However, your Cancel button's code never sets
blnAddOwner back to False, so is it possible for one of these events to be
run for a new record when you didn't mean for the code to run?


(8) When you say a blank record is added to the database, do I understand
that your table has a primary key (autonumber field) and other fields, and
that the primary key field has a value and the other fields do not? Are
those other fields actually "empty" (contain a Null value), or do they
contain an empty string ("") value? Is the blank record in the table that is
the main form's recordsource data source, or is the blank record in one of
the subforms' recordsource data source tables? When you have clicked the
cancel button for the second time, does the focus go to the cboSelectOwner
control correctly when the code "ends"? Or is the focus still on the cancel
button?