Recording duplicate entries question

[Chris1977]

Here is the situation i've encountered.

Employees will be receiving calls from our agents and will be given case numbers. Each of these cases will be recorded in an access database for reporting purposes.

The problem right now is that if agent "A" calls in with a case number of 154444 it will be logged......however is agent "B" calls in a few minutes later with the same case number the call can't be recorded because of the duplicate value.

I know we can set it up to all duplicates but for recording purposes that is not a good thing. We want only the first instance of the case number to be used for overall recording while the 2nd, 3rd etc instances will be recorded as well but not included in the reporting data.

The best thing that could happen is for every duplicate a number could be assigned to the end of the string.

For example agent A calls in with 45555 as their case number. Agent B calls in a few moments later with 45555 as their case number. Now what i want to have happen is:

Agent A's case number is recorded for reporting.
Agent B's case number is recorded as 45555-2 so it shows as a 2nd instance of that case. Is this possible? If so could some of you folks who are better programmers than i am give me some insight as to how.

Thanks
Chris

 

[Trendsetter]

Each case entry needs to be checked in the database to see if it exists, if it does then you need to count the number of records with that prefix and then add 1 to that number to be added to the entry you are making now.

In psuedo code:
open a recordset of the cases
isolate all records with the specific number at the left using Left([CaseID],5)
Count the number of records with rst.recordCount
Use the existing Case ID and concatenate the next number to it, something like:
NextNum = Left([CaseID],5) & "-" & (rst.RecordCount + 1)

 

[Chris1977]

Hey,

First off thanks for the reply. Unfortunately i'm not a programmer so pretty much all of what you posted went over my head. If you have time could you simplify that a bit for me..........maybe some sample code will make it clearer.

Thanks again
chris