Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


ms access cannot find the record "|"referred to in your expression

Posted on 2010-11-22
Medium Priority
Last Modified: 2013-11-28
Hi I do not why am i getting this message " Ms access cnnot find the field " | " referred to in your expression , when i click the save button to save the record. And this happens only when i am about to save the first record, but this does not show up when i add 2 nd record or 3 rd record . it happens only with 1st record.
Question by:vihaan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 75
ID: 34190027
Can you show the code associated with the Save operation ?


Author Comment

ID: 34190107
Private Sub button_save_rec_Click()
On Error GoTo Err_button_save_rec_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    Exit Sub

    MsgBox Err.Description
    Resume Exit_button_save_rec_Click
End Sub
LVL 13

Expert Comment

ID: 34190129
It sounds like your csv file wasn't imported correctly to its destination table in Access.
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

LVL 75
ID: 34190171
Lets try this first.  Since the DoMenuItem commands were depricated more that 10 years ago by Microsoft ... use this to Save a record:

Private Sub button_save_rec_Click()
    Me.Dirty = False ' Save the record
End Sub

Also, do you have *any* code in the Form Before Update event ?

LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 34190356

FWIW I've been using the DoMenuItem since A2 days as well and I've seen this pop-up from time to time as a spurious error.    The vertical pipe ( | ) is a place holder for Access to place a field name.

 Never could figure out a rhyme or reason for it popping up.  I just modified my framework code (part of which is below) to ignore it.

 No Points here please.


On Error Resume Next

      ' Check for error but skip 2001 and ones caused by Access bugs.
      If Err > 0 And Err <> 2001 And Err <> 2110 And Err <> 2501 And InStr(Error$(Err), "|") = 0 Then
        gstrMBTitle = "Error while saving record."
        gstrMBMsg = "An error occured while trying to save the record."
        gstrMBMsg = gstrMBMsg & Chr$(13) & Chr$(10)
        gstrMBMsg = gstrMBMsg & Chr$(13) & Chr$(10) & "The error was: "
        gstrMBMsg = gstrMBMsg & Chr$(13) & Chr$(10) & "    " & Err & "  " & Error$(Err)
        gstrMBMsg = gstrMBMsg & Chr$(13) & Chr$(10)
        gstrMBMsg = gstrMBMsg & Chr$(13) & Chr$(10) & "Please check all fields and try again."
        gintMBDef = vbOKOnly + vbCritical
        gintMBBeep = True
        gintMBLog = False
        Call DisplayMsgBox
        StdFormCt = False
        StdFormCt = True
      End If
      On Error GoTo StdFormCt_UnexpectedError

Open in new window


Author Comment

ID: 34190444
Private Sub Form_BeforeUpdate(Cancel As Integer)

    'Captures most recent date
    Me.frm_qna_contacts_update_date = Date
    'Captures shortname of windows operating system
    Me.frm_qna_contacts_update_user = VBA.Environ("USERNAME")

End Sub
LVL 75
ID: 34190480

the Name of a Field in table, a Control or a Form?

Same for


You error is most likely occurring on one of these two lines in the BU event.

LVL 31

Expert Comment

by:Helen Feddema
ID: 34190731
The modern syntax for saving a record is

DoCmd.RunCommand acCmdSaveRecord

In my experience, getting that error message referring to a field called "|" often indicates table corruption.  If you open the table and see Chinese characters in some fields, that confirms the diagnosis <g>.
LVL 75
ID: 34190761
Check for any possible typos in those two names.
Because you do have code in the BU event, when you attempt to Save, errors will occur in the BU event if "something isn't right"


Author Comment

ID: 34190790
Hi, the field names are form field names in BU event .
LVL 75
ID: 34191171
Did you check for typos?

Put a breakpoint on the beginning of the BU event ... then single step through (using F8) until the error occurs and see exactly which line it occurs on.

Another syntax you can try:

Private Sub Form_BeforeUpdate(Cancel As Integer)

    'Captures most recent date
    Me("frm_qna_contacts_update_date") = Date
    'Captures shortname of windows operating system
    Me("frm_qna_contacts_update_user") = VBA.Environ("USERNAME")

End Sub

Author Closing Comment

ID: 34345030

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question