Preventing duplicate entries or values

[mraetrudeaujr]

This is kind of a two-parter:

First, I need to prevent the users from entering duplicate numbers (office generated) into a particular field. These will always be unique in this table. For now, I have to let them have access to the table, but this is only temporary. I am weaning them from entering data directly into the table, and so I have built a data-entry form. This form will give them their next number automatically, but this doesn't prevent them from going into the table itself and putting in a number that has already been assigned. This is where I need the help.

Second, I am creating a table where all of the values will be unique. These values will be referenced on another form (control) and then put with the record in the main table. I want to prevent typing in the same 'country' name in twice. This table will only be setup once and then there will not be anymore data entry into it. So how should I proceed?

 

[PHV]

To prevent duplicate even when entering data directly into the table, the simplest way is to create an unique index on the field.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mraetrudeaujr]

Yes, I did this...and it worked, but only when I tried to "Add New Record" did it return an error preventing me from 'saving' this record. What I would like is when I type it in and hit the TAB key, the error pops up. I have twelve pages of "Country" information to type in this table, and I would like to catch any errors 'on the fly' or as soon as possible.

 

[PHV]

As JetSQL lacks triggers you have to use a form to do that.
A common way is to play with the DLookUp function in the BeforeUpdate event procedure of the textbox.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mraetrudeaujr]

PHV,

I looked it up in the Help file, and I understand how it would work, but I don't know how to adjust it to work with my form. I think that you would use it to look up my "SearchValue", "ValidCode", and "TableText" controls and compare the current value to these values listed in the table...right? If so, then what would it look like?