make sure that primary key is unique

[teddie]

How do I write a routine that checks to make sure that a person isn't editing a record such that the newly edited record's primary key will match that of a record previously entered in the database? I though of opening a resultset whose SQL would have the resultset populated if there were any record with a matching primary key. But duh, the resultset will be always be populated unless you edit the primary key itself.

[bashfirst]

Your primary key definition might not be appropriate if it is possible to have a user enter a duplicate. For instance, First and Last Name combined isn't a very good primary key because too many people show no originality in naming their children.

However, if you can't come up with a truly unique key, then grab the new, unsaved primary key off the form and do a query
SELECT COUNT(*) FROM myTable WHERE
myTable.Field1 = '" & txt1.Text & "' AND myTable.Field2 = '" & txt2.Text & "'"
If you don't get 0, then you can reject the record.

Hope this helps.
Bash

[teddie]

Actually an error from Oracle would occur if you tried to save a record with an ununique primary key. This is the error that I'm how to handle with vb code. I want to disallow users from trying to save a record with an ununiqe primary key.

[JHausmann]

If you have an "update" or "validate" button, it's quite simple. Create a boolean function that uses a sql statement to retrieve the key from the database, then call the function from the button press.

Public Function CheckKey()as boolean

CheckKey = False
Dim rChk As Recordset


Set rChk = database_object.OpenRecordset("Select * from table where uniquekey=" & frmMain.texttovalidate, dbOpenSnapshot)

If rChk.EOF = False Then 'set flag to indicate data exists
CheckKey = True
rChk.Close
End If


End Function

[teddie]

If the primary key is not being edited the sql will find the original unedited record, right?

[JHausmann]

The function posted only looks to see if a record exists. It would be up to you to determine what the user is attempting to do. You could save the data retrieved (that you want to check) by the user in fields that could be compared. If the primary key retrieved is different than the one saved, you could then run the function to see if the user is attempting to change something you don't want changed.