Access Datasheet Issue

[new]

Hi,



I just moved two tables from access to sql.

AccountRegistration
Annuities

I had created one form with a subdatasheet based on these two tables. Once I linked the tables through sql this form is working fine.When I add new record I am able to update the fields in subdatasheet too.

On the same two tables I had created a datasheet with a joint query which lists fields from both the tables and I had given add and edit permission on this form so I could just start the new row and start adding data in both table fields. But Once I moved the tables to sql I am not able to add records in the columns from the second table i.e annuities.SO if I start adding data in column from table account registration it lets me add and then I tab to a column in Annuities it does not let me add any data.

I tried refreshing and requerying but that is not helping. Any suggestions are welcome. Please help.

Thanks

[RuralGuy]

See if there is anything in this link that will help pin down the problem: http://allenbrowne.com/ser-61.html

[new]

Thanks for replying.

I went through the list on the link but all of the settings suggested are in right order.

The thing is the same query was updatable when the tables were in Access but when I moved to Sql some how something changed and it became unupdatable.

Also if I go to that query window and try to append or update it. It allows me to run that query. it does have all the options for append and update available for the query.
I am able to update the rows that are already existing. It just does not allow me to update the annuities table for the new row.

here's my query

SELECT [Account Registration].ID, [Account Registration].AccountRegistration1, [Account Registration].AccountRegistration2, Annuities.AccountRegistration, [Account Registration].ApplicantDOB, [Account Registration].OFACVerified, [Account Registration].RepLastName, [Account Registration].RepFirstName, [Account Registration].RepInitial, [Account Registration].CRDNumber, [Account Registration].MetafileID, Annuities.DateRecdAtMTL, Annuities.RegistrationType, Annuities.NewAcctApp, Annuities.InvAckForm, Annuities.InvRepLetter, Annuities.VAForm, Annuities.TransactionOrderForm, Annuities.IdemAgreement, Annuities.SponsorAPP, Annuities.SponsorName, Annuities.SponsorDirectFormsIncluded, Annuities.CheckAmount, Annuities.CheckNumber, Annuities.CheckPayor, Annuities.CheckPayee, Annuities.ForwardedOn, Annuities.By, Annuities.InternalComments, Annuities.AgentComments, Annuities.FollowUp, Annuities.Completed
FROM [Account Registration] LEFT JOIN Annuities ON [Account Registration].ID = Annuities.AccountRegistration
WHERE (((Annuities.AccountRegistration)=[Account Registration].[ID]) AND ((Annuities.DateRecdAtMTL) Between [Forms]![Products Data by Date]![StartDateCtl] And [Forms]![Products Data by Date]![EndDateCtl]));


Thanks again for your help.

[new]

the issue was resolved. The issue was in the property of the query. I had not mentioned parent and child fields and it was working fine in access but that did create issue when the database moved to sql.

[RuralGuy]

That's great! Thanks for posting back with your solution.