qry will not allow update of table

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

petePrinterAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:


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
 
zwseemmConnect With a Mentor Commented:
Its this field
Custblock([custName],[CustAddress1],[CustAddress2],[CustTown],[CustCounty],[CustPostcode]) AS AddressBlock,

It can't update that because its a combination of fields.
0
 
petePrinterAuthor Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
petePrinterAuthor Commented:
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
 
joeviCommented:
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
 
petePrinterConnect With a Mentor Author Commented:
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
 
petePrinterAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.