'This recordset is not updatable'

[MA04]

Hi all,

I was wondering if it is possible to allow the user to update (edit) a query that at present does not allow me to edit it, it has message 'This recordset is not updatable', is it possible to get around this problem?

Thanks in advance,
MA.

 

[cheerio]

A recordset is not updateable if the database cannot be sure which record in which table you would be trying to change. By definition therefore you cannot allow the user to edit.

You need to redefine your query so that it is updateable.

If you want to continue the debate post the SQl for your query and identify the primary key in each table.

Note a form will normally have one table driving the form and sub forms showing data in related tables. Generally you should not base a form on a join. The point here is that the parent table rows would be repeated for each row in the related table which again creates update problems.

 

[MA04]

Thanks for the reply, i have also tried to change the properties of the query recordset to dynaset(Inconsistent Updates), which allows me to update one field which is not an expression (postcode below) but does not allow me to edit any of the others it gives message: 'Field ... is based on an expression and can't be edited'.

I have pasted my query below and have highlighted the three expression fields that its not allowing me to update if it helps:

SELECT I1.postcode, IIf(IIf(Len(Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1))>0,Len(Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1)),0)<4,Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1) & " " & I1.Strd & I1.STR,Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1) & IIf(IsNull([Strd]),"",", " & [Strd]) & IIf(IsNull([Strd]) Or Not IsNull([Locdd] & [Locd]),IIf(IsNull([STR]),'',', ' & [I1].[STR]))) AS Expr1, [/COLOR RED] IIf(IsNull([Locd]) And IsNull([Locdd]) And Not IsNull([Strd]),[I1].[STR],IIf(IsNull([I1].[Locdd]),[I1].[Locd],[I1].[Locdd] & IIf(IsNull([Locd]),'',', ' & [Locd]))) AS Expr2, [/COLOR BLUE] IIf(IsNull([I1].[PTN]),[I1].[CNT],[I1].[PTN] & IIf(IsNull([CNT]),"",", " & [CNT])) AS Expr3 [/COLOR GREEN]
FROM ma_dbo_uk AS I1, integers AS S1, integers AS S2
WHERE (((I1.postcode) Like [Enter Postcode:]) AND ((Mid$(";" & [I1].[PRMF] & ";",[S1].[num],1))=';') AND ((Mid$(";" & [I1].[PRMF] & ";",[S2].[num],1))=';') AND (([S1].[num]+1)<[S2].[num]) AND ((InStr(1,Mid$(";" & [I1].[PRMF] & ";",[S1].[num]+1,[S2].[num]-[S1].[num]-1),";"))=0));

Any help very much appreciated,

MA.

 

[PHV]

How access may know which field to update in which table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MA04]

Hi PHV,

I wanted to allow the user to edit any of the fields in the query, it does not need to update the original table but just the query view. This is because i have the query bound to a form, so the user will edit the query on a form and a command button on the form will allow me to append the changes to the relevant table.

M-.

 

[PHV]

will allow me to append the changes to the relevant table
Really ?
You're able to know which field in which row to update in ma_dbo_uk if any of this 3 calculated column is modified ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MA04]

Sorry bad explanation,
What i meant was when the above query is run on form view text boxes are populated, i have a command button which runs a append query appending the fields of the record being viewed to another table not the table the search query above is searching through which is ma_dbo_uk but table ma_enq. I would like to give the user the option of editing any mistake/omissions before appending to the other table ma_enq.

Hope thats clearer,
M_.

 

[PHV]

I suggest you to convert your query into a maketable one, then bind your form to this temporary table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MA04]

Thanks very much for that suggestion, but how would i allow the user to keep using the make table query on my form. What i mean is i want to allow the user the option of having multiple searches, but how would i go about incorporating this on my form.

M-.

 

[MA04]

The problem i get with the make table query at present is that the user will have to create the temp' table before entering the form, so it is not alllowing the user the option of multiple seraches with it. The problem i get if i want to create a table with all the data is that the table is too big, it would create millions of records.

However is it possible on the from to have text boxes bound to the text boxes that are bound to fields in the query, in the hope it may allow me to edit the details in them and could have append button running off them. Do you understand what i mean?, if so how may i achieve this?

M-.

 

[MA04]

any ideas on how i may allow the user to edit the select query search result on form view via text and combo boxes,

Thanks again in advance,
M-.