VBA Module to Find/Replace

to solve my big headache, I have table with a lot of records and every record contain unique number. However couples hundred of records have new number and I need to find and replace. I can do that one by one, but this is waste of time specially when I have those numbers in multiple tables.

I have one table tbl_status with two column:

OLD_num and NEW_num

Something like this:
P2345 P3445
P1000 P3446
P123 P3447

Than I have table with data tbl_Articles

Now I will like to make VBA function to loop thru table tbl_Article and if find any of number listed in table tbl_status under field OLD_num to replace with NEW_num

I hope I did explain well, English is my second language

Sponsored Links:

Related Topics

(vba) edit module n macro in different access db
I want to perform editing(find and replace) module(s) and macro(s) from different access database(s).

no progress in editing ( find/replace text ) macro,

well this is my code for replacing word in module ( please see attachment ) its only works when in the same database,
but as soon as I add 'Set Dbs = OpenDatabase(dbname) it didn't work.

(vba) edit module n macro in different access db
im stuck in vba for last 2 days.
I want to perform editing module(s) and macro(s) from different access database(s).

no progress in editing ( find/replace text ) macro,

well this is my code for replacing word in module ( please see attachment )
its only works when in the same database,
but as soon as I add 'Set Dbs = OpenDatabase(dbname) it didnt work

Find and Replace on a huge amount of records
I am trying to find and replace certain characters that are in every field of table.

The catch is: The table has 600K records in it.
So when I use CTRL H (Find and Replace), it works great.but only for the first 10000 records or so. It won't do any after that.

Then I found a module:

Public Function fReplace(ByVal Expression As String, ByVal Find As String, ByVal Replace As String) As String
Dim strTmp As String, n As Integer

Use the Find and Replace dialog box to change data
This topic explains how to use the Find and Replace dialog box to find and optionally replace data in an Access database.
* Learn about using the Find and Replace dialog box
* Find and replace data in a table
* Find and replace data in a form
* Find data in a query result set
* Find wildcard characters
* Examples of wildcards in use
* Find quotation marks and null or blank values
* Find and Replace dialog box control reference

Replacing a Filter Query in a Module
I created a Form which I want to open with one of four different filters (depending on how I want the data to be sorted). I also created a generic filter (called FilterForm) which I will always use to open the Form.

I want to create a Module to replace FilterForm with one of the four sort filters (FilterSortA, FilterSortB, etc.) depending on a parameter I pass to the Module.

My problem is I cannot seem to find a Module statement which will allow me to copy/overlay FilterForm with FilterSortA. In Macro language there is a DeleteObj and CopyObj command but I have to hardcode the FilterSort names which I want to avoid.

So my question is, is there a statement(s) which I can code in a Module which will allow me to replace FilterForm with FilterSortA, FilterSortB, etc.? Thank you for your time.

Find and replace question
There is a table in one of our MDBs that I need to add a character at the end of every record in a column

I tried using find a replace and Finding * and replacing with *U2 but that just replaces everything with literally *U2.

I have tried using combinations of ^&U2 with similar results.

Is there a simple way to accomplish this using the Find/replace dialog box

Use the Find and Replace dialog box to change data
You use the Find and Replace dialog box when you want to find and optionally replace small amounts of data, and when you are not comfortable using a query to find or replace data. The dialog box resembles the Find tools that you see in other programs, but it contains some features that make it more useful for searching relational databases. For example, you can search on a format applied to data, and you can choose to match part or all of the data in a field.

Assign vba module code to a access form button
I know there is a way to do this, but cannot find it on the web. I want to take eight different functions, all contained in one vba module, and run all of them with the click of a button I created on a form

VBA Code To Global VBA
s I have a question about saving VBA As Global Module.

While working with Access and VBA There are many VBA codes which we use very frequently for example:
Private Sub close_Click()
End Sub

How to save this as Module and then call to a button

Need module to replace MS Access Database
I intend to replace my ms-access database by my dotnetnuke portal.
The database holds adresslists and projektdata in more than 50 related tables. Now I'm looking for a dnn-modul to easily create forms and reports like it is possible with ms-access. Is there any module out there to meet this demand ?

Get the "Replace" out of Find/Replace
I have a find button on my form that opens the Find/Replace dialog. Because I don't want my users wantonly replacing half the entries in the table, my find button first sets AllowEdits to False.

The problem I'm having is setting it back to True when the dialog is closed. I've found that if I set it before the Find dialog is closed, the Replace tab will magically appear when it gets focus again. Is there a way to tell if the dialog is still open before allowing edits?

Use Find and Replace in Access 2007
You can use find and replace in Microsoft Access 2007 to find a field and then replace it with a different field in a quick and easy manner. Watch this video tutorial to find out how to use find and replace in Access 2007.

Replace Iff statement with Module
I use in query (see below) a iff statement to select the next task.
Now I want to replace this iff statement with a module, but it didn't work.

How to change the form title or caption from VBA.
I'm trying to create a module that when a form opens it will look at the module and set the forms title as per what it says in the module.
I've been looking arround but can't seem to find a specific way of doing it

Find and Replace in Access 2007
I have address columns:

705 First St #5 |
804 Main St #16 |

I just want to remove the pound (#) signs. How do find/replace for just the #?

I tried the Replace Function but it deletes the entire cell. I also tried the RemoveAlpha but I get an error saying its an undefined function.

Running a VBA Module from a Macro
How do you run a VBA module from a macro? I tried the OpenModule action, but all it did was to open the module. I had thought this would run it since OpenQuery runs the query, not just opens it.

Deleting Records using VBA Module
What is the recommended method for deleting records using a VBA module? Currently I am using an SQL method that specifies the table name and the upper time stamp date from which values should be deleted.

My database is continually being updated with new files from this same VBA module. I would like to delete old records based on a rolling window (say 30 days). Would this be the best way to go? It seems a bit slow.but then again my database contains 1 million + records.

Remove Letter from Field
This is an unusual problem. My wife downloaded a file from the Virginia State Immunization Registry and wants to upload the file into their electronic medical records. It downloads the file into Excel except it added an A or N in front of the first name. We tried to use the find and replace but the file is so large that it would take forever to get done. We tried the find and replace the A with but it removes all the As including those in the name. I figured that I could import the file into access and then come up with a VBA code to just remove the A or N from the first name either in the current field (FName) or create a second field (FName2). I could then build a query and export to excel. The first name would be correct and ready to upload. Does anyone have any suggested VBA code I could use to accomplish this?

Proper syntax for replace()
When using the replace() function, is it possible to refer to a variables in the find and replace parameters?

Replace(«string», «find», «replace»)

I can use a formula like this, where the replacement text is a table field. Replace([textin],"Find this text string",[cname])

What I want to do is get the text to find, and the variable NAME to use for replacement from a table like this:

txtfind | replacefieldname

"" [customername]
"" [shipaddress]
"" [cnumber]

Imagine a query that would replace a series of placeholders, set up as some text between brackets, with the data from a query or table that contains the three fields shown. It's like a mailmerge function.

I can't figure out the proper syntax that would refer to the txtfind and replacefieldname variables in the replace() function.

This is part of a larger project.

Replace Quotation Marks
So I am trying to use a query to replace quotation marks in EVERY column in a table. Now, I have two problems here.

1.) I think I am looking at replacing each column individually. For example, for a table with 4 columns, I am looking at.
Replace([Table]![Column1], etcetc), then
Replace([Table]![Column2], etcetc), then
Replace([Table]![Column3], etcetc), then
Replace([Table]![Column4], etcetc)

This is NOT a big deal to me (Problem 2 is the bigger problem!

2.) As everyone knows the Replace functions works as such so that:
Replace("alphabet", "bet", "hydro") would return "alphahydro". You are supposed to surround the 2nd function (find) and third function (replacement) with quotation marks.

However, I am trying to replace the quotation marks in the table! And in the 2nd function (find), I am surrounding the quotation mark symbol with quotation marks (so it looks like """) and it is not working!