Solved

qry will not allow update of table

Posted on 2010-11-20
7
532 Views
Last Modified: 2013-11-27
Hi
i have a select qry that is the data-source a form showing data from a number of tables.
The form relates to a quotation.
The form shows all records correctly but if i try to amend any of the data the form does not accept it.
Data entry ect on the form is set to yes.
I believe its something with the qry rather than the form because i can not amend the data in the data sheet view of qry.
qry sql below
SELECT tblEnquires.enquiresID, tblEnquires.enqNumber, tblEnquires.enqCustRef, tblEnquires.enqDate, tblEnquires.enqDetail, Custblock([custName],[CustAddress1],[CustAddress2],[CustTown],[CustCounty],[CustPostcode]) AS AddressBlock, tblContacts.contactJob, tblContacts.contactName, tblCompany.compWebsite, tblCompany.compEmail, tblCompany.compPhone, tblCompany.compFax, tblContacts.contactEmail
FROM tblCompany, (tblEnquires INNER JOIN tblCustomers ON tblEnquires.CustAutoNum = tblCustomers.custAutoNum) INNER JOIN tblContacts ON (tblCustomers.custAutoNum = tblContacts.custAutoNum) AND (tblEnquires.contactID = tblContacts.contactID);

Open in new window

0
Comment
Question by:petePrinter
7 Comments
 
LVL 6

Assisted Solution

by:zwseemm
zwseemm earned 100 total points
Comment Utility
Its this field
Custblock([custName],[CustAddress1],[CustAddress2],[CustTown],[CustCounty],[CustPostcode]) AS AddressBlock,

It can't update that because its a combination of fields.
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 400 total points
Comment Utility


see the possible reasons and workarounds from this link

When can I update data from a query?
http://msdn.microsoft.com/en-us/library/aa198446%28office.10%29.aspx


in some cases, you can update the records if the form's RecordsetType property is set to Dynaset (Inconsistent Updates).
as suggested from the above link
0
 

Author Comment

by:petePrinter
Comment Utility
zwseemm
Thanks for your prompt reply.
I am happy not to update this field their is only two other fields that the user may want to ammend.
Can this be done please?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:petePrinter
Comment Utility
zwseemm
i have deleted address block form the query and it still fails.
good try


capricorn1
Thank you a useful link.
Dynaset inconsistent does not work.

perhaps i could use another form based on a simple query linked to the only table i wish to update?





0
 
LVL 4

Expert Comment

by:joevi
Comment Utility
I'm not seeing any relationship between tblCompany and any other tables in the query and the fact that updates ain't happening may be related to table keys.

Have you tried setting up a Parent/Child form with tblCustomers, tblContacts and tblEnquires (and tblCompany if there's a relationship)? If nothing else, designing the form may help you identify the update problem(s).
0
 

Assisted Solution

by:petePrinter
petePrinter earned 0 total points
Comment Utility
joevi
thank you for looking at this.
tbl company is a single record tbl just providing contact details ect .
the other tbls are linked with relationships
i have a data entry form that works.
It takes a single customer details and a linked contact and adds their PKs to  an enquiry table with extra data about that enquiry.
All works fine.
I then use the above qry to provide a quotation form that then creates a PDF to send out.
This works fine but i can not amend the enquiry at this stage.
i think i will go with a workround.
when quotation is on screen use a button to open a form that will just update the fields and update the quotation.
Thanks for looking at this
Pete
0
 

Author Closing Comment

by:petePrinter
Comment Utility
Thanks for the pointers that this will not work.
I have stopped chasing and used a workaround as above.
Thank you all for your comments
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now