Solved the recordset is not updatable

[Number11]

So i have a form who's record source is a query which is looking at 2 tables to filter out by product. with the two tables within the same query i am unable to update the records and get this error, if i remove once of the tables it all works fine, so how do i get around this.

Is it best to Copy records to a temp table update them and then move them back - if so how do i do this without create a duplicate:?

 

[theDBguy]

Hi. Can you post the SQL statement of your query?

 

[Number11]

Hi. Can you post the SQL statement of your query?

uummm dont havr an SQL statement?

 

[theDBguy]

uummm dont havr an SQL statement?

Okay. Please explain what is the relationship between the two tables in your query.

 

[Number11]

Okay. Please explain what is the relationship between the two tables in your query.

Sure - Thanks - The Relationship is customer number to filter out only customers with a number 10 so the query only shows customer with 10 as there number. its filtering say 30 records to just 3

 

[jdraw]

If your form has a query as its recordsource, please copy and post the recordsource info.
Go to -->Form design, form properties, Data, recordsource

 

[Number11]

If your form has a query as its recordsource, please copy and post the recordsource info.
Go to -->Form design, form properties, Data, recordsource

Thats just showing the query name which is "tobeworked"

 

[jdraw]

Ok, then go tot he query "tobeworked" ; design view, SQL and copy the code; then paste to a post.

 

[Number11]

Ok, then go tot he query "tobeworked" ; design view, SQL and copy the code; then paste to a post.

dont have the SQL Option soz

 

[The_Doc_Man]

What version of Access are you running? And is this maintained by someone else? Because normally you can't NOT have the SQL option when viewing a query. Unless the query is somehow external to the database.

 

[Number11]

What version of Access are you running? And is this maintained by someone else? Because normally you can't NOT have the SQL option when viewing a query. Unless the query is somehow external to the database.

MS access 365 version 16.0 Build 11929, the tables are in backend and linked to from end

 

[MajP]

 

[Number11]

View attachment 79620

Ok, then go tot he query "tobeworked" ; design view, SQL and copy the code; then paste to a post.

SELECT Orders.[Customer Number], Orders.[Order date], Orders.[|Serial], Orders.[Delivereddate]

FROM Orders INNER JOIN Master ON Orders.[Customer Number] = Master.[Account No]

WHERE (((Orders.[Delivery Date]) Between Eval('Forms!Date_Working.txtBeginOrderDate') And Eval('Forms!Date_Working.txtEndOrderDate')) AND ((Orders.[Status])="Completed"));

 

[cheekybuddha]

Did you copy and paste this, or type it up yourself?

Where/What is table 'Outcome' ? Should that be 'Orders'?

Also, do you really have a field called '|Serial' ?

If so, you can just replace the SQL with:

SELECT [Customer Number], [Order date], [|Serial], [Delivereddate] 
FROM Orders 
WHERE [Delivereddate] Between Eval('Forms!Date_Working.txtBeginOrderDate') And Eval('Forms!Date_Working.txtEndOrderDate')
  AND Status ="Completed";

 

[Number11]

Did you copy and paste this, or type it up yourself?

Where/What is table 'Outcome' ? Should that be 'Orders'?

Also, do you really have a field called '|Serial' ?

If so, you can just replace the SQL with:

SELECT [Customer Number], [Order date], [|Serial], [Delivereddate]
FROM Orders
WHERE [Delivereddate] Between Eval('Forms!Date_Working.txtBeginOrderDate') And Eval('Forms!Date_Working.txtEndOrderDate')
  AND Status ="Completed";

yeah i typed in - so is your code looking in the master table for the customer number too?

 

[cheekybuddha]

>> so is your code looking in the master table for the customer number too <<
It doesn't need to it the tables have a defined relationship on that field. You'd normally use a combobox in your form based on the customer number from master table to enter that value.

Give us the true names of the fields for the Orders table, or fix them yourself and you should be off to the races.

 

[Number11]

>> so is your code looking in the master table for the customer number too <<
It doesn't need to it the tables have a defined relationship on that field. You'd normally use a combobox in your form based on the customer number from master table to enter that value.

Give us the true names of the fields for the Orders table, or fix them yourself and you should be off to the races.

it doesn't filter the customers - unless i add

FROM Outcome INNER JOIN Master ON Outcome.[Account Number] = Master.[Account No]

and once i do get the same error as b4

 

[cheekybuddha]

Hi,

Remember, we can't see your screen and don't know your database.

Please take a little time to describe to us the tables involved (table names, fields, datatypes, primary keys etc). Use real names, not similar names.

Or post a copy of your db, or a screenshot of your relationships table with all the table fields visible.

Thanks,

d

 

[The_Doc_Man]

OK, you still get the "query not updateable" error? Here is an article to consider:

Microsoft Access tips: Why is my query read-only?

List of reasons why a Microsoft Access query will yield non-editable results.

allenbrowne.com

In particularly, I would look to the reasons involving JOIN cases. First and foremost, check the indexing on the fields involved in that INNER join.