"Recordset is not updateable" for combobox in Ms Access 2007.

[HiGu]

I have a combobox that has the following as its rowsorure:

SELECT qryMIMATRIX.A_JOBNO FROM qryMIMATRIX;

The problem is that the combobox drops down but when I try to select any record, the status bar text is: "Recordset is not updateable"
qryMIMATRIX:

TRANSFORM First(qryMIMATRIXSELECT!A_PLANDATE) AS [FirstOfvolgende inspectie]
SELECT qryMIMATRIXSELECT.A_ID, qryMIMATRIXSELECT.A_PLANDATE, qryMIMATRIXSELECT.A_LOCATION, qryMIMATRIXSELECT.A_JOBNO, IIf(IsNull(qryMIMATRIXSELECT!A_PROJECTID),qryMIMATRIXSELECT!A_EQUIPDESCR,"**13M** " & qryMIMATRIXSELECT!A_EQUIPDESCR) AS exprA_EQUIPDESCR, qryMIMATRIXSELECT.A_NENGROUP, qryMIMATRIXSELECT.A_PRIORITY, qryMIMATRIXSELECT.A_MENO, qryMIMATRIXSELECT.A_DAYO
FROM qryMIMATRIXSELECT
GROUP BY qryMIMATRIXSELECT.A_ID, qryMIMATRIXSELECT.A_PLANDATE, qryMIMATRIXSELECT.A_LOCATION, qryMIMATRIXSELECT.A_JOBNO, IIf(IsNull(qryMIMATRIXSELECT!A_PROJECTID),qryMIMATRIXSELECT!A_EQUIPDESCR,"**13M** " & qryMIMATRIXSELECT!A_EQUIPDESCR), qryMIMATRIXSELECT.A_NENGROUP, qryMIMATRIXSELECT.A_PRIORITY, qryMIMATRIXSELECT.A_MENO, qryMIMATRIXSELECT.A_DAYO
ORDER BY qryMIMATRIXSELECT.A_PLANDATE
PIVOT qryMIMATRIXSELECT.periodes In ("0","2","4","6","8","10","12","14","16","18","20","22","24","26","28","30","32","34","36","38","40","42","44","46","48","50","52");

Allow Edits property is set to true.

I have another combobox that uses this query:

SELECT DISTINCT (qryMIMATRIX.A_LOCATION) FROM qryMIMATRIX UNION SELECT '<ALL>' from qryMIMATRIX;

and I am able to select values it works fine.

[Stewart Ross]

I suspect you are confusing two things here. If your combo is bound to a field in the underlying recordsource of your form it is not the combo's recordsource you need to look at. It is the recordsource of your form which is the problem.

If the form's recordsource is not updatable you will not be able to edit anything at all, nor will you be able to add new records.

We have an Insight article which gives further details on why queries may not be updatable, linked here for reference: http://bytes.com/topic/access/insigh...-non-updatable

-Stewart

[HiGu]

Sorry if I misinterpret you but then, how is the other combobox working?Also, the reocrdsource of the form is: qryMIMATRIX

[Stewart Ross]

Your other combo may well be unbound - that is, not bound to a field in the underlying recordsource. I don't know as you have not told us, but that seems the most logical explanation to me.

The queries you have posted do not help much with this question. The drop-down list component of a combo can be fed from any suitable table or query, regardless of whether that table or query is read-only or not. Note that the drop-down list is a different entity - a combobox combines a drop-down list with a textbox, and the textbox may or may not be bound to a field in the underlying form's recordsource. The listbox part of a combo may work perfectly; if the control is bound to a non-updatable field you will be unable to select a value from the drop-down list, no matter what you do.

qryMIMatrix which you show in post #1 is not updatable and never will be - it's a crosstab query after all, something which summarises data from multiple rows.

If qryMIMatrix is simply being used as the rowsource for the combo drop-down list it will not matter that it is not updatable. As I said in post #2, what will matter is whether or not the recordsource for your form is updatable.

-Stewart

[HiGu]

So dumb of me.I put the name of the column in the control source also and the combobox happens to be in the header section.I just tried keeping the control source blank and it worked.Thanks Stewart!
You pointed me in the right direction.