MS Access Forum / Forms Programming / April 2006
DoCmd.GoToRecord , , acNewRec
|
|
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,
|
|
|