Converting Queries to Code

[Guest]

Good Morning al

I have been following the recent articles about using pasting the sql behind a query int
a private sub. I have tried using this for an update query but with no success. Does th
syntax change at all

I am running 2000 and my sub code is

docmd.RunSql "UPDATE Completed SET Completed.Name = [ED]![Name]

The table names are Completed and ED and I am trying to update the
Name fiel

Has anybody got any suggestions

Thanks for any tip

Bil

 

[Albert D. Kallal]

Good Morning all

I have been following the recent articles about using pasting the sql behind a query into
a private sub. I have tried using this for an update query but with no success. Does the
syntax change at all ?

No, not really, but then again, the sql has to be right in the first place.

I am running 2000 and my sub code is

docmd.RunSql "UPDATE Completed SET Completed.Name = [ED]![Name]"

The problem with above that what record do you want from table Ed?

In other words, you are trying to set a record in table Completed, and you
are trying to get a value from table ED, but which value do you want from
table ed?

Further, you have to make sure the sql can distinguish between:

docmd.RunSql "UPDATE Completed SET Completed.Name = 'hello' "

The above sql would set all the values in the field of "name" to hello. So,
what happens if you had a field called hello? In your case, you are trying
to set all values to "[ED]![Name]". I suspect, that this would be of no use,
and what you REALLY want is to fetched the value in the table called ed.
However, as mentioned, you need to specify WHAT record in table ed you want.

It is unlikely your above sql example runs ok in the query builder (get your
code working in the query builder first, and THEN try pasting it into your
code.

If for each record in table Completed, there is a single record from table
ED that has the same keyid, then you can do a join, and then run a update.

So, you need to identify what record from Completed you want to update (or
perhaps all records). And, you need to identity, or choose what value you
want from table ED.

 

[Guest]

Thank you for the respons

The query I use to update table Ed does work to update the field "Name
with the Completed Table field "Name
But the sql in the query gives me [ED]![Name
I've tried removing the brackets and ! and keeping the end " but no succes

docmd.RunSql "UPDATE Completed SET Completed.Name = ED.Name
Is the code I am using wrong

Bil

 

[Albert D. Kallal]

Thank you for the response

The query I use to update table Ed does work to update the field "Name"
with the Completed Table field "Name"

Ah...yes very well. When you run the query...it PROMPTS you to enter the
[ed]![name] value...am I on the right track?

Also...your sql as posted is not updating table ED (can I assume that the
above is a type-o?).

As written, you are updating table Completed...not table ED.

docmd.RunSql "UPDATE Completed SET Completed.Name = ED.Name"
Is the code I am using wrong ?

Well..if the above sql works in the query builder, then it should work. What
kind of error message are you getting?


.. Did you try the above code in the query builder?


You generally need to grab the user input BEFORE you run the query. Much
like:

dim strWhatName as string

strWhatName = InputBox("Enter new value for name")

if strWhatName <> "" then
docmd.RunSql "UPDATE Completed SET Completed.Name = '" & strWhatName &
"'"
end if

Also, perhaps you are mixing the term forms and tables here?

If ED.name is a value from a form...then ALWAYS qualify the full object
name...like:

docmd.RunSql "UPDATE Completed SET Completed.Name = forms![ED]!Name"

The above would take the value from a form called Ed, and a field called
name.

 

[gandalf]

1)The problem with

"UPDATE Completed SET Completed.Name = ED.Name"

is that there is no reference to the table ED


2)If you use wish to use a variable try concatenating the
value to the string (watch out for ' which need to be
doubled to '')

"UPDATE Completed SET Completed.Name = " & [ED]![Name]

This is updating the table Completed

3) Perhaps you want

UPDATE ED INNER JOIN Compleded ON ED.PK=COMPLETED.PK
SET ED.NAME=Completed.NAME
WHERE ED.PK = ....

4)Mind each database has its own SQL-dialect

 

[Van T. Dinh]

Your description seems to contradict with the SQL statement.

Are you trying to update the values in Table "Completed" or Table "ED"?

Your SQL at present updates values in Table "Completed", NOT Table "ED".

Also, there is no link/relationship between Table "Completed" and Table
"ED". If there are more than 1 Record in Table "ED", *which* "Name" value
from Table "ED" you want to use to update Table "Completed"?