Solved

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

Posted on 2010-11-22
12
564 Views
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.
0
Comment
Question by:vihaan
12 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Can you show the code associated with the Save operation ?

mx
0
 

Author Comment

by:vihaan
Comment Utility
Private Sub button_save_rec_Click()
On Error GoTo Err_button_save_rec_Click


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

Err_button_save_rec_Click:
    MsgBox Err.Description
    Resume Exit_button_save_rec_Click
   
End Sub
0
 
LVL 13

Expert Comment

by:Rick
Comment Utility
It sounds like your csv file wasn't imported correctly to its destination table in Access.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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 ?

mx
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility

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.

JimD.


On Error Resume Next

      DoCmd.DoMenuItem A_FORMBAR, A_FILE, A_SAVERECORD



      ' 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

      Else

        StdFormCt = True

      End If

      On Error GoTo StdFormCt_UnexpectedError

Open in new window

0
 

Author Comment

by:vihaan
Comment Utility
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
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Question:

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

Same for

Me.frm_qna_contacts_update_user

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

mx
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
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>.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
vihaan:
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"

mx
0
 

Author Comment

by:vihaan
Comment Utility
Hi, the field names are form field names in BU event .
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
0
 

Author Closing Comment

by:vihaan
Comment Utility
good
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now