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

Stop duplicate records on table?

100+
P: 283
Hi everybody (as Dr. Nick would say),

I am having a problem stopping duplicate records on my table. What I have is a form where you can enter in a persons day off request. What im trying to do is make it so if you enter in a persons name and date it will look on the table and see if that record already exisits. If it does then show an error message saying this record exisits if not then just save the record.

What I tried to do was set up the table so that the name of the person and the date off, were the Primary keys so that way it would not let you save a duplicate record. It was working ok for a little while but now i keep getting the error message:2105. Can anyone help me out setting up a VBA code that might work better to handle this instead of using the Primary Key option??

Thanks for the help!
Jul 5 '10 #1

✓ answered by Delerna

Slenish
in the error

'(([EmployeeName]=Smith, John) AND ([DayOff]=#7/2/2010#))'.

This bit looks a little strange to me

[EmployeeName]=Smith, John
I was expecting to see something more like
[EmployeeName]="Smith, John"

Are you sure you haven't forgotten to put the quotes around the name when writing your code?

like this
Expand|Select|Wrap|Line Numbers
  1. If DCount("*", _ 
  2.           "[TableName]", _ 
  3.           "(([PersonID]=" & chr(34) & Me.PersonID  & chr(34) &  ") AND (" & _ 
  4.           "[DateField]=#" & Format(Me.DateControl, "m/d/yyyy") & "#))") > 0 Then 
  5.     Call MsgBox("This record already exists.", vbExclamation) 
  6.     Cancel = True 
  7. End If 
  8.  
Added bits in bold
I think 34 is ascii for "


Alternatively use single quotes like MSEO did
The important point is the quotes surrounding the name and I think you might be missing it

Share this Question
Share on Google+
28 Replies


100+
P: 255
Is there any "must enter" field you leave in blank other than primary keys? check the field properties in the table.

If you're still having the error, try avoid the error message like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdNew_Click()
  2. on error got MyErr
  3.     DoCmd.GoToRecord acActiveDataObject, , acNewRec
  4.     Call CarryOver(Me)
  5. MyExit:
  6.   exit sub
  7. MyErr:
  8.   if err.number<>2105 then
  9.     msgbox err.description
  10.   end if
  11.   resume MyExit
  12. End Sub
In another way, you can use recordset to loop through the records and see if there's matching values.
Expand|Select|Wrap|Line Numbers
  1. Do While Not [Your table with record].EOF
  2.      If [Enter date] = [Date in table] then
  3.          If [Enter Name] = [Name in table] then
  4.               save the record
  5.          end if
  6.      end if
  7.      [Your table with record].movenext
  8. loop
  9.  
Jul 5 '10 #2

NeoPa
Expert Mod 15k+
P: 31,768
This is all a little unclear.

You say you have an error message 2105. I'll dig that up for you as that's just a number.
You can't go to the specified record. You may be at the end of a recordset.
Your version may have had more info associated, like table name or such.

It sounds like we would need to see the code that is causing this to determine what is actually happening.

As far as indexing goes, a Primary Key is not required to preclude duplicates. That would be any unique key. Is this what you mean? I would suggest setting up a unique key anyway, regardless of whether or not you want to rely on the facility to protect you from duplicates.

The logic you've described should work. If it's not working then I expect the code is not correctly implementing the logic. Again, we need to see the code to be able to help here properly.
Jul 6 '10 #3

ADezii
Expert 5K+
P: 8,699
As previously indicated by NeoPa, a Unique (No Duplicates allowed), Composite Index on the [Name] and [Date Off] Fields would solve your problem at the Table Level.
Jul 6 '10 #4

100+
P: 675
I would like to know how the current date is determined. If [Enter date] = Date(), then the results would be different from [Enter date] = Now().
Jul 6 '10 #5

100+
P: 283
Wow really apprecaite all the help with this one :)

let me go in to some more detail about what's going on and see if you guys can come up with any suggestions. Its odd because like NeoPa said it should be working but im not sure why.

When i get the error message popping up (2105) it kicks me back to this part of my code and highlights the part I put in bold.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command75_Click()
  2. On Error GoTo Err_Command75_Click
  3.  
  4.  
  5.     DoCmd.GoToRecord , , acNewRec
  6.  
  7. Exit_Command75_Click:
  8.     Exit Sub
  9.  
  10. Err_Command75_Click:
  11.     MsgBox err.Description
  12.     Resume Exit_Command75_Click
  13.  
  14. End Sub
  15.  
As both NeoPa and ADezii are suggesting about changing the Unique property to No Duplicates Allowed that causes a problem because I need it to allow duplicates for the name and date just not together.

To answer OldBirdMan's question the date is being put in from a form. There is a text box you type in the date and then click a save record button that saves the information to the table. The Date is not set to =Now() or anything else it is what you type it. The box is just formated to accept a date.

I tried the code that Colintis provided and then i got all kinds of problems. Im still working with it trying to make adjustments with it to see if I cant get something to work.

So far its a real head scratcher. The entire DB is brand new so the table that everything is going in to and the forms for inputing the info are brand new and are still in the testing phase. I even deleted the table once and rebuilt it again just to see if something go corupted. I tried compact and repair as well.

Any other questions or ideas let me know. Thanks everyone! :D
Jul 7 '10 #6

NeoPa
Expert Mod 15k+
P: 31,768
Is the recordset updatable?

A non-updatable recordset is usually the cause for such an error.
Jul 7 '10 #7

ADezii
Expert 5K+
P: 8,699
@slenish
I need it to allow duplicates for the name and date just not together.
This is exactly what a Unique, Composite Index on 'both' Fields will allow.
Jul 7 '10 #8

100+
P: 283
Hi NeoPa,

As far as i can tell the table it updatable. I set the unique index to no on everything and it the table seems to take information with no problem. Its just when i set things to yes no duplicate or set a primary key i get the error. Is there any way i can check to see if the table is not-updatable.

ADezii,
I tried setting the index for name and date to Yes No Duplicate but i still get the same error (2105) if i try to select the same day and name.

Not sure if this helps but I have the form directly linked to the table which it is saving on. Was wondering if it would be better to set up an update query?
Jul 7 '10 #9

NeoPa
Expert Mod 15k+
P: 31,768
slenish: As far as i can tell the table it updatable. I set the unique index to no on everything and it the table seems to take information with no problem. Its just when i set things to yes no duplicate or set a primary key i get the error. Is there any way i can check to see if the table is not-updatable.
It is generally fine for tables. A way to check is trying to add new record. If there is an option there then it's updatable.
slenish: I tried setting the index for name and date to Yes No Duplicate but i still get the same error (2105) if i try to select the same day and name.
This is not the point ADezii is talking about. Composite indices are not set up in the properties of the individual fields. They are set up using View / Indexes when in design mode of the table.
slenish: Not sure if this helps but I have the form directly linked to the table which it is saving on. Was wondering if it would be better to set up an update query?
I very much doubt it. It won't solve your problem, but it will probably make your project more complicated.
Jul 8 '10 #10

100+
P: 283
Hi NeoPa,

Thanks for the more indepth details. Really apprecaite you helping me on this. I tried adding records and it will save a record with no problem.

I Now seem to have a whole new head scratcher (still dealing with the same issue).

Ok I kept trying various ideas and what all of you have suggested and still seemd to get no where. Then I decide to rebuild the table again from scratch. I made a new table and tried setting the two fields as the primary keys again. Then I also changed all of the other fields indexes to yes duplicates ok, then i used the first code that colintis suggested and put that in place on the save record button.

Now when I press the button I still get the error (2105) if I have the program open so i can get to the back end and make adjustments but if i open the program as a user it works. I can not save the same record, i dont get the debug error box popping up...its strange any idea why??

Also i have a question about making an error msgbox pop up when it finds the record already exists should i post that in this forum or should I make a new one?
Jul 9 '10 #11

mseo
100+
P: 181
hi,
as I understood from your question, that you need to check on two fields to prevent the duplication based on those fields.
I think you can use something like this
Expand|Select|Wrap|Line Numbers
  1. If DCount("[pk]", "tbl_name", "[dayoff field]='" & Me!dayoff& "' AND [personID]=" & Me!personID) > 0 Then
  2.     MsgBox "this record is already exist", vbExclamation
  3.     Me.Undo
  4.     Cancel = True
  5.  
I assumed that the dayoff field is text and the personId is number
hope this helps
Jul 10 '10 #12

NeoPa
Expert Mod 15k+
P: 31,768
I would say that MSEO is working on fundamentally the right lines Slenish. However, I would also include the two fields together in your table as a Composite Key for fuller safety.

The code I would use. based on MSEO's suggestion, would be :
Expand|Select|Wrap|Line Numbers
  1. If DCount("*", _
  2.           "[TableName]", _
  3.           "(([PersonID]=" & Me.PersonID & ") AND (" & _
  4.           "[DateField]=#" & Format(Me.DateControl, "m/d/yyyy") & "#))") > 0 Then
  5.     Call MsgBox("This record already exists.", vbExclamation)
  6.     Cancel = True
  7. End If
Jul 10 '10 #13

ADezii
Expert 5K+
P: 8,699
slenish, I'm sending you a simple Database consisting of a single Table named tblNames. This Table has only 2 Fields named [First] and [Last] and contains a Composite, Unique Index (not Primary Key), involving these 2 Fields. If you attempt to enter an 'exact' combination of the [First] and [Last] Fields, namely: Ty Cobb <==> Ty Cobb, Access will generate an Error, and simply will not allow this to happen.
Attached Files
File Type: zip Composite Index.zip (8.4 KB, 145 views)
Jul 10 '10 #14

mseo
100+
P: 181
oh, sorry
I assumed that dayoff datatype is number, but it should be date/time and this requires additional syntax as Neopa did
Jul 10 '10 #15

100+
P: 283
Hey guys,

Really appreciate all the help with this.

Thanks for taking the time to write out the code MSEO and NeoPa.

Really apprciate the example DB ADezii (Thats funny you are in Philly im right outside of Philly!).

Here is where i am so far. After looking ADezii's example DB i figured out what you were talking about (finally ;) ) and tried that method. It works but i still contiune to get the 2105 error. I also tried to use the code that MSEO and NeoPa did but I get an Error 3075 Syntax error (Comma) in Query expression. So now i dont know whats going on.

The example ADezii gave works but its only as a front end user. When ever im on the back end I keep getting the error. Im wondering if it could have anything to do with the form itself and how information is being entered in that could cause the error. Im messing with things now on my form to see if that could be the cause. All i know is i keep gettin the 2105 error and it keeps saying its a problem with the 'acNewRec' code on my save button.

Cant thank you guys enough for all of the support. I might just have to let it go for now and look at rebuildling from scratch to see if that elminates the problem.
Jul 14 '10 #16

mseo
100+
P: 181
@slenish
hi,
try to set the format of the dayoff control in the From properties to short date

hope this helps
Jul 15 '10 #17

100+
P: 283
Hi MSEO,

I tried your idea and still nothing. I think its because of the name field. The names pull from a list and there is a comma in the name to seperate last from first.
Jul 15 '10 #18

mseo
100+
P: 181
@slenish
hi,
can you post the metadata of your table and controls names in your Form, please
Jul 15 '10 #19

NeoPa
Expert Mod 15k+
P: 31,768
slenish: I also tried to use the code that MSEO and NeoPa did but I get an Error 3075 Syntax error (Comma) in Query expression. So now i dont know whats going on.
Can you post exactly (use Copy/Paste) the code of the procedure that's crashing and indicate which line is throwing up the error. It's hard to know what we're dealing with otherwise.
Jul 15 '10 #20

100+
P: 283
Ok here is the error copy and pasted...


Run-time error '3075':
Syntax error (comma) in query expression
'(([EmployeeName]=Smith, John) AND ([DayOff]=#7/2/2010#))'.

I used the code that you posted and changed the names to my table name, column names, and form box names. Also im wondering if the date could be a problem because i have the date on the table set to Long, and the date on the form is set to short??

Also im putting this in the onClick of the command button to save the form should is that the best place to put it? I tried to put the code in the forms before and after update but that didnt work either.

Thanks :)
Jul 16 '10 #21

mseo
100+
P: 181
hi, slenish
take a look at the attached DB, It would solve your issue
but you should set the datatype of the date field in your table as date/time, then put the code in beforeupdate trigger and add some code to prevent the personid and dayoff date controls from being null in your form
P.S if the personid control is combobox or whatever as the datatype of personid is number it doesn't make any difference in the combobox and the format of the name within it, because the name in the combobox is actually the PK of the table
the code would be like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If DCount("[pkj]", "tbl_name", "[dayoff]=" & Format(Me!Dayoff1, "\#mm\/dd\/yyyy#") & " AND [personID]=" & Me!personID1) > 0 Then
  3.     MsgBox "this record is already exist", vbExclamation
  4. Me.Undo
  5. End If
  6. End Sub
Hope this helps
Attached Files
File Type: zip MSEODEMO.zip (122.8 KB, 160 views)
Jul 17 '10 #22

100+
P: 283
Hi MSEO,

Thanks for the demo DB I tried it and it works great. I did some adjustments to it changing the personID field to a text instead of a number, and im getting an error (2001) now. Its interesting how I keep getting problems. Almost like its not meant to be haha! I'm still playing with it seeing if i cant figure something out. Thanks a lot for the help. :)
Jul 19 '10 #23

mseo
100+
P: 181
@slenish
so it would be like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If DCount("[pkj]", "tbl_name", "[dayoff]=" & Format(Me!Dayoff1, "\#mm\/dd\/yyyy#") & _
  3. "AND  [personID]= '" & Me!personID1 & "'") > 0 Then
  4.     MsgBox "this record is already exist", vbExclamation
  5. Me.Undo
  6. End If
  7. End Sub
  8.  
hope this helps
Jul 19 '10 #24

mseo
100+
P: 181
@slenish
or use this syntax:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If DCount("[pkj]", "tbl_name", "[personID]= '" & Me!personID1 & "' And [dayoff]=" & Format(Me!Dayoff1, "\#mm\/dd\/yyyy#")) > 0 Then
  3.     MsgBox "this record is already exist", vbExclamation
  4. Me.Undo
  5. End If
  6. End Sub
the difference here, I removed the additional double qoute
hope this helps
Jul 19 '10 #25

Delerna
Expert 100+
P: 1,134
Slenish
in the error

'(([EmployeeName]=Smith, John) AND ([DayOff]=#7/2/2010#))'.

This bit looks a little strange to me

[EmployeeName]=Smith, John
I was expecting to see something more like
[EmployeeName]="Smith, John"

Are you sure you haven't forgotten to put the quotes around the name when writing your code?

like this
Expand|Select|Wrap|Line Numbers
  1. If DCount("*", _ 
  2.           "[TableName]", _ 
  3.           "(([PersonID]=" & chr(34) & Me.PersonID  & chr(34) &  ") AND (" & _ 
  4.           "[DateField]=#" & Format(Me.DateControl, "m/d/yyyy") & "#))") > 0 Then 
  5.     Call MsgBox("This record already exists.", vbExclamation) 
  6.     Cancel = True 
  7. End If 
  8.  
Added bits in bold
I think 34 is ascii for "


Alternatively use single quotes like MSEO did
The important point is the quotes surrounding the name and I think you might be missing it
Jul 19 '10 #26

NeoPa
Expert Mod 15k+
P: 31,768
I think the boys have already pointed you in the right direction. Just to clarify though, why this would be different from the code I posted. I posted code that worked with a [PersonID], and my assumption was that the data would be numeric. As it appears you are working with string, or text, data, the literals must be enclosed in quotes (single preferably for SQL). See Quotes (') and Double-Quotes (") - Where and When to use them for a fuller explanation of this.
Jul 19 '10 #27

100+
P: 283
Sorry it took me a little while to come back around to this post! I had to get away from this and take a break. Really frustrating!

I just tried out what Delerna posted and for some reason that one worked!! I dont know if its because of the chr(34) part or what. But it works now.

Thank you to everyone for helping me with this. Could not have got it with out all of your help!!

Also thanks for the link about Quotes NeoPa Very helpful, its nice to have a reference for those since you have to use them always so percisly or else nothing works right.

:D
Jul 28 '10 #28

NeoPa
Expert Mod 15k+
P: 31,768
slenish: Also thanks for the link about Quotes NeoPa Very helpful, its nice to have a reference for those since you have to use them always so percisly or else nothing works right.
Absolutely. Exactly right.
Jul 29 '10 #29

Post your reply

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