Cmd button to close doesn't trigger error handler

andy_dyer

andy_dyer

Registered User.
Local time
Today, 19:37
Joined
Jul 2, 2003
Messages
806
Hi,

I have some required fields, and trying to close the form once it has been dirtied and these fields are not complete should generate my nice error message from within my error handler...

Code: 
Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3314 Or DataErr = 3316 Or DataErr = 3317 Then
MsgBox "The Client Name, Project Number and Project Title are all Required - you must supply a value for these fields", vbInformation
Response = acDataErrContinue
End If
End Sub

However when i click my command button to close the form and return to the main menu I get the crazy talk Access Run Time Error Message 3317 (which you can see from my handler should be captured) where it invites me to debug etc which would scare the crap out of my users...

My command button code is simply:

Code: 
Private Sub cmdReturntoMenu_Click()
Me.Refresh
DoCmd.Close
DoCmd.OpenForm "frmMainMenu"

End Sub

However I've found that if I leave my close button enabled in the top right corner of my form window then if this is clicked it triggers my message instead and then if I try to push through it states it won't be able to save the entry...

I don't know if there is something wrong with my handler or the code on the command button but it certainly isn't doing what I want it too...

Any ideas would be gratefully received!!
 
Your close button probably needs to save the data.

try:

Code: 
Private Sub cmdReturntoMenu_Click()

' save any changes
If Me.Dirty Then Me.Dirty = False

DoCmd.Close acForm, Me.Name   ' Changed to be sure to close only this form
DoCmd.OpenForm "frmMainMenu"

End Sub


I would also use the form's Before Update event to validate the data before the error is generated. This will allow you top cancel the record save.

Example:
Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)



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

   MsgBox "You must enter a Company Name.", vbCritical, "Data entry error..."
   Me.CompanyName.BorderColor = vbRed
   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
 
Afternoon

I am far from an expert but the way I get around required fields is to create a hidden textbox for each required field & then assign a value to the textbox dependant on the required field;

Private Sub The Client Name_AfterUpdate()
If Len(Me.The Client Name & vbNullString) = 0 Then
TextBox = 0
Else
TextBox = 1
End If
End Sub

Then I create a final hidden textbox which adds the values of the required field textboxes, I then use this textbox value in an if statement to do what I require;

Private Sub cmdReturntoMenu_Click()
If Me!TextBoxTotal < 3 Then
MsgBox "The Client Name, Project Number and Project Title are all Required - you must supply a value for these fields", vbInformation
Response = acDataErrContinue
Else
DoCmd.Close
DoCmd.OpenForm "frmMainMenu"
End If
End Sub


There is probably a much more professional way which is less longwinded, but I am learning & this method works.

Hope it helps

Freddy
 
Thanks Guys...

I'm trying to go with the method suggested by HiTechCoach and have this as my before update code:

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)

' perform data validation
If IsNull(Me.Client) Then
   MsgBox "You must select a Client Name.", vbCritical, "Data entry error..."
   Me.Client.BackColor = vbRed
   DoCmd.GoToControl "Client"
      
   Cancel = True

ElseIf IsNull(Me.ProjectNumber) Then
   MsgBox "You must select a Project Number.", vbCritical, "Data entry error..."
   Me.ProjectNumber.BackColor = vbRed
   Me.ProjectNumber.SetFocus
      
   Cancel = True

ElseIf IsNull(Me.ProjectTitle) Then
   MsgBox "You must select a Project Title.", vbCritical, "Data entry error..."
   Me.ProjectTitle.BackColor = vbRed
   DoCmd.GoToControl "ProjectTitle"
      
   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
    Else

    End If
    
End If

End Sub

My problem is that I need to be able to give people a chance to fill in the details and then undo the entry if they choose to...

At present without one of these fields it pops up the error, then it immediately moves to do you want to undo all changes - then even if i click no it still exits the form...

I want but don't know how to amend my code to say if one of the three fields is null then flag it and ask if they want to go back and input or exit...

If they choose exit then warn them all changes will be lost, if they choose not to exit it should just set the focus back on the first missing field...

Can this be done??

Thanks to you both for your help!!
 
I'm still stuck on my before update code as above - if anyone can help I really want to fix this issue and hand the database over to user testing...

Last bug...

For now at least!!

:)
 
you need to use the FORM update event to do this validation

something like the following

THE RED LINES are critical - by setting update to TRUE you are telling access not to accept the values. This then cancels the event, and will also stop the form closing - so your users can deal with the invalid entries.

You can get smarter with this, but this is the basic functionality

Code: 
sub form update (cancel as integer)

if nz(requiredtextfield,"")="" then
  msgbox("field 1 not filled in")
  [COLOR="red"]cancel = true[/COLOR]
  [COLOR="red"]exit sub[/COLOR]
end if

if nz(requiredvalue,0)=0 then
  msgbox("field 2 not filled in")
  [COLOR="Red"]cancel = true[/COLOR]
  [COLOR="red"]exit sub[/COLOR]
end if
etc

end sub
 
Hi Dave,

Thanks for coming back to me - I've made the changes but it hasn't improved much...

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)

' perform data validation
If IsNull(Me.Client) Then
   MsgBox "You must select a Client Name.", vbCritical, "Data entry error..."
   Me.Client.BackColor = vbRed
   DoCmd.GoToControl "Client"
      
   Cancel = True
   Exit Sub

ElseIf IsNull(Me.ProjectNumber) Then
   MsgBox "You must select a Project Number.", vbCritical, "Data entry error..."
   Me.ProjectNumber.BackColor = vbRed
   Me.ProjectNumber.SetFocus
      
   Cancel = True
   Exit Sub

ElseIf IsNull(Me.ProjectTitle) Then
   MsgBox "You must select a Project Title.", vbCritical, "Data entry error..."
   Me.ProjectTitle.BackColor = vbRed
   DoCmd.GoToControl "ProjectTitle"
      
   Cancel = True
   Exit Sub

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
    Else

    End If
    
End If

End Sub

Now if I just choose the client but not the project number it gives me the error message tellign me that I must select a project number I click my one message box button "ok" and it closes the form... I need it to not do this and give me a chance to take me back to the form to input and only exit after warning me that data will be lost...

Any further ideas?
 
You must log in or register to reply here.

Similar threads

Gigitty
Never seen this error linking a form
Replies
8
Views
390
Gigitty
Gigitty
B
Solved How do I set the focus to a component on the main from an Error Event of a subform
Replies
4
Views
1,132
bmaccess
B
riktek
Closing Form Instances
Replies
15
Views
1,622
MarkK
MarkK
W
How to Populate a Control in Code and Make it Behave As Though Populated in the GUI
Replies
10
Views
517
arnelgp
arnelgp
J
code to close my current form on pressing the button to open my Main Menu
Replies
16
Views
2,380
Edgar_
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom