Hi, Karen.
In the third "IF" block, I believe Allen meant to type:
If Cancel Then
The preferred method for applying these restrictions is by using VBA, since
these restrictions will only apply to this form, not every form in the
database. It will also be easy to do global search and replace whenever
changes are needed for this code, such as when one of these field names is
changed.
However, if all forms in this database application need these same
restrictions, it makes more sense to place the restrictions in a single place
at the table level, not in multiple code modules. To assign this table level
constraint, do the following:
Open the table in Design View and change the Field Properties of all three
of these fields, ContactPerson, ContactPhone, and Contact Email to:
Required: No
Allow Zero Length: No
Next, right-click on the table's title bar and select "Properties" from the
pop-up menu to open the Table Properties dialog window. Copy and paste the
following into the "Validation Rule" Property (it's all one line):
Not (IsNull([ContactPerson])) And (Not (IsNull([ContactPhone])) Or (Not
(IsNull([Contact Email]))))
Next, copy and paste the following into the "Validation Text" Property (it's
all one line):
You must enter a contact person and either a phone number or E-mail address.
This next step won't affect the table constraint, but while you're here you
should turn off the default Subdatasheet Name Property by changing [Auto] to
[None].
(As a matter of fact, you should copy, paste, and run the code Allen has
available under the "Tables: SubdatasheetName" section on the following Web
page to turn off this default property for all tables in the database at once:
http://allenbrowne.com/bug-09.html
And while you're on that Web page, the code under the "Fields: Allow Zero
Length" section should probably be copied, pasted, and run on this database
to fix this ill-advised default property as well, and then this property
turned back on manually only for the very rare occasions when it is needed.
Check out the other great tips Allen has provided on his Web site.)
Close the Table Properties dialog window, save the table and change to
Datasheet View. You may test the new table contstraint by inserting a new
record and leaving out any of the required properties. You will see that
failure to provide the required fields will result in the appropriate
reminder to the user.
One recommendation I would make is that the "ContactPerson" should be split
into two fields, one for the first name and one for the last name in order to
provide more functionality and better control over the data. (For example,
It's hard to sort by last name when the last name starts somewhere in the
middle of the field if the data is stored in a "first name - last name"
organization, and it's hard to identify just the people named Karen if the
data is stored in a "last name - comma - space - first name" organization.)
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)
- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.
Allen Browne said:
Use the BeforeUpdate event procedure of the *form* to check that everything
is on order before the record is saved.
The following example is crafted so that it is easy to add as many checks as
you need, and the user gets just one message at the end of all checks about
what is wrong.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
If IsNull(Me.[Contact Person]) Then
Cancel = True
strMsg = strMsg & "Contact Person required." & vbCrLf
End If
If IsNull(Me.[Phone Number]) And IsNull(Me.[Contact Email]) Then
Cancel = True
strMsg = strMsg & "Phone number or email required." & vbCrLf
End If
If Canel Then
strMsg = strMsg & vbCrLf & "Correct the entry, or press <Esc> to
undo."
MsgBox strMsg, vbExclamation, "Invalid data"
End If
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Karen said:
I have a form and I need to create a requirement that has the user enter a
Contact Person and Phone Number OR a Contact Person and Contact E-mail.
The
fields on the form are ContactPerson, ContactPhone, and Contact Email. I
am
not sure how to go about this. I am limited in my knowledge of VBA code.
