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

Microsoft Access can't find the field 'I' referred to in your expression

bhcob1
P: 19
Hi, Whenever I delete a record my command button, the record deletes, a list displaying all records is updated and then a message box appears:

Microsoft Access can't find the field 'I' referred to in your expression

It only has the option of clicking 'OK', when I do this everything is fine again.
I would like to know what is going on and how to fix this problem. Below is parts of the code that I believe the problem is in.

The form is frmDeviation based on tblDeviation, the list displaying all the records used for navigation is lstDeviation, and their are several other lists which display related records in other tables.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3.  
  4. Private Sub Form_Current()
  5.  
  6.     'Sets value selected in related list to Null every time the user changes record
  7.     Me![lstReqAndDeviation2] = Null
  8.     Me![lstDeviationAndSG21] = Null
  9.  
  10.     'Requery all the lists displaying relationships
  11.     'Updates every time the user changes records
  12.     Me!lstReqAndDeviation2.Requery
  13.     Me!lstDeviationAndSG21.Requery
  14.  
  15.     'Removes users ability to Add, Edit and Delete
  16.     'Exception is if user is on new form
  17.     Dim fStatus As Boolean
  18.     Dim nfStatus As Boolean
  19.     fStatus = Me.NewRecord
  20.     nfStatus = Not fStatus
  21.  
  22.     Me.Reference_Number.Locked = nfStatus
  23.     Me.Issue.Locked = nfStatus
  24.     Me.Title.Locked = nfStatus
  25.     Me.Airworthiness_Impact.Locked = nfStatus
  26.     Me.Airworthiness_Assessment.Locked = nfStatus
  27.     Me.Planned_Operations_Impact.Locked = nfStatus
  28.     Me.Planned_Operations_Assessment.Locked = nfStatus
  29.     Me.Manuals_Impact.Locked = nfStatus
  30.     Me.Manuals_Assessment.Locked = nfStatus
  31.     Me.Training_Impact.Locked = nfStatus
  32.     Me.Training_Assessment.Locked = nfStatus
  33.     Me.LSA_Impact.Locked = nfStatus
  34.     Me.LSA_Assessment.Locked = nfStatus
  35.  
  36.     cmdEditRecord.Enabled = Not fStatus
  37.     cmdDeleteRecord.Enabled = fStatus
  38.  
  39.     ' Code to give the detail section a different colour when in view mode and edit mode
  40.     If (fStatus = True) Then
  41.         Me.Detail.BackColor = RGB(255, 255, 255)
  42.         Else: Me.Detail.BackColor = RGB(211, 211, 211)
  43.         End If
  44.  
  45. End Sub
  46.  
  47. Private Sub lstDeviation_AfterUpdate()
  48.  
  49.     Me!lstReqAndDeviation2.Requery
  50.     Me!lstDeviationAndSG21.Requery
  51.  
  52.     ' Find the record that matches the control.
  53.     Dim rs As Object
  54.     Set rs = Me.Recordset.Clone
  55.     rs.FindFirst "[ID] = " & Str(Nz(Me![lstDeviation], 0))
  56.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  57.  
  58. End Sub
  59.  
  60. Private Sub cmdDeleteRecord_Click()
  61.  
  62. On Error GoTo Err_cmdDeleteRecord_Click
  63.  
  64.     DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
  65.     DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
  66.  
  67. Exit_cmdDeleteRecord_Click:
  68.     Exit Sub
  69.  
  70. Err_cmdDeleteRecord_Click:
  71.     MsgBox Err.Description
  72.     Resume Exit_cmdDeleteRecord_Click
  73.  
  74. End Sub
  75.  
  76. Private Sub Form_Activate()
  77. ' Refresh Form after it gains focus (ie, another window is closed such as the edit relationship window
  78. ' Required so that lists show correct relationships
  79.             DCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
  80. End Sub
  81.  
  82. Private Sub Form_AfterDelConfirm(Status As Integer)
  83. 'Refresh Form after a record has been deleted (updates lists)
  84.          DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
  85. ' Updates the counter after record deletion, counts distinct Deviation numbers
  86. Me.lblDeviationCount = "(" & DCount("*", "qryDeviationCount") & ")"
  87. End Sub
  88.  
  89.  
Feb 6 '07 #1
Share this Question
Share on Google+
11 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Expand|Select|Wrap|Line Numbers
  1. ' Updates the counter after record deletion, counts distinct Deviation numbers
  2. Me.lblDeviationCount = "(" & DCount("*", "qryDeviationCount") & ")"
  3.  
If you think this could be the line causing the error can you give the SQL of the query qryDeviationCount.
Feb 6 '07 #2

NeoPa
Expert Mod 15k+
P: 31,186
Please can you post which line of your code this error message appears after.

BTW Assuming the Me.lblDeviationCount of the last line refers to a Label control, you need to assign the value to the .Caption property if you want it to work.
Feb 6 '07 #3

Rabbit
Expert Mod 10K+
P: 12,316
Do a compile and it should at least jump to the general area of the code that's causing the problem.
Feb 6 '07 #4

bhcob1
P: 19
I have deduced that the error is being cause in the following code as suggested:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_AfterDelConfirm(Status As Integer)
  3. 'Refresh Form after a record has been deleted (updates lists)
  4.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
  5.  
  6. ' Updates the counter after record deletion, counts distinct Deviation numbers
  7. Me!lblDeviationCount = "(" & DCount("*", "qryDeviationCount") & ")"
  8.  
  9. End Sub
  10.  
The Query that is being called has the following SQL
Expand|Select|Wrap|Line Numbers
  1. SELECT tblDeviation![Reference Number] AS Expr1
  2. FROM tblDeviation
  3. GROUP BY tblDeviation![Reference Number];
  4.  

The reason for the group by is because each [Reference Number] can have several [Issues], i am only interested in how many differerent [Reference Number], i am using Acc2000.

All i want is for the count label to update after a record has been deleted, is there another event i can run the code after?

Thanks guys
Feb 6 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Try changing this to ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_AfterDelConfirm(Status As Integer)
  3. 'Refresh Form after a record has been deleted (updates lists)
  4.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
  5.  
  6. ' Updates the counter after record deletion, counts distinct Deviation numbers
  7. Me!lblDeviationCount.Caption = "(" & DCount("[Reference Number]", "qryDeviationCount") & ")"
  8.  
  9. End Sub
  10.  
The query shouldn't be showing the As Expr1 as there is no reason for it. Make sure that [Reference Number] is the correct fieldname.

Expand|Select|Wrap|Line Numbers
  1. SELECT [Reference Number]
  2. FROM tblDeviation
  3. GROUP BY [Reference Number];
  4.  
Mary
Feb 7 '07 #6

NeoPa
Expert Mod 15k+
P: 31,186
In a situation like this you can even use (a bit more efficient for the SQL engine) :
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT [Reference Number]
  2. FROM tblDeviation;
Also, NB for the OP.
Please try to read all the posts as you're still not using the .Caption property in your code which was first pointed out back in post #3.
Feb 7 '07 #7

bhcob1
P: 19
I have tried all your suggestions and none work, the caption property is not even recognised.

I believe it has something to the with where the code is (the event), as i have the exact same code for the on load event and it works perfectly.
Feb 7 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
I have tried all your suggestions and none work, the caption property is not even recognised.

I believe it has something to the with where the code is (the event), as i have the exact same code for the on load event and it works perfectly.
OK can we establish if this control is a label or a textbox? If it's a textbox then what is the datatype. Also did you check the query?
Feb 7 '07 #9

NeoPa
Expert Mod 15k+
P: 31,186
I have tried all your suggestions and none work, the caption property is not even recognised.
For some reason I don't understand, the auto-complete feature doesn't work for the .Caption property. Have you tried typing it in by hand and seeing if it works for you? (When you type it in, it should change it from '.caption' to '.Caption' so you'll know it's recognised it.)
Feb 8 '07 #10

bhcob1
P: 19
Thanks NeoPa, it worked

For some reason I don't understand, the auto-complete feature doesn't work for the .Caption property. Have you tried typing it in by hand and seeing if it works for you? (When you type it in, it should change it from '.caption' to '.Caption' so you'll know it's recognised it.)
Feb 9 '07 #11

NeoPa
Expert Mod 15k+
P: 31,186
Was that the whole problem then?
That would be a nuisance (I have to watch my language on here you understand ;))!
Feb 9 '07 #12

Post your reply

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