Checking For Duplicates

[ddrew]

I have a Listbox on a fprm which has a button attached to it, the button allows you select one of the items in the listbox and append it to a table. the problem is that it currently allows you to add the item as many times as you like. How can I check for duplicates?

Private Sub CmdAdd_Click()
'Add new record to tblShootingTasks
    strSQL = "INSERT INTO tblShootingTasks ( ShootID, ContactName, Task ) " _
             & "SELECT [Forms]![frmTasks]![ShootDateiD] AS ShootID, [Forms]![frmTasks]![Combo15] AS ContactName, [Forms]![frmTasks]![Frame17] AS Task;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL

End Sub

 

[Magster]

You could create a unique index on the table that would prevent duplicates from being added into the table.

 

[ddrew]

Dont know how to, thats the problem!

 

[Brianwarnock]

It's on the properties for the field in design view of the table.

Brian

 

[ddrew]

Ok I think we are talking about (Indexed Yes (No Duplicates))

That being the case it wont work in this particular circumstance as the table holds names that populate three seperate listboxes on a form, an individual could potentially be in each list on a form and can be in more than one record.

So still not sure how to go about this!

 

[Brianwarnock]

So it's not an item as per the first post but the combination of three fields that must be unique, correct? But I still don't understand your requirement as you say they can be in more than one record!

However you can create a multi field index , read here

http://office.microsoft.com/en-gb/a...index-to-improve-performance-HA010210347.aspx


Brian

 

[ddrew]

Ill try to explain, might have to post the DB. But here goes:

the table is populated by selecting the indiviudal from a ListBox and clicking the add button (append qry) The person can be designated as a beater, a picker up or a gun, when they are added to the table they are also allocted to Shoot via a ShootID. So a person can be in the table lots of times as either a beater, a picker Up or a Gun but can only be allocated to a ShootID once but can be associated to many ShootIDs. (My origional post was incorrect)

Hope that makes sense, but I suspect not!

 

[Brianwarnock]

LOL It does not help me solve your problem off hand and I have to go, will revisit later, does my edit regarding multi field indexes help?

BTW I'm ancient and so is my software I can only view mdb not accdb

Brian

 

[Magster]

The first thing you have to do is decide what combination of fields will make your record unique. The easiest way to think about this is if you are building a table of cities within a country - an example would be San Jose within Calif within the US. So your unique index would be a combination of the following field names: Country, State, City.

You have to create this index in your table by following the instructions provided by Brian to create an index that would prevent duplicates of this combination. When you do this, Access will prevent the user from trying to add San Jose Calif US twice.

 

[ddrew]

Ok ive had a good go at this but Im not getting the results that I want.

By doing it this way I can only add a name to the table once. I want to be able to add it many times but only once to any record!

My Field Names are:

IDTaskings - AutoNUmber (Primary Key)
ShootID - Number (This is the ID of the Shoot that they are being tasked to)
ContactName - Text (The name of the person)
Task - Text (The task that they will be doing on that Shoot)

The name should be able to appear many times in this table but only once against any ShootID

To explain furthur:

Matt Smith has been assigned to ShootID 001 with a job as beater, he could also be assigned to ShootID 002 also as a beater.
He could be assigned to ShootID 003 as a PickerUp!
But he shouldn't be able to be assigned to ShootID 003 as a Beater because he's already been assigned a a PickerUp!

Hope that makes sense!

 

[Brianwarnock]

So your unique identification is shootid+contact name+task, did you try that?

Brian

 

[ddrew]

Yup, tried that and it allowed me to add Matt Smith as a beater to ShootID 001 but not to ShootID 002 as anything at all.

 

[Brianwarnock]

That would suggest that you have not got the unique ids correct or perhaps still have no dups on contactname, I think that we might need to see your DB to make further progress.

Brian

 

[ddrew]

OK heres the file, unfortunatley I cant downgrade the version to mdb as it says Im using a lot of the new features!

If anyone can open it, open frmEntries and create a new entry. Close the form then open frmTasks click on the one of the entries in the listbox and then choose Beater, Gun or Picker up and the listbox will populate. Select soneone and then choose ADD. If you go back to the frmEntries you should see that person added to the record.

Im trying to prevent being able to add the person to a record more than once!
any questions please ask.

 

[ddrew]

I wonder if somebody could have a look at my attached DB please. the problem I have is; if you open the form frmEntries and select the top date in the listbox you will see that I have added the same person to the same listbox on the same record more than once.

The name get added from the form frmTasks, I need to find a way of only allowing a person to be added to one of the three listboxes once on each record on the main form of frmEntries.