Recordset not Updatable (1 Viewer)

cMcDowell23

New member
Local time
Today, 01:17
Joined
Jan 24, 2023
Messages
8
I have a Query and a form that uses this Query for a Purchase Order database for our company. We had this same database created for us a long time ago and throughout the years there has been changes we made that has now rendered the old database unusable. Now I am recreating the database based off the old one with a few tweaks. Now I have a query that is not updateable and can't figure out for the life of me why it isn't working... I have compared the SQL code to the old database SQL code but it hurts my brain because I have named things differently.

Long story short, I need the query to pull all the information and have the combo boxes pull up the lists of data and be able to select a new record if required.

Here is the SQL code for my query:
SELECT [Work Entry Summary].[PO#], [Work Entry Summary].Date, [Work Entry Summary].Customer, [Work Entry Summary].Requestor, [Work Entry Summary].[Scope Of Work], [Work Entry Summary].[Panel Invoice #], [Work Entry Summary].[Quote #], [Work Entry Summary].Status, [Work Entry Summary].[Customer PO/WO#], [Work Entry Summary].Location
FROM (([Work Entry Summary] INNER JOIN [Total Info Part 1 Query] ON [Work Entry Summary].[PO#] = [Total Info Part 1 Query].[PO#]) INNER JOIN [Total Info Part 2 Query] ON [Total Info Part 1 Query].[PO#] = [Total Info Part 2 Query].[PO#]) INNER JOIN [Total Info Final Query] ON [Total Info Part 2 Query].[PO#] = [Total Info Final Query].[PO#]
ORDER BY [Work Entry Summary].[PO#];

Let me know if you need any further information.
 

ebs17

Well-known member
Local time
Today, 09:17
Joined
Feb 7, 2020
Messages
2,022
Your query in a readable and understandable form:
SQL:
SELECT
   [Work Entry Summary].[PO#],
   [Work Entry Summary].Date,
   [Work Entry Summary].Customer,
   [Work Entry Summary].Requestor,
   [Work Entry Summary].[Scope Of Work],
   [Work Entry Summary].[Panel Invoice #],
   [Work Entry Summary].[Quote #],
   [Work Entry Summary].Status,
   [Work Entry Summary].[Customer PO/WO#],
   [Work Entry Summary].Location
FROM
   (
      ([Work Entry Summary]
         INNER JOIN [Total Info Part 1 Query]
         ON [Work Entry Summary].[PO#] = [Total Info Part 1 Query].[PO#]
      )
      INNER JOIN [Total Info Part 2 Query]
      ON [Total Info Part 1 Query].[PO#] = [Total Info Part 2 Query].[PO#]
   )
   INNER JOIN [Total Info Final Query]
   ON [Total Info Part 2 Query].[PO#] = [Total Info Final Query].[PO#]
ORDER BY
   [Work Entry Summary].[PO#]
A query is not an Excel spreadsheet where you can do EVERYTHING. Even with a JOIN there can be such assignment problems within the tables involved, so that the query can no longer be updated.

An universal solution is to use a single form for editing for each single table => saved records are table records.

In the present case there is another variant: All fields in the SELECT part come from a table. This allows you to move the other tables from the FROM part to the WHERE part as a filter.
SQL:
SELECT
   [Work Entry Summary].[PO#],
   [Work Entry Summary].Date,
   [Work Entry Summary].Customer,
   [Work Entry Summary].Requestor,
   [Work Entry Summary].[Scope Of Work],
   [Work Entry Summary].[Panel Invoice #],
   [Work Entry Summary].[Quote #],
   [Work Entry Summary].Status,
   [Work Entry Summary].[Customer PO/WO#],
   [Work Entry Summary].Location
FROM
   [Work Entry Summary]
WHERE
   [Work Entry Summary].[PO#] IN
      (
         SELECT
            [Total Info Part 1 Query].[PO#]
         FROM
            ([Total Info Part 1 Query]
               INNER JOIN [Total Info Part 2 Query]
               ON [Total Info Part 1 Query].[PO#] = [Total Info Part 2 Query].[PO#]
            )
            INNER JOIN [Total Info Final Query]
            ON [Total Info Part 2 Query].[PO#] = [Total Info Final Query].[PO#]
      )
ORDER BY
   [Work Entry Summary].[PO#]
Another question is whether you need all those tables at all. Some people like to take everything they have with them: In the picture, not just the shoes on their feet, but also their own shoes with cupboards and shelves.
 

cMcDowell23

New member
Local time
Today, 01:17
Joined
Jan 24, 2023
Messages
8
Thank you! I fixed why my Query was uneditable, but now my form isn't pulling any information form this Query. :(
 

cMcDowell23

New member
Local time
Today, 01:17
Joined
Jan 24, 2023
Messages
8
Your query in a readable and understandable form:
SQL:
SELECT
   [Work Entry Summary].[PO#],
   [Work Entry Summary].Date,
   [Work Entry Summary].Customer,
   [Work Entry Summary].Requestor,
   [Work Entry Summary].[Scope Of Work],
   [Work Entry Summary].[Panel Invoice #],
   [Work Entry Summary].[Quote #],
   [Work Entry Summary].Status,
   [Work Entry Summary].[Customer PO/WO#],
   [Work Entry Summary].Location
FROM
   (
      ([Work Entry Summary]
         INNER JOIN [Total Info Part 1 Query]
         ON [Work Entry Summary].[PO#] = [Total Info Part 1 Query].[PO#]
      )
      INNER JOIN [Total Info Part 2 Query]
      ON [Total Info Part 1 Query].[PO#] = [Total Info Part 2 Query].[PO#]
   )
   INNER JOIN [Total Info Final Query]
   ON [Total Info Part 2 Query].[PO#] = [Total Info Final Query].[PO#]
ORDER BY
   [Work Entry Summary].[PO#]
A query is not an Excel spreadsheet where you can do EVERYTHING. Even with a JOIN there can be such assignment problems within the tables involved, so that the query can no longer be updated.

An universal solution is to use a single form for editing for each single table => saved records are table records.

In the present case there is another variant: All fields in the SELECT part come from a table. This allows you to move the other tables from the FROM part to the WHERE part as a filter.
SQL:
SELECT
   [Work Entry Summary].[PO#],
   [Work Entry Summary].Date,
   [Work Entry Summary].Customer,
   [Work Entry Summary].Requestor,
   [Work Entry Summary].[Scope Of Work],
   [Work Entry Summary].[Panel Invoice #],
   [Work Entry Summary].[Quote #],
   [Work Entry Summary].Status,
   [Work Entry Summary].[Customer PO/WO#],
   [Work Entry Summary].Location
FROM
   [Work Entry Summary]
WHERE
   [Work Entry Summary].[PO#] IN
      (
         SELECT
            [Total Info Part 1 Query].[PO#]
         FROM
            ([Total Info Part 1 Query]
               INNER JOIN [Total Info Part 2 Query]
               ON [Total Info Part 1 Query].[PO#] = [Total Info Part 2 Query].[PO#]
            )
            INNER JOIN [Total Info Final Query]
            ON [Total Info Part 2 Query].[PO#] = [Total Info Final Query].[PO#]
      )
ORDER BY
   [Work Entry Summary].[PO#]
Another question is whether you need all those tables at all. Some people like to take everything they have with them: In the picture, not just the shoes on their feet, but also their own shoes with cupboards and shelves.
Yes you are correct and I have corrected my query JOINs, and my query has become editable, but now my form isn't pulling the data from my query...
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:17
Joined
Sep 21, 2011
Messages
14,607
Is there any data in the query?
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:17
Joined
Sep 21, 2011
Messages
14,607
So upload your db, compacted, with enough data to see the issue, and instructions on how to recreate.
 

ebs17

Well-known member
Local time
Today, 09:17
Joined
Feb 7, 2020
Messages
2,022
but now my form isn't pulling the data from my query...
If you say so, so it will be. My wand has a limited range.
 

cMcDowell23

New member
Local time
Today, 01:17
Joined
Jan 24, 2023
Messages
8
So upload your db, compacted, with enough data to see the issue, and instructions on how to recreate.
Okay I think I have compacted my database properly.

How to Use:
ON Main Menu Form - Click Purchase Order Entry Form Button
That will open Work Entry Form which is the form that is not pulling my data from "Work Entry Summary Query"
NOTE: My back, next and other little buttons don't work yet (have to create the macros still) so you just have to navigate through database objects on the Navigation Pane.

Thanks for all your help and let me know if you have any questions.
 

Attachments

  • 2023 Purchase Orders.accdb
    1.2 MB · Views: 96

Gasman

Enthusiastic Amateur
Local time
Today, 08:17
Joined
Sep 21, 2011
Messages
14,607
Too late a version for me, sorry. :(
Someone else will need to look at it.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:17
Joined
Sep 21, 2011
Messages
14,607
Darn! Is that maybe my issue? That I am using a late version of Access? or would that help me maybe?
No, highly unlikely, I just cannot open a DB later that 2007 as my version is so old. I have no need to obtain a later version now as I hardly use Access anymore, and if I do, it is fine for my needs.
Someone else will be along to take a look.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:17
Joined
Feb 19, 2002
Messages
43,720
You have set the form's Order Entry property to Yes. That means you can only enter records and not view old ones. Change the order entry property to No.

Rather than scrolling through purchase orders, I would add a couple of search boxes such as a date range, customer, requestor, and Po#. others too if that makes sense. I personally do not use filters for this since my BE's are almost always SQL Server. Instead I use a WHERE clause in my RecordSource query that references the selection fields. This means that the form always opens empty. Then the user enters some criteria and the form is requeried to show the selected records.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:17
Joined
May 21, 2018
Messages
8,685
attached
 

Attachments

  • 2023 Purchase Orders.accdb
    1.2 MB · Views: 99

Users who are viewing this thread

Top Bottom