ADP - You can't go to the specified record

[SparkByte]

I have a "New Call" form that works just fine in the original Access Application but now that it has been converted to an ADP (Access Project) I cannot find a way to get the form to start on a new record.

I have tried

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
'Startnewcall_Click
End Sub

which errors out with "You can't go to the specified record."

and setting the "Data Entry" property on the form to "yes" causes the form to come up blank. In blank I mean nothing is visible on the form not even the field names much less where to enter the new record data.

Just so you know here is some background on this project.

There are 3 tables plus the Swtchboard that have been migrated/moved over to the SQL server. I have performed this to both SQL Server 2000 and 2005 for test purposes.

The version of access is 2003 so the migration works best with the SQL2k server. Well at least the Designer works anyway.

I spent about 2 weeks cleaning up table names and column names then going through the forms, reports, quearies, and VBA code to eliminate obvious problem areas before attempting to "upsize" the Access Application.

Because of problems with some of the Date and Time fields in the [Activity] table I have found it easiest to migrate that data through SSIS.

Getting the Add New Call form working I hope is the last stumbling block needed before letting the department test the "Upgraded" version.


Any help would be GREATLY appreciated!!!!!!!!!!!!!!!!!!

 

[missinglinq]

In a regular database, if a form is bound to a record source and

The record source is empty (as it is when Data Entry is set to Yes)

and

New records cannot be added

no controls will show on the form.

This behavior and the "You can't go to the specified record" both indicate that the form is, indeed, Read-Only.

The reasons that a bound form cannot have new records added include:

1) AllowAdditions for the form is set to No
2) The underlying query the form is based on is Read-Only
3) User doesn't have Write Permission for the folder where the data resides.
4) Form's Recordset Type is set to Snapshot

To see if #2 is the case, from the Objects Dialog box go to Queries and click
on the query that your form is based on to open it. Now try to enter a new
record directly into the query. If you're unable to do so, this is the cause
of your problem.

If your query is Read-Only, follow this link to Allen Browne’s article
explaining the reasons this happens.

http://allenbrowne.com/ser-61.html

I've no experience with using ADP and this may be something related to that or it may be related to the migration to SQL Server. I seem to remember that back ends on SQL Server create Read-Only scenario if it has no PK or Indexed field, but I can't swear to this.

 

[SparkByte]

Well I believe your suspiian on #2 are correct. I am at a loss as to why though. The account used to attatch to the backend SQL server has full sa priv's. Strang thing is that the ADP file connected to my SQL 2005 and SQL 2000 servers both behave the same. I can't add records to the table directly on either DB, which is making me think it has to do with security. I just can't seem to track down if it is SQL server or Access that is the culprit.

Thanks
John

 

[SparkByte]

Thanks for your help. I found the answer.

http://support.microsoft.com/kb/304179

John Fuhrman