closing a message box

[Rockape]

Hi,
grateful for advice:

1. I have the following code which is assigned to the beforeupdate event on the form.
2. I also have a button on this form which runs a macro which closes this form and saves the data.

The routine is as follows:

I populate several fields within the form. I then press the button which checks all the fields and if any field is left unpopulated then a message box highlights the missing fields. If all the fields are populated the record is saved.

My purpose:

I would like the following to happen:
If I press YES the msg box would close and I would return to the unfinished form (for editing)
If I press NO the msg box would close the form and the entries would not be saved.

However:

My problem is that when the message box pops up with the missing fields and I either press yes "I get a runtime error 2501". If i press no the routine works, i.e. the message box and the form close.


The code is :
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim blnError As Boolean
Dim testmsg As Integer

If Nz(Me.add1, "") = "" Then
blnError = True
strMsg = strMsg & "Address1" & vbCrLf
End If

If Nz(Me.add2, "") = "" Then
blnError = True
strMsg = strMsg & "Address2" & vbCrLf
End If

If Nz(Me.DOB, "") = "" Then
blnError = True
strMsg = strMsg & "Date of Birth" & vbCrLf
End If

If Nz(Me.Tel_No, "") = "" Then
blnError = True
strMsg = strMsg & "Telephone Number" & vbCrLf
End If

If blnError Then
strMsg = "YOU ARE MISSING: " & vbCrLf & strMsg & vbCrLf & "Please fix them..."
testmsg = MsgBox(strMsg, vbYesNo, "Errors Exist")
End If

If testmsg = vbYes Then
DoCmd.Close
End If

If testmsg = vbNo Then
DoCmd.RunMacro ("close newholder")
End If

End Sub


Cheers

 

[HiTechCoach]

To stop the update from happening, you set events parameter for Canecl liek this:

Cancel = True

I do not see that anywhere in your code.

I use something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

 Cancel = False


' perform data validation
If IsNull(Me.CompanyName) Then

   MsgBox "You must enter a Company Name.", vbCritical, "Data entry error..."
   
   ' highlight the form
   Me.CompanyName.BorderColor = vbRed   ' this is reset in the control's  events
   DoCmd.GoToControl "CompanyName"
      
   Cancel = True


End If


If Not Cancel Then
  ' passed the validation process

    If Me.NewRecord Then
        If MsgBox("Data will be saved, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
            Cancel = True
        Else
            ' run code for new record before saving
        
        End If
    
    
    Else
        If MsgBox("Data will be modified, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
            Cancel = True
        Else
           ' run code before an existing record is saved
           ' example: update date last modified
            
        End If
    End If

End If


' if the save has been canceled or did not pass the validation , then ask to Undo changes
If Cancel Then

    If MsgBox("Do you want to undo all changes?", vbYesNo, "Confirm") = vbYes Then
        Me.Undo

    End If
    
End If

End Sub

 

[Rockape]

Hi,

Thanks for your reply..

By clicking on the Yes button (ie i want to fix the errors), i just want to close the message box and continue with the process of populating the missing fields.

Regards

 

[HiTechCoach]

By clicking on the Yes button (ie i want to fix the errors), i just want to close the message box and continue with the process of populating the missing fields.

To do what you want using the Before Update event, you set Cancel = True. This stops the save from happening so that you can go back to the form and "continue with the process of populating the missing fields". This is exactly what my code example does.

Using Access's form events:

1) enter data ob a form. Use the control's After update events to validate data

2) initial a record save. There are multiple weasy this can happen.

3) Use the form's Before update event to before dat validation. This event is used because it has a Cancel parameter

Private Sub Form_BeforeUpdate([b]Cancel[/b] As Integer)

3a) If the BeforeUpdate event's Cancel parameter is set to True then the save record is "canceled" and the user is retrun back to the form to make additional data entry.

If the event has been canceled, then you can ask the user is they want to undo (Me.Undo) the changes.


3b) If BeforeUpdate event's Cancel parameter is False then the record is saved into the table.



Hope this helps ...

 

[Rockape]

hi,

Many thanks


I'll work with your advice. Much appreciated.

Regards

 

[Rockape]

this is my final code:

If blnError Then
strMsg = "YOU ARE MISSING: " & vbCrLf & strMsg & vbCrLf & "Please fix them..."
testmsg = MsgBox(strMsg, , "Errors Exist")
Cancel = True
Else: DoCmd.close acForm, "newnominator"
End If

and still the underlying form will close when i press Yes.

Regards