Problem with UPDATE

[hanski]

Hi

I have a very simple update in my VBA but it does not work. What is
wrong with it?
I have a databases called ALLNAMES and CUSTOMERS.

I would like to update my ALLNAMES database when customers have bought
from me over 100 dollars. In CUSTOMERS I place TRUE in a GOOD field if
customer have bought over 100 dollars. I would like to update my
ALLNAMES database too, but it gives an error about "one parameter has
not value"...


UPDATE ALLNAMES SET GOOD = TRUE WHERE (SELECT GOOD FROM CUSTOMERS
WHERE GOOD = TRUE)

hanski

 

[Douglas J. Steele]

WHERE (SELECT GOOD FROM CUSTOMERS WHERE GOOD = TRUE)

isn't correct: you need some way of relating some field from ALLNAMES to
some field in the selected records from CUSTOMERS.

Is there a field in common? For example, does ALLNAMES have a CustomerID
field, the same as CUSTOMERS?

If so, try

UPDATE ALLNAMES SET GOOD = TRUE WHERE CustomerID IN (SELECT CustomerID FROM
CUSTOMERS
WHERE GOOD = TRUE)

If not, post back the details of the two tables.

 

[hanski]

WHERE (SELECT GOOD FROM CUSTOMERS WHERE GOOD = TRUE)

isn't correct: you need some way of relating some field from ALLNAMES to
some field in the selected records from CUSTOMERS.

Is there a field in common? For example, does ALLNAMES have a CustomerID
field, the same as CUSTOMERS?

If so, try

UPDATE ALLNAMES SET GOOD = TRUE WHERE CustomerID IN (SELECT CustomerID FROM
CUSTOMERS
WHERE GOOD = TRUE)

If not, post back the details of the two tables.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)










- Näytä siteerattu teksti -

Hi

Yes, I have an common field like Customerid in both databases, but
when I do like this:

Dim con As New ADODB.Connection
Dim AN As New ADODB.Recordset

Set con = CurrentProject.Connection

AN.Open "update ALLNAMES " & _
"set GOOD = true where CUSTOMERID in (select CUSTOMERID from CUSTOMERS
WHERE GOOD = TRUE)", con, adOpenDynamic, adLockOptimistic



it gives me an error: " At least one enforced parameter has not been
given a value".

What is wrong??

Hanski

 

[Douglas J. Steele]

Action queries (Update, Insert Into, Delete) don't generate recordsets: only
Select queries do.

Try

Dim con As New ADODB.Connection

Set con = CurrentProject.Connection

con.Execute "update ALLNAMES " & _
"set GOOD = true where CUSTOMERID in " & _
"(select CUSTOMERID from CUSTOMERS " & _
"WHERE GOOD = TRUE)"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi

Yes, I have an common field like Customerid in both databases, but
when I do like this:

Dim con As New ADODB.Connection
Dim AN As New ADODB.Recordset

Set con = CurrentProject.Connection

AN.Open "update ALLNAMES " & _
"set GOOD = true where CUSTOMERID in (select CUSTOMERID from CUSTOMERS
WHERE GOOD = TRUE)", con, adOpenDynamic, adLockOptimistic



it gives me an error: " At least one enforced parameter has not been
given a value".

What is wrong??