Problem updating memo field with string data from form

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • sara

    #1

    Problem updating memo field with string data from form

    I have a Memo field in a table to hold notes from a conversation a
    social worker has had with a client (this is for a non-profit).

    If the user needs to update the memo field, I need to find the
    original record (which I am doing fine - getting the correct key)
    and
    I need to update the "notes" with the new value from the form, which I
    store in a String Variable.

    (I've tried storing in a Variant variable; didn't work for me)

    I get the info I need, close the form, and run the SQL and I get "Type
    Mismatch in Criteria".

    I'm baffled (ok, it doesn't take much!) [Code below]

    Any help is much appreciated.

    Thanks -
    Sara

    ' First, save the Initial call record - with updates
    DoCmd.RunComman d acCmdSaveRecord

    lngClientKey = Me.txtClientKey
    lngInteractionK ey = DLookup("Intera ctionKey",
    "tblInteraction History", _
    "ClientKey = " & lngClientKey & " AND InteractionType Key =
    6")

    strPresProblem = Me.txtPresentin gProblem

    ' *******???????? ***???

    ' ON UPDATE initial call, update Presenting problem in Interaction
    History (only audit is in Initial Call info audit record)



    ' *******???????? ***???


    DoCmd.Close acForm, Me.Name

    strSQL = "UPDATE tblInteractionH istory " _
    & " SET tblInteractionH istory.Notes = " & strPresProblem & ""
    _
    & " WHERE tblInteractionH istory.Interact ionKey = " &
    lngInteractionK ey


    DoCmd.RunComman d strSQL

  • Bob Quintal

    #2
    Re: Problem updating memo field with string data from form

    Sara,

    why are you going this circuitous route to update a field?

    It's far simpler to open a form to the correct record, Display
    the existing memo field data, in a locked textbox, allow the
    user to add comments in an unbound textbox, then simply
    concatenate the update to the bound textbox using

    me!memofield = me!memofield & me!additionfiel d
    me.dirty = false

    in the on_click of a " save additions" CommandButton

    It's even simpler if you allow the user to edit the existing
    memo field.


    If you insist in doing it your way, move the Docmd.Close
    ,me.name to after the DoCmd.RunSQL.


    Q

    sara <saraqpost@yaho o.comwrote in
    news:1184532328 .356872.326470@ k79g2000hse.goo glegroups.com:
    I have a Memo field in a table to hold notes from a
    conversation a social worker has had with a client (this is
    for a non-profit).
    >
    If the user needs to update the memo field, I need to find the
    original record (which I am doing fine - getting the correct
    key) and
    I need to update the "notes" with the new value from the form,
    which I store in a String Variable.
    >
    (I've tried storing in a Variant variable; didn't work for me)
    >
    I get the info I need, close the form, and run the SQL and I
    get "Type Mismatch in Criteria".
    >
    I'm baffled (ok, it doesn't take much!) [Code below]
    >
    Any help is much appreciated.
    >
    Thanks -
    Sara
    >
    ' First, save the Initial call record - with updates
    DoCmd.RunComman d acCmdSaveRecord
    >
    lngClientKey = Me.txtClientKey
    lngInteractionK ey = DLookup("Intera ctionKey",
    "tblInteraction History", _
    "ClientKey = " & lngClientKey & " AND
    InteractionType Key =
    6")
    >
    strPresProblem = Me.txtPresentin gProblem
    >
    ' *******???????? ***???
    >
    ' ON UPDATE initial call, update Presenting problem in
    Interaction History (only audit is in Initial Call info audit
    record)
    >
    >
    >
    ' *******???????? ***???
    >
    >
    DoCmd.Close acForm, Me.Name
    >
    strSQL = "UPDATE tblInteractionH istory " _
    & " SET tblInteractionH istory.Notes = " &
    strPresProblem & ""
    _
    & " WHERE tblInteractionH istory.Interact ionKey = " &
    lngInteractionK ey
    >
    >
    DoCmd.RunComman d strSQL
    >
    >


    --
    Bob Quintal

    PA is y I've altered my email address.

    --
    Posted via a free Usenet account from http://www.teranews.co m

    Comment

    • Bob Quintal

      #3
      Re: Problem updating memo field with string data from form

      sara <saraqpost@yaho o.comwrote in
      news:1184599230 .171623.54830@5 7g2000hsv.googl egroups.com:
      >
      Bob -
      Thanks for the response.
      >
      I did what you said and still get the "type Mismatch" error.
      Im just wondering if the syntax of the Update section of the SQL
      is wrong but throwing up the wrong message.


      anyway, to debug this, first put some additional formatting into
      the SQL statement

      strSQL = "UPDATE tblInteractionH istory " & vbNewLine _
      & "SET tblInteractionH istory.Notes = " & vbNewLine _
      & strPresProblem & vbNewLine _
      & " WHERE tblInteractionH istory.Interact ionKey = " _
      & lngInteractionK ey & ";"

      debug.print strSQL

      Docmd.RunSQL strSQL

      Run the code. Opem the immediate window {ctrl-G} key
      Examine the actual sql that's being passed to the runSQL
      statement. Maybe we can see the problem.

      >
      I still may be "working too hard" (I'm fairly inexperienced,
      so I hear that rather often!), but let me explain what's
      happening on this one.
      >
      [snipped]
      >
      Make sense? (This organization is so amazing - providing
      services for people many would soon forget or wish would go
      away, that I am trying to do everything I can so that their
      money goes where it's needed most).
      >
      Long explanation! Many thanks -
      Sara
      >
      >


      --
      Bob Quintal

      PA is y I've altered my email address.

      --
      Posted via a free Usenet account from http://www.teranews.com

      Comment

      • sara

        #4
        Re: Problem updating memo field with string data from form

        On Jul 16, 4:32 pm, Bob Quintal <rquin...@sPAmp atico.cawrote:
        sara <saraqp...@yaho o.comwrote innews:11845992 30.171623.54830 @57g2000hsv.goo glegroups.com:
        >
        >
        >
        Bob -
        Thanks for the response.
        >
        I did what you said and still get the "type Mismatch" error.
        >
        Im just wondering if the syntax of the Update section of the SQL
        is wrong but throwing up the wrong message.
        >
        anyway, to debug this, first put some additional formatting into
        the SQL statement
        >
        strSQL = "UPDATE tblInteractionH istory " & vbNewLine _
        & "SET tblInteractionH istory.Notes = " & vbNewLine _
        & strPresProblem & vbNewLine _
        & " WHERE tblInteractionH istory.Interact ionKey = " _
        & lngInteractionK ey & ";"
        >
        debug.print strSQL
        >
        Docmd.RunSQL strSQL
        >
        Run the code. Opem the immediate window {ctrl-G} key
        Examine the actual sql that's being passed to the runSQL
        statement. Maybe we can see the problem.
        >
        >
        >
        I still may be "working too hard" (I'm fairly inexperienced,
        so I hear that rather often!), but let me explain what's
        happening on this one.
        >
        [snipped]
        >
        >
        >
        Make sense? (This organization is so amazing - providing
        services for people many would soon forget or wish would go
        away, that I am trying to do everything I can so that their
        money goes where it's needed most).
        >
        Long explanation! Many thanks -
        Sara
        >
        --
        Bob Quintal
        >
        PA is y I've altered my email address.
        >
        --
        Posted via a free Usenet account fromhttp://www.teranews.co m
        Thanks for the reply, Bob. This just appeared (I've noticed posts
        have been severely delayed lately). I was wondering if and hoping you
        would reply, and I'll now get to work on this and post back.

        sara

        Comment

        • sara

          #5
          Re: Problem updating memo field with string data from form

          On Jul 16, 4:32 pm, Bob Quintal <rquin...@sPAmp atico.cawrote:
          sara <saraqp...@yaho o.comwrote innews:11845992 30.171623.54830 @57g2000hsv.goo glegroups.com:
          >
          >
          >
          Bob -
          Thanks for the response.
          >
          I did what you said and still get the "type Mismatch" error.
          >
          Im just wondering if the syntax of the Update section of the SQL
          is wrong but throwing up the wrong message.
          >
          anyway, to debug this, first put some additional formatting into
          the SQL statement
          >
          strSQL = "UPDATE tblInteractionH istory " & vbNewLine _
          & "SET tblInteractionH istory.Notes = " & vbNewLine _
          & strPresProblem & vbNewLine _
          & " WHERE tblInteractionH istory.Interact ionKey = " _
          & lngInteractionK ey & ";"
          >
          debug.print strSQL
          >
          Docmd.RunSQL strSQL
          >
          Run the code. Opem the immediate window {ctrl-G} key
          Examine the actual sql that's being passed to the runSQL
          statement. Maybe we can see the problem.
          >
          >
          >
          I still may be "working too hard" (I'm fairly inexperienced,
          so I hear that rather often!), but let me explain what's
          happening on this one.
          >
          [snipped]
          >
          >
          >
          Make sense? (This organization is so amazing - providing
          services for people many would soon forget or wish would go
          away, that I am trying to do everything I can so that their
          money goes where it's needed most).
          >
          Long explanation! Many thanks -
          Sara
          >
          --
          Bob Quintal
          >
          PA is y I've altered my email address.
          >
          --
          Posted via a free Usenet account fromhttp://www.teranews.co m
          Bob -
          My other post hasn't appeared yet, but I have been working on your
          suggestion:

          ?strsql
          UPDATE tblInteractionH istory
          SET tblInteractionH istory.Notes =
          This is his problem on 719 upodating pores prob
          WHERE tblInteractionH istory.Interact ionKey =
          36;

          "This is his problem on 719 upodating pores prob" is the data I want
          to REPLACE (Update to) in the memo field tblInteractionH istory.Notes.

          I am wondering if I need (somehow) to have the SQL have "This is his
          problem...." in quotes. I tried a few ideas, but couldn't get it.

          Could that be it?

          Sara

          Comment

          • John Welch

            #6
            Re: Problem updating memo field with string data from form

            Sara, you are correct - your type mismatch problem comes because you
            need to have quotes around any text in your SQL string. There are two
            ways to do this:

            1
            strSQL = "UPDATE tblInteractionH istory " _
            & " SET tblInteractionH istory.Notes = '" & strPresProblem & "'"

            2
            strSQL = "UPDATE tblInteractionH istory " _
            & " SET tblInteractionH istory.Notes = """ & strPresProblem & """"

            Note that this: "" has a similar function to '

            The advantage to "" is that if the notes have already have the
            '(apostrophe) character, the sql will crash if you use the ' but not the ""

            -John

            Comment

            • sara

              #7
              Re: Problem updating memo field with string data from form

              On Jul 19, 1:46 pm, John Welch <so...@nospam.c omwrote:
              Sara, you are correct - your type mismatch problem comes because you
              need to have quotes around any text in your SQL string. There are two
              ways to do this:
              >
              1
              strSQL = "UPDATE tblInteractionH istory " _
              & " SET tblInteractionH istory.Notes = '" & strPresProblem & "'"
              >
              2
              strSQL = "UPDATE tblInteractionH istory " _
              & " SET tblInteractionH istory.Notes = """ & strPresProblem & """"
              >
              Note that this: "" has a similar function to '
              >
              The advantage to "" is that if the notes have already have the
              '(apostrophe) character, the sql will crash if you use the ' but not the ""
              >
              -John
              John -
              I was Hugely excited to see that a) I might have figured something out
              and b) you gave me more info about the ' vs ".

              I tried it
              ?strSQl
              UPDATE tblInteractionH istory
              SET tblInteractionH istory.Notes =
              " & strPresProblem & "" & vbNewLine WHERE
              tblInteractionH istory.Interact ionKey = 86;

              And still get the same error ("Type mismatch")
              One question - Why the "double Quote" after strPresProblem in the
              resolved SQL string? Could this be part of the problem?
              " & strPresProblem & ""

              Sara

              Comment

              • John Welch

                #8
                Re: Problem updating memo field with string data from form

                Hi Sara, oops, I had a mistake in what I sent you. Because I didn't
                include the WHERE clause I had the quotes wrong for your situation. You
                wouldn't want to close them off before the WHERE, so there should only
                be three right before the WHERE (the first starts a new string and the
                second 2 are like ' . You got it right again!

                Try using this SQL:
                strSQL = "UPDATE tblInteractionH istory SET tblInteractionH istory.Notes =
                """ & strPresProblem & """ WHERE tblInteractionH istory.Interact ionKey =
                " & lngInteractionK ey


                -John

                sara wrote:
                On Jul 19, 1:46 pm, John Welch <so...@nospam.c omwrote:
                >Sara, you are correct - your type mismatch problem comes because you
                >need to have quotes around any text in your SQL string. There are two
                >ways to do this:
                >>
                >1
                >strSQL = "UPDATE tblInteractionH istory " _
                > & " SET tblInteractionH istory.Notes = '" & strPresProblem & "'"
                >>
                >2
                >strSQL = "UPDATE tblInteractionH istory " _
                > & " SET tblInteractionH istory.Notes = """ & strPresProblem & """"
                >>
                >Note that this: "" has a similar function to '
                >>
                >The advantage to "" is that if the notes have already have the
                >'(apostrophe ) character, the sql will crash if you use the ' but not the ""
                >>
                >-John
                >
                John -
                I was Hugely excited to see that a) I might have figured something out
                and b) you gave me more info about the ' vs ".
                >
                I tried it
                ?strSQl
                UPDATE tblInteractionH istory
                SET tblInteractionH istory.Notes =
                " & strPresProblem & "" & vbNewLine WHERE
                tblInteractionH istory.Interact ionKey = 86;
                >
                And still get the same error ("Type mismatch")
                One question - Why the "double Quote" after strPresProblem in the
                resolved SQL string? Could this be part of the problem?
                " & strPresProblem & ""
                >
                Sara
                >

                Comment

                • sara

                  #9
                  Re: Problem updating memo field with string data from form

                  On Jul 20, 12:23 pm, John Welch <so...@nospam.c omwrote:
                  Hi Sara, oops, I had a mistake in what I sent you. Because I didn't
                  include the WHERE clause I had the quotes wrong for your situation. You
                  wouldn't want to close them off before the WHERE, so there should only
                  be three right before the WHERE (the first starts a new string and the
                  second 2 are like ' . You got it right again!
                  >
                  Try using this SQL:
                  strSQL = "UPDATE tblInteractionH istory SET tblInteractionH istory.Notes =
                  """ & strPresProblem & """ WHERE tblInteractionH istory.Interact ionKey =
                  " & lngInteractionK ey
                  >
                  -John
                  >
                  >
                  >
                  sara wrote:
                  On Jul 19, 1:46 pm, John Welch <so...@nospam.c omwrote:
                  Sara, you are correct - your type mismatch problem comes because you
                  need to have quotes around any text in your SQL string. There are two
                  ways to do this:
                  >
                  1
                  strSQL = "UPDATE tblInteractionH istory " _
                  & " SET tblInteractionH istory.Notes = '" & strPresProblem & "'"
                  >
                  2
                  strSQL = "UPDATE tblInteractionH istory " _
                  & " SET tblInteractionH istory.Notes = """ & strPresProblem & """"
                  >
                  Note that this: "" has a similar function to '
                  >
                  The advantage to "" is that if the notes have already have the
                  '(apostrophe) character, the sql will crash if you use the ' but not the ""
                  >
                  -John
                  >
                  John -
                  I was Hugely excited to see that a) I might have figured something out
                  and b) you gave me more info about the ' vs ".
                  >
                  I tried it
                  ?strSQl
                  UPDATE tblInteractionH istory
                  SET tblInteractionH istory.Notes =
                  " & strPresProblem & "" & vbNewLine WHERE
                  tblInteractionH istory.Interact ionKey = 86;
                  >
                  And still get the same error ("Type mismatch")
                  One question - Why the "double Quote" after strPresProblem in the
                  resolved SQL string? Could this be part of the problem?
                  " & strPresProblem & ""
                  >
                  Sara- Hide quoted text -
                  >
                  - Show quoted text -
                  John -
                  Still getting the same error! (Type Mismatch)

                  SQL resolves to:
                  UPDATE tblInteractionH istory SET tblInteractionH istory.Notes = "This
                  is his problem. Testing key = 86 - 1" WHERE
                  tblInteractionH istory.Interact ionKey =86

                  Code: (I copied yours and tried it with no continuation characters,
                  and finally like this so I could see all the code in the window)

                  strSQL = "UPDATE tblInteractionH istory SET
                  tblInteractionH istory.Notes = """ & strPresProblem & """ " _
                  & " WHERE tblInteractionH istory.Interact ionKey =" &
                  lngInteractionK ey

                  It seems SO simple ! Update a field - and the SQL looks fine (to me,
                  at least).

                  Any ideas now? I even Decompiled and recompiled.

                  sara

                  Comment

                  • John Welch

                    #10
                    Re: Problem updating memo field with string data from form

                    Well now the problem with the notes is fixed. I reread your original
                    post and realized that the error you got was "type mismatch in
                    criteria", which means the mismatch is in the Where clause. So it isn't
                    liking

                    WHERE tblInteractionH istory.Interact ionKey =" & lngInteractionK ey

                    are you sure the field InteractionKey in tblInteractionH istory is a long
                    integer type field? and that the variable lngInteractionK ey is really
                    a long int variable?

                    -john

                    sara wrote:
                    On Jul 20, 12:23 pm, John Welch <so...@nospam.c omwrote:
                    >Hi Sara, oops, I had a mistake in what I sent you. Because I didn't
                    >include the WHERE clause I had the quotes wrong for your situation. You
                    >wouldn't want to close them off before the WHERE, so there should only
                    >be three right before the WHERE (the first starts a new string and the
                    >second 2 are like ' . You got it right again!
                    >>
                    >Try using this SQL:
                    >strSQL = "UPDATE tblInteractionH istory SET tblInteractionH istory.Notes =
                    >""" & strPresProblem & """ WHERE tblInteractionH istory.Interact ionKey =
                    >" & lngInteractionK ey
                    >>
                    >-John
                    >>
                    >>
                    >>
                    >sara wrote:
                    >>On Jul 19, 1:46 pm, John Welch <so...@nospam.c omwrote:
                    >>>Sara, you are correct - your type mismatch problem comes because you
                    >>>need to have quotes around any text in your SQL string. There are two
                    >>>ways to do this:
                    >>>1
                    >>>strSQL = "UPDATE tblInteractionH istory " _
                    >>> & " SET tblInteractionH istory.Notes = '" & strPresProblem & "'"
                    >>>2
                    >>>strSQL = "UPDATE tblInteractionH istory " _
                    >>> & " SET tblInteractionH istory.Notes = """ & strPresProblem & """"
                    >>>Note that this: "" has a similar function to '
                    >>>The advantage to "" is that if the notes have already have the
                    >>>'(apostrophe ) character, the sql will crash if you use the ' but not the ""
                    >>>-John
                    >>John -
                    >>I was Hugely excited to see that a) I might have figured something out
                    >>and b) you gave me more info about the ' vs ".
                    >>I tried it
                    >>?strSQl
                    >>UPDATE tblInteractionH istory
                    >> SET tblInteractionH istory.Notes =
                    >>" & strPresProblem & "" & vbNewLine WHERE
                    >>tblInteractio nHistory.Intera ctionKey = 86;
                    >>And still get the same error ("Type mismatch")
                    >>One question - Why the "double Quote" after strPresProblem in the
                    >>resolved SQL string? Could this be part of the problem?
                    >>" & strPresProblem & ""
                    >>Sara- Hide quoted text -
                    >- Show quoted text -
                    >
                    John -
                    Still getting the same error! (Type Mismatch)
                    >
                    SQL resolves to:
                    UPDATE tblInteractionH istory SET tblInteractionH istory.Notes = "This
                    is his problem. Testing key = 86 - 1" WHERE
                    tblInteractionH istory.Interact ionKey =86
                    >
                    Code: (I copied yours and tried it with no continuation characters,
                    and finally like this so I could see all the code in the window)
                    >
                    strSQL = "UPDATE tblInteractionH istory SET
                    tblInteractionH istory.Notes = """ & strPresProblem & """ " _
                    & " WHERE tblInteractionH istory.Interact ionKey =" &
                    lngInteractionK ey
                    >
                    It seems SO simple ! Update a field - and the SQL looks fine (to me,
                    at least).
                    >
                    Any ideas now? I even Decompiled and recompiled.
                    >
                    sara
                    >

                    Comment

                    • sara

                      #11
                      Re: Problem updating memo field with string data from form

                      On Jul 20, 4:23 pm, John Welch <so...@nospam.c omwrote:
                      Well now the problem with the notes is fixed. I reread your original
                      post and realized that the error you got was "type mismatch in
                      criteria", which means the mismatch is in the Where clause. So it isn't
                      liking
                      >
                      WHERE tblInteractionH istory.Interact ionKey =" & lngInteractionK ey
                      >
                      are you sure the field InteractionKey in tblInteractionH istory is a long
                      integer type field? and that the variable lngInteractionK ey is really
                      a long int variable?
                      >
                      -john
                      >
                      >
                      >
                      sara wrote:
                      On Jul 20, 12:23 pm, John Welch <so...@nospam.c omwrote:
                      Hi Sara, oops, I had a mistake in what I sent you. Because I didn't
                      include the WHERE clause I had the quotes wrong for your situation. You
                      wouldn't want to close them off before the WHERE, so there should only
                      be three right before the WHERE (the first starts a new string and the
                      second 2 are like ' . You got it right again!
                      >
                      Try using this SQL:
                      strSQL = "UPDATE tblInteractionH istory SET tblInteractionH istory.Notes =
                      """ & strPresProblem & """ WHERE tblInteractionH istory.Interact ionKey =
                      " & lngInteractionK ey
                      >
                      -John
                      >
                      sara wrote:
                      >On Jul 19, 1:46 pm, John Welch <so...@nospam.c omwrote:
                      >>Sara, you are correct - your type mismatch problem comes because you
                      >>need to have quotes around any text in your SQL string. There are two
                      >>ways to do this:
                      >>1
                      >>strSQL = "UPDATE tblInteractionH istory " _
                      >> & " SET tblInteractionH istory.Notes = '" & strPresProblem & "'"
                      >>2
                      >>strSQL = "UPDATE tblInteractionH istory " _
                      >> & " SET tblInteractionH istory.Notes = """ & strPresProblem & """"
                      >>Note that this: "" has a similar function to '
                      >>The advantage to "" is that if the notes have already have the
                      >>'(apostroph e) character, the sql will crash if you use the ' but not the ""
                      >>-John
                      >John -
                      >I was Hugely excited to see that a) I might have figured something out
                      >and b) you gave me more info about the ' vs ".
                      >I tried it
                      >?strSQl
                      >UPDATE tblInteractionH istory
                      > SET tblInteractionH istory.Notes =
                      >" & strPresProblem & "" & vbNewLine WHERE
                      >tblInteraction History.Interac tionKey = 86;
                      >And still get the same error ("Type mismatch")
                      >One question - Why the "double Quote" after strPresProblem in the
                      >resolved SQL string? Could this be part of the problem?
                      >" & strPresProblem & ""
                      >Sara- Hide quoted text -
                      - Show quoted text -
                      >
                      John -
                      Still getting the same error! (Type Mismatch)
                      >
                      SQL resolves to:
                      UPDATE tblInteractionH istory SET tblInteractionH istory.Notes = "This
                      is his problem. Testing key = 86 - 1" WHERE
                      tblInteractionH istory.Interact ionKey =86
                      >
                      Code: (I copied yours and tried it with no continuation characters,
                      and finally like this so I could see all the code in the window)
                      >
                      strSQL = "UPDATE tblInteractionH istory SET
                      tblInteractionH istory.Notes = """ & strPresProblem & """ " _
                      & " WHERE tblInteractionH istory.Interact ionKey =" &
                      lngInteractionK ey
                      >
                      It seems SO simple ! Update a field - and the SQL looks fine (to me,
                      at least).
                      >
                      Any ideas now? I even Decompiled and recompiled.
                      >
                      sara- Hide quoted text -
                      >
                      - Show quoted text -
                      John -
                      InteractionKey in the table is the key to the table and is an
                      AutoNumber.

                      Dim lngInteractionK ey As Long - is in the form
                      (frmUpdateIniti alCall)

                      lngInteractionK ey = DMax("Interacti onKey", "tblInteraction History",
                      _
                      "ClientKey = " & lngClientKey & " AND InteractionType Key =
                      6") - is the statement executed to get the interaction key. (I am
                      looking for the "most recent" date in case the call comes in for a
                      former client, so it is an "initial" call (again).

                      So though I was quite sure the answers to your questions were both
                      "yes", above is the code/info to demonstrate all are long (unless I
                      misunderstand something)

                      Next?

                      Sara



                      Comment

                      • John Welch

                        #12
                        Re: Problem updating memo field with string data from form

                        I'm running out of ideas. One thing to try is to take the resolved SQL
                        and copy and paste it into the query builder as a new query then try to
                        run it from there. It sometimes gives you more information than the vb
                        error message.

                        -john

                        sara wrote:
                        John -
                        InteractionKey in the table is the key to the table and is an
                        AutoNumber.
                        >
                        Dim lngInteractionK ey As Long - is in the form
                        (frmUpdateIniti alCall)
                        >
                        lngInteractionK ey = DMax("Interacti onKey", "tblInteraction History",
                        _
                        "ClientKey = " & lngClientKey & " AND InteractionType Key =
                        6") - is the statement executed to get the interaction key. (I am
                        looking for the "most recent" date in case the call comes in for a
                        former client, so it is an "initial" call (again).
                        >
                        So though I was quite sure the answers to your questions were both
                        "yes", above is the code/info to demonstrate all are long (unless I
                        misunderstand something)
                        >
                        Next?
                        >
                        Sara
                        >
                        >
                        >

                        Comment

                        • sara

                          #13
                          Re: Problem updating memo field with string data from form

                          On Jul 20, 6:08 pm, John Welch <so...@nospam.c omwrote:
                          I'm running out of ideas. One thing to try is to take the resolved SQL
                          and copy and paste it into the query builder as a new query then try to
                          run it from there. It sometimes gives you more information than the vb
                          error message.
                          >
                          -john
                          >
                          >
                          >
                          sara wrote:
                          John -
                          InteractionKey in the table is the key to the table and is an
                          AutoNumber.
                          >
                          Dim lngInteractionK ey As Long - is in the form
                          (frmUpdateIniti alCall)
                          >
                          lngInteractionK ey = DMax("Interacti onKey", "tblInteraction History",
                          _
                          "ClientKey = " & lngClientKey & " AND InteractionType Key =
                          6") - is the statement executed to get the interaction key. (I am
                          looking for the "most recent" date in case the call comes in for a
                          former client, so it is an "initial" call (again).
                          >
                          So though I was quite sure the answers to your questions were both
                          "yes", above is the code/info to demonstrate all are long (unless I
                          misunderstand something)
                          >
                          Next?
                          >
                          Sara- Hide quoted text -
                          >
                          - Show quoted text -
                          John -
                          I did that,

                          UPDATE tblInteractionH istory SET tblInteractionH istory.Notes = "This
                          is his problem. Testing key = 86 -7" WHERE
                          tblInteractionH istory.Interact ionKey =86

                          and it works just fine and dandy.

                          SO,?????/

                          I also tried to "resolve" the fields - I had this idea that since the
                          form was updating tblInitialCall. PresentingProbl em, I could make an
                          update query and have tblInteractionH istory.Notes update to
                          tblInitialCall. PresentingProbl em. Not working - but that's for 2
                          reasons:

                          1. I can't figure out how to get the DMAX (find the highest key on
                          interactionHist ory with a type of 6 )- so I update the corret record
                          on interactionHist ory

                          2. I JUST tried to get the Client Key (trying to break it down and do
                          one little thing at a time) from the form. Form frminitialcall is
                          OPEN with a record. I run a Select Query and JUST try to get the
                          value for: forms]![frmUpdateInitia lCall].[txtClientKey]

                          SELECT [forms]![frmUpdateInitia lCall].[txtClientKey] AS unk;

                          And the result is a Martian Character:

                          THIS seems to be the problem! But why?

                          *** I ALSO tried:

                          UPDATE tblInteractionH istory INNER JOIN tblInitialCallI nfo ON
                          tblInteractionH istory.ClientKe y = tblInitialCallI nfo.ClientKey SET
                          tblInteractionH istory.Notes = [tblInitialCallI nfo].[CallerPresProbl em]
                          WHERE (((tblInitialCa llInfo.CallerKe y)=[forms]![frmUpdateInitia lCall].
                          [txtCallerKey]));

                          Which runs, but since there are 2 InitialCall (type 6) records, it
                          wants to update BOTH of them.


                          When I add the criteria (DMAX problem here) to get just the RIGHT
                          InteractionHist ory record, I get NO record selected (I'm guessing it's
                          my KEY field on the form).

                          UPDATE tblInteractionH istory INNER JOIN tblInitialCallI nfo ON
                          tblInteractionH istory.ClientKe y = tblInitialCallI nfo.ClientKey SET
                          tblInteractionH istory.Notes = [tblInitialCallI nfo].[CallerPresProbl em]
                          WHERE (((tblInitialCa llInfo.CallerKe y)=[forms]![frmUpdateInitia lCall].
                          [txtCallerKey]) AND
                          ((tblInteractio nHistory.Intera ctionKey)=DMax( "InteractionKey ","tblInteracti onHistory",
                          ("ClientKey" =[forms]![frmUpdateInitia lCall].[txtClientKey]) And
                          ("InteractionTy peKey"=6))));

                          I *feel* like I'm narrowing in on the problem, but just can't get that
                          last little piece to fit. The problem is just "Type Mismatch" - not
                          "in Criteria" now.

                          Also, I did delete and re-add the callerKey and ClientKey fields on
                          the form - just in case it was bad or something. No change.

                          Are we getting somewhere? (I know it's Sunday - I generally work on
                          this app nights and weekends since it's volunteer work ...)

                          Sara

                          Comment

                          • sara

                            #14
                            Re: Problem updating memo field with string data from form

                            On Jul 20, 6:08 pm, John Welch <so...@nospam.c omwrote:
                            I'm running out of ideas. One thing to try is to take the resolved SQL
                            and copy and paste it into the query builder as a new query then try to
                            run it from there. It sometimes gives you more information than the vb
                            error message.
                            >
                            -john
                            >
                            >
                            >
                            sara wrote:
                            John -
                            InteractionKey in the table is the key to the table and is an
                            AutoNumber.
                            >
                            Dim lngInteractionK ey As Long - is in the form
                            (frmUpdateIniti alCall)
                            >
                            lngInteractionK ey = DMax("Interacti onKey", "tblInteraction History",
                            _
                            "ClientKey = " & lngClientKey & " AND InteractionType Key =
                            6") - is the statement executed to get the interaction key. (I am
                            looking for the "most recent" date in case the call comes in for a
                            former client, so it is an "initial" call (again).
                            >
                            So though I was quite sure the answers to your questions were both
                            "yes", above is the code/info to demonstrate all are long (unless I
                            misunderstand something)
                            >
                            Next?
                            >
                            Sara- Hide quoted text -
                            >
                            - Show quoted text -
                            I did it!!! Well, more accurately, you helped and Allen Browne
                            provided the final solution - let me explain.

                            I had another problem where an Update query wasn't working - never
                            connected the two problems. Allen was hjelping me with the other and
                            pointed me toward his site:
                            How to use the Execute method to run action queries in Microsoft Access, avoiding unnecessary confirmation dialogs while still being notified of any errors and knowing if the query completed successfully.

                            to learn a bit about dbExecute.

                            I applied the concept to my work problem, and somehow managed to
                            connect the concept to this db. It works! SO, now I do:

                            strSQL = "UPDATE tblInteractionH istory SET
                            tblInteractionH istory.Notes = """ & strPresProblem & """ " _
                            & " WHERE tblInteractionH istory.Interact ionKey =" &
                            lngInteractionK ey

                            Set db = DBEngine(0)(0)
                            db.Execute strSQL, dbFailOnError
                            MsgBox db.RecordsAffec ted & " record(s) updated."
                            Set db = Nothing

                            And it updates!! I am so excited, I can barely contain myself (next
                            stop, I'll go get a Life!).

                            Anyway, thanks so much for sticking this out with me! I hope this
                            makes sense to you as a solution as well. It works!

                            sara

                            Comment

                            • John Welch

                              #15
                              Re: Problem updating memo field with string data from form

                              Good catch. I got so caught up on the SQL that I didn't notice the
                              docmd.runcomman d
                              -john

                              sara wrote:
                              On Jul 20, 6:08 pm, John Welch <so...@nospam.c omwrote:
                              >I'm running out of ideas. One thing to try is to take the resolved SQL
                              >and copy and paste it into the query builder as a new query then try to
                              >run it from there. It sometimes gives you more information than the vb
                              >error message.
                              >>
                              >-john
                              >>
                              >>
                              >>
                              >sara wrote:
                              >>John -
                              >> InteractionKey in the table is the key to the table and is an
                              >>AutoNumber.
                              >> Dim lngInteractionK ey As Long - is in the form
                              >>(frmUpdateIni tialCall)
                              >> lngInteractionK ey = DMax("Interacti onKey", "tblInteraction History",
                              >>_
                              >> "ClientKey = " & lngClientKey & " AND InteractionType Key =
                              >>6") - is the statement executed to get the interaction key. (I am
                              >>looking for the "most recent" date in case the call comes in for a
                              >>former client, so it is an "initial" call (again).
                              >>So though I was quite sure the answers to your questions were both
                              >>"yes", above is the code/info to demonstrate all are long (unless I
                              >>misundersta nd something)
                              >>Next?
                              >>Sara- Hide quoted text -
                              >- Show quoted text -
                              >
                              I did it!!! Well, more accurately, you helped and Allen Browne
                              provided the final solution - let me explain.
                              >
                              I had another problem where an Update query wasn't working - never
                              connected the two problems. Allen was hjelping me with the other and
                              pointed me toward his site:
                              How to use the Execute method to run action queries in Microsoft Access, avoiding unnecessary confirmation dialogs while still being notified of any errors and knowing if the query completed successfully.

                              to learn a bit about dbExecute.
                              >
                              I applied the concept to my work problem, and somehow managed to
                              connect the concept to this db. It works! SO, now I do:
                              >
                              strSQL = "UPDATE tblInteractionH istory SET
                              tblInteractionH istory.Notes = """ & strPresProblem & """ " _
                              & " WHERE tblInteractionH istory.Interact ionKey =" &
                              lngInteractionK ey
                              >
                              Set db = DBEngine(0)(0)
                              db.Execute strSQL, dbFailOnError
                              MsgBox db.RecordsAffec ted & " record(s) updated."
                              Set db = Nothing
                              >
                              And it updates!! I am so excited, I can barely contain myself (next
                              stop, I'll go get a Life!).
                              >
                              Anyway, thanks so much for sticking this out with me! I hope this
                              makes sense to you as a solution as well. It works!
                              >
                              sara
                              >

                              Comment

                              Working...