Recordset not Updatable (1 Viewer)

[cMcDowell23]

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.

 

[Gasman]

Start here

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

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

allenbrowne.com

 

[ebs17]

Your query in a readable and understandable form:

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.

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]

Thank you! I fixed why my Query was uneditable, but now my form isn't pulling any information form this Query.

 

[cMcDowell23]

Your query in a readable and understandable form:

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.

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]

Is there any data in the query?

 

[cMcDowell23]

Is there any data in the query?

Yes I have test data to see if my queries are pulling the correct information.

 

[Gasman]

So upload your db, compacted, with enough data to see the issue, and instructions on how to recreate.

 

[ebs17]

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]

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.

 

[Gasman]

Too late a version for me, sorry.
Someone else will need to look at it.

 

[cMcDowell23]

Too late a version for me, sorry.
Someone else will need to look at it.

Darn! Is that maybe my issue? That I am using a late version of Access? or would that help me maybe?

 

[Gasman]

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]

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]

attached