By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,377 Members | 3,038 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,377 IT Pros & Developers. It's quick & easy.

Want Access to notify me if a record already exists

P: 15
Hi,

I want Access to return an error message that says an identical entry already exists. I do not want to set the field to 'no duplicates' because it is a name field and it is possible to have different entries with the same name.

It is for a Claimant Master table in Access 2007. Each claimant could appear in the Jobs table many times but should only have one record relating to them in the Claimant Master table. When I enter a claimant for a new job I want some way of knowing there is somebody with the same name already entered. The trouble is, if I do not allow duplicates, I will not be able to enter two distinct people (who live at separate addresses) with the same name.

The idea is that once the error message notifies me that someone else has the same name, I can cross check to see if they have the same address (e.g. are in fact the same person). I would then just use their ID number in the Jobs table and not need to enter all of their details into the database.

Is there code that checks a field for a matching entry and returns a message to alert you?

Thnx
deejow
Jul 21 '07 #1
Share this Question
Share on Google+
6 Replies


missinglinq
Expert 2.5K+
P: 3,532
One way to check to see if the name already exists in the table is to use DCount() .

Expand|Select|Wrap|Line Numbers
  1. Private Sub NameInForm_BeforeUpdate(Cancel As Integer)
  2. If DCount("[YourNameField]", "YourForm", "[YourNameField]= '" & Me![NameInForm] & "'") > 0 Then 
  3.   MsgBox "Name Is Already In Database!"
  4. End If
  5. End Sub
  6.  
Where

NameInForm is the control holding the name
YourNameField is the name of the table field holding the name
YourForm is the name of the underlying table holding YourNameField

Be sure Line # 2 above is all on one line in your code.

Welcome to TheScripts!

Linq ;0)>
Jul 21 '07 #2

P: 15
One way to check to see if the name already exists in the table is to use DCount() .

Expand|Select|Wrap|Line Numbers
  1. Private Sub NameInForm_BeforeUpdate(Cancel As Integer)
  2. If DCount("[YourNameField]", "YourForm", "[YourNameField]= '" & Me![NameInForm] & "'") > 0 Then 
  3.   MsgBox "Name Is Already In Database!"
  4. End If
  5. End Sub
  6.  
Where

NameInForm is the control holding the name
YourNameField is the name of the table field holding the name
YourForm is the name of the underlying table holding YourNameField

Be sure Line # 2 above is all on one line in your code.

Welcome to TheScripts!

Linq ;0)>
Thank yopu so much.

For those interested here is final code used...

Expand|Select|Wrap|Line Numbers
  1.  Private Sub SubjLastName_LostFocus()
  2.  If DCount("[SubjLastName]", "Claimantmaster", "[SubjLastName]= '" & Me![SubjLastName] & "'") > 0 Then
  3.         MsgBox "Name Is Already In Database!"
  4.     End If
  5. End Sub
Thnx again!

deej
Aug 4 '07 #3

missinglinq
Expert 2.5K+
P: 3,532
Glad we could help!

Linq ;0)>
Aug 5 '07 #4

NeoPa
Expert Mod 15k+
P: 31,494
A new question was entered in here by mistake (Hijack). It's been moved to Detect Matching Record.
Nov 9 '13 #5

100+
P: 547
i use the following code
Expand|Select|Wrap|Line Numbers
  1. Dim Answer As Variant
  2.  Answer = DLookup("[fieldname]", "table.....", "[fieldname] = '" & Me.fieldname & "'")
  3. If Not IsNull(Answer) Then
  4. MsgBox "Duplicate record Found" & vbCrLf & "This  will now be deleted. ", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
  5.  
  6.  Cancel = True
  7.  Me.Undo
  8.  End If
Nov 9 '13 #6

NeoPa
Expert Mod 15k+
P: 31,494
Your code cancels the entry of any duplicates. The requirement is to allow entry, but only after confirming with the operator that there is no mistake.

See the linked thread for code that handles that fully. Frankly, the code is very similar to yours, but with a bit more included.
Nov 9 '13 #7

Post your reply

Sign in to post your reply or Sign up for a free account.