Code to Remove Duplicates with a Button Control 3

[Stangleboy]

Alright this is going to be a little difficult and I do not know where to start and what forums to use (VBA, Access Forms or SQL) but I believe the answer lies in VBA coding so I am posting on this forum. I have an Access database with a SQL back-end for training function. I had the system working in perfect Business logic but the customer wants to shift away from logic.
I have a form called course and a sub-form that list the employees taking the course, to stop duplicate employees from being entered I made the employee number, course number and no show fields in a transition table- unique in the SQL table, perfect logic. The issue is that, there are multiple ways to get employees enrolled into the a course (InfoPath, e-mail, using the Access form, etc.), staff (aka- bosses and big wigs) did not want to waste time addressing the errors of duplicate employee from being entered, since they can add staff via building, or classification or hire date in groups and 1 or 2 employees (that were already enrolled be other means) could cause the duplication error.
So the solution I see is that the bosses and big wigs can enter in the staff as they see fit without getting duplication errors and then the training staff will click a “button” control on the course form and VBA code would run to delete the duplicates or triplicate (so on) on the course to close enrollment. Is there anyone that knows VBA code that will search the sub-form for duplicates/triplicates (leaving one record) and delete the rest?
Please help, the idea sounds good just need to implement, thank you all for your time in advance.

 

[Andrzejek]

Just a shot in the dark here, but if you make your field in your DB unique and do not allow duplicate entries in it, you will not get them twice, or trice (is that a word?) And as for “duplication errors” – just handle it in your code and don’t even display it to the user. Done.

Have fun.

---- Andy

 

[Stangleboy]

Great suggestion, and I tired that but then the job fails and if they were adding a group of 50 employees through InfoPath or Access control, and there was only one duplicate, then none of the employees get added.

 

[randy700]

Here's a roundabout solution.
Let all of your entry methods populate a table that allows duplicates.
Create a query that appends the entries from that table to your actual table, which will not allow duplicates.
When running the append query, the duplicates will be ignored and all other entries will be added to your table.
Follow up with a delete query to empty the first table.


Randy

 

[Andrzejek]

You may start by finding duplicates (or multiplicates).

Here's a handy query for finding duplicates in a table. Suppose you want to find all email addresses in a table that exist more than once:

SELECT email, 
 COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

Have fun.

---- Andy

 

[TheAceMan1]

How are ya Stangleboy . . .

[blue]So the solution I see is that the bosses and big wigs can enter in the staff as they see fit without getting duplication errors and then the training [purple]staff will click a "button"[/purple] control on the course form and VBA [purple]code would run to delete the duplicates[/purple] ...[/blue]

Have a look here: faq701-5721

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Stangleboy]

These are great answers I will try them all to see which ones works with the flow, thank you all for your time.

 

[Andrzejek]

the training staff will click a "button" control on the course form and VBA code would run to delete the duplicates or triplicate (so on) on the course to close enrollment.

I wouldn't use the 'button' to do this.

I would have a little procedure at the end of adding new records and run it every time. Automatically when new rocrds are added. So you don't have to use any button or any user interaction to remove multiple entries.

Have fun.

---- Andy