Query not updating (1 Viewer)

NLR · Jul 9, 2018

Good morning,

I have the following query:
SELECT [Customer Returns].[Return Fixed?], Mid([Company Name],18) AS [Comp Name], IIf([File Name]=[3rdPartyList]![3rdPartyName10],[3rdPartyList]![Customer]," ") AS Originator, [Customer Returns].[Processed Date], [Customer Returns].[Individual Name], [Customer Returns].[Individual ID], [Customer Returns].Amount, [Customer Returns].[Return Code], [Customer Returns].ReturnCodeDesc, [Customer Returns].[Effective Date], [Effective Date]+6 AS [Date Due], [Customer Returns].SEC, [Customer Returns].[Correction or Notes], [Customer Returns].Comments, [Customer Returns].DRorCR, *
FROM [Customer Returns] LEFT JOIN 3rdPartyList ON [Customer Returns].[File Name] = [3rdPartyList].[3rdPartyName10]
WHERE ((([Customer Returns].[Return Fixed?])=False))
ORDER BY Mid([Company Name],18), [Customer Returns].[Processed Date], [Customer Returns].[Company Name];

The query doesn't allow me to update the [Return Fixed?] field which is required when complete; I'd appreciate any help with this.

Thanks so much in advance!

Minty · Jul 9, 2018

I would advice removing any special characters such as ?#@ and spaces from your field names, they WILL cause you an issue at some point, as well as a heap of extra typing.

? is a wild character wildcard, I'm surprised Access didn't complain when you created it.

As to your query, assuming this is the recordsource for a Form the calculated IIf field is probably the culprit, as it is in the results so Access will try and evaluate it for all records.

In Query Design view temporarily remove that and see if you can magically edit things in the datasheet view of the query.

NLR · Jul 9, 2018

Hi Minty
I will heed your advice going forward... thank you.
I removed the field, but no luck. The only time I can update the record is when I remove the 3rdPartyList table.
Any other thoughts?

Thank you!

Minty · Jul 9, 2018

Try changing the join type to include all [Customer Returns] and only the other tables [3rdPartyList] if they match. (Outer join?)

Then as a real gotcha, try renaming that table / query so that it's name doesn't start with a number. I know SQL server doesn't allow that, and it might be another little "Access Funny"

Frothingslosh · Jul 9, 2018

As a rule, Access nearly always deems a recordset as non-updatable if there's a join of any sort, even if no fields from the joined table are included in the output. I've run into that when my joined table was literally only used for a where clause.

(And Minty, his join is already an outer join - Access and SQL server just let you leave off OUTER because there's no other type of LEFT or RIGHT join.)

That's a big reason we're always telling people 'one form, one table'. In this case, you won't be able to change the data as long as you keep that join.

NLR · Jul 9, 2018

Ok, thank you for the info; however, I need to add one field from one query to another query and then display on a form (which I would like to be able to update). How can I do this?

Thank you!

Frothingslosh · Jul 9, 2018

You cannot have two tables underlying a single form and be able to update that form. If you want your users to be able to update that one field, then you need to put it in a subform. If it's just for display, put a DLookup in the control as the data source.

NLR · Jul 9, 2018

I need to link the subform to the main form so that the subform will only show when linked to a record on the main form. I need to update a field on the main form. Is there a way to do this? I'm beginning to think I won't be able to...

Thank you!

jdraw · Jul 9, 2018

I need to update a field on the main form.

Can you give more details or a sample?
If you have to update something on the main form, why do you need to display/involve the subform?

For example if I had an Order form with an OrderDetail subform, I could change OrderDate on Order form without involving OrderDetail table.

NLR · Jul 9, 2018

I have two tables one for returns and one that shows the main company. When I receive the info for the return it doesn't include the main company; just a sub company. I need to show the main company the sub refers to in order know what account it belongs to. I can create a query that will link on the names and shows all the fields I need however I'm not able to update.

jdraw · Jul 9, 2018

Can you show your tables and relationships? It seems to me you may be missing a table(s). Please tell us more about a Return and why you only identify subcompany.

Company may have 0,1 or more Subcompany(s)

It seems all your Orders may be related/identified by sub Company somehow???
You would need a table and query to relate company and its subcompanies.

My guess is your tables and relationships do not match your business requirements.

NLR · Jul 9, 2018

The data for the Customer Returns comes from an outside source and cannot be changed. I don't have a way to link the return table to the main table.

jdraw · Jul 9, 2018

How do you know which subCompany belongs to which Main company?

Your pdf doesn't show all fields in each table.

Also, embedded spaces in field names will be a problem (syntax) at some point. Similarly with special characters (#) in field names.

Your data model should relate all tables.

NLR · Jul 9, 2018

The outside source for the subCompany table uses the first 10 characters of the company name which is the only way I can know how it relates to the Main company table.

jdraw · Jul 9, 2018

So, can you take all of the records you have and identify the duplicates in the first 10 characters of the CompanyName.
Can you tell us about
Company Name
COName
CompName
CompName10

Also, do you have descriptions for
ACHCustomers
ACH3rdPartyCustomers
ACHCustomer Returns

I think you have enough info to identify Companies and subCompanies, but need to know more abut the application.

When you get data from an outside source, that doesn't prevent you (necessarily) from relating that data to tables you control.

Good luck.

MajP · Jul 9, 2018

As a rule, Access nearly always deems a recordset as non-updatable if there's a join of any sort, even if no fields from the joined table are included in the output.

Maybe I do not understand what you are trying to say, but as written that is not correct. There are many ways to make a non-updateable query, but far from "always". More the exception than the rule. Most reasons
http://www.fmsinc.com/microsoftaccess/query/non-updateable/

Pat Hartman · Jul 10, 2018

As a rule, Access nearly always deems a recordset as non-updatable if there's a join of any sort, even if no fields from the joined table are included in the output. I've run into that when my joined table was literally only used for a where clause.

That is so wrong I have to agree. All of my forms are bound to queries and most of the queries include joins to lookup tables. While standard practice is one table, one form. In a relational database, you frequently want to display data from tables you are not updating. A common example is an order entry form. Don't you want to see the customer name and contact info on this form? You are not updating the customer table, you are updating the order header. You are just showing customer details for reference. best practice is to lock the customer fields on the order entry form to avoid accidental updating but the join in and of itself does not make the query not-updatable.

If you run into this issue frequently, you are probably not using primary keys correctly or not making foreign keys base on the correct fields.

isladogs · Jul 10, 2018

Frothingslosh said:
You cannot have two tables underlying a single form and be able to update that form. If you want your users to be able to update that one field, then you need to put it in a subform. If it's just for display, put a DLookup in the control as the data source.

I also disagree with this second comment.
I know several experienced forum members only use one table per form.
I don't subscribe to that view
My forms are often based on more than one table. I can think of one student assessment form with a record source based on 8 tables and it is fully updateable. The form also contains 8 subforms most of which are based on more than one table. Some subforms are read only but only where I have done so deliberately.

However I will agree that in many cases, the above is a recipe for an unintended read only form.
As long as the conditions causing forms to be read only are avoided there won't be an issue,
As well as the FMS link from an earlier reply, Allen Browne explains the issues well at http://allenbrowne.com/ser-61.html

Query not updating (1 Viewer)

Registered User.

AWF VIP

Registered User.

AWF VIP

Premier Pale Stale Ale

Registered User.

Premier Pale Stale Ale

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Attachments

Super Moderator

Registered User.

Attachments

Super Moderator

You've got your good things, and you've got mine.

Super Moderator

MVP / VIP

Similar threads

Users who are viewing this thread