Can you "Find and Replace" programatically?

[James Ivey]

Access Newbie. Not a programmer.

Got a Membership database with 450 members.

Got a field called Membership_Status that can be "Expired", "Active",
"Inactive", "Pending New", "Pending Active", "New Member" and "Deceased"

I would like programatically to:

1. First, go thru the whole database and change all "Expired" to "Inactive"

2. Then go thru the whole database and change all "Active" and "New Member"
to "Expired"

3. Then go thru the whole database and change all "Pending New" to "New
Member"

4. Then go thru the whole database and change all "Pending Active" to
"Active"

I know I can do it all manually using Find and Replace, but can it be done
all at once? I'm guessing this is what modules are for?

James

 

[Guest]

If you have absolutely no life and nothing to do for the next few days, then
"Find and Replace" might be good, but my suggestion would be to use an update
query. Check your Access help file. You'll find info there on how to set one
up. You may need to run a few of them to make all your changes, but it'll
still be approximately ONE MIIIIIIIIILLION times faster than "F & R"

HTH
Good Luck

 

[Albert D. Kallal]

Access Newbie. Not a programmer.

Got a Membership database with 450 members.

Got a field called Membership_Status that can be "Expired", "Active",
"Inactive", "Pending New", "Pending Active", "New Member" and "Deceased"

I would like programatically to:

1. First, go thru the whole database and change all "Expired" to
"Inactive"

I think because you are new to this, I going to suggest that you use the
query builder. I could also post code, but I think you be better using the
query builder.

So, lets use the query builder, to change our status field.

click on the query tab, and create a new query. (choose design view).

when prompted for what table, select your tblMembers.

now, in the menu, go query->update query.

In the first "grid" box, select your Membership_status field from the combo
box.

Field: Membership_status

for the Update to: box, type in

Inactive

now, click on the criteria field

And type in expired.

You can now save this query for future use to run it over and over.

To run this query, simply click on the ! (exclamation on the menu bar).

One thing to keep in mind here. These commands are POWERFUL, and wipe our a
column of data in a flash. so, make a backup (copy) before you play with
this.

again:
make *please* a back up before you try this...

2. Then go thru the whole database and change all "Active" and "New
Member"
to "Expired"

Ok, lets make another query, exactly like the first.

now right below the Criteria: in the query builder, you see there is a "or".
Simply type in your 2nd criteria.

you should see something like:

Field: Membership_status
Table: tblMemebers
Update To: "Expired"
Criteria: "Active"
or: "New Member"

Note that you might have to make the window larger, and note that after the
"first"
"or", you can continue to type additional criteria (so, you not limited to 2
values here).

3. Then go thru the whole database and change all "Pending New" to "New
Member"

again, just create another query...and do the same steps as above...

4. Then go thru the whole database and change all "Pending Active" to
"Active"

Ok, and for this, make another query.

Since your saving each query, then next time, you not have to re-do the
above, but just run 5 queries. (note that just opening a update query will
run it, so, be carefaull again).

I know I can do it all manually using Find and Replace, but can it be done
all at once? I'm guessing this is what modules are for?

Actually, once you get all those query working, then you can actually write
code in a module to run those 5 queries one after anther with the click of a
button.

I think we save that click button code for another post, but it is quite
easy.

good luck.... and if you get the quires working, then feel free to ask how
to use code to run those query...

 

[James Ivey]

Ah.. of course. That makes sense.

So say I create a separate update query for each step.

Can I then somehow run all 4 queries at one time - sequentially?

James

 

[James Ivey]

Albert, thank you very much.

I'm building the queries as we speak... I'll test them and make sure they
work right, and then post a new thread on how to make them all fire at one
go.

Thanks again... I really appreciate the step-by-step.

James

 

[tina]

to run them automatically, you can use a macro or vba procedure. in a macro,
start with the SetWarnings action, setting to False, then an OpenQuery
action for each Update query, and last *but not least* another SetWarnings
action, setting to True.

hth

 

[James Ivey]

Thanks Tina. I appreciate it!

James

to run them automatically, you can use a macro or vba procedure. in a
macro,
start with the SetWarnings action, setting to False, then an OpenQuery
action for each Update query, and last *but not least* another SetWarnings
action, setting to True.

hth

 

[tina]

you're welcome

Thanks Tina. I appreciate it!

James