Checking to see if a record already exists

[BradB]

I'm adding records to the database, and I don't want duplicate data. So, I made the Date and the Staff Name the primary key. I wanted one date per person. When a duplicate is found alert the user of the date and the Staff Name.

Are there any good examples of how to check to see if a record already exists? If the record already exists, can I get a message box to say,"10/01/01 Brad Boydston already exists!"?

BradB

 

[Databaseguy]

Access will tell you when you try to add a dulplicate record with a Duplicate Value error message. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[BradB]

True, but does it tell me the exact record that's at fault?

When a duplicate is found alert the user of the date and the Staff Name.
Example:

msgbox "The following record(s) are already in the database. Please remove the following records." rs.date & " " & rs.staffName

 

[CosmoKramer]

See if this works for you, I use it a lot:

Dim strMsg As String, strX as String
strMsg = "Patient number " & Forms!frmMain!txtPatID & " exists and cannot be added"

If Not IsNull(strX = DLookup("[PatID]", "tblDemog", "[PatID] ='" _
    & Forms!frmMain!txtPatID & "'")) Then
  MsgBox (strMsg)
  Forms!frmMain!txtPatID.SetFocus
  Forms!frmMain.Refresh
  GoTo Exit_CmdFindRec_Click
End If

It checks to see if a record exists with that ID and if it does displays a custom message box you can define. It sets the focus back to that field and exits the code....

HTH

 

[Databaseguy]

Oh, that would be very different then ......Nevermind

Actually, go into your help file and search for FindFirst method of the recordset object. There's an example in there you could tweak to suit your needs. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[Aivars]

Hi, BradB!

Use BeforeUpdate procedure for checking of existing data:

private sub MyNoDuplicateField_BeforeUpdate(Cancel as Integer)
dim rst as recordset

if isnull(MyNoDuplicateField) then
msgbox "Enter ...."
cancel=true
exit sub
end if

set rst=currentdb.openrecordset("Select Field1 From MyTable Where Field1=" & MyNoDuplicateField & ";&quot
'MyTable >> table name
'Field1 >> field name for verification (not duplicate)
'MyNoDuplicateField >> form control name which you want to add into table (data source to Field1
'Use ' for text type fields (...Where Field1='" & MyNoDuplicateField & "';&quot or # for date type fields (...Where Field1=#" & MyNoDuplicateField & "#;&quot
if not rst.eof then
'Recordset contains new data
mcgbox "Record already exist..."
cancel=true
endif
rst.close
set rst=nothing
end sub

Aivars

 

[Fred]

If you want one record per StaffName, why did you make the primary key a segmentation of Date+StaffName?

If you make the primary just StaffName, then this would automatically stop the duplicates and when a StaffName record was retrieved the date could simply be changed.