I have developed an Access form technique in which can add customers / jobs / sub jobs by assembling the data I want in the Customer record, then insert that data into a table especially designed to add records to the Customer table. I then execute a query that do the adds from my table ([1 QB Update]), typically, one new Customer record at a time. This all works good!
I want to then immediately execute another sql that will retrieve the newly added record(s) using the jumpin "FullName", so I can in turn update [1 QB Update] with the ListID of the new record. I tried:
UPDATE [1 QB Update], Customer SET [1 QB Update].ListID = [customer].[ListID] WHERE ((([1 QB Update].Name)=[customer].[fullname]));
I cancelled Access after it became unresponsive. I then assumed that I could join to the jumpin FullName, so i tried:
UPDATE [1 QB Update] INNER JOIN Customer ON [1 QB Update].Name = Customer.FullName SET [1 QB Update].ListID = [customer].[ListID];
and
UPDATE [1 QB Update] INNER JOIN Customer ON [1 QB Update].Name = Customer.FullName SET [1 QB Update].ListID = [customer].[ListID] WHERE ((([1 QB Update].Name)=[customre].[fullname]));
Again, Access went into "La - La" land.
I then executed the sql using a literal to access FullName as follows:
UPDATE [1 QB Update], Customer SET [1 QB Update].ListID = [customer].[ListID] WHERE (((Customer.FullName)="Young Rembrandts"));
This last sql worked fine.
How do I write a query or sql that will use a data field in my [1 QB Update] table to use the FullName jumpin feature to retrieve the Customer record?
Wayne
|