Primary Key Violation in Update Query (1 Viewer)

[DocNice]

Hey, I am getting a primary key violation when I try to run an update query.

My primary key is a combination of two fields, ScheduleID and SchedulePage. That way for each schedule I can only have one Page 1, one Page 2, etc.


When I want to insert a page (say a new Page 2), I need to update the table so that Page 2 becomes Page 3, Page 3 becomes Page 4, and so on.

The problem is, since it starts at the bottom, when I tell it to increase the page number by one, it's conflicting with the primary key of the next record.

Any ideas? BTW, if the solution has to do with sorting, I need to make sure it's something that always defaults back to the correct sort, since users may be able to change the sort and accidentally save it. Plus, I'll need to do the same thing in reverse (delete a page).

DoCmd.RunSQL "UPDATE Pages " & _
"SET Pages.SchedulePage = [SchedulePage]+1 " & _
"WHERE (((Pages.ScheduleID)= " & varScheduleID & ") AND ((Pages.SchedulePage)>" & varSchedulePage & "));"

 

[Vassago]

Do you need to have a primary key on that field? Generally, you don't want to have to change the data in your primary key later. You could accomplish this programmtically using VBA, if that is an option. A simple query won't work unless you take off the primary key.

 

[DocNice]

I need to make sure that there are NEVER two records with both the same ScheduleID and SchedulePage in this table.

If it can't be done with the primary key this way, can you point me in the right direction for doing this programmatically with VBA?

BTW, this is an access interface to SQL tables on a server.

Thanks!

 

[DocNice]

Alright, I figured a workaround. I created a query which listed my primary key field, SchedulePage, in descending order.

Then I ran the Update query on the query instead of the table.