473,718 Members | 1,949 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

19 New Member
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
  2. Option Compare Database
  4. Private Sub Form_Current()
  6.     'Sets value selected in related list to Null every time the user changes record
  7.     Me![lstReqAndDeviation2] = Null
  8.     Me![lstDeviationAndSG21] = Null
  10.     'Requery all the lists displaying relationships
  11.     'Updates every time the user changes records
  12.     Me!lstReqAndDeviation2.Requery
  13.     Me!lstDeviationAndSG21.Requery
  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
  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
  36.     cmdEditRecord.Enabled = Not fStatus
  37.     cmdDeleteRecord.Enabled = fStatus
  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
  45. End Sub
  47. Private Sub lstDeviation_AfterUpdate()
  49.     Me!lstReqAndDeviation2.Requery
  50.     Me!lstDeviationAndSG21.Requery
  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
  58. End Sub
  60. Private Sub cmdDeleteRecord_Click()
  62. On Error GoTo Err_cmdDeleteRecord_Click
  64.     DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
  65.     DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
  67. Exit_cmdDeleteRecord_Click:
  68.     Exit Sub
  70. Err_cmdDeleteRecord_Click:
  71.     MsgBox Err.Description
  72.     Resume Exit_cmdDeleteRecord_Click
  74. End Sub
  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
  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
Feb 6 '07 #1
11 8414
14,534 Recognized Expert Moderator MVP
Expand|Select|Wrap|Line Numbers
  1. ' Updates the counter after record deletion, counts distinct Deviation numbers
  2. 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.
Feb 6 '07 #2
32,569 Recognized Expert Moderator MVP
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.
Feb 6 '07 #3
12,516 Recognized Expert Moderator MVP
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
19 New Member
I have deduced that the error is being cause in the following code as suggested:

Expand|Select|Wrap|Line Numbers
  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
  6. ' Updates the counter after record deletion, counts distinct Deviation numbers
  7. Me!lblDeviationCount = "(" & DCount("*", "qryDeviationCount") & ")"
  9. End Sub
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];

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
14,534 Recognized Expert Moderator MVP
Try changing this to ...

Expand|Select|Wrap|Line Numbers
  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
  6. ' Updates the counter after record deletion, counts distinct Deviation numbers
  7. Me!lblDeviationCount.Caption = "(" & DCount("[Reference Number]", "qryDeviationCount") & ")"
  9. 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.

Expand|Select|Wrap|Line Numbers
  1. SELECT [Reference Number]
  2. FROM tblDeviation
  3. GROUP BY [Reference Number];
Feb 7 '07 #6
32,569 Recognized Expert Moderator MVP
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
19 New Member
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
14,534 Recognized Expert Moderator MVP
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
32,569 Recognized Expert Moderator MVP
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

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

Similar topics

by: JMCN | last post by:
hello i receive a runtime error '2465' whenever i run my module in access 97. it says 'Run-time error '2465' OOB Reports can't find the field "DuplicatePayments' referred to in your expression. You may have misspelled the field name or the field may have been
by: andykevans | last post by:
Hi Guys, Pretty much a novice at Access so I apologise if this is obvious: I have an Access form called MainForm1. On the form is an execute button which starts a vb script. The script runs along nicely importing lots of records using the docmd.transfertext command. It then, at random points in the records, will return the above 2450 error saying it can't find the MainForm1, even though it's open and the only form in the database with no...
by: Crayola465 | last post by:
Im trying to create an autofil for the city and state fields of my database by just entering a zip code. I have tried a few different things but it doesnt populate correctly. I haven't had to write programming since college so Im not very up to date on my language and I cant seem to figure it out. If anyone has any ideas, I would really appreciate it.
by: klaka | last post by:
Hello, Could u please help me with this error? Microsoft access can't find the field 'I' referred to in your expression or The field 'I' already exists It appears after an "Export to Excel" button is pressed, I have made lot of tests and I realized that it works fine before a split database...after that the error appears.
by: CindySue | last post by:
I'm using a subform linked to the main form by a bidder number field. In the subform, I added a group header and put the field LS in it so that it would list all records designated as Live first under a heading that says Live, and then all that are designated as Silent under a heading that says Silent. The subform works beautifully by itself, but when I put it in the main form, I get an error that says "Access can't find the field 'Live or...
by: dougmeece | last post by:
Hello everyone, I have created a search button but I receive the message "Microsoft Access can't fine the field "|" referred to in your expression" when I try to use it. I have looked online for this error but the fix did not pertain to this problem because the table or field name did not match any reservered Access words as described. My records search form will have two combo boxes and one or two search buttons. I am just trying to...
by: KrazyKasper | last post by:
I created two Command buttons in a form, one to preview the entire report and one to "cancel" or exit the form. They both work. I'm trying to create a third Command button to select/filter data from the report. My code to filter the data is: Private Sub SelectedContract_Click() On Error GoTo Err_SelectedContract_Click strFilter = " = '" & Me! & "'" DoCmd.OpenReport "OPTIMIZEIT-Audit1", acViewPreview, , strFilter ...
by: kcdoell | last post by:
Hello: I have the following afterupdate event: Private Sub GWP_AfterUpdate() 'Updates the Total calculation in the control "SumGWP" on the quick reference 'table that is located on the form With Me! DoCmd.Requery
by: zufie | last post by:
I have a main form containing a command SEND button that prompts an email form to pop up. The email address(es) that are supposed to appear on the email form are those corresponding to the respective agency record appearing on the main form at the current time. Instead, what I get is the email form without the appropriate information in the textboxes of the email form.
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.