Solved

qry will not allow update of table

Posted on 2010-11-20
7
540 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 6

Assisted Solution

by:zwseemm
zwseemm earned 100 total points
ID: 34180016
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 400 total points
ID: 34180025


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
ID: 34180029
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:petePrinter
ID: 34180113
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
ID: 34180511
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
ID: 34180675
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
ID: 34211350
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

628 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