Re: Converting Queries to Code

From: Albert D. Kallal (pleasenonosspammkallal_at_msn.com)
Date: 04/08/04 

Date: Thu, 8 Apr 2004 00:59:21 -0600

"Bill Nichols" <anonymous@discussions.microsoft.com> wrote in message
news:D8339A94-7DC5-412B-842D-4C8CBEE7648B@microsoft.com...
> 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. 

--
Albert D. Kallal        (MVP)
Edmonton, Alberta Canada
pleasenonoSpamKallal@msn.com
http://www.attcanada.net/~kallal.msn

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: cascading combo boxes on a subform question
    ... of SortOrder from both tables. ... Once I get this code working, ... > Then when it runs open the Immediate Window and see if the SQL ... > SQL View of a new query, and then switch the query to design view to see ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)