And If after reading Rick's and Jeff's and Klatuu's posts it still
doesn't get it for you ....
You might make a copy of your existing form and then, on the copy;
base a new combobox on your data entry/massage form on a new query
based on the same table as the Form with a calculated field which is
ConcatenatedName: [LastName] & ", " & [FirstName] & " " &
[DateofBirth] (or some other differentiating piece of data common to
everyone in your application such as member number, etc.). That query
needs only return the recordID of the Form's underlying table and that
new field. Set the combobox's Autoexpand property on.
Set most of the Form's data properties off.
Using the above paradigm, a name in the application will be quickly
found by starting to type it in. It will certainly be in the combobox
if completely typed in correctly; a good safety check and prevents
typos creating new information. If you type in a name with many hits,
the differentiating information will tell your user if one of the hits
is the current target. If in either of the above cases, click a
button you've created to Edit the found record by doing a GotoRecord
on the record ID to be found as the bound column (usually you'd want
that to be first column. Column numbers are either 0 or 1 based
depending where you reference them). In the next line of code after
the GoToRecord, turn on the Form's edit property. When you're done
editing the record, be sure disallow further edits. Put that code
into the Form's AfterUpdate event.
If the record you seek doesn't exist then Click the other button
you've just created that will GoToRecord ...acNewRec. Fiddle the
Form's data properties to allow you to enter new data. Enter the new
record and save it. Since you've already put the restoration of the
Form's protection in it's AfterUpdate event that part is all done.
If you purge your table of duplicate records and then use the above
paradigm then the only way you'd get duplicative records is if
1: You have some really obtuse or even hostile users.
2 You provide more than one data entry/massage form based on the
main table under discussion.
3. You allow your users to get into your data tables directly.
That's a guarantee of disaster.
4. Your users have the ability to alter your design. Don't let
that happen. Distribute MDE Front Ends rather than MDB.
If you clean out the duplicates, apply the above and duplicates
continue to surface there are other things you can do. For one thing
add in some of the other solutions proposed in this thread.
Post back as issues persist or arise.
HTH
--
-Larry-
--
Klatuu said:
If after reading Rick's and Jeff's posts, you still need another method, you
could use the DLookup:
If Not IsNull(DLookup("[Last Name]", "Census", "[Last Name] = '" &
Me.last_name & "' And [First Name] = '" & Me.first_name & "'"))
Will find a duplicate for both names
:
That solution works great for keeping the duplicate records off the table,
but I would like my users to see when they have created a duplicate so that
they may correct the problem. I'm looking for an error code to pop up or
something similar.
Great suggestion and I'm going to keep that in place just in case. Thanks
for any help you can provide!
:
Jennyrd wrote:
I would like to use a control to check that the first name field and
the last name field on my form are not duplicate in the table. It is
a situation where both have to be duplicate on the same record in my
table to error. We want to be sure that the "new users" who are
entered are indeed new. The field names in the table are First Name
and Last Name. On the form they are first_name and last_name. The
form's name is Input Form and the Table's name is Census. Please let
me know if I can supply any additional information.
If you create a unique index on the table consisting of both fields then the
database engine will prevent duplicates for you. Of course you could still
get...
John Smith
Johnathon Smith
Johnny Smith
Jack Smith
etc..
