Form coding question

[Guest]

Hi everyone,
I am just setting some rules for my form via code and was in need of some
advise/help. I have coded the following:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.First_Name) Then
MsgBox "Please enter the your first name", vbCritical
Cancel = True
First_Name.SetFocus
End If
End Sub
If I wanted to add addional fields to check if they are empty before the
form closes let say received date and zipcode, how would the code change?
Any help is apprecieted.

 

[Guest]

Would i just put something like the following?:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.First_Name) Then
MsgBox "Please enter the first name ", vbCritical
Cancel = True
First_Name.SetFocus
End If
If IsNull(Me.ReceivedDate) Then
MsgBox "Please enter the received date ", vbCritical
Cancel = True
ReceivedDate.SetFocus
End If
End Sub
When i put this code in it runs for both fields but then i get a message
popup The doMenuItem action was canceled.

 

[BruceM]

One option is to use ElseIf:

If IsNull(Me.First_Name) Then
MsgBox "Please enter the first name ", vbCritical
Cancel = True
First_Name.SetFocus
ElseIf IsNull(Me.ReceivedDate) Then
MsgBox "Please enter the received date ", vbCritical
Cancel = True
ReceivedDate.SetFocus
End If

Depending on the details of your project you may be able to use some text
strings in several places. For instance:

Dim strPE as string
strPE = "Please enter the "

If IsNull(Me.First_Name) Then
MsgBox strPE & "first name", vbCritical
Cancel = True
First_Name.SetFocus
ElseIf IsNull(Me.ReceivedDate) Then
MsgBox strPE & "received date ", vbCritical
Cancel = True
ReceivedDate.SetFocus
End If

It may not make much sense to use a named string for those few words, but it
may give an idea of how to simplify the code in some cases. Again, the
details of validation tend to be project-specific.

 

[Guest]

Bruce
Thanks for your input. I am getting the following message after click the
command button that saves:
popup The doMenuItem action was canceled
Is there a way to get rid of this message? I first get my message to enter
received date. I click ok and then the domenuitem message pops up. If i
save via closing the form i do not get this message so i know it has
something to do with the command button. The command button was created via
the wizard.

 

[BruceM]

I am leaving for the day, and will not be able to respond further until
Monday, but I will mention a few things. What is your version of Access?
What is the code behind the command button? It sounds as if it is something
the wizard created. If the intent is to save the record, all you need is:
Me.Dirty = False
for the command button's Click event. If more is needed, please provide
details.
Saving the record (which also happens when you attempt to navigate to
another record, move to a subform, close the database, and several other
things) will cause the Before Update event to run. It occurs to me that you
should probably do Cancel = True last, and that you should use a slightly
different syntax for the name of the control. Also, it is a good idea to
give the text box bound to a field a different name than the field itself.
Even if Access doesn't get confused, you may. I use txtReceivedDate as the
name of a text box bound to the ReceivedDate field. There are other
choices, but I think the main thing is to find something that makes sense to
you. Here is a modified syntax you could try:

If IsNull(Me.First_Name) Then
MsgBox "Please enter the first name ", vbCritical
Me.txtFirst_Name.SetFocus
Cancel = True
ElseIf IsNull(Me.ReceivedDate) Then
MsgBox "Please enter the received date ", vbCritical
Me.txtReceivedDate.SetFocus
Cancel = True
End If

 

[geppo]

Ciao "scott04" <[email protected]> ha scritto nel messaggio



Private Sub Comando11_Click()
On Error GoTo Err_Comando11_Click


.................................
..................................
..................................



Exit_Comando11_Click:
Exit Sub

Err_Comando11_Click:
MsgBox Err.Description ------------------------------> this is the
guilty one
Resume Exit_Comando11_Click

End Sub

Bruce
Thanks for your input. I am getting the following message after click the
command button that saves:
popup The doMenuItem action was canceled
Is there a way to get rid of this message? I first get my message to
enter
received date. I click ok and then the domenuitem message pops up. If i
save via closing the form i do not get this message so i know it has
something to do with the command button. The command button was created
via
the wizard.

----cut----

 

[Guest]

Thanks Bruce. My verison i am using is 2003. The code for the click is:
Private Sub Save_Click()
On Error GoTo Err_Save_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_Save_Click:
Exit Sub
Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click
End Sub
Let me know if i should use something different in the click event. The
code for the click of me.dirty = False works if i am just saving but if one
of my fields is empty and the error pops up a get a debugger error.

 

[BruceM]

Use something like this instead of MsgBox Err.Description:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & _
"in Save_Click"
Use it for all code, except change Save_Click to the event in which the code
occurs. That way if there is an error you will be able to identify its
source.
You should be able to use Me.Dirty = False instead of DoCmd.DoMenuItem...
If you are getting an error message when a required field is blank, it seems
the Before Update code is running, but something is triggering an error
rather than canceling when a field is blank. Be sure there is error
handling in the Before Update event. Also, open the VBA editor and click
Tools > Options. Click the General tab, and be sure that Break on Unhandled
Errors is selected in Error Trapping.
When providing information about an error it is important that you mention
the error number and message, the event in which the error occurs, and the
code that triggers the error. The information you have provided is too
general to allow for useful suggestions about its cause and cure.

 

[Guest]

Bruce,
Thanks for suggestion. The error message i receive after the code runs is:
Error 2501 (The Domenuitem action was cancelled) in Save_Click. Any idea on
how i can get around this popup?
Scott

 

[Guest]

When i use the code:
Private Sub Save_Click()
On Error GoTo Err_Save_Click
Me.Dirty = False
Exit_Save_Click:
Exit Sub
Err_Save_Click:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & _
"in Save_Click"
Resume Exit_Save_Click
End Sub

and leave one of my required fields blank i receive the message to enter in
my required field. When i hit ok, it then enters in the error "Error 2101
(The setting you entered isnt valid for this property) in Save_Click." Any
ideas is appreciated. Thanks

 

[Guest]

I changed the code to:
Private Sub saves_Click()
On Error GoTo Err_saves_Click
Me.Dirty = False
Exit_saves_Click:
Exit Sub
Err_saves_Click:
If Err.Number = 2101 Then
Resume Next
Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & _
"in Save_Click"
Resume Exit_saves_Click
End If
End Sub
Thanks Bruce for getting me on the right path!

 

[BruceM]

You have the right idea. The reason for using Me.Dirty = False is that when
the data entered into the form has not yet been saved, the record is said to
be "dirty". Setting "dirty" to false is accomplished by saving the record.
However, before the record is saved the Before Update code runs. When a
required field is blank, Before Update cancels the save. Back to Me.Dirty =
False, the assignment of False fails because the record was not saved, and
you receive error 2101 (that's the way I understand it). Error handling
will take care of that. For trapping a single error I find the following
syntax convenient:
If Err.Number <> 2101 Then
MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & _
"in Save_Click"
End If
ResumeExit_Save_Click

There is a fine freeware utility called MZ_Tools that can, among other
things, add customizable error handling at the click of a button. It is
located here:
http://www.mztools.com/v3/mztools3.aspx

Briefly, after the utility is installed, open the VBA editor, click Tools >
MZ-Tools, and select Options (or Options may be on the toolbar; I can't
recall the default setting). Click the Error Handler, and take a look at
the default error message. You can add variables from the drop-down box to
customize the message to your preference. I use the generic ProcErr and
ProcExit rather than things such as Err_Save_Click and Exit_Save_Click, but
you can add the name to those lines as you prefer. I won't go into the
details about setting it up, but it is a very good yet unobtrusive tool.