Update query

[samotek]

I want to write a function looking into a query :
SELECT Customers.afid, orders1.customerid
FROM Customers INNER JOIN orders1 ON Customers.Customerid = orders1.customerid;

With the following condition :
If customers.afid = 3 , to update the order1.cusomerid to 124

Like that :
UPDATE Customers INNER JOIN orders1 ON Customers.Customerid = orders1.customerid SET orders1.customerid = 124
WHERE (((Customers.afid)=3));

Also, when customers.afid = 4, to update order1.customerid to 320
I have 12 cases of the afid,so perhaps code is better
Etc etc

Can you help ?

 

[YevS]

This should help.

Function UpdateIDQry(ID As Integer) As String

Select Case ID
Case 3
UpdateIDQry= "Update.....orders1.customerid = 124 .... Where Customers.afid=3"
Case 4
UpdateIDQry= "Update.....orders1.customerid = 320 .... Where Customers.afid=4"
Case 5
UpdateIDQry= you get the idea
end select

This function will return the query string when you pass it the id number like so:

docmd.runSQL UpdateIDQry(MyRS!afid)

You just need to set your recordset to the "Customers" table.