Solved

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

Posted on 2016-08-10
13
40 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
  • 5
  • 5
  • 3
13 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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 3

Expert Comment

by:bfuchs
Comment Utility
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
Comment Utility
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
 
LVL 3

Expert Comment

by:bfuchs
Comment Utility
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
Comment Utility
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 3

Expert Comment

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

Open in new window

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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
Comment Utility
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 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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 34

Assisted Solution

by:PatHartman
PatHartman earned 500 total points
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

763 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now