Stop duplicate records on table?

[slenish]

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!

[colintis]

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:

Private Sub cmdNew_Click()
on error got MyErr
    DoCmd.GoToRecord acActiveDataObject, , acNewRec
    Call CarryOver(Me)
MyExit:
  exit sub
MyErr:
  if err.number<>2105 then
    msgbox err.description
  end if
  resume MyExit
End Sub

In another way, you can use recordset to loop through the records and see if there's matching values.

Do While Not [Your table with record].EOF
     If [Enter date] = [Date in table] then
         If [Enter Name] = [Name in table] then
              save the record
         end if
     end if
     [Your table with record].movenext
loop
 

[NeoPa]

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.

[ADezii]

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.

[OldBirdman]

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().

[slenish]

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.

Private Sub Command75_Click()
On Error GoTo Err_Command75_Click
 
 
    DoCmd.GoToRecord , , acNewRec
 
Exit_Command75_Click:
    Exit Sub
 
Err_Command75_Click:
    MsgBox err.Description
    Resume Exit_Command75_Click
 
End Sub
 

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

[NeoPa]

Is the recordset updatable?

A non-updatable recordset is usually the cause for such an error.

[ADezii]

@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.

[slenish]

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?

[NeoPa]

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.

[slenish]

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?

[mseo]

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

If DCount("[pk]", "tbl_name", "[dayoff field]='" & Me!dayoff& "' AND [personID]=" & Me!personID) > 0 Then
    MsgBox "this record is already exist", vbExclamation
    Me.Undo
    Cancel = True
 

I assumed that the dayoff field is text and the personId is number
hope this helps

[NeoPa]

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 :

If DCount("*", _
          "[TableName]", _
          "(([PersonID]=" & Me.PersonID & ") AND (" & _
          "[DateField]=#" & Format(Me.DateControl, "m/d/yyyy") & "#))") > 0 Then
    Call MsgBox("This record already exists.", vbExclamation)
    Cancel = True
End If

[ADezii]

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.

[mseo]

oh, sorry
I assumed that dayoff datatype is number, but it should be date/time and this requires additional syntax as Neopa did

[slenish]

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.

[mseo]

@slenish
hi,
try to set the format of the dayoff control in the From properties to short date

hope this helps

[slenish]

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.

[mseo]

@slenish
hi,
can you post the metadata of your table and controls names in your Form, please

[NeoPa]

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.

[slenish]

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 :)

[mseo]

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:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("[pkj]", "tbl_name", "[dayoff]=" & Format(Me!Dayoff1, "\#mm\/dd\/yyyy#") & " AND [personID]=" & Me!personID1) > 0 Then
    MsgBox "this record is already exist", vbExclamation
Me.Undo
End If
End Sub

Hope this helps

[slenish]

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. :)

[mseo]

@slenish
so it would be like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("[pkj]", "tbl_name", "[dayoff]=" & Format(Me!Dayoff1, "\#mm\/dd\/yyyy#") & _
"AND  [personID]= '" & Me!personID1 & "'") > 0 Then
    MsgBox "this record is already exist", vbExclamation
Me.Undo
End If
End Sub
 

hope this helps

[mseo]

@slenish
or use this syntax:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("[pkj]", "tbl_name", "[personID]= '" & Me!personID1 & "' And [dayoff]=" & Format(Me!Dayoff1, "\#mm\/dd\/yyyy#")) > 0 Then
    MsgBox "this record is already exist", vbExclamation
Me.Undo
End If
End Sub

the difference here, I removed the additional double qoute
hope this helps

[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

If DCount("*", _ 
          "[TableName]", _ 
          "(([PersonID]=" & chr(34) & Me.PersonID  & chr(34) &  ") AND (" & _ 
          "[DateField]=#" & Format(Me.DateControl, "m/d/yyyy") & "#))") > 0 Then 
    Call MsgBox("This record already exists.", vbExclamation) 
    Cancel = True 
End If 
 

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

[NeoPa]

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.

[slenish]

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

[NeoPa]

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.