Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Cannot edit field in form based on query

Posted on 2007-07-31
10
Medium Priority
?
707 Views
Last Modified: 2013-11-28
I have created a form which is based on a query. The SQL of the query is:
SELECT qrycreditcontroltotal.[Company Name], qrycreditcontroltotal.[Old SP Invoice No], qrycreditcontroltotal.[Invoice Date], qrycreditcontroltotal.[Chase Date], qrycreditcontroltotal.[PO No], qrycreditcontroltotal.SumOfTotal, qrycreditcontroltotal.[Project Title], qrycreditcontroltotal.[Job ID], qrycreditcontroltotal.[Date Paid], qrycreditcontroltotal.[Amount Paid], qrycreditcontroltotal.Paid, [SumOfTotal]-[Amount Paid] AS Balance
FROM qrycreditcontroltotal;

The problem is that it will not let me edit the field [Amount Paid]. Is there any way around this?
0
Comment
Question by:Dotrooney
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 17

Expert Comment

by:JezWalters
ID: 19598692
What does qrycreditcontroltotal do - if it contains 'DISTINCT' or a 'FROM <table>, <table>' clauses (amongst other reasons) the resultant record set will not be editable.
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 19598693
Are these TOTALS (sums) fields in your query?  If so, this may be why you cannot edit the fields.
Queries don't allow editing if certain JOINS or query types.
You may have to open the recordset without the 'math' and try it.

Scott C
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 19598710
Another possible reason is that your [Amount Paid] field is a calculated value returned by your qrycreditcontroltotal query.

Calculated values can't be changed (1+1 always equals 2!)  :-)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Expert Comment

by:logicalfeline
ID: 19598775
If [Amount Paid] is not a calculated field then you could modify your query to select the primary key field of the table that [Amount Paid] is a field in. You could then create a small subform which uses this table as a database source and displays only the [Amount Paid] field, add it to your main form and use the key field to link the subform to the main form. This would then allow you to edit the amount paid and refreshing your form would recalculate the query and display the correct amount outstanding.

If you think this sounds like a possible solution and need further explanation then let me know.
0
 

Author Comment

by:Dotrooney
ID: 19598786
The [Amount Paid] field is not a calculated field but the [Invoice Amount] field is as it is made up of several entries, the [Cost] field. The [Cost] field is a Sum field and the rest are Group By. The SQL for the qrycreditcontrol query is:

SELECT [Tbl Jobs].[Job ID], [Tbl Clients].[Client ID], [Tbl Clients].[Company Name], [Tbl Jobs].[Project Title], [Tbl SP Invoice Main].[PO No], [Tbl SP Invoice Main].[Invoice Date], [Tbl SP Invoice Main].[Old SP Invoice No], [Tbl SP Invoice Main].Paid, [Tbl SP Invoice Main].[Date Paid], Sum([Tbl SP Invoice Sub].Cost) AS SumOfCost, ([Cost]*17.5)/100 AS VAT, (([Cost]*17.5)/100)+[Cost] AS [Invoice Amount], [Tbl SP Invoice Main].[Amount Paid]
FROM (([Tbl Clients] INNER JOIN [Tbl Jobs] ON [Tbl Clients].[Client ID] = [Tbl Jobs].[Client Name]) INNER JOIN [Tbl SP Invoice Main] ON [Tbl Jobs].[Job ID] = [Tbl SP Invoice Main].[Job No]) INNER JOIN [Tbl SP Invoice Sub] ON [Tbl SP Invoice Main].[SP Invoice ID] = [Tbl SP Invoice Sub].[SP Invoice ID]
GROUP BY [Tbl Jobs].[Job ID], [Tbl Clients].[Client ID], [Tbl Clients].[Company Name], [Tbl Jobs].[Project Title], [Tbl SP Invoice Main].[PO No], [Tbl SP Invoice Main].[Invoice Date], [Tbl SP Invoice Main].[Old SP Invoice No], [Tbl SP Invoice Main].Paid, [Tbl SP Invoice Main].[Date Paid], ([Cost]*17.5)/100, (([Cost]*17.5)/100)+[Cost], [Tbl SP Invoice Main].[Amount Paid]
ORDER BY [Tbl Jobs].[Project Title], [Tbl SP Invoice Main].[Invoice Date];

Is there another way around this, basically I want to see the total of the invoice amount on a form so that when the payments come in the person can enter the payment received. The reason for this is that sometimes people may only pay a part payment so I cannot just mark it as fully paid.

I have increased the points on this one.
0
 

Author Comment

by:Dotrooney
ID: 19598835
logiccalfeline,

the problem would be that I wanted to have a continuous form or datasheet which showed all the invoices outstanding. When the payment comes in,it is referenced by the invoice number so the person entering the data goes down the list to the relevant invoice number and enters the amount paid
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 19598849
I think you'll find queries that have a GROUP BY clause aren't editable.

When you group data like you're doing, Access can't determine which [Tbl SP Invoice Main] record to edit when you change your [Amount Paid] value.
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 19598859
You should be able to restructure your query to yield a partially editable record set - which fields in which tables need to be editable and which don't?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 19598872
You are going to have to find a new approach to this.
There is no possibility of editing any value if the query involves a group-by.

In my experience, payments are recorded against an  invoice record which is created when the invoice is generated; I don't see any value in re-calculating the invoice amount every time you want to look at it.  Once an invoice is issued it is fixed and you can't then risk changes to underlying detail causing a change to the invoice total.
0
 
LVL 1

Accepted Solution

by:
logicalfeline earned 1000 total points
ID: 19598937
Dotrooney:

You could use a subform in the way I described and still use a continous form for your main form as described - the subform will be displayed continuously along with the main form. I have used this subform method to solve similar problems to yours in the past.
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

578 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