Best way to check that data is unique??

[gibben]

I am looking for the best way to ensure that a recordset is unique before writing it to the database??

I was considering setting up composite keys within the tables but would still rather capture it at the forms level before it reaches the tables.

I have been trying to create a function the would search through a table for a match, if it found one then would not allow the record to be written ....


This is what I had (Hardwired Prototype):
Private Function DoSQL()


Dim SQL As String
SQL = "SELECT Master_Location.Name FROM Master_Location WHERE Master_Location.Name =""Invermay"""
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL)
If rs.NoMatch Then
MsgBox "No record was found."
'Code to allow save
Else: MsgBox "A matching record has been found."
'Code to warn user that record exists
End If
rs.Close
End Function

Any help with this would be much appreciated

 

[BoulderBum]

SELECT DISTINCT whatever FROM some_table WHERE ...

With the DISTINCT keyword, the recordset returned will contain only unique values.

Hope that helps.

 

[scriverb]

First of all this Function should be declared as Public in a database module not Private. Also, your rs.NoMatch property expression will not work for you. You need to use the rs.RecordCount property in the expression.
If rs.RecordCount = 0 Then
MsgBox "No record was found."
Else
MsgBox "Record was found."
End If

ACCESS Help on .NoMatch
To locate a record, use the Seek method on a table-type Recordset object or one of the Find methods on a dynaset- or snapshot-type Recordset object. Check the NoMatch property setting to see whether the record was found.
If the Seek or Find method is unsuccessful and the NoMatch property is True, the current record will no longer be valid. Be sure to obtain the current record's bookmark before using the Seek method or a Find method if you'll need to return to that record.

If you wanted to use the .NoMatch property use the following code :
Public Function FindRec(vName as string)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Master_Location", dbOpenDynaset)
rs.FindFirst "[Name] = '" & vName & "'"
If rs.NoMatch Then
'No record was found - No MsgBox needed
'Code to allow Save operation.
Else
MsgBox "A matching record has been found."
'Code to clear screen or allow editing make unique
End If
rs.Close
db.Close
End Function

Make a call to this function from the calling procedure with this code:

FindRec(Me![Name_Control])

The user will only see a message when a match is found. They would then should be allowed to edit their entry or clear it and start over. You can code that in the function where the comments are indicated.

Post back with any questions.



Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[gibben]

Thanks a lot scriverb,

That code was just the trick and everyones help is much appreciated. Is it possible to modify this so that a combination of fields can be checked or will it work with only one field at a time ??

EG:
rs.FindFirst "[Field1], [Field2] = '" & variable1, variable2& "'"

Thanks

 

[scriverb]

This should do it. You see you can build as complicated an expression as you need.

rs.FindFirst "[Field1]= '" & variable1 & "' and [Field2]= '" & variable2 & "'"

Post back if you need anything more.



Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???