Instant data entry verification on form.

  • Thread starter Thread starter DJW
  • Start date Start date
D

DJW

I have 12 fields on my form. The first field's properties are set to
"indexed (no duplicates)" at the table level and also set with a primary
key. Next I created a form using the wizard so these values transferred
over to my form. Here's my problem.

When I enter data into the first field on my form, I am allowed to continue
entering data in the other eleven fields. When I get to the last field and
try to enter the next record, I get the generic error message that says the
value I entered into the first field already exists. While this is true, I
want to know this as soon as I attempt to leave the first field so I don't
waste my time entering data into the other eleven fields needlessly, only to
have to go back and redo the entry.

In plain english what I'd like to do before leaving the first field is to
check all other records in that field in the table and if that value already
exists, then pop up a custom message box notifying the user and set the
focus back to the field not allowing the user to go any further until a
correct value is entered. I know how to make custom message boxes and how
to set focus back to the field. What I don't know is how to check the
existing records already entered into the table to make sure the value I
just entered has not already been entered.

My table name is "tbl_Cases" and my first field name in the table that is
"indexed (no duplicates)" is named "Casenumber".

Any help is appreciated. Thank you.
 
DJW said:
I have 12 fields on my form. The first field's properties are set to "indexed
(no duplicates)" at the table level and also set with a primary key. Next I
created a form using the wizard so these values transferred over to my form.
Here's my problem.

When I enter data into the first field on my form, I am allowed to continue
entering data in the other eleven fields. When I get to the last field and
try to enter the next record, I get the generic error message that says the
value I entered into the first field already exists. While this is true, I
want to know this as soon as I attempt to leave the first field so I don't
waste my time entering data into the other eleven fields needlessly, only to
have to go back and redo the entry.

In plain english what I'd like to do before leaving the first field is to
check all other records in that field in the table and if that value already
exists, then pop up a custom message box notifying the user and set the focus
back to the field not allowing the user to go any further until a correct
value is entered. I know how to make custom message boxes and how to set
focus back to the field. What I don't know is how to check the existing
records already entered into the table to make sure the value I just entered
has not already been entered.

My table name is "tbl_Cases" and my first field name in the table that is
"indexed (no duplicates)" is named "Casenumber".
Click to expand...

In the BeforeUpdate event of the TextBox bound to Casenumber...

If DCount("*", "tbl_Cases", "Casenumber = " & Me!TextBoxName & "") > 0 Then
MsgBox "Duplicate Case Number"
Cancel = True
End If
 
Back
Top