Specific Update Query Problem

[McArthurGDM]

Consider that I have 2 tables, Table1 and Table2

I have information in Table1 that I want to update with information in Table2

However, I DO NOT want to write over previously inputted information in a given column in Table1. I only want to fill in the BLANK cells. This is easy enough to do when you are trying to update 1 and only 1 column.


However, suppose that I have 20 columns and I want to fill in the blank information at random spots in Table1 with the newly filled in information from Table2. Updating those NULLS in 20 columns requires that you do it 20 different times - to do it easily.

However, is there a way that I can do it all in one query? I can't seem to get it to work.

Thanks for the help,

[ranman256]

THE EASIEST...
you can build 20 queries and put them in a macro
then just run the macro

IF you know vba,
What I do is have a table of the fields I want to update.
Some vb code gets all the fields then updates them .
sSql = "update table ([" & vField & "]) from table2.fle

[McArthurGDM]

I was thinking that VBA would be the answer to this problem. I wouldn't know where to begin, though. Not without some help, anyways.

[ranman256]

If you dont know vba, the best way would be the 20 upd queries in a macro.
then run the macro.
make 1 upd qry, save as, change the field, save as, change the fld, etc.

REMEMBER to turn off updates setwarnings OFF
or you have to answer 20 yes questions.

[McArthurGDM]

I was able to set it up in VBA, actually. You should run SQL, updating 1 row at a time, through VBA in this case, as such - here is an example:

Private Sub Command0_Click()

Dim strQuery(1 To 4) As String

strQuery(1) = "A"
strQuery(2) = "B"
strQuery(3) = "C"
strQuery(4) = "D"

For i = 1 To 4
DoCmd.RunSQL ("UPDATE Table1 INNER JOIN Table2 ON Table1.key = Table2.key SET Table1." & strQuery(i) & " = [Table2]." & strQuery(i) & " WHERE (((Table1." & strQuery(i) & ") Is Null));")
Next i

End Sub

[McArthurGDM]

Also, you might want to make sure you have something in there so you don't have to click "YES" each time it updates.

[June7]

Use:

CurrentDb.Execute "UPDATE ...")

and the warning won't popup and don't need to use SetWarnings.