Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / April 2006

Tip: Looking for answers? Try searching our database.

DoCmd.GoToRecord , , acNewRec

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Rick A - 25 Apr 2006 23:08 GMT
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,

Signature

Rick

Ken Snell (MVP) - 26 Apr 2006 02:44 GMT
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.
Signature


       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,

Signature

Rick

Rick Allison - 26 Apr 2006 12:47 GMT
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.

Signature

Rick Allison

> 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
[quoted text clipped - 17 lines]
>
> Thanks,
Ken Snell (MVP) - 27 Apr 2006 04:28 GMT
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.
Signature


       Ken Snell
<MS ACCESS MVP>

> Ken,
>
[quoted text clipped - 109 lines]
>>
>> Thanks,
Rick A - 28 Apr 2006 15:32 GMT
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

Signature

Rick Allison

> Are the "add" and "cancel" buttons in the same section of the main form?
>
[quoted text clipped - 8 lines]
> 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
[quoted text clipped - 129 lines]
>>>
>>> Thanks,
Ken Snell (MVP) - 30 Apr 2006 19:47 GMT
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?
Signature


       Ken Snell
<MS ACCESS MVP>

> Here's the code...

< snipped >

>> Are the "add" and "cancel" buttons in the same section of the main form?
>>
[quoted text clipped - 151 lines]
>>>>
>>>> Thanks,
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.