“This recordset is not updateable”

[DeanRowe]

Hi,

I am trying to check a “Check Box” on my query, but Access won’t allow me to do so as “This recordset is not updateable” - Could anyone please offer an idea of how I can get around this problem…..

I buy lots of products from a supplier, for each product ordered I assign an order ID, and have a check box called “Delivered” for each product to show whether it has been delivered or not. When the delivery arrives I print out our order onto paper and tick each product that has arrived, then I go onto the Access database and tick all of the boxes for the products that have arrived.

This way, any products that haven’t arrived and have not been checked show up on a query to show all products that have the “Delivered” field unchecked.

9 times out of 10 all of the products arrive, so I want to perform a group query on the order ID, and then check the box for the whole order to show that all the products arrived – rather than having to tick off each product (sometimes there can be up to 50 products, and I’ve already gone through the process once on paper so if they are all there I don’t want to have to do it all over again).

For example, the following shows the printed report for the order “1234”:

[Order ID] [Product ID] [Supplier ID] [Delivered]
1234 1 Supplier A NO
1234 2 Supplier A NO
1234 3 Supplier A NO
1234 4 Supplier A NO
1234 5 Supplier A NO
1234 6 Supplier A NO
1234 7 Supplier A NO
1234 8 Supplier A NO
1234 9 Supplier A NO

I’ve been through the delivery from Supplier A and all of the products are there.
I perform a group query on the “Order ID”, “Supplier ID” and “Delivered” fields to return the following

[Order ID] [Supplier ID] [Delivered]
1234 Supplier A NO

I want to be able to tick the “Delivered” check box and it automatically check each “Delivered” box for the 9 products mentioned above.

Does anyone know if this is possible? I’ve spent ages going through the properties of the query, changing the Recordset Types and trying to use forms, and going through help files but I can’t find any options. I did come across the possibility of using an update query – however I don’t know how to perform a “Group By” total on the “Order ID”.

Any help or advice would be very much appreciated.

Thank you for your time.

Dean

 

[RuralGuy]

See if this link sheds any light on your issue.

 

[ajetrumpet]

I perform a group query on the “Order ID”, “Supplier ID” and “Delivered” fields to return the following

[Order ID] [Supplier ID] [Delivered]
1234 Supplier A NO

I think that pretty much answers the "non-updateable" mystery for you.

I want to be able to tick the “Delivered” check box and it automatically check each “Delivered” box for the 9 products mentioned above.

Does anyone know if this is possible?

Sure it's possible, but not going about it at the query level. If I wanted to make this as elegant as possible, I would make a search form, search for the order ID, then populate a few controls with information about that order. Then run an SQL command (UPDATE) that would update the checkboxes to "True" for all the product ID's that I have not listed in another control on my form. How about something like this process...??

 

[DeanRowe]

Thank you once again RuralGuy, the proble seems to be "It has a GROUP BY clause. A Totals query is always read-only."

Is there any way to skirt the issue? for example by somehow coding an update query to return the same results? or should I just face the facts and move onto something else?

Thank you again RuralGuy, you've been a godsend on this project.

 

[DeanRowe]

thank you ajetrumpet, it worked perfectly!

thanks again!