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

[bhcob1]

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.

 
Option Compare Database
 
Private Sub Form_Current()
 
    'Sets value selected in related list to Null every time the user changes record
    Me![lstReqAndDeviation2] = Null
    Me![lstDeviationAndSG21] = Null
 
    'Requery all the lists displaying relationships
    'Updates every time the user changes records
    Me!lstReqAndDeviation2.Requery
    Me!lstDeviationAndSG21.Requery
 
    'Removes users ability to Add, Edit and Delete
    'Exception is if user is on new form
    Dim fStatus As Boolean
    Dim nfStatus As Boolean
    fStatus = Me.NewRecord
    nfStatus = Not fStatus
 
    Me.Reference_Number.Locked = nfStatus
    Me.Issue.Locked = nfStatus
    Me.Title.Locked = nfStatus
    Me.Airworthiness_Impact.Locked = nfStatus
    Me.Airworthiness_Assessment.Locked = nfStatus
    Me.Planned_Operations_Impact.Locked = nfStatus
    Me.Planned_Operations_Assessment.Locked = nfStatus
    Me.Manuals_Impact.Locked = nfStatus
    Me.Manuals_Assessment.Locked = nfStatus
    Me.Training_Impact.Locked = nfStatus
    Me.Training_Assessment.Locked = nfStatus
    Me.LSA_Impact.Locked = nfStatus
    Me.LSA_Assessment.Locked = nfStatus
 
    cmdEditRecord.Enabled = Not fStatus
    cmdDeleteRecord.Enabled = fStatus
 
    ' Code to give the detail section a different colour when in view mode and edit mode
    If (fStatus = True) Then
        Me.Detail.BackColor = RGB(255, 255, 255)
        Else: Me.Detail.BackColor = RGB(211, 211, 211)
        End If
 
End Sub
 
Private Sub lstDeviation_AfterUpdate()
 
    Me!lstReqAndDeviation2.Requery
    Me!lstDeviationAndSG21.Requery
 
    ' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = " & Str(Nz(Me![lstDeviation], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
 
End Sub
 
Private Sub cmdDeleteRecord_Click()
 
On Error GoTo Err_cmdDeleteRecord_Click
 
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
 
Exit_cmdDeleteRecord_Click:
    Exit Sub
 
Err_cmdDeleteRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdDeleteRecord_Click
 
End Sub
 
Private Sub Form_Activate()
' Refresh Form after it gains focus (ie, another window is closed such as the edit relationship window
' Required so that lists show correct relationships
            DCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub
 
Private Sub Form_AfterDelConfirm(Status As Integer)
'Refresh Form after a record has been deleted (updates lists)
         DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
' Updates the counter after record deletion, counts distinct Deviation numbers
Me.lblDeviationCount = "(" & DCount("*", "qryDeviationCount") & ")"
End Sub
 
 

[MMcCarthy]

' Updates the counter after record deletion, counts distinct Deviation numbers
Me.lblDeviationCount = "(" & DCount("*", "qryDeviationCount") & ")"
 

If you think this could be the line causing the error can you give the SQL of the query qryDeviationCou nt.

[NeoPa]

Please can you post which line of your code this error message appears after.

BTW Assuming the Me.lblDeviation Count 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.

[Rabbit]

Do a compile and it should at least jump to the general area of the code that's causing the problem.

[bhcob1]

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

 
Private Sub Form_AfterDelConfirm(Status As Integer)
'Refresh Form after a record has been deleted (updates lists)
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
 
' Updates the counter after record deletion, counts distinct Deviation numbers
Me!lblDeviationCount = "(" & DCount("*", "qryDeviationCount") & ")"
 
End Sub
 

The Query that is being called has the following SQL

SELECT tblDeviation![Reference Number] AS Expr1
FROM tblDeviation
GROUP BY tblDeviation![Reference Number];
 

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

[MMcCarthy]

Try changing this to ...

 
Private Sub Form_AfterDelConfirm(Status As Integer)
'Refresh Form after a record has been deleted (updates lists)
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
 
' Updates the counter after record deletion, counts distinct Deviation numbers
Me!lblDeviationCount.Caption = "(" & DCount("[Reference Number]", "qryDeviationCount") & ")"
 
End Sub
 

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.

SELECT [Reference Number]
FROM tblDeviation
GROUP BY [Reference Number];
 

Mary

[NeoPa]

In a situation like this you can even use (a bit more efficient for the SQL engine) :

SELECT DISTINCT [Reference Number]
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.

[bhcob1]

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.

[MMcCarthy]

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?

[NeoPa]

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.)