"You may not go to specified record" when trying to add a new reco

[Robman]

A search of the above error indicates (most of the time) that there is a
required field missing. After researching this extensively I cannot find a
way to determine what is the true cause of this error since I have checked
the form and the query and table definition until my eyes grow fuzzy and
cannot find which field that is required is not being filled in!
A SQLTrace does not show anything as it would appear that the attempt to add
the new record is blocked by Access.
Another hint might be that the form will not allow me to create a new record
either if I use the DoCMD.Gotorecord,,acNewRec command or if I try to use the
form navigation buttons as the new record one is greyed out.
I keep looking for an error log somewhere that gives more information on
"what" field is missing but cannot find one.
Does anyone have any idea on how you can troubleshoot this more definitively?
Thanks
Robman

[Tony Toews [MVP]]

Post by Robman
A search of the above error indicates (most of the time) that there is a
required field missing. After researching this extensively I cannot find a
way to determine what is the true cause of this error since I have checked
the form and the query and table definition until my eyes grow fuzzy and
cannot find which field that is required is not being filled in!
A SQLTrace does not show anything as it would appear that the attempt to add
the new record is blocked by Access.

SQL Server backend? I recall something along the lines of either
there is no primary key on the table or Access didn't know about the
primary key.

Also a TimeStamp/RowVersion field is strongly suggest on tables which
Access updates.

Tony

[Robman]

Tony:
Thanks for the reply.
I had discoverd the need for the Unique Identifier earlier BUT forgot a
couple of steps that would be required since I was working with a linked
table.

So if anyone else has this problem here is the solution:

1) Make sure that you have a unique index on the table in question. I
recommend an integer that you set as an idendity and then increment by the
seed number (in Access this was easier to do - you just used an Auto Number)
2) After creating the unique index you must delete the linked table and
relink it to your Access Frone End.
3) You must edit your query (if not using the table as a record source for
your form) to add the unique identifier to your query.
4) You may need to add the unique identifier to the form in question.

That should do it. It has fixed everyone of this type of problem that I had
with the upsized database. It would appear that for whatever reason the
unique identifiers that were in the tables before the upsize did not come
accross.

Robman

Post by Tony Toews [MVP]

Post by Robman
A search of the above error indicates (most of the time) that there is a
required field missing. After researching this extensively I cannot find a
way to determine what is the true cause of this error since I have checked
the form and the query and table definition until my eyes grow fuzzy and
cannot find which field that is required is not being filled in!
A SQLTrace does not show anything as it would appear that the attempt to add
the new record is blocked by Access.

SQL Server backend? I recall something along the lines of either
there is no primary key on the table or Access didn't know about the
primary key.
Also a TimeStamp/RowVersion field is strongly suggest on tables which
Access updates.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/