Solved the recordset is not updatable (1 Viewer)

Number11

Member
Local time
Today, 20:14
Joined
Jan 29, 2020
Messages
607
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

I’m here to help
Staff member
Local time
Today, 13:14
Joined
Oct 29, 2018
Messages
21,358
Hi. Can you post the SQL statement of your query?
 

Number11

Member
Local time
Today, 20:14
Joined
Jan 29, 2020
Messages
607
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

Super Moderator
Staff member
Local time
Today, 16:14
Joined
Jan 23, 2006
Messages
15,364
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

Member
Local time
Today, 20:14
Joined
Jan 29, 2020
Messages
607
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

Super Moderator
Staff member
Local time
Today, 16:14
Joined
Jan 23, 2006
Messages
15,364
Ok, then go tot he query "tobeworked" ; design view, SQL and copy the code; then paste to a post.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:14
Joined
Feb 28, 2001
Messages
27,001
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

Member
Local time
Today, 20:14
Joined
Jan 29, 2020
Messages
607
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

You've got your good things, and you've got mine.
Local time
Today, 16:14
Joined
May 21, 2018
Messages
8,463
sqlview.png
 

Number11

Member
Local time
Today, 20:14
Joined
Jan 29, 2020
Messages
607
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"));
 
Last edited:

cheekybuddha

AWF VIP
Local time
Today, 20:14
Joined
Jul 21, 2014
Messages
2,237
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:
Code:
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

Member
Local time
Today, 20:14
Joined
Jan 29, 2020
Messages
607
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:
Code:
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

AWF VIP
Local time
Today, 20:14
Joined
Jul 21, 2014
Messages
2,237
>> 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

Member
Local time
Today, 20:14
Joined
Jan 29, 2020
Messages
607
>> 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

AWF VIP
Local time
Today, 20:14
Joined
Jul 21, 2014
Messages
2,237
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

Immoderate Moderator
Staff member
Local time
Today, 15:14
Joined
Feb 28, 2001
Messages
27,001
OK, you still get the "query not updateable" error? Here is an article to consider:


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.
 

Users who are viewing this thread

Top Bottom