Solved

How do I write a VBA procedure to replace certain field values in Access 2010?

Posted on 2016-08-10
13
66 Views
Last Modified: 2016-08-11
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")
0
Comment
Question by:Liberty4all
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
13 Comments
 
LVL 36

Expert Comment

by:PatHartman
ID: 41750927
Your code is not producing a valid SQL string.

When you  build SQL strings in VBA and you get errors when you try to run them, add a stop in the code on the line that runs the query.  Print the SQL String to the debug window.  If you don't see the error, copy the string and paste it into the QBE in SQL view and see what error you get there.  Sometimes you will get a better error message from QBE than from VBA.

If you don't know what a valid update query string looks like, build one using QBE and then switch to SQL View.  Copy the syntax you see in that view.

PS - your schema is not normalized if you are having to change this string in 32 tables.  Your time would be better spent on fixing the normalization problem.  A string such as this should appear in one and only one table.  if you need the string in other places, use a query to join to the table where the string is defined.
0
 
LVL 4

Expert Comment

by:bfuchs
ID: 41751041
change this
    strTable1 = tbl_HIP_vs_LowIncBrrwrs_ByAA_Test
    strAA1 = tbl_HIP_vs_LowIncBrrwrs_ByAA_Test.[AA NAME]

Open in new window

to this
    strTable1 = "tbl_HIP_vs_LowIncBrrwrs_ByAA_Test"
    strAA1 = "tbl_HIP_vs_LowIncBrrwrs_ByAA_Test.[AA NAME]"

Open in new window

0
 

Author Comment

by:Liberty4all
ID: 41751106
bfuchs: I added quotes as you suggested above and the code compiles.  When I run it an error appears saying it can't find the field '|1' referred to my expression.  The field AA NAME is correct and there is a space in the name which is why I have it enclosed in brackets.  The code breaks on the following line.

strAA1 = Replace([AA NAME], "CHICAGO_NAPERVILLE_JOLIET_IL", "CHICAGO_NAPERVILLE_ELGIN_IL_WI")

Do you know what '|1' refers to?
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 4

Expert Comment

by:bfuchs
ID: 41751139
the replace function expects a string, you must first assign the contents of AA Name to a string variable (you can use something like sVar=dlookup(..) ) and then have the string variable as first agrument of replace function.
0
 

Author Comment

by:Liberty4all
ID: 41751202
I made the following modifications.  The code compiles but when running it says it cannot find tbl_HIP_vs_LowIncBrrwrs_ByAA_Test.[AA NAME] and breaks on the line beginning with strAAvar1 = DLookup.  I have minimal experience with Dlookup so I likely have some missing quotes, parentheses or something else.  The spelling of tbl_HIP_vs_LowIncBrrwrs_ByAA_Test.[AA NAME] is correct.

Please advise what changes I should make.

Dim strSQL As String
    Dim strTable1 As String
    Dim strAA1 As String
    Dim strAAvar1 As String
       
    strTable1 = "tbl_HIP_vs_LowIncBrrwrs_ByAA_Test"
    strAA1 = "tbl_HIP_vs_LowIncBrrwrs_ByAA_Test.[AA NAME]"
    strAAvar1 = DLookup("CHICAGO_NAPERVILLE_JOLIET_IL", "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([strAAvar1], "CHICAGO_NAPERVILLE_JOLIET_IL", "CHICAGO_NAPERVILLE_ELGIN_IL_WI")
0
 
LVL 4

Expert Comment

by:bfuchs
ID: 41751216
try this
    strAAvar1 = DLookup("[AA NAME]", "tbl_HIP_vs_LowIncBrrwrs_ByAA_Test")

Open in new window

0
 
LVL 36

Expert Comment

by:PatHartman
ID: 41751226
The syntax for DLookup() is

SomeVariable = DLookup("some column name", "some table or query name", "some where clause withouth the word WHERE")

This will place the value contained by "some column name" into the variable named SomeVariable.

You appear to be using a data value as "some column name" rather than a column name and you have no where clause so the result returned will be random.

Can you tell us in words what you think this expression should do?
0
 

Author Comment

by:Liberty4all
ID: 41751260
bfuchs: I made your recommended change
 strAAvar1 = DLookup("[AA NAME]", "tbl_HIP_vs_LowIncBrrwrs_ByAA_Test")

The code compiles and runs but no change occurs in the table.

Pat: after making the above change I then made the following modification:
strAAvar1 = DLookup("[AA NAME]", "tbl_HIP_vs_LowIncBrrwrs_ByAA_Test", "[AA NAME]=CHICAGO_NAPERVILLE_JOLIET_IL")

The code compiles but when running produces an error where it apparently doesn't recognize CHICAGO_NAPERVILLE_JOLIET_IL for WHERE.  The code breaks on the line for strAAvar1.  If I make the WHERE portion read only CHICAGO_NAPERVILLE_JOLIET_IL the result is still the same.  I don't know how else to write the WHERE portion.  I just want to replace  CHICAGO_NAPERVILLE_JOLIET_IL with CHICAGO_NAPERVILLE_ELGIN_IL_WI in the AA NAME field.
0
 

Author Comment

by:Liberty4all
ID: 41751305
The attached file contains the table and code I'm using.  The table has been stripped of sensitive information so most of the fields are blank.  The table is for a report so there is no primary key and no indexed fields.  The AA names appear multiple times as each AA has multiple institutions.  

I tried using different combinations of double and single quotes in the row for strAA1 = Replace but without success.  I think I'm within a hair of making this work.  The code compiles and runs but no change occurs in the table.  I appreciate your time in helping me make this work.
AA-Update.accdb
0
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41752328
I modified the database to do what you said you wanted to do.  Your approach wouldn't work since you need to update the data and your code wasn't doing that.

I added two controls to the form.  A combo for the old value to make selecting it easier and a text box for the new value.  I created a query that uses the two controls on the form.  I added validation code to make sure that before running the update query both fields had data.  And then I requeried the combo so it would show the new value.

To repeat my original comment, the table is not correctly normalized.  Consider fixing the problem so that you don't have to do this bulk update.
AA-UpdateFixed.accdb
0
 

Author Comment

by:Liberty4all
ID: 41752793
Pat, thanks for all the time you put into this.  I'm guessing it's not possible to do mass updates by specifying the AA names and tables to be updated in code statements?  If so, please advise and I will close the question.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 41752907
I used a saved querydef.  You could use a query you build on the fly.  Change my querydef to SQL View and use that as your starting point.    You would have to change the SQL String to make the table and column names variables that your code fills.   Instead of using OpenQuery, you would use the RunSQL method with the SQL String as an argument rather than the query name in the example I made.

There is no clean way to do this since it is exceedingly poor practice for a relational database.  So, you'll have to use somewhat of a curmudgeon approach.  The most automation you can get is to create a table of table and column name pairs and write a VBA loop that reads the table and builds the SQL based on the table and column name in the record.  Of course if some tables have multiple columns, then it becomes much more complex.  I'm assuming you are only going to change one name at a time, you just want to change it in a bunch of tables so you can leave the two controls on the form as is.  Just run the loop to update multiple tables, one at a time rather than running the single query.

You really need to look at redesigning this app.  You seem to have a bunch of spreadsheets that you are calling tables and nothing good comes from that design.
0
 
LVL 36

Assisted Solution

by:PatHartman
PatHartman earned 500 total points
ID: 41752919
I changed the query to use an alias which will limit the characters you need to change as you build the SQL String.

strSQL =  "UPDATE " & rs!TableName & "As t SET t.[" & rs!ColumnName & "] = [forms]![Form1]![txtNewName]
WHERE (((t.[AA NAME])=[forms]![Form1]![cboOldName]));"

DoCmd.RunSQL strSQL

So each time through the loop you get a different value for TableName and Column name
1

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question