find and replace

[Guest]

Is there a way to have two text boxes in the form header and in one box will
call
Find
and the other box
Replace

and have a command button to find what I type in the Find box and replace it
with what I type in the replace box?

Thanks for your help
Chey

 

[Steve Schapel]

Chey,

The short answer is Yes. The details depend on *where* you want this
find/replace to happen... To the data in a control on the form? To the
data in a particular field in all records of a table? Or what?

 

[Guest]

I have a form with a query. I have 2 unbound fields in the form header.
The query is called Change PVN. (This query pulls from 3 tables.) I vant
to type in a PVN (Provider Vendor Number) and then type in the replacement
PVN. Then hit GO and it changes all the PVN's in that query.
Thanks for your time
Chey

 

[Steve Schapel]

Chey,

The code in the Click event of your Go button could be like this...

If IsNull(Me.txtFind + Me.txtReplace) Then
MsgBox "Find and Replace values both required"
Else
CurrentDb.Execute "UPDATE [Change PVN] SET PVN = " & Me.txtReplace & _
" WHERE PVN = " & Me.txtFind, dbFailOnError
End If

 

[Steve Schapel]

Chey,

The code I gave you assumes 'Change PVN' is the name of the query, PVN
is the name of the field in the query that you want to change. I used
txtReplace and txtFind, because Find and Replace are Reserved Words, and
should not be used as the name of a field or control. And it looks like
you missed out the _ at then end of the first line of the code.

 

[Guest]

Okay I changed Find a Replace to txtFind and txtReplace. But now it says
that it may refere to more than one. I have 3 fields that need to be
changed. How do I have it change in all three fields.
My fields are
tblChild Care Providers.Vendor Number
tblGrantCompletion.Vendor Number
tbltblChild Care Provider Comments.Vendor Number

Am I able to have all of these change?
Thanks
Chey

Chey,

The code I gave you assumes 'Change PVN' is the name of the query, PVN
is the name of the field in the query that you want to change. I used
txtReplace and txtFind, because Find and Replace are Reserved Words, and
should not be used as the name of a field or control. And it looks like
you missed out the _ at then end of the first line of the code.

--
Steve Schapel, Microsoft Access MVP

CurrentDb.Execute "UPDATE [Change PVN] SET PVN = " & Me.Replace &
" WHERE PVN = " & Me.Find, dbFailOnError
this turns up red when I click on go.
what does set PVN?
I have 3 fields from 3 different table that contain a PVN the fields are
tblChild Care Providers.Vendor Number
tblGrantCompletion.Vendor Number
tbltblChild Care Provider Comments.Vendor Number

all of these or in query Change PVN
I am not use if this is why it was red.
Thanks again for your time.
Chey

 

[Steve Schapel]

Chey,

Do you mean that you have all 3 of these Vendor Number fields in the
query? Are there relationships/joins between the tables based on these
fields? Can you post back with the SQL view of the Change PVN query?
Are these Vendor Number fields the same as the PVN field you mentioned
originally? Are these Vendor Number fields of a text or number data
type? Is the Vendor Number the primay key field in any of these 3
tables? Thanks.

 

[Steve Schapel]

Chey,

In the Relationships window, you can set up the relationships between
these tables, with Cascade Updates enforced. Then, you can just do it
like this...
CurrentDb.Execute "UPDATE [tblChild Care Providers] SET [Vendor
Number] = " & Me.txtReplace & _
" WHERE [Vendor Number] = " & Me.txtFind, dbFailOnError

 

[Steve Schapel]

Chey,

Vendor Number would have to be the primary Key field in tblChild Care
Providers, or at least be Indexed as 'no duplicates'. Is that what you
have? And then you will have a one-to-many relationship between Child
Care Providers and Grant Completion, and another one between Child Care
Providers and Providers Comments (if I understand you correctly, that is!)

 

[Guest]

well I made sure that my primary table-Providers could not have any
duplicates. When I do the one to many it tells me I can't do it. It gives
an example that there is information in my other tables that are not in my
primary table. Is there away around that. I don't want to delete any
information in my other tables. If I have to delete information, what is the
best way to find out what doesent belong?

 

[Steve Schapel]

Chey,

Well, there is a problem here. The Child Care Providers table is sort
of the "master table" of providers, right? And each Provider has a
Vendor Number associated with it, right? And this Vendor Number is
unique to the Provider, right? And the Vendor Number is the basis of
what relates the Provider to both the Grant Completions and to the
Providers Comments, right. So in this case, there should be no records
in the Grant Completion table or the Providers Comments table which do
not have an associated Provider in the Child Care Providers table. In
fact, it makes no sense at all for there to be such records, because you
are unable to identify which Provider they belong to, because the
Provider does not exist in the database. They are "orphan" records.
You might as well delete them, because they are of no use, as far as I
can see.

You can identify these records like this...
- Make a query that includes both the Child Care Providers table and
the Grant Completion table, and join them on the Vendor Number field
from both
- double-click on the join line between the tables and select the
option that says "all records in Grant Completion and matching Child
Care Providers records"
- enter the Vendor Number field from both tables to the query design grid
- in the criteria of the Vendor Number from Child Care Providers,
enter Is Null
- run the query (click the toolbar button with the red [!] icon)
This should identify the Grant Completion records that do not have a
corresponding Provider, so you can delete them, or otherwise decide what
to do.
Repeat the above process with the Providers Comments.

 

[Guest]

Okay so I did everything you said. I did the relationships and they work
great so then on my command button I have
Private Sub Label9_Click()
CurrentDb.Execute "UPDATE [tblChild Care Providers] SET [Vendor Number] = "
& Me.txtReplace & _
" WHERE [Vendor Number] = " & Me.txtFind, dbFailOnError

End Sub

I made sure all my spelling was correct.
It says syntax error in update statement.
I am going to go back and double check again all my spellings. My 2 unbound
fields are exactly what you have. I copied and pasted them.
So on my form I have 2 unbound fields
I type the Old PVN in one and the New PVN in the other. When I hit the
command button is when the error message comes up.
Thanks for your time
Chey

Chey,

Well, there is a problem here. The Child Care Providers table is sort
of the "master table" of providers, right? And each Provider has a
Vendor Number associated with it, right? And this Vendor Number is
unique to the Provider, right? And the Vendor Number is the basis of
what relates the Provider to both the Grant Completions and to the
Providers Comments, right. So in this case, there should be no records
in the Grant Completion table or the Providers Comments table which do
not have an associated Provider in the Child Care Providers table. In
fact, it makes no sense at all for there to be such records, because you
are unable to identify which Provider they belong to, because the
Provider does not exist in the database. They are "orphan" records.
You might as well delete them, because they are of no use, as far as I
can see.

You can identify these records like this...
- Make a query that includes both the Child Care Providers table and
the Grant Completion table, and join them on the Vendor Number field
from both
- double-click on the join line between the tables and select the
option that says "all records in Grant Completion and matching Child
Care Providers records"
- enter the Vendor Number field from both tables to the query design grid
- in the criteria of the Vendor Number from Child Care Providers,
enter Is Null
- run the query (click the toolbar button with the red [!] icon)
This should identify the Grant Completion records that do not have a
corresponding Provider, so you can delete them, or otherwise decide what
to do.
Repeat the above process with the Providers Comments.

--
Steve Schapel, Microsoft Access MVP

well I made sure that my primary table-Providers could not have any
duplicates. When I do the one to many it tells me I can't do it. It gives
an example that there is information in my other tables that are not in my
primary table. Is there away around that. I don't want to delete any
information in my other tables. If I have to delete information, what is the
best way to find out what doesent belong?

 

[Guest]

Cancel my last question I figured it all out. I don't need the find and
replace any more since you showed me how to do the relationships. I can just
fix it in one table and it will update in another.
Thanks for your help.

Chey,

Well, there is a problem here. The Child Care Providers table is sort
of the "master table" of providers, right? And each Provider has a
Vendor Number associated with it, right? And this Vendor Number is
unique to the Provider, right? And the Vendor Number is the basis of
what relates the Provider to both the Grant Completions and to the
Providers Comments, right. So in this case, there should be no records
in the Grant Completion table or the Providers Comments table which do
not have an associated Provider in the Child Care Providers table. In
fact, it makes no sense at all for there to be such records, because you
are unable to identify which Provider they belong to, because the
Provider does not exist in the database. They are "orphan" records.
You might as well delete them, because they are of no use, as far as I
can see.

You can identify these records like this...
- Make a query that includes both the Child Care Providers table and
the Grant Completion table, and join them on the Vendor Number field
from both
- double-click on the join line between the tables and select the
option that says "all records in Grant Completion and matching Child
Care Providers records"
- enter the Vendor Number field from both tables to the query design grid
- in the criteria of the Vendor Number from Child Care Providers,
enter Is Null
- run the query (click the toolbar button with the red [!] icon)
This should identify the Grant Completion records that do not have a
corresponding Provider, so you can delete them, or otherwise decide what
to do.
Repeat the above process with the Providers Comments.

--
Steve Schapel, Microsoft Access MVP

well I made sure that my primary table-Providers could not have any
duplicates. When I do the one to many it tells me I can't do it. It gives
an example that there is information in my other tables that are not in my
primary table. Is there away around that. I don't want to delete any
information in my other tables. If I have to delete information, what is the
best way to find out what doesent belong?