Function to test for existence of record

[CAS]

I am trying to create a function that can be called from several forms to validate an entry in a table.

What I would like to do is pass a user-input value to the function, have the function test to see if a record with the passed value exists in a specific table (in the primary key field), and return a value of true if the record exists, false if it doesn't.

For some reason, I can't seem to get this to work with either FindRecord, FindFirst, Seek, or DLookUp functions but I'm sure I'm doing something wrong. For what it's worth, the name of the table to be searched has the same name as the database and this seems to be causing problems, even when I stick a Tables! in front of the table name.

The function will be used when the user clicks on an "add new record" button to test whether the primary key already exists before the user goes through the entire form (since Access only appears to test whether the primary key is duplicated just before the data is written into the new record). If there are better ways to accomplish this, that would be helpful too.

Any thoughts? Thanks in advance.

 

[Jack Cowley]

Hmmm. Sounds like you are allowing the user to create the Primary key. My suggestion would be to us Autonumber as the Primary key and your problems are over.

If you are letting the user create the Primary key then you have to check the Primary key entry after they enter it but before Access saves it.

In the Before Update event of the Primary key code similar to this would work but I strongly suggest you consider Autonumber...

If Not IsNull(DLookup("[PrimaryField]","YourTable","[PrimaryField] = " & Me.PrimaryField))
Msgbox "Primary Key exists"
Cancel = True
End If

 

[shacket]

I speak from experience: use an Autonumber Primary Key field!! I built a database for people that stored the SSN and therefore used that as primary key (no 2 alike, right?). It is a nightmare.

I have since gone back and added Autonumbered PK fields. You don't give anything up - you can still have another field carry the value you were using for a primary key (SSN in my case) - but you gain a TON because you are not dependant on the user for the all important Primary Key.

 

[CAS]

Thanks for the suggestions. Unfortunately the data that becomes the primary key has already been created before the information is entered into the database, so I truly do need to ensure that the primary key doesn't exist before the data is entered.

I was using exactly the DLookUp code as suggested but it didn't work. It just returned no value (rather than Null or a non-null value).

The way I have it set up now is using a popup form to get the primary key value, then trying to have it check that value against the records already in the system, then allow a new record to be added to the table if the primary key does not yet exist.

 

[AlanS]

In Re:

"Unfortunately the data that becomes the primary key has already been created before the information is entered into the database, so I truly do need to ensure that the primary key doesn't exist before the data is entered."

I suggest you go with an autonumber key field. You can still set up a unique index on a non-key field, which will effectively keep users from entering two records with the same data in that field, and you will also avoid the problems of having users select the actual primary key values.