Check for duplicate indexes

[lizray]

I have a table with a primary Index consisting of 2 fields, the index does not allow duplicates. The user can input data via a form, into both fields,fld1 and fld2. what is the fastest way to check if the combined fields input will cause a duplicate in the primary index ?

 

[Andrzejek]

[tt]SELECT * FROM YourTable
WHERE fld1 = valkueFromFormfld1
AND fld2 = valFromFormFld2
[/tt]
If returned anything, this record already is in the table,
if returned nothing, you are OK

Have fun.

---- Andy

 

[lizray]

Thanks Andy, I am not familiar with SQL, but would the rest of code need to set up a recordset and test for zero records. Would it be faster to use:
SELECT fld1 FROM YourTable
WHERE fld1 = valkueFromFormfld1
AND fld2 = valFromFormFld2

 

[Andrzejek]

It would be to a great benefit to you to get yourself familiar with SQL and recordsets, since you are in Access. And there is not much difference if you are going after * or one field to check if there is a record with fld1 and fld2 or not. You could also use a SELECT COUNT() for this check, or even DLOOKUP.

Have fun.

---- Andy

 

[lizray]

Thanks again Andy. You are right I should come up to speed on SQL. I had hoped there may have been a fast, special way of checking for Index values

 

[Andrzejek]

Another, 'lazy' approach to this problem (not recommended) is to allow user to insert a new record and catch the error if the insert violates the ‘2 field index’ rule.

Have fun.

---- Andy