Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

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

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
Liberty4all
Asked:
Liberty4all
  • 5
  • 5
  • 3
2 Solutions
 
PatHartmanCommented:
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
 
bfuchsCommented:
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
 
Liberty4allAuthor Commented:
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
bfuchsCommented:
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
 
Liberty4allAuthor Commented:
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
 
bfuchsCommented:
try this
    strAAvar1 = DLookup("[AA NAME]", "tbl_HIP_vs_LowIncBrrwrs_ByAA_Test")

Open in new window

0
 
PatHartmanCommented:
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
 
Liberty4allAuthor Commented:
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
 
Liberty4allAuthor Commented:
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
 
PatHartmanCommented:
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
 
Liberty4allAuthor Commented:
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
 
PatHartmanCommented:
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
 
PatHartmanCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now