473,327 Members | 2,007 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Preventing Duplicate Entries in a database

Hi, I am struggling to get my code working to prevent duplicate repair numbers from being added. The Table is called "Repair Forms".
When I get it working to a degree all it does is errors no matter what I type with the "msg" output that a duplicate record exists.
Any ideas?

================================================== ====

Expand|Select|Wrap|Line Numbers
  1. Private Sub Repair_No_BeforeUpdate(Cancel As Integer)
  2.  
  3. If (Not IsNull(DLookup("[Repair_No]", _
  4. "[Repair Forms]", "[Repair_No] ='" _
  5. & Me.Repair_No & "'"))) Then
  6. MsgBox "Duplicate Repair Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
  7. Cancel = True
  8. Me!Repair_No.Undo
  9. End If
  10.  
  11. End Sub
May 9 '16 #1
5 1028
Seth Schrock
2,965 Expert 2GB
Try changing your reference to the Repair_No control to use the Text property of that control.
Expand|Select|Wrap|Line Numbers
  1. If (Not IsNull(DLookup("[Repair_No]", _
  2. "[Repair Forms]", "[Repair_No] ='" _
  3. & Me.Repair_No.Text & "'"))) Then
  4. ...
May 9 '16 #2
Still throwing the MsgBox "Duplicate Repair Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate".

This is even trying a unique number so it shouldnt even throw the error.

Repair Numbers themselves are Classified like "D142/3/4 etc"

Not sure if I have made an error but the Box/field is called "Repair_No".
The Control Source on that box is "Repair No"
May 9 '16 #3
Yup, my bad. Renamed the box to the same as control source and it finally works happily.
Thanks for the help :)
May 9 '16 #4
PhilOfWalton
1,430 Expert 1GB
Try to isolate your problem by typing in the Immediate Window
Print DLookup("[Repair_No]", _
"[Repair Forms]", "[Repair_No] =" Chr$(34) & "XXX" & Chr$(34))
Substitute the XXX with a valid Repair Number say D1432 and then an invalid repair number and see if the results are as expected.

Sorry. I tend to use Chr$(34) instead of inverted commas, find it less eye strain.

Phil
May 9 '16 #5
zmbd
5,501 Expert Mod 4TB
Do you also have a key in the table that forces unique entry in the field? You can then use the execute method with fail on error, trap for the error and cancel if DAO.Database error "3022". I do this quite often when inserting new serial numbers etc..
May 12 '16 #6

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

Similar topics

1
by: Gary Lundquest | last post by:
It appears to me that MySQL version 4 returns an error messge when doing an Insert that results in duplicate entries. Version 3 did NOT return an error - it dropped the duplicate entries and ran...
3
by: andreas.maurer1971 | last post by:
Hi all, since a few years I use the following statement to find duplicate entries in a table: SELECT t1.id, t2.id,... FROM table AS t1 INNER JOIN table AS t2 ON t1.field = t2.field WHERE...
18
by: Elroyskimms | last post by:
I have a table using an identity column as its Primary Key and two columns (table reduced for simplicity) EmployeeNumber and ArrivalTime. CREATE TABLE ( IDENTITY (1, 1) NOT NULL , (10)...
4
by: sri2097 | last post by:
Hi all, I'm storing number of dictionary values into a file using the 'cPickle' module and then am retrieving it. The following is the code for it - # Code for storing the values in the file...
5
by: Chris Lasher | last post by:
Hello Pythonistas! I'm looking for a way to duplicate entries in a symmetrical matrix that's composed of genetic distances. For example, suppose I have a matrix like the following: A B ...
1
by: calebm12 | last post by:
Quick Question. I gotta a database with fields firstname, lastname, and hobby, etc. I dont want to allow duplicate entries for the name. For instance....no john smith twice....but there can be a...
5
by: Manish | last post by:
The topic is related to MySQL database. Suppose a table "address" contains the following records ------------------------------------------------------- | name | address | phone |...
3
by: humblemally | last post by:
Goodmorning all - I created a form where you enter employee skillsets. There are about 15 different skills and the employees choose the skills they have from the list. I have created a field...
9
by: rjshrader | last post by:
I have a table (tblStatus) with three fields (CustomerID, StatusType and StatusDate). I use an unbound form with three text boxes to enter data into the table when a command button (cmdSave) is...
7
by: php_mysql_beginer911 | last post by:
Hi .. i am trying to update a table where if field contents any duplictaed entries than one of the field should be updated with random number which is unique so i can make all entries unique i...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.