Error-trapping Forms

[Dudley]

For the life of me I can't get my error-trapping and record-saving to work. I've been searching the forum and the 'net and just failing to get it.

There are two relevant forms, a Client Data form and a Data form. The Client Data form includes a subform that shows an abbreviated version of the data entered on the Data form and a button calling the Data form. The process of calling the Data form from the Client form enters the primary key of the client in the Data form. (I removed all the other fields on the Data form, and set up default data entries to focus on this error/saving problem.)

The Data form has a Cancel button, a Save button, and a Client Data button, which closes the form and returns to the Client form. There are several required fields in the table for the Data form, so I have a fnValidateForm (in Module1) that ensures that the required fields all have entries. There is also a unique index on the ClientID and the Date to prevent duplicate entries. (I'm about to make a second entry to trouble-shoot the error-trapping for this, as well.) The purposes of the buttons are self-explanatory, I think. Cancel causes Me.Undo, Save runs DoCmd.RunCommand acCmdSaveRecord, and Client Data is supposed to open the other form, updated with the Data entry just created. But something is going wrong and often the record is not actually saved.

The problem is in BeforeUpdate and has to do with the "acCmdSaveRecord command not being available now" and I just can't figure it out. Here's the code in BeforeUpdate:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim response As Variant
    
    'Verify that data are entered in all Required fields
    If Me.Dirty Then
        If Not fnValidateForm(Me) Then Exit Sub
    End If
    
    Select Case MsgBox("This record contains changes.  Do you want to save your changes to the current record?" & vbCrLf & "  Yes:         Saves Changes" & vbCrLf & "  No:          Undo Changes" & vbCrLf, vbYesNo + vbQuestion, "Save Your Changes?")
        Case vbYes: 'Save the changes
            response = MsgBox("Are you certain that you want to save your changes to this record?  If you choose 'No' your changes will be erased.", vbYesNo + vbQuestion, "Verify Changes")
                If response = vbYes Then
                    If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord   'error 2115 on the docmd statement
                    MsgBox "Your changes have been saved.", vbOKOnly + vbInformation, "Changes Saved"
                Else
                    Me.Undo
                    If Me.Dirty = False Then MsgBox "Your changes have been erased.", vbOKOnly + vbInformation, "Changes Erased"
                End If
        Case vbNo: 'Undo changes
            Me.Undo
            If Me.Dirty = False Then MsgBox "Your changes have been erased.", vbOKOnly + vbInformation, "Changes Erased"
        Case Else: 'Default case to trap any errors
            'Do nothing
    End Select
End Sub

When this code is activated by the Client Data button, I get error 2115 - The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Access from saving the data in the field. Only one field has a validation rule, and that's getting set properly (strTypeOfVisit). The highlighted code in Before Update is the DoCmd.RunCommand acCmdSaveRecord text.

I've tried just leaving the code under If vbYes blank, since I found one of Pat Hartman's entries talking about how good Access is at saving records when forms are closed, but that wasn't reliable. I tried putting a "your record was saved" msgbox under the form's AfterUpdate event, and that seemed to work for some reason - obviously I am absolutely missing the relevant variable here.

Oh, before you ask, they really really want all those nutty prompts about "do you really want to save???".

Can anyone help me figure this out??

 

[RuralGuy]

Without looking at it closer, you can *not* save records in the BeforeUpdate event of the form. You are in the event precisely *because* you are in the process of saving a record!

 

[boblarson]

Without looking at it closer, you can *not* save records in the BeforeUpdate event of the form. You are in the event precisely *because* you are in the process of saving a record!

I was just going to say that. If you want it to "save" then just don't do anything. If you don't, set Cancel = True.

 

[Dudley]

RG: Oh. That explains it. Thanks.

Both of you: I had it blank for a while, but sometimes the record isn't saved and I don't get an error message about it - it just doesn't show up. So I think my error-handling on the form is messed up. Why would it not save and not give a message?

 

[boblarson]

Well, I think there's a lot going on that is contributing to things. You're not really using error handling, you are manipulating things based on the validation and, it may be more efficient to your situation for you to validate the fields yourself instead of relying on a generic validation code. I know that is much more code but it also gives you more control to watch what is happening when and why.

Also, you don't need to test for If Me.Dirty inside of the Before Update event of the form. If it wasn't dirty it wouldn't fire the event.

Also, I'm a bit confused as to what you are trying to accomplish and why you are doing it this way. For example, when you click on the ADD DATA button in the Client form, it brings up the LSP Data form. But, there's no way to go to a new record. There's more but I'm not into typing a lot tonight.

There's really

 

[unclejoe]

Hi Dudley,

It seems that the line,

If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord   'error 2115 on the docmd statement

Try replacing it to "DoCmd.RunCommand acCmdSave".

I have replaced it and so far it's working.

 

[Dudley]

Hi UncleJoe, Thanks for your suggestion. That one saves the changes to the form as well as the record, doesn't it? I think that's why I wasn't doing it. But I'll try it. I can't remember if I'm actually changing the form anywhere, so if I'm not, this might be the ticket. Thanks!!

 

[boblarson]

Hi UncleJoe, Thanks for your suggestion. That one saves the changes to the form as well as the record, doesn't it? I think that's why I wasn't doing it. But I'll try it. I can't remember if I'm actually changing the form anywhere, so if I'm not, this might be the ticket. Thanks!!

Yes, it saves DESIGN CHANGES, not records. DoCmd.RunCommand acCmdSaveRecord is what you would use to save records, but in this case you should NOT be using it, because as mentioned before you don't need to try to force a save in the form's Before Update; in fact it logically should be impossible as it is already in the process of saving the record.

 

[Dudley]

Thanks Bob. I won't go there. Actually, I've been working on an alternative route. I'm putting all the data validation code into a function that I can call from form_BeforeUpdate as well as the Buttons on the form. I think I'm making progress. Thanks for being patient.

 

[unclejoe]

Hi Dudley,

Yes, that's right. I might be wrong, but I believe the problem lies at this subroutine "GetControlNameByTabOrder". This code is changing the control's Tab Index property. This is one area you should start looking at.

Hi UncleJoe, Thanks for your suggestion. That one saves the changes to the form as well as the record, doesn't it? I think that's why I wasn't doing it. But I'll try it. I can't remember if I'm actually changing the form anywhere, so if I'm not, this might be the ticket. Thanks!!

 

[Dudley]

Uncle Joe, Someone on the forum (sorry to have forgotten your name!) gave me that code so that fnValidateForm (Rich's code?) would return blank required fields in the tab order instead of jumping all over the form. I don't fully understand it, but I thought it wasn't changing the tab order but essentially making some kind of array that held the fields in tab order so fnValidateForm would then run through the array and return problems in tab order.

 

[unclejoe]

Hi Dudley,

Yes, you're right. I notice there is a function where you set the background of a control and it is on the OnGotFocus, this might be the cause.

Uncle Joe, Someone on the forum (sorry to have forgotten your name!) gave me that code so that fnValidateForm (Rich's code?) would return blank required fields in the tab order instead of jumping all over the form. I don't fully understand it, but I thought it wasn't changing the tab order but essentially making some kind of array that held the fields in tab order so fnValidateForm would then run through the array and return problems in tab order.

 

[Dudley]

Getting There

Hi. Thanks to everybody who has been helping me. Wanted to show you where I'm going. Seems to be getting there.

I moved the validation code from form_BeforeUpdate to a function that comes back either True (save data), False (Delete data) or Null (Data need more work). Here's the function:

Public Function VerifyFormData()
    Dim response As Variant

'can a function have 3 states?  yes (good to save); no (delete record), null (need more work)?
    
    Select Case MsgBox("Do you want to save your changes to this record?" & vbCrLf & "  Yes:         Saves Changes" & vbCrLf & "  No:          Undo Changes" & vbCrLf, vbYesNo + vbQuestion, "Save Your Changes?")
        Case vbYes: 'Save the changes
            response = MsgBox("Are you certain that you want to save your changes?  If you choose 'No' you will be returned to the form to continue.", vbYesNo + vbQuestion, "Verify Changes")
                If response = vbYes Then
                    'Verify that data are entered in all Required fields
                    If CancelDueToDupLSP(Me!DateOfLSP, Me.Name) Then
                        Me.DateOfLSP.SetFocus
                        VerifyFormData = Null   'needs more work
                        Exit Function
                    End If
                    If Not fnValidateForm(Me) Then Exit Function
                    VerifyFormData = True   'good to save
                    Exit Function
                    'Once fields are validated, do nothing, record will be saved as form closes
                Else
                    VerifyFormData = Null  'needs more work
                End If
        Case vbNo: 'Undo changes
            response = MsgBox("Are you certain that you want to delete your changes?  If you choose 'Yes' your changes will be erased.", vbYesNo + vbQuestion, "Verify Delete Changes")
                If response = vbYes Then
                    VerifyFormData = False  'Data need to be deleted
                Else
                    VerifyFormData = Null   'need more work
                End If
        Case Else: 'Default case to trap any errors
            'Do nothing
    End Select

End Function

Then, I call the function from the buttons the user has available. Here's an example:

    Dim stLinkCriteria As String
    
    'Verify that data are entered in all Required fields and the date is ok
    Select Case VerifyFormData
        Case True   'save and move on
            stLinkCriteria = "[lngClientID]=" & lngCLIENT
            DoCmd.Close acForm, Me.Name
            DoCmd.OpenForm "frmClients", , , stLinkCriteria
        Case False  'delete data and close form
            Me.Undo
            Me.cboCID = lngCLIENT
            stLinkCriteria = "[lngClientID]=" & lngCLIENT
            DoCmd.Close acForm, Me.Name
            DoCmd.OpenForm "frmClients", , , stLinkCriteria
        Case Null   'continue working with data
            MsgBox "Your data have not been saved yet.", vbOKOnly + vbInformation, "Save Needed"
    End Select

lngCLIENT is a form variable set when the form opens or the client changes.

It all seems to be working. I need to check it out a bit more. I'm worried about the redundancy of the Select Case statements in each button, but I haven't figured out a more efficient approach yet.

Any ideas are welcome!!!