Validating Primary Keys
[Originally posted by Ann]
I want to validate primary keys to be a set length and a message box to appear if it already used in the database.
for example i have orderid and want to be 4 digits long and not to be saved if it exists in orders table already.
Re:Validating Primary Keys
[Originally posted by Nick Williams]
If you really wanted this to be posted under the database category then read on.
To validate the field length all you really need to do is check the value of the number. If its greater than zero and less than 9999 then it should be OK. If you want the number to be greater than 1000 than just change the first part of the test.
To determine if the order already exists on the file, define an input only recordset that only has orderid in its defined fields and has a select to only bring back the orderid you are looking for. Then simply do an open of your recordset then check myrecordset.RecordCount to see if the record was found. This way you dont even need to read the record in to the program.
Alternately you could use SQL to count the number of records in your table that have a matching orderid.
Select Count (*) Into MyCountField from MyTable Where TableField = orderid.
Then, if MyCountField <> 0, your order number is already in use.
Hope this helps.
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center