468,253 Members | 1,255 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,253 developers. It's quick & easy.

Saving form is changing my recordsource

Seth Schrock
2,951 Expert 2GB
I have developed a rather complex query using three separate subqueries (they aren't nested together. They each link to the main query). Because of its complexity, I developed it in a querydef until I got it working the way that I wanted it to. I then copied and pasted the SQL code into the form's RecordSource property through the Zoom box. Everything works great until I hit save. Then, all my controls get a warning saying that their control source is invalid. I try go to into design view on the form's Record Source and it says that it has an error in the FROM clause. I go back to the Zoom box and it has placed my subqueries in square brackets instead of parentheses. This seems to be the cause of the problem, but I can't get it to not change what I copy and paste into it. I was able to save my querydef just fine and it runs fine. I have now just changed the recordsource to the name of my querydef, but I have gotten into the habit of just having the raw code in my RecordSources so that I can see my RecordSource without a lot of looking.

Does anyone know why saving my form is changing my RecordSource and breaking it?

Here is the original query
Expand|Select|Wrap|Line Numbers
  1. SELECT B.BillId_pk, B.CustId_fk, B.BillDate, B.Paid
  2.     , (SplitAmt*0.3)+LesAmt AS TotalBill, TotalPayments, ((SplitAmt*0.3)+LesAmt)-TotalPayments AS AmtDue
  3. FROM ((tblBills AS B INNER JOIN 
  4.     (SELECT BillId_fk, Sum(ReductionAmt) As SplitAmt 
  5.         FROM tblBillDetails INNER JOIN tblLineItem 
  6.         ON tblBillDetails.LineItemId_fk = tblLineItem.LineItemId_pk
  7.     WHERE LineItemTypeId_fk = 1
  8.     GROUP BY BillId_fk)  
  9.     AS S 
  10. ON B.BillId_pk = S.BillId_fk) INNER JOIN 
  11.     (SELECT BillId_fk, Sum(ReductionAmt) As LesAmt
  12.     FROM tblBillDetails INNER JOIN tblLineItem
  13.     ON tblBillDetails.LineItemId_fk = tblLineItem.LineItemId_pk
  14.     WHERE LineItemTypeId_fk = 2
  15.     GROUP BY BillId_fk)  
  16.     AS L 
  17. ON B.BillId_pk = L.BillId_fk) INNER JOIN 
  18.     (SELECT BillId_fk, Sum(PaymentAmt) AS TotalPayments 
  19.         FROM tblPayments 
  20.         GROUP BY BillId_fk)  
  21.     AS P 
  22. ON B.BillId_pk = P.BillId_fk;
  23.  
and here is what it gets changed to
Expand|Select|Wrap|Line Numbers
  1. SELECT B.BillId_pk, B.CustId_fk, B.BillDate, B.Paid
  2.     , (SplitAmt*0.3)+LesAmt AS TotalBill, TotalPayments, ((SplitAmt*0.3)+LesAmt)-TotalPayments AS AmtDue 
  3. FROM ((tblBills AS B INNER JOIN 
  4.     [SELECT BillId_fk, Sum(ReductionAmt) As SplitAmt     
  5.         FROM tblBillDetails INNER JOIN tblLineItem     
  6.         ON tblBillDetails].[LineItemId_fk = tblLineItem].[LineItemId_pk     
  7.         WHERE LineItemTypeId_fk = 1     
  8.         GROUP BY BillId_fk] 
  9.     AS S 
  10. ON B.BillId_pk = S.BillId_fk) INNER JOIN 
  11.     [SELECT BillId_fk, Sum(ReductionAmt) As LesAmt     
  12.         FROM tblBillDetails INNER JOIN tblLineItem     
  13.         ON tblBillDetails].[LineItemId_fk = tblLineItem].[LineItemId_pk     
  14.         WHERE LineItemTypeId_fk = 2     
  15.         GROUP BY BillId_fk] 
  16.     AS L 
  17. ON B.BillId_pk = L.BillId_fk) INNER JOIN 
  18.     (SELECT BillId_fk, Sum(PaymentAmt) AS TotalPayments 
  19.         FROM tblPayments 
  20.         GROUP BY BillId_fk)  
  21.     AS P 
  22. ON B.BillId_pk = P.BillId_fk; 
(Notice the square brackets on lines 4 through 8 and 11 through 15 surrounding the subqueries)

I just noticed that the square bracket starting line 4 ends on line 6 and then restarts to end on line 8. The next subquery does the same thing.
Dec 28 '14 #1

✓ answered by Stewart Ross

Hi Seth. This bug/issue with subqueries, in one form or another, has been around in Access for a very long time, though it's the first time I've known of it in the context of a form's recordsource.

Access uses a non-standard syntax for derived tables (subqueries in this case) where square brackets get substituted for the correct parenthesis (see, for example, this link:http://answers.microsoft.com/en-us/o...1-fe7191ae9591

As you have already got field names that don't need brackets around them (one of the avoiding tactics advised in the linked answer above) I think you will just have to continue to use the work round where you have stored the querydef you have developed then used the name of the stored query as the recordsource for your form.

Sometimes Access can be frustrating to use, and these bugs/issues that have never been fixed do not help at all.

-Stewart

2 1055
Stewart Ross
2,545 Expert Mod 2GB
Hi Seth. This bug/issue with subqueries, in one form or another, has been around in Access for a very long time, though it's the first time I've known of it in the context of a form's recordsource.

Access uses a non-standard syntax for derived tables (subqueries in this case) where square brackets get substituted for the correct parenthesis (see, for example, this link:http://answers.microsoft.com/en-us/o...1-fe7191ae9591

As you have already got field names that don't need brackets around them (one of the avoiding tactics advised in the linked answer above) I think you will just have to continue to use the work round where you have stored the querydef you have developed then used the name of the stored query as the recordsource for your form.

Sometimes Access can be frustrating to use, and these bugs/issues that have never been fixed do not help at all.

-Stewart
Dec 29 '14 #2
Seth Schrock
2,951 Expert 2GB
Thank-you for that link. It gives me a few things to try at least. If they don't work, at least I do have my work-around.
Dec 29 '14 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by ColinWard | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.