Changed BE to SQL - Sub form no longer allows data entry

[PeterOC]

Hi,

I've moved my database backend tables etc to SQL Server, keeping the mdb format for the front end. My problem is with one of the forms. The subform no longer displays to allow data entry. If there are existing records the controls are visible with the data and it's possible to edit the information.

Is there a fundamental difference in the way forms/subforms work when the Data source is sql?

The data source from the main form is:

SELECT tblDailyComments.*
FROM tblDailyComments
WHERE (((tblDailyComments.JobID)=[Forms]![frmMainDataEntry]![ctlJobID]) AND ((tblDailyComments.OperationDate)=[Forms]![frmMainDataEntry]![txtOperationDate]));

The data source for the subform is a table:
tblDailyOperatingData

The Form / subform are linked on a field called DiaryID for which initially there are no records that link the two data sources.

Any help would be much appreciated,

Peter.

EDIT: PS there's no relationships set up between the tables involved in either of the DB's.

 

[pbaldy]

Make sure the tables have key(s) in SQL Server, or you won't be able to add records. SQL Server is more sensitive to that than Access is.

 

[PeterOC]

Thanks Paul for the reply,

There was a previous DB (Maintenance) that was upsized to SQL server and is up and running. I'm currently migrating another DB (Operations). The Operations.mdb already contains some tables that link to the SQL server tables of Maintenance. One of these tables doesn't contain a primary key but as the original Operations DB is showing the subform controls I don't think this is the problem.

I'm going to correct the table anyway to give it a primary key. There's a column DODIndexID that looks like it's supposed to have unique values but it contains 500 odd duplicates.

I've got 2 versions of the Operations DB. The original is showing the subform detail. The backup isn't. I can't find any difference between the 2 DB's as one is a copy of the other. The only difference seems to be that the data entry line at the bottom of the subform is missing on the backup DB. When I open up the subform in a new window for the original DB the Data Entry property is set to NO. I don't get it.

I hope I've explained vaguely coherently. If you have any further suggestions I'd love to hear them.

atb,

P