Problem updating memo field with string data from form

[sara]

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.RunCommand acCmdSaveRecord

lngClientKey = Me.txtClientKey
lngInteractionKey = DLookup("InteractionKey",
"tblInteractionHistory", _
"ClientKey = " & lngClientKey & " AND InteractionTypeKey =
6")

strPresProblem = Me.txtPresentingProblem

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

' 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 tblInteractionHistory " _
& " SET tblInteractionHistory.Notes = " & strPresProblem & ""
_
& " WHERE tblInteractionHistory.InteractionKey = " &
lngInteractionKey
DoCmd.RunCommand strSQL

[Bob Quintal]

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!additionfield
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 <sa*******@yahoo.comwrote in
news:11**********************@k79g2000hse.googlegr oups.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.RunCommand acCmdSaveRecord

lngClientKey = Me.txtClientKey
lngInteractionKey = DLookup("InteractionKey",
"tblInteractionHistory", _
"ClientKey = " & lngClientKey & " AND
InteractionTypeKey =
6")

strPresProblem = Me.txtPresentingProblem

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

' 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 tblInteractionHistory " _
& " SET tblInteractionHistory.Notes = " &
strPresProblem & ""
_
& " WHERE tblInteractionHistory.InteractionKey = " &
lngInteractionKey
DoCmd.RunCommand strSQL

--
Bob Quintal

PA is y I've altered my email address.

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

[Bob Quintal]

sara <sa*******@yahoo.comwrote in
news:11*********************@57g2000hsv.googlegrou ps.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 tblInteractionHistory " & vbNewLine _
& "SET tblInteractionHistory.Notes = " & vbNewLine _
& strPresProblem & vbNewLine _
& " WHERE tblInteractionHistory.InteractionKey = " _
& lngInteractionKey & ";"

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

[sara]

On Jul 16, 4:32 pm, Bob Quintal <[e-mail]:

sara <[e-mail] innews:11*********************@57g2000hsv.googlegr oups.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 tblInteractionHistory " & vbNewLine _
& "SET tblInteractionHistory.Notes = " & vbNewLine _
& strPresProblem & vbNewLine _
& " WHERE tblInteractionHistory.InteractionKey = " _
& lngInteractionKey & ";"

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.com

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

[sara]

On Jul 16, 4:32 pm, Bob Quintal <[e-mail]:

sara <[e-mail] innews:11*********************@57g2000hsv.googlegr oups.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 tblInteractionHistory " & vbNewLine _
& "SET tblInteractionHistory.Notes = " & vbNewLine _
& strPresProblem & vbNewLine _
& " WHERE tblInteractionHistory.InteractionKey = " _
& lngInteractionKey & ";"

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.com

Bob -
My other post hasn't appeared yet, but I have been working on your
suggestion:

?strsql
UPDATE tblInteractionHistory
SET tblInteractionHistory.Notes =
This is his problem on 719 upodating pores prob
WHERE tblInteractionHistory.InteractionKey =
36;

"This is his problem on 719 upodating pores prob" is the data I want
to REPLACE (Update to) in the memo field tblInteractionHistory.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

[John Welch]

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 tblInteractionHistory " _
& " SET tblInteractionHistory.Notes = '" & strPresProblem & "'"

2
strSQL = "UPDATE tblInteractionHistory " _
& " SET tblInteractionHistory.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

[sara]

On Jul 19, 1:46 pm, John Welch <[e-mail]:

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 tblInteractionHistory " _
& " SET tblInteractionHistory.Notes = '" & strPresProblem & "'"

2
strSQL = "UPDATE tblInteractionHistory " _
& " SET tblInteractionHistory.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 tblInteractionHistory
SET tblInteractionHistory.Notes =
" & strPresProblem & "" & vbNewLine WHERE
tblInteractionHistory.InteractionKey = 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

[John Welch]

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 tblInteractionHistory SET tblInteractionHistory.Notes =
""" & strPresProblem & """ WHERE tblInteractionHistory.InteractionKey =
" & lngInteractionKey
-John

sara wrote:

On Jul 19, 1:46 pm, John Welch <[e-mail]:

>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 tblInteractionHistory " _
& " SET tblInteractionHistory.Notes = '" & strPresProblem & "'"

2
strSQL = "UPDATE tblInteractionHistory " _
& " SET tblInteractionHistory.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 tblInteractionHistory
SET tblInteractionHistory.Notes =
" & strPresProblem & "" & vbNewLine WHERE
tblInteractionHistory.InteractionKey = 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

[sara]

On Jul 20, 12:23 pm, John Welch <[e-mail]:

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 tblInteractionHistory SET tblInteractionHistory.Notes =
""" & strPresProblem & """ WHERE tblInteractionHistory.InteractionKey =
" & lngInteractionKey

-John

sara wrote:

On Jul 19, 1:46 pm, John Welch <[e-mail]:

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 tblInteractionHistory " _
& " SET tblInteractionHistory.Notes = '" & strPresProblem & "'"

2
strSQL = "UPDATE tblInteractionHistory " _
& " SET tblInteractionHistory.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 tblInteractionHistory
SET tblInteractionHistory.Notes =
" & strPresProblem & "" & vbNewLine WHERE
tblInteractionHistory.InteractionKey = 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 tblInteractionHistory SET tblInteractionHistory.Notes = "This
is his problem. Testing key = 86 - 1" WHERE
tblInteractionHistory.InteractionKey =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 tblInteractionHistory SET
tblInteractionHistory.Notes = """ & strPresProblem & """ " _
& " WHERE tblInteractionHistory.InteractionKey =" &
lngInteractionKey

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

[John Welch]

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 tblInteractionHistory.InteractionKey =" & lngInteractionKey

are you sure the field InteractionKey in tblInteractionHistory is a long
integer type field? and that the variable lngInteractionKey is really
a long int variable?

-john

sara wrote:

On Jul 20, 12:23 pm, John Welch <[e-mail]:

>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 tblInteractionHistory SET tblInteractionHistory.Notes =
""" & strPresProblem & """ WHERE tblInteractionHistory.InteractionKey =
" & lngInteractionKey

-John

sara wrote:

>>On Jul 19, 1:46 pm, John Welch <[e-mail]:
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 tblInteractionHistory " _
& " SET tblInteractionHistory.Notes = '" & strPresProblem & "'"
2
strSQL = "UPDATE tblInteractionHistory " _
& " SET tblInteractionHistory.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 tblInteractionHistory
SET tblInteractionHistory.Notes =
" & strPresProblem & "" & vbNewLine WHERE
tblInteractionHistory.InteractionKey = 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 tblInteractionHistory SET tblInteractionHistory.Notes = "This
is his problem. Testing key = 86 - 1" WHERE
tblInteractionHistory.InteractionKey =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 tblInteractionHistory SET
tblInteractionHistory.Notes = """ & strPresProblem & """ " _
& " WHERE tblInteractionHistory.InteractionKey =" &
lngInteractionKey

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

[sara]

On Jul 20, 4:23 pm, John Welch <[e-mail]:

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 tblInteractionHistory.InteractionKey =" & lngInteractionKey

are you sure the field InteractionKey in tblInteractionHistory is a long
integer type field? and that the variable lngInteractionKey is really
a long int variable?

-john

sara wrote:

On Jul 20, 12:23 pm, John Welch <[e-mail]:

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 tblInteractionHistory SET tblInteractionHistory.Notes =
""" & strPresProblem & """ WHERE tblInteractionHistory.InteractionKey =
" & lngInteractionKey

-John

sara wrote:
On Jul 19, 1:46 pm, John Welch <[e-mail]:
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 tblInteractionHistory " _
& " SET tblInteractionHistory.Notes = '" & strPresProblem & "'"
2
strSQL = "UPDATE tblInteractionHistory " _
& " SET tblInteractionHistory.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 tblInteractionHistory
SET tblInteractionHistory.Notes =
" & strPresProblem & "" & vbNewLine WHERE
tblInteractionHistory.InteractionKey = 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 tblInteractionHistory SET tblInteractionHistory.Notes = "This
is his problem. Testing key = 86 - 1" WHERE
tblInteractionHistory.InteractionKey =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 tblInteractionHistory SET
tblInteractionHistory.Notes = """ & strPresProblem & """ " _
& " WHERE tblInteractionHistory.InteractionKey =" &
lngInteractionKey

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 lngInteractionKey As Long - is in the form
(frmUpdateInitialCall)

lngInteractionKey = DMax("InteractionKey", "tblInteractionHistory",
_
"ClientKey = " & lngClientKey & " AND InteractionTypeKey =
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

[John Welch]

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 lngInteractionKey As Long - is in the form
(frmUpdateInitialCall)

lngInteractionKey = DMax("InteractionKey", "tblInteractionHistory",
_
"ClientKey = " & lngClientKey & " AND InteractionTypeKey =
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

[sara]

On Jul 20, 6:08 pm, John Welch <[e-mail]:

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 lngInteractionKey As Long - is in the form
(frmUpdateInitialCall)

lngInteractionKey = DMax("InteractionKey", "tblInteractionHistory",
_
"ClientKey = " & lngClientKey & " AND InteractionTypeKey =
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 tblInteractionHistory SET tblInteractionHistory.Notes = "This
is his problem. Testing key = 86 -7" WHERE
tblInteractionHistory.InteractionKey =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.PresentingProblem, I could make an
update query and have tblInteractionHistory.Notes update to
tblInitialCall.PresentingProblem. Not working - but that's for 2
reasons:

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

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]![frmUpdateInitialCall].[txtClientKey]

SELECT [forms]![frmUpdateInitialCall].[txtClientKey] AS unk;

And the result is a Martian Character:

THIS seems to be the problem! But why?

*** I ALSO tried:

UPDATE tblInteractionHistory INNER JOIN tblInitialCallInfo ON
tblInteractionHistory.ClientKey = tblInitialCallInfo.ClientKey SET
tblInteractionHistory.Notes = [tblInitialCallInfo].[CallerPresProblem]
WHERE (((tblInitialCallInfo.CallerKey)=[forms]![frmUpdateInitialCall].
[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
InteractionHistory record, I get NO record selected (I'm guessing it's
my KEY field on the form).

UPDATE tblInteractionHistory INNER JOIN tblInitialCallInfo ON
tblInteractionHistory.ClientKey = tblInitialCallInfo.ClientKey SET
tblInteractionHistory.Notes = [tblInitialCallInfo].[CallerPresProblem]
WHERE (((tblInitialCallInfo.CallerKey)=[forms]![frmUpdateInitialCall].
[txtCallerKey]) AND
((tblInteractionHistory.InteractionKey)=DMax("Inte ractionKey","tblInteractionHistory",
("ClientKey"=[forms]![frmUpdateInitialCall].[txtClientKey]) And
("InteractionTypeKey"=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

[sara]

On Jul 20, 6:08 pm, John Welch <[e-mail]:

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 lngInteractionKey As Long - is in the form
(frmUpdateInitialCall)

lngInteractionKey = DMax("InteractionKey", "tblInteractionHistory",
_
"ClientKey = " & lngClientKey & " AND InteractionTypeKey =
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:
http://allenbrowne.com/ser-60.html
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 tblInteractionHistory SET
tblInteractionHistory.Notes = """ & strPresProblem & """ " _
& " WHERE tblInteractionHistory.InteractionKey =" &
lngInteractionKey

Set db = DBEngine(0)(0)
db.Execute strSQL, dbFailOnError
MsgBox db.RecordsAffected & " 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

[John Welch]

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

sara wrote:

On Jul 20, 6:08 pm, John Welch <[e-mail]:

>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 lngInteractionKey As Long - is in the form
(frmUpdateInitialCall)
lngInteractionKey = DMax("InteractionKey", "tblInteractionHistory",
_
"ClientKey = " & lngClientKey & " AND InteractionTypeKey =
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:
http://allenbrowne.com/ser-60.html
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 tblInteractionHistory SET
tblInteractionHistory.Notes = """ & strPresProblem & """ " _
& " WHERE tblInteractionHistory.InteractionKey =" &
lngInteractionKey

Set db = DBEngine(0)(0)
db.Execute strSQL, dbFailOnError
MsgBox db.RecordsAffected & " 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

[sara]

On Jul 23, 8:15 pm, John Welch <[e-mail]:

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

sara wrote:

On Jul 20, 6:08 pm, John Welch <[e-mail]:

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 lngInteractionKey As Long - is in the form
(frmUpdateInitialCall)
lngInteractionKey = DMax("InteractionKey", "tblInteractionHistory",
_
"ClientKey = " & lngClientKey & " AND InteractionTypeKey =
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:
http://allenbrowne.com/ser-60.html
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 tblInteractionHistory SET
tblInteractionHistory.Notes = """ & strPresProblem & """ " _
& " WHERE tblInteractionHistory.InteractionKey =" &
lngInteractionKey

Set db = DBEngine(0)(0)
db.Execute strSQL, dbFailOnError
MsgBox db.RecordsAffected & " 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- Hide quoted text -

- Show quoted text -

Do you know where I can read about this "Execute" - "Set db"? The
only one I knew was docmd.runcommand.

Sara