By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,459 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

This Recordset is not updateable - Error

anoble1
100+
P: 225
I have asked this question in the past but I can't figure this one out. It is a little different. I have a checkbox that keeps giving me the error.
I have 2 attachments.
Any help?

Is it because I am doing the filter in VBA?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. 'Filter this form to show the feeder selected
  3. Forms!frmMain!frmAll.Controls("PO").SetFocus
  4. Me.RecordSource = "SELECT tblInformation.*, tblDivision.BranchName, tblInformation.PO FROM tblInformation INNER JOIN tblDivision ON tblInformation.Branch = tblDivision.BranchNumber WHERE (((tblInformation.PO)='" & Forms!frmMain!frmAll.Controls("PO").Text & "'));"

Aug 25 '17 #1

✓ answered by NeoPa

Sorry. Let me look at that again. I misread the SQL all scrunched up into a single line. I should know better. I should have reformatted before commenting.

Now I look more carefully it seems you have referred to a field in your WHERE clause that's included twice in the SELECT clause (tblInformation.PO). I often find it helps to take the resultant SQL string and create a QueryDef object with it and see what it reports when trying to run it. In this case, as a QueryDef object will automatically add an ALIAS for the second occurrence you may not see the same behaviour, but it's worth a try. I expect the solution is simply to remove the second instance of tblInformation.PO from your SELECT clause.

Other SQL debugging tips can be found at How to Debug SQL String.

Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,492
Hi.

I don't think it's because you're doing it in VBA. It seems clear that it's because you have multiple tables without JOINs. See Reasons for a Query to be Non-Updatable for a better understanding of what makes a recordset non-updatable. The fourth item on the list describes your situation exactly.
Aug 25 '17 #2

anoble1
100+
P: 225
I guess I am not understanding. I don't have 3 or more tables and only 1 link here.
Aug 28 '17 #3

NeoPa
Expert Mod 15k+
P: 31,492
Sorry. Let me look at that again. I misread the SQL all scrunched up into a single line. I should know better. I should have reformatted before commenting.

Now I look more carefully it seems you have referred to a field in your WHERE clause that's included twice in the SELECT clause (tblInformation.PO). I often find it helps to take the resultant SQL string and create a QueryDef object with it and see what it reports when trying to run it. In this case, as a QueryDef object will automatically add an ALIAS for the second occurrence you may not see the same behaviour, but it's worth a try. I expect the solution is simply to remove the second instance of tblInformation.PO from your SELECT clause.

Other SQL debugging tips can be found at How to Debug SQL String.
Aug 28 '17 #4

Post your reply

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