This Recordset is not updateable

[KerryL]

I'm receiving this error when I click a Refresh button after adding data to a subform:

"This Recordset is not updateable"


I checked the MS Knowledgebase and found that this msg appears when trying to update a linked table that doesn't have a primary key. However, this isn't the case here; the table in question has a PK and is not externally linked. (One of the fields is linked to another table, but the table itself is not linked from outside of Access.)

Any suggestions would be appreciated.

TX
kerry

 

[SQLSister]

What is the query the subform is based on?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[willir]

This seems to be the same issue posted elsewhere...
Data in field disappears after refresh

I had requested some info too.

 

[KerryL]

Thank you for your willingness to help.

The subform isn't based on a query, it's based on a table. However, what appears in the subform is filtered so that only records associated with the record on the main form are displayed in the subform.

 

[willir]

If you have a required field that is not included in the "filter", you will not be able to update / create the record.

Once again, it would be helpful to have specific names of fields, Row and Record sources.

 

[KerryL]

Here are the properties for the form and for the field on it that keeps going blank. (I apologize for the lousy standards--I inherited this mess from someone else.)

FORM
Name: Services
Source: Services table
Filter: [ser_id]=340003 (of course, this varies)

FIELD
Name: Combo18
Control Source: customer_id
Row Source: SELECT DISTINCT services.customer_id, Business_Customers.OrganizationName
FROM Business_Customers INNER JOIN services ON Business_Customers.[ADU_CustomerD Number] = services.customer_id;


The filter is brought in from a previous form. The customer-id control source is brought in as well, and that's what needs to be saved to the services table. But when another field on the form is modified, the customer_id field goes blank and I get the error message.

If I try and save the record first I get an error msg stating that the DoMenuItem action was cancelled.

Thank you for your help.

 

[BNPMike]

Looking at this quickly, you can't update a DISTINCT query. Could that be it. Does it work if you take out DISTINCT?

 

[willir]

Hi Kerry

Mike's point about DISTINCT is possible. Since you are selecting against the table Business_Customers, I am hoping that there is only one customer per record.

However...

"This Recordset is not updateable"

... is only part of the decribed problem.


The other part, per another post ...

When detailed info is added to a subform on Form B and then a refresh button is clicked, no problem. However, if one of the fields on Form B is updated and then the refresh button is clicked, the Company name on Form B disappears--goes blank

I have seen this type action before where the control source and bound column don't match.

In review, let's look at your combo box Combo18

Control Source: customer_id
Row Source: SELECT DISTINCT services.customer_id, Business_Customers.OrganizationName
FROM Business_Customers INNER JOIN services ON Business_Customers.[ADU_CustomerD Number] = services.customer_id;

The three things not yet provided
- bound column
- number of columns
- column widths

You want to display the name of the customer, specifically, Business_Customers.OrganizationName.

To make this comb box work properly,
CoundColumn: 1 (customer_id on form match the data type customer_id on Services table)
ColumnCount: 2 (customer_id and OrganizationName)
ColumnWidths: 0";1.2" (hide 1st column cusomter_id, so 2nd column OrganizationName is displayed. The important thing is 0" - zero width; the 1.2" could be 1" or 1.5" -- not too important for this excecise))

The fact that the OrganizationName disappears may be because it is expecting a customer_id as defined by the ControlSource but the BoundColumn from your SELECT statement is providing something different that is not compatable with the customer_id on the Services table. Since the returned data is not valid, a blank is displayed.

Now, if Customer_ID is required for the Service table (which seems important to me), the record can not be updated because there is nolonger a valid customer_id value on the form.

ColumnCount is important because if the number does match the columns in the SELECT statement for the RowSource, Access will get confused.

Why does Refresh work -- the ControlSource for Combo18 is already valid and the Access will not try to reset the customer_id.

But when you enter "detail info" to the subform, you are some how triggering Combo18 to reload.

Another thing could also be happening as a result of the same action. Your "Form A" and "Form B" (subform?) have to be linked. I suspect customer_id may play a role in the linking between the two forms. With no customer_id, there can be no linkage between the forms. And what may be worse, you may be creating wacky / orphaned records (but I think you are safe here since I already stated that "customer_id" is probably required).

So, did I miss by a "country mile"?

Richard

 

[KerryL]

All the DISTINCT qualifier does is limit the list to unique records (since there are many service records per customer record). I tried taking it out but it acted the same way.

Richard, here is what you asked for:
Bound column = 1
No columns = 2
Col widths = 0;2

I didn't get a chance to try your suggestions today but I'll give it a shot first thing tomorrow. Thank you very much for the extensive help.

BTW, all I'm trying to do with the field in question is to bring the Company Name in from the previous form that was open. It only needs to be there visually so the user knows he's entering detailed service data for that company. However, when the service record is saved, it obviously needs to be linked (via the CustID) back to that particular company.

There's another issue on this form (the subform) but I'd like to take care of the disappearing Company Name issue first. Thanks again.