I am currently getting a very odd key violation when appending values to a table. The strangest thing is that it is only happening some of the time.
I have a form where people can create schedules. They can create single day events or they can select events that happen every Tuesday from today until the last day of September for example. Their schedule gets parked into a temporary table where they can then edit any details (for deleting bank holiday dates, etc.,) and they then click a button to append the data to the schedule table.
When I do single-day events they append fine, but with the multiple dates I am having some problems and can't work out why. This is because there seems to be no rhyme and reason. When I select dates that are on the same dates as ones already in the schedule table I sometimes get a 'key violation' error. This only happens sometimes. For instance I just chose 10 different dates to append, all which were already featured in the schedules table and only one had a key violation. I then tried adding the same dates again with different data and all 10 had key violations.
In the schedules table the primary key is an autonumber. I have set the 'dates' field as Indexed Yes (Duplicates Ok) and made sure that all other fields are either not indexed or specify Duplicates Ok.
Can anyone think what this problem could be? I am really at a loss at the moment. I keep testing to try and find the problem but just can't work out why exactly this error is coming up.
I can now definitely say that it is a problem with the 'dates' field. I have changed the append query so that the only fields being appended are the 'date' field and one other and each time there is a key violation. If I append all data except for the 'date' field it appends file and if I only append the 'date' field it works fine, but as soon as I try and append the 'date' field along with any other field, be it a number, text or memo it has a problem.
What the devil is going on? Has anyone experienced anything like this before?