Solved

qry will not allow update of table

Posted on 2010-11-20
7
538 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

738 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