(IF) Comparing two tables and copy/pasting

[sdrew1]

Hello, I'm fairly new to VBA and after reading forums for a few hours today I can't find an answer to the (presumably very simple question) below.
Any help v.gratefully received!

My aim is to paste data from table2.field2 into table1.field2, but with the following conditions: only if table2.field1 matches table1.field1, and only if table1.field2 is empty.

Enjoy going back to simple basics with my question, someone, no doubt! ;-)

 

[pr2-eugin]

Hello, Welcome to AWF..
Try this..

UPDATE Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field2 SET Table1.Field2 = Table2.Field2
WHERE (((Table1.Field1) Is Null));

 

[sdrew1]

Thanks very much for your help. I'm now seeing if I can find out what else to put in the VBA in order to get it to work, e.g. at the start of the code.

Many thanks.

 

[pr2-eugin]

You are welcome. Glad to help.

 

[MSAccessRookie]

Hello, Welcome to AWF..
Try this..

The following code is based on the requirements:

1. My aim is to paste data from table2.field2 into table1.field2
2. if table2.field1 matches table1.field1
3. if table1.field2 is empty

UPDATE Table1 INNER JOIN Table2 [COLOR=blue][B]ON Table1.Field1 = Table2.Field1[/B][/COLOR] [COLOR=green][B]SET Table1.Field2 = Table2.Field2[/B][/COLOR]
[COLOR=red][B]WHERE (((Table1.Field2) Is Null))[/B];[/COLOR]

I see that I am not the first to respond.

I noticed that my response differed, so I took time to explain why I came up with the solution that I did

 

[pr2-eugin]

I see that I am not the first to respond.

I noticed that my response differed, so I took time to explain why I came up with the solution that I did

Yes, the field names and table names were a bit confusing, I did try hard to put them right but table1.field1, table2.field2 .. but you got it right..

 

[sdrew1]

Thanks very much all, and for the help to get the right names in order. Cheers

 

[sdrew1]

I've now tried the above code (thank you!).

Sadly when I run it, I get the error message "Compile error: Syntax error".

Is this because I need to write in at the top of the VBA code, where to look for table1 or field 1? If so, could anyone help with how to do that correctly?


Many thanks,
sdrew1

 

[pr2-eugin]

Could you show the code you are actually using this statement along with?

 

[sdrew1]

thanks for your help. I really am a nOOb with this. Though I'm enjoying learning about it! Help is very much appreciated though!! Here's what I've got going so far...No doubt wrong...


Private Sub Infill_field_gaps()

Dim Database1 As Database
Dim Table1 As TableDef
Dim Table2 As TableDef
Dim Field1 As Field
Dim Field2 As Field

UPDATE Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1 SET Table1.Field2 = Table2.Field2 WHERE (((Table1.Field2) Is Null))

End Sub

 

[pr2-eugin]

Okay, so are you trying to CREATE tables dynamically using VBA and then updating those created tables? Looks like you have created the variables, but actually have not created any.. Is that what you are trying to do here?

 

[sdrew1]

I've already got the database, tables and fields already made.
My aim is to (kind of) update table 1 with new data from table 2.
There are two caveats though: firstly, I only want one of the fields in table 1 to update (the other fields in table 1 have old data compared to that within table 2, but I don't want those updates). Secondly, I only want the field to update if the field (i.e. field 2 of table 1) is completely blank.

Does that make any sense?

 

[pr2-eugin]

Okay.. Try this..

Private Sub Infill_field_gaps()
    Dim strSQL As String
    
    strSQL = "UPDATE Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1 SET Table1.Field2 = Table2.Field2 WHERE (((Table1.Field2) Is Null));"
    CurrentDb.Execute strSQL, dbFailOnError
End Sub

 

[sdrew1]

Thanks for your help. I've put your suggested code in and saved the vba. Then I've tried to the run it and nothing happens. I've tried several solutions, such as closing Access and reopening it but to no avail. There isn't an error message, there's just simply no action.

Any thoughts?

 

[pr2-eugin]

When you say nothing happens do you mean that the Table is not updated? Or no message comes up? Since we have used dbFailOnError, it might not throw any error but might simply fail.. so take away the option and see what happens.. If nothing happens upload a MDB version of your stripped down DB..

 

[sdrew1]

You're right with your suggestion: The table sadly doesn't update. (Also, no message comes up).

Thanks for your suggestions:
-I've tried removing dbFailOnError from the code, but still no improvement.
-I'll try your second suggestion now...

Or, Is it perhaps becuase I have to state that the field 1 is a number field and the field 2 a text field?

 

[pr2-eugin]

As long as the fields you are trying to match on the Join statement are the same type and the values you are assigning are the same type.. It should work.. Dis you try updating it in a Query? rather than using VBA?

 

[sdrew1]

Thanks very much!

I've tried it in a Query using the following design/SQL but again there is no update of the table. Perhaps linked to that is that Access throws up a message box saying "Enter Parameter Value: Table 1".

I can't see why the Syntax might be wrong. Any clues?

UPDATE Table1, Table2 SET Table2.Field2 = Table1.Field2
WHERE (((Table2.Field2) Is Not Null) AND ((Table2.Field1)=(Table2.Field1));



Note: I know that the syntax is different to what you suggested to put in VBA but the change was due to Access changing it into that automatically, as I filled in the Query Design.

 

[pr2-eugin]

I am not 100% sure, but

"Enter Parameter Value: Table 1"

is an indication that the table does not exist..

 

[MSAccessRookie]

Thanks very much!

I've tried it in a Query using the following design/SQL but again there is no update of the table. Perhaps linked to that is that Access throws up a message box saying "Enter Parameter Value: Table 1".

I can't see why the Syntax might be wrong. Any clues?

UPDATE Table1, Table2 SET Table2.Field2 = Table1.Field2
WHERE (((Table2.Field2) Is Not Null) AND ((Table1.Field1)=(Table2.Field1));



Note: I know that the syntax is different to what you suggested to put in VBA but the change was due to Access changing it into that automatically, as I filled in the Query Design.

Should that have been Table1.Field1, or is that a Type-O?

By the way, an alternative to your SQL Format is the Standard MS Access SQL Format below.

UPDATE Table1 INNER JOIN Table2 ON Table1.Field1=Table2.Field1
SET Table2.Field2 = Table1.Field2
WHERE Table2.Field2 Is Not Null