Field/Form Validation

  • Thread starter Thread starter Amy Luby
  • Start date Start date
A

Amy Luby

I am so new to Access - please forgive the newbie questions!

I have four fields from the same table and must validate in this way:

1) First_name
2) Last_Name
3) Home_Phone
4) Email_addr

If 1 & 2 currently exist in the table, show existing record
If 1 & 2 are not in the table but 3 is, then show existing record
If 1 & 2 are not in the table but 4 is, then show existing record

I know this needs to be done an a before or after update event, but I am
confused as to which field should contain the event. Or, should I write the
event based on the after update event for the form itself?

Thank you for your time!
 
This could be done in each field as it is typed in, but may be easier to do
in the form's BeforeUpdate event. You can use DCount statements in the
form's BeforeUpdate event to verify the results. If the Count is greater
than 0, then the value already exists. For the #1 and 2 item, you can
specify more than one criteria in the DCount statement.

Example:
If DCount("*", "[Table1]", "[First_name]=""" & Me.txtFirst_name & """ And
[Last_name]=""" & Me.txtLast_name & """") >= 1 Then

The reason for the multiple double quotes instead of using single quotes
inside the double quotes to deliniate the text data is because names,
especially last names, may have apostrophes in them. If they do, the single
quotes won't work.

When you bring up the existing record, use the criteria in the DCount
statement to filter the form. There may be more than one current record with
that particular criteria. To filter the form, this criteria would be used in
the Where parameter of the DoCmd.OpenForm call. If you also open the form
with the acDialog window mode argument, the code in the BeforeUpdate event
will pause until the form is closed or hidden. When you close the form and
the code resumes, you could make the next command be a MsgBox asking the
user if they want to continue adding the record. If they choose No, then set
Cancel = True to cancel the saving of the record. The record will still be
"dirty" at this point and if they try to save it again, the whole thing will
repeat. At this point, they will either have to Undo the record by pressing
the Esc key (twice, the first time undoes the last field changed) or make
changes to the record and try to save it again.
 
Wayne - Thank you for your response! It has helped immensely!

--
Amy Luby, President
Microsoft Certified Business Solutions Partner
Mobitech
Omaha, NE 68137
402-330-0707(o)
402-614-3519(f)

www.mobitech.biz
Wayne Morgan said:
This could be done in each field as it is typed in, but may be easier to do
in the form's BeforeUpdate event. You can use DCount statements in the
form's BeforeUpdate event to verify the results. If the Count is greater
than 0, then the value already exists. For the #1 and 2 item, you can
specify more than one criteria in the DCount statement.

Example:
If DCount("*", "[Table1]", "[First_name]=""" & Me.txtFirst_name & """ And
[Last_name]=""" & Me.txtLast_name & """") >= 1 Then

The reason for the multiple double quotes instead of using single quotes
inside the double quotes to deliniate the text data is because names,
especially last names, may have apostrophes in them. If they do, the single
quotes won't work.

When you bring up the existing record, use the criteria in the DCount
statement to filter the form. There may be more than one current record with
that particular criteria. To filter the form, this criteria would be used in
the Where parameter of the DoCmd.OpenForm call. If you also open the form
with the acDialog window mode argument, the code in the BeforeUpdate event
will pause until the form is closed or hidden. When you close the form and
the code resumes, you could make the next command be a MsgBox asking the
user if they want to continue adding the record. If they choose No, then set
Cancel = True to cancel the saving of the record. The record will still be
"dirty" at this point and if they try to save it again, the whole thing will
repeat. At this point, they will either have to Undo the record by pressing
the Esc key (twice, the first time undoes the last field changed) or make
changes to the record and try to save it again.
Click to expand...
 
Back
Top