This Recordset Is Not Updateable

[scoughlan]

I have a query created from 2 linked tables (see attached screen dump). If I create a new record I can fill in the fields from the tblPatients but not those form tblPlanningList. I went through Roger's Access Blog which was very helpful and I found that following his solution no.5 under Recordsets Are Updateable Under Certain Conditions does indeed work.



http://rogersaccessblog.blogspot.com...eable-why.html

That is, if I complete the fields from the first table, then save, it then allows me to completed the fields from the second table. Is there anyway to make this automatic? Sorry for being dumb: I'm just new at this!

[TheShabz]

Can you give an example of what you want to do? Of course, a PatientID has to exist in tblPatients before you can create a PlanningList entry for that Patient.

[scoughlan]

Well, in the query it's not very intuitive for the user to add the first three fields, and then know they have to save before adding the rest of the fields. I just wondered if there was a way to make Access automatically create a blank record for the second table, so the user just tabs accross? Or, am I using the wrong device? Would it work in a form? I created a form with a subform with some of the fields from each, and that works, but all I really want is a datasheet line that users tab across and fill. I'm beginning to regret separating the data into 2 tables, but it's to help avoid duplicates of the patients. Each patient can have many Planning Records but each Planning Record mus only have one Patient. Thanks for your help, by the way.

[TheShabz]

ok lets say you have 3 textboxes:
txt1
txt2
txt3

txt1 and txt2 are for the parent table and txt 3 is for the child table. In the AfterUpdate of txt2 force a save with:
If Me.Dirty Then Me.Dirty = False.
Source

[boblarson]

The problem is due to the fact that you are dealing with a one to many situation. I would be using a form/subform for this but if you want the quick way to deal with it so you can have them all on a single line - just open the query in design view, right-click on the gray area in the Query By Example Grid where the table shows up and select PROPERTIES. Then change the Recordset Type from Dynaset to Dynaset (Inconsistent Updates).

[scoughlan]

Thanks Bob, that's amazing. Such an easy fix and it works perfectly now!