How do I write a VBA procedure to replace certain field values in Access 2010?
Posted on 2016-08-10
I have 32 tables with some field values that need updating. I'm trying to write a procedure that will update them simultaneously instead of doing it manually many times using a query. I created the following with the intent of adding a strSQL statement for each field value to be updated. The field that needs updating is AA NAME and in this example I want to change the value CHICAGO_NAPERVILLE_JOLIET_IL to CHICAGO_NAPERVILLE_ELGIN_IL_WI. When compiling the code an error appears saying tbl_HIP_vs_LowIncBrrwrs_ByAA_Test is not defined. I verified the spelling. I'm guessing this process could be simplified but it's the result of getting multiple "variable not defined" errors.
Any suggestions on what I should change would be appreciated.
Dim strSQL As String
Dim strTable1 As String
Dim strAA1 As String
strTable1 = tbl_HIP_vs_LowIncBrrwrs_ByAA_Test
strAA1 = tbl_HIP_vs_LowIncBrrwrs_ByAA_Test.[AA NAME]
strSQL = "SELECT [AA NAME], " & _
"FROM tbl_HIP_vs_LowIncBrrwrs_ByAA_Test " & _
"WHERE tbl_HIP_vs_LowIncBrrwrs_ByAA_Test.[AA NAME]=""CHICAGO_NAPERVILLE_JOLIET_IL"";"
strAA1 = Replace([AA NAME], "CHICAGO_NAPERVILLE_JOLIET_IL", "CHICAGO_NAPERVILLE_ELGIN_IL_WI")