Results 1 to 6 of 6
  1. #1
    Join Date
    May 2007
    Posts
    12

    Unanswered: INSERT INTO query not working .. confused..

    I have a query Ive created from information gathered from a form.
    I want to create a new record in the table based on the information
    entered in the fields on the form. Ive check the data and its there and
    is right but when I try to run the query it tells me .
    Can't find the field "|" referred to in your expression.
    at first I though it was cause I mistyped a field name (its a rather
    long query) so just to make sure I was doing the format right
    I use the following:
    Tbl_Parts_SQL = "INSERT INTO " & "tblparts" & " (" & "PartName" & ") VALUES ('" & Part_Name_Entered & "')"

    Part_Name_Entered is from the form and is a text field
    the field PartName in the tblparts is also text.
    "INSERT INTO tblparts (PartName) VALUES ('XXXXXX')"
    that is what is shown in the "Watch" window.
    any ideas
    tkx for your help ..

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    can you post the full code snippet using your simplified query, along with the specific error received?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2007
    Posts
    12
    sorry not sure what your asking but.
    (there is a lot of otherthings im doing in the procedure but heres some more)

    In the table I have
    PartName Text This is the new part name
    Cost Currency Cost of part
    Vender Text Venders Name

    In the form I have a button "Submit" and "On Click" I have

    Dim Tbl_Parts_SQL As Variant
    Dim Part_Name_Entered as String
    ....
    Part_Name_Entered = Me.NewPartName
    ........
    'Put information in the Table - tblparts
    Tbl_Parts_SQL = "INSERT INTO " & "tblparts" & " (" & "PartName" & ") VALUES ('" & Part_Name_Entered & "')"

    and I get the error message :
    Microsoft Access Can't Fin the Field "|" referred to in your expression
    OK

    and thats it ..

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I don't see anything in that code other than creating a string... is there a reason tbl_parts_sql is a variant btw?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Part_Name_Entered = Me.NewPartName
    Tbl_Parts_SQL = "INSERT INTO " & "tblparts" & " (" & "PartName" & ") VALUES ('" & Part_Name_Entered & "')"

    seems a very complicated way to write
    Tbl_Parts_SQL = "INSERT INTO tblparts(PartName) VALUES ('" & Me.NewPartName & "')"

    but both should do the job unless PartName has some extra meaning (e.g. var or const or control name) or there are constraints on the table that reject the INSERT.

    whichever SQL you decide to use, just after the line
    Tbl_Parts_SQL =
    insert a line reading
    Stop
    and when it stops, Ctrl-G and in the thus-displayed immediate window type:
    ? Tbl_Parts_SQL
    and hit the return key.
    copy/paste the SQL here if just seeing the SQL doesn't answer the question for you.

    izy

    later review: obvious that my comment unless PartName has some extra meaning is irrelevant since PartName is quoted literal text
    Last edited by izyrider; 06-13-07 at 13:09.
    currently using SS 2008R2

  6. #6
    Join Date
    May 2007
    Posts
    12
    Thanks everyone for the replies .. things working ok now .. and Ive got a better understanding of what I did .. again tkx.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •