469,353 Members | 2,053 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

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

bhcob1
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
11 7684
MMcCarthy
14,534 Expert Mod 8TB
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
32,184 Expert Mod 16PB
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
12,516 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
32,184 Expert Mod 16PB
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
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
14,534 Expert Mod 8TB
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
32,184 Expert Mod 16PB
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
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
32,184 Expert Mod 16PB
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.

Similar topics

1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.