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

    
   [B] DoCmd.GoToRecord , , acNewRec[/B]

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]

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

    
   [B] DoCmd.GoToRecord , , acNewRec[/B]

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

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