VBA: Read table & write values to another table

[compooper]

Hi folks!!

How do you read values from a specific field in a table through vba?
AND THEN


Take those values and write them to another field depending on criteria like if the specific value is empty, or if it has this string, etc etc

[pbaldy]

Common methods of getting a value include DLookup() and opening a recordset on the table. To write, common methods include the AddNew method of a recordset and executing SQL (either a saved query or SQL built in VBA).

[rpeare]

my chosen method is this

dim db as database
dim rst as recordset
dim ssql as string
dim sVariable1 as string
dim iVariable2 as long

set db = currentdb
ssql = "SELECT * FROM TableName WHERE (<criteria>)"
set rst = db.openrecordset ssql
rst.movefirst

do while rst.eof <> true
svariable1 = rst.fields("<textfieldname>")
ivariable2 = rst.fields("<numberfieldname")
loop

For updating or inserting a record you would use something similar

ssql = "UPDATE <UpdateTableName> SET <fieldname1> = <insert value1>, <fieldname2> = <insert value2>"
db.execute ssql

the add record would be similar except it would be INSERT INTO instead of UPDATE.

If you are updating a record on the current recordset you could use

rst.edit
rst.fields("<Fieldname>").value = <new value>
rst.update

rst.update

[compooper]

okay thanks.
Is there a way though to have conditions set for the values you read.
i.e.
If CoID = 6 Or CoID = 8 Then

Do While rst.EOF <> True
TTDescr = rst.Fields("Testtable.Svc_description")
TTCode = rst.Fields("Testtable.Svc_code")
Loop
But I don't want to read every value in Testtable.Svc_description, only the ones that are NOT NULL.

[orange]

Have you tried to do this with an Update query? Just a thought.

[compooper]

Yes I have.

I usually use Update Queries, but the problem I am having is with the inner join I want to use.

I was hoping in the INNER JOIN I could use multiple IIF statements, but its not allowed.

So I'm guessing VBA is the way to go

[orange]

Yes I have.

I usually use Update Queries, but the problem I am having is with the inner join I want to use.

I was hoping in the INNER JOIN I could use multiple IIF statements, but its not allowed.

So I'm guessing VBA is the way to go

The general syntax for what I'm suggesting (a sample from my test db)

UPDATE table4 AS A
 INNER JOIN table5 AS B 
ON A.SalesCode = B.SalesCode 
SET A.SalesValue = B.salesvalue
WHERE (((A.SalesCode)=4)) OR
 (((A.SalesCode)=5));