Solved

qry will not allow update of table

Posted on 2010-11-20
7
535 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
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

778 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