Access 97 Records Update Question

[Randy Galliano]

Hello,

I have a very simple form that has a text box linked to a field in a table
in Access. The form is in update mode and I have moved to a new record.
The field doesn't allow duplicates, and if a duplicate record is entered, I
want to test for it and let the user know that the entry already exists.
How can I access the recordset that is bound to a form and see if a
duplicate value exists? I know how to get to VB script on events, just not
how to address the recordset.

Any help is greatly appreciated.

Regards,

Randy.

 

[Jerry Porter]

Look in Help for RecordsetClone.

A better way might be to use dLookup to look for the value in the
table, instead of working with the recordset.

Also, if you have created a unique index for this field in the design
of the table, Access will present the user with an ugly error message
when they try to save the record. You can use the Error event of the
form to trap this and send a nicer error.

Jerry

 

[John Vinson]

I know how to get to VB script on events, just not
how to address the recordset.

Me.RecordsetClone is the recordset upon which the form is based.

John W. Vinson[MVP]

 

[Randy Galliano]

Jerry,

Thank you for the suggestions. I have tried to trap the error with an on
error, but I get more messages about not being able to write to the SQL
database (which this is pointing to).

How does the dlookup work, or can you point me to some doc? I would rather
work on the table directly because of locking and other issues. Is there a
way to work on the table directly? This way, I could just use the form as
input, and not bind it to anything.

Regards,

Randy.

 

[Jerry Porter]

Randy,

You wouldn't be able to use On Error to trap an error caused by an
entry in a form, since it's not running any of your code when the error
occurs. But the form has an Error event to handle this situation. You
can find it in the properties window for the form.

You should be able to find DLookup in Help, or type it into a code
window and press F1.
As an example, if your field is named MyField and the form control is
txtMyField, and if it's a text field, then

DLookup("MyField","MyTable","MyField = ' " & Me!txtMyField & " ' ")

would be Null if the value exists, and non-null if it does. The
expression means "look up the value in MyField in the table MyTable
where the value of MyField in the table = the entry in the form.
I put spaces around the single quotes for readability, but they would
need to be removed.

If it's a numeric field, it would be simpler:
DLookup("MyField","MyTable","MyField = " & Me!txtMyField)

If by "work on the table directly" you mean write data to table, you
can do that by executing SQL statements in code (see the Execute
method), or by using a recordset.

Jerry