Hello Steve,
Thank you for your reply. Sorry if the post was confusing. I thought I was
clear. In reference to "not working," I wrote: "I get the Access error
messages, but I want to over-ride the Access messages and use the code I
have." In other words, the user makes his/her selections and clicks a button
to open a new form, but if they have left anything unaswered that needs to be
answered, they should get an error message explaining what they need to do,
the focus should be set to the control in question and the event to open the
new form should be canceled. That was happening, but I was getting an Access
error message and not the error message that I wrote for the user.
I didn't see a reply so I kept working on the problem and finally got the
results I needed ... somewhat.
I'll explain it as best I can. I have a main form and a subform. The
subform has a tab control. On the tab control, I have subforms. On one of
the subforms on the tab control, I need to make sure that the user has made a
selection or entered data for 3 of the controls on that subform before they
move to the next tab on the tab control. The code for this works fine. On
the main form (which remains open,) I need to do the same thing: make sure
that the user has made a selection/entered data for at least 3 of the
controls on the main form before they click a button to open the subform.
The problem is that even though I'm using the same type of code on the main
form as on the subform that's on the tab control, I get a second error
message with the main form that I don't get with the subform that's on the
tab control. I get an Access message that says "You canceled the previous
operation".
I tried re-writing the code as you suggested below, Steve, but I still get
the second (Access) error message. Except this time it reads: "The
DoMenuItem action was canceled." Hope this makes sense.
If anyone can help, I would appreciate it. How do I get rid of that second
error message? I'm still new to Access and I greatly appreciate any help!
Here's the code as it is now:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull([VendorName]) Then
MsgBox "Please enter Vendor's Name.", , "Vendor Name"
DoCmd.CancelEvent
Me![VendorName].SetFocus
ElseIf IsNull([VendorRiskTypeID]) Then
MsgBox "Please select the Vendor Risk Type.", , "Vendor Risk Type"
DoCmd.CancelEvent
Me![VendorRiskTypeID].SetFocus
ElseIf IsNull([VendorInvoicingTypeID]) Then
MsgBox "Please select the Vendor's Invoicing Type.", , "Vendor Invoicing
Type"
DoCmd.CancelEvent
Me![VendorInvoicingTypeID].SetFocus
End If
End Sub
--
DM
SteveS said:
By "not working" I am guessing that your custom message box does not appear
when you leave a combo box "empty".
When you create a number field in a table, the default value property is set to
0 (zero). So when you are adding records, the combo box has a value (zero); so
it is not null.
Open the TABLE in design view and check the default value property for the
combo boxes. If they have an entry, delete it. Save the table. Open the form
and see if your message box appears.
Have you set breakpoints in the code and set watches to see what the values are
for the combo boxes? Learning how to debug your code using breakpoints, watches
and the immediate window (using debug.print) will help you improve your coding
skills.
I added two lines to your code. If the first or second IF() is true, you need
to exit the sub to leave the focus on that control.
'*************************************
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull([VendorName]) Then
MsgBox "Please enter a vendor name.", , "Vendor's Name"
Me![VendorName].SetFocus
Cancel = True
Exit Sub ' <<<<<<<<< added
End If
If IsNull([VendorRiskTypeID]) Then
MsgBox "Please select the vendor's risk type.", , "Vendor's Risk Type"
Me![VendorRiskTypeID].SetFocus
Cancel = True
Exit Sub ' <<<<<<<<< added
End If
If IsNull([VendorInvoicingTypeID]) Then
MsgBox "Please select the vendor's invoicing type.", , "Vendor's
Invoicing Type"
Me![VendorInvoicingTypeID].SetFocus
Cancel = True
End If
End Sub
'************************************
HTH
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)
Hello, I've read all of the posts and set my code following examples, but
it's still not working. I have a form that has 3 combo boxes that I need to
verify aren't empty. The main form is connected to 2 of the combo boxes in
1-to-many relationships. I get the Access error messages, but I want to
over-ride the Access messages and use the code I have. I'm using If ... Then
... End If for all 3 boxes at the form level, like I've read in previous
posts, but it's not working, what am I doing wrong??!!?? Please help!!
Here's the code I'm using:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull([VendorName]) Then
MsgBox "Please enter a vendor name.", , "Vendor's Name"
Me![VendorName].SetFocus
Cancel = True
End If
If IsNull([VendorRiskTypeID]) Then
MsgBox "Please select the vendor's risk type.", , "Vendor's Risk Type"
Me![VendorRiskTypeID].SetFocus
Cancel = True
End If
If IsNull([VendorInvoicingTypeID]) Then
MsgBox "Please select the vendor's invoicing type.", , "Vendor's
Invoicing Type"
Me![VendorInvoicingTypeID].SetFocus
Cancel = True
End If
End Sub
