Need quick way change old field name to new one everywhere ?

[Steve]

Hi,
I had a field name with gaps in, created in my early days ! Never any
problem until now.
I have had need to fill the gaps with _ for it to work in an unbound text
box code to pull in a value from another form...and of course gaps are
unacceptable !
I am going through all my forms, records, queries etc as it now upsets them
all. Will take ages ! Is there a quick way of getting access to look for
all occurences of the old field name and alter it to the new one ?
Cheers
Steve

 

[bhicks11 via AccessMonster.com]

Hi Steve,

You can use text with spaces but you must surround with brackets []. I avoid
because it is cleaner and I use VBA a lot.

Bonnie

http://www.dataplus-svc.com


Here's a blurb out of the Access Help:

Names of fields, controls (control: A graphical user interface object, such
as a text box, check box, scroll bar, or command button, that lets users
control the program. You use controls to display data or choices, perform an
action, or make the user interface easier to read.), and objects in Microsoft
Access:

Can be up to 64 characters long.
Can include any combination of letters, numbers, spaces, and special
characters except a period (.), an exclamation point (!), an accent grave (`),
and brackets ([ ]).
Can't begin with leading spaces.
Can't include control characters (ASCII values 0 through 31).
Can't include a double quotation mark (") in table, view, or stored procedure
(stored procedure: A precompiled collection of SQL statements and optional
control-of-flow statements that is stored under a name and processed as a
unit. The collection is stored in an SQL database and can be run with one
call from an application.) names in a Microsoft Access project (Microsoft
Access project: An Access file that connects to a Microsoft SQL Server
database and is used to create client/server applications. A project file
doesn't contain any data or data-definition-based objects such as tables and
views.).
Although you can include spaces in field, control, and object names, most
examples in the Microsoft Access documentation show field and control names
without spaces because spaces in names can produce naming conflicts in
Microsoft Visual Basic for Applications (Visual Basic for Applications (VBA):
A macro-language version of Microsoft Visual Basic that is used to program
Windows applications and is included with several Microsoft applications.) in
some circumstances.

When you name a field, control, or object, it's a good idea to make sure the
name doesn't duplicate the name of a property (property: A named attribute of
a control, a field, or an object that you set to define one of the object's
characteristics (such as size, color, or screen location) or an aspect of its
behavior (such as whether the object is hidden).) or other element used by
Microsoft Access; otherwise, your database can produce unexpected behavior in
some circumstances. For example, if you refer to the value of a field called
Name in a table NameInfo using the syntax NameInfo.Name, Microsoft Access
displays the value of the table's Name property rather than the value of the
Name field.

Another way to avoid unexpected results is to always use the ! operator
instead of the . (dot) operator to refer to the value of a field, control, or
object. For example, the following identifier explicitly refers to the value
of the Name field rather than the Name property:

[NameInfo]![Name]

 

[Steve]

Hi,
Thats good to know.
However I could do with a way of finding instances of a changed field name
and updating that to the new name across the entire access file as I do find
occurrences of such and all I get is enter parameter value, wish it would
say what I have gone and altered !
I alter a fieldname and need reports and queries and so on to still work,
....dont we all

Steve

Hi Steve,

You can use text with spaces but you must surround with brackets []. I
avoid
because it is cleaner and I use VBA a lot.

Bonnie

http://www.dataplus-svc.com


Here's a blurb out of the Access Help:

Names of fields, controls (control: A graphical user interface object,
such
as a text box, check box, scroll bar, or command button, that lets users
control the program. You use controls to display data or choices, perform
an
action, or make the user interface easier to read.), and objects in
Microsoft
Access:

Can be up to 64 characters long.
Can include any combination of letters, numbers, spaces, and special
characters except a period (.), an exclamation point (!), an accent grave
(`),
and brackets ([ ]).
Can't begin with leading spaces.
Can't include control characters (ASCII values 0 through 31).
Can't include a double quotation mark (") in table, view, or stored
procedure
(stored procedure: A precompiled collection of SQL statements and optional
control-of-flow statements that is stored under a name and processed as a
unit. The collection is stored in an SQL database and can be run with one
call from an application.) names in a Microsoft Access project (Microsoft
Access project: An Access file that connects to a Microsoft SQL Server
database and is used to create client/server applications. A project file
doesn't contain any data or data-definition-based objects such as tables
and
views.).
Although you can include spaces in field, control, and object names, most
examples in the Microsoft Access documentation show field and control
names
without spaces because spaces in names can produce naming conflicts in
Microsoft Visual Basic for Applications (Visual Basic for Applications
(VBA):
A macro-language version of Microsoft Visual Basic that is used to program
Windows applications and is included with several Microsoft applications.)
in
some circumstances.

When you name a field, control, or object, it's a good idea to make sure
the
name doesn't duplicate the name of a property (property: A named attribute
of
a control, a field, or an object that you set to define one of the
object's
characteristics (such as size, color, or screen location) or an aspect of
its
behavior (such as whether the object is hidden).) or other element used by
Microsoft Access; otherwise, your database can produce unexpected behavior
in
some circumstances. For example, if you refer to the value of a field
called
Name in a table NameInfo using the syntax NameInfo.Name, Microsoft Access
displays the value of the table's Name property rather than the value of
the
Name field.

Another way to avoid unexpected results is to always use the ! operator
instead of the . (dot) operator to refer to the value of a field, control,
or
object. For example, the following identifier explicitly refers to the
value
of the Name field rather than the Name property:

[NameInfo]![Name]

Hi,
I had a field name with gaps in, created in my early days ! Never any
problem until now.
I have had need to fill the gaps with _ for it to work in an unbound text
box code to pull in a value from another form...and of course gaps are
unacceptable !
I am going through all my forms, records, queries etc as it now upsets
them
all. Will take ages ! Is there a quick way of getting access to look for
all occurences of the old field name and alter it to the new one ?
Cheers
Steve

 

[Duane Hookom]

Tony Toews has links to several search and replace utilities on his web page
at http://www.granite.ab.ca/access/thirdparty.htm.
--
Duane Hookom
Microsoft Access MVP

Hi,
Thats good to know.
However I could do with a way of finding instances of a changed field name
and updating that to the new name across the entire access file as I do find
occurrences of such and all I get is enter parameter value, wish it would
say what I have gone and altered !
I alter a fieldname and need reports and queries and so on to still work,
....dont we all

Steve

Hi Steve,

You can use text with spaces but you must surround with brackets []. I
avoid
because it is cleaner and I use VBA a lot.

Bonnie

http://www.dataplus-svc.com


Here's a blurb out of the Access Help:

Names of fields, controls (control: A graphical user interface object,
such
as a text box, check box, scroll bar, or command button, that lets users
control the program. You use controls to display data or choices, perform
an
action, or make the user interface easier to read.), and objects in
Microsoft
Access:

Can be up to 64 characters long.
Can include any combination of letters, numbers, spaces, and special
characters except a period (.), an exclamation point (!), an accent grave
(`),
and brackets ([ ]).
Can't begin with leading spaces.
Can't include control characters (ASCII values 0 through 31).
Can't include a double quotation mark (") in table, view, or stored
procedure
(stored procedure: A precompiled collection of SQL statements and optional
control-of-flow statements that is stored under a name and processed as a
unit. The collection is stored in an SQL database and can be run with one
call from an application.) names in a Microsoft Access project (Microsoft
Access project: An Access file that connects to a Microsoft SQL Server
database and is used to create client/server applications. A project file
doesn't contain any data or data-definition-based objects such as tables
and
views.).
Although you can include spaces in field, control, and object names, most
examples in the Microsoft Access documentation show field and control
names
without spaces because spaces in names can produce naming conflicts in
Microsoft Visual Basic for Applications (Visual Basic for Applications
(VBA):
A macro-language version of Microsoft Visual Basic that is used to program
Windows applications and is included with several Microsoft applications.)
in
some circumstances.

When you name a field, control, or object, it's a good idea to make sure
the
name doesn't duplicate the name of a property (property: A named attribute
of
a control, a field, or an object that you set to define one of the
object's
characteristics (such as size, color, or screen location) or an aspect of
its
behavior (such as whether the object is hidden).) or other element used by
Microsoft Access; otherwise, your database can produce unexpected behavior
in
some circumstances. For example, if you refer to the value of a field
called
Name in a table NameInfo using the syntax NameInfo.Name, Microsoft Access
displays the value of the table's Name property rather than the value of
the
Name field.

Another way to avoid unexpected results is to always use the ! operator
instead of the . (dot) operator to refer to the value of a field, control,
or
object. For example, the following identifier explicitly refers to the
value
of the Name field rather than the Name property:

[NameInfo]![Name]

Hi,
I had a field name with gaps in, created in my early days ! Never any
problem until now.
I have had need to fill the gaps with _ for it to work in an unbound text
box code to pull in a value from another form...and of course gaps are
unacceptable !
I am going through all my forms, records, queries etc as it now upsets
them
all. Will take ages ! Is there a quick way of getting access to look for
all occurences of the old field name and alter it to the new one ?
Cheers
Steve

 

[Klatuu]

Here is a link to a favorite of mine:
http://www.rickworld.com/download.html


--
Dave Hargis, Microsoft Access MVP

Hi,
Thats good to know.
However I could do with a way of finding instances of a changed field name
and updating that to the new name across the entire access file as I do find
occurrences of such and all I get is enter parameter value, wish it would
say what I have gone and altered !
I alter a fieldname and need reports and queries and so on to still work,
....dont we all

Steve

Hi Steve,

You can use text with spaces but you must surround with brackets []. I
avoid
because it is cleaner and I use VBA a lot.

Bonnie

http://www.dataplus-svc.com


Here's a blurb out of the Access Help:

Names of fields, controls (control: A graphical user interface object,
such
as a text box, check box, scroll bar, or command button, that lets users
control the program. You use controls to display data or choices, perform
an
action, or make the user interface easier to read.), and objects in
Microsoft
Access:

Can be up to 64 characters long.
Can include any combination of letters, numbers, spaces, and special
characters except a period (.), an exclamation point (!), an accent grave
(`),
and brackets ([ ]).
Can't begin with leading spaces.
Can't include control characters (ASCII values 0 through 31).
Can't include a double quotation mark (") in table, view, or stored
procedure
(stored procedure: A precompiled collection of SQL statements and optional
control-of-flow statements that is stored under a name and processed as a
unit. The collection is stored in an SQL database and can be run with one
call from an application.) names in a Microsoft Access project (Microsoft
Access project: An Access file that connects to a Microsoft SQL Server
database and is used to create client/server applications. A project file
doesn't contain any data or data-definition-based objects such as tables
and
views.).
Although you can include spaces in field, control, and object names, most
examples in the Microsoft Access documentation show field and control
names
without spaces because spaces in names can produce naming conflicts in
Microsoft Visual Basic for Applications (Visual Basic for Applications
(VBA):
A macro-language version of Microsoft Visual Basic that is used to program
Windows applications and is included with several Microsoft applications.)
in
some circumstances.

When you name a field, control, or object, it's a good idea to make sure
the
name doesn't duplicate the name of a property (property: A named attribute
of
a control, a field, or an object that you set to define one of the
object's
characteristics (such as size, color, or screen location) or an aspect of
its
behavior (such as whether the object is hidden).) or other element used by
Microsoft Access; otherwise, your database can produce unexpected behavior
in
some circumstances. For example, if you refer to the value of a field
called
Name in a table NameInfo using the syntax NameInfo.Name, Microsoft Access
displays the value of the table's Name property rather than the value of
the
Name field.

Another way to avoid unexpected results is to always use the ! operator
instead of the . (dot) operator to refer to the value of a field, control,
or
object. For example, the following identifier explicitly refers to the
value
of the Name field rather than the Name property:

[NameInfo]![Name]

Hi,
I had a field name with gaps in, created in my early days ! Never any
problem until now.
I have had need to fill the gaps with _ for it to work in an unbound text
box code to pull in a value from another form...and of course gaps are
unacceptable !
I am going through all my forms, records, queries etc as it now upsets
them
all. Will take ages ! Is there a quick way of getting access to look for
all occurences of the old field name and alter it to the new one ?
Cheers
Steve

 

[Steve]

Thanks Dave and Duane.

one persons review has:-

ReplaceWiz:


1. Ability to specify the objects to search in (forms, reports.).
2. Install and un-install feature.
3. Case sensitive search.
4. Different search options (Any Part of Text, Start of Word, End of
Word, Whole Word, Start of Line, End of Line, Whole Line, Pattern
(WildCard ) Find Only).
5. Ability to chose Selection of objects.
6. Ability to chose selection of properties.
7. Ability to Export, Print and view search results.
8. Ability to utilise system resources
9. Help file.
10. Price $ 45.


Find and Replace:


1. Ability to specify the objects to search in(forms, reports .).
2. No install and un-install facility.
3. Case sensitive search.
4. No different search options.
5. Ability to chose selection of objects???
6. Ability to chose selection of properties???
7. Ability to log find/changes
8. Fast search option
9. Help file.
10. Price $29.


Find and replace has one form or interface which is handy and you can
confirm each find and replace , ReplaceWiz is wizard like (4~5 Pages)
and searches all the objects first then you can chose which to replace
and which to keep.



Not sure what No install and uninstall facility means !.



no uninstall perhaps, but no install !

as for the ????

What is your experience Dave of #2 #5 #6 for FindAndReplace ?



I see there is also a freebie.
http://www3.bc.sympatico.ca/starthere/findandreplace/



I have simple needs, I change a field name and want to update all references
to it with the new name, and ensure all forms, macros, reports, queries run.

For the moment, thats the occasional need...however it would be great to
have the folowing function...:-

I also could do with knowing...which of these will analyse a dbase and let
me know why I get Enter Parameter Value when I thought all was well ?

or why I get #name? in an unbound text window which I had hoped would
display a value from a related form.

Cheers

Steve

Here is a link to a favorite of mine:
http://www.rickworld.com/download.html


--
Dave Hargis, Microsoft Access MVP

Hi,
Thats good to know.
However I could do with a way of finding instances of a changed field
name
and updating that to the new name across the entire access file as I do
find
occurrences of such and all I get is enter parameter value, wish it would
say what I have gone and altered !
I alter a fieldname and need reports and queries and so on to still work,
....dont we all

Steve

Hi Steve,

You can use text with spaces but you must surround with brackets []. I
avoid
because it is cleaner and I use VBA a lot.

Bonnie

http://www.dataplus-svc.com


Here's a blurb out of the Access Help:

Names of fields, controls (control: A graphical user interface object,
such
as a text box, check box, scroll bar, or command button, that lets
users
control the program. You use controls to display data or choices,
perform
an
action, or make the user interface easier to read.), and objects in
Microsoft
Access:

Can be up to 64 characters long.
Can include any combination of letters, numbers, spaces, and special
characters except a period (.), an exclamation point (!), an accent
grave
(`),
and brackets ([ ]).
Can't begin with leading spaces.
Can't include control characters (ASCII values 0 through 31).
Can't include a double quotation mark (") in table, view, or stored
procedure
(stored procedure: A precompiled collection of SQL statements and
optional
control-of-flow statements that is stored under a name and processed as
a
unit. The collection is stored in an SQL database and can be run with
one
call from an application.) names in a Microsoft Access project
(Microsoft
Access project: An Access file that connects to a Microsoft SQL Server
database and is used to create client/server applications. A project
file
doesn't contain any data or data-definition-based objects such as
tables
and
views.).
Although you can include spaces in field, control, and object names,
most
examples in the Microsoft Access documentation show field and control
names
without spaces because spaces in names can produce naming conflicts in
Microsoft Visual Basic for Applications (Visual Basic for Applications
(VBA):
A macro-language version of Microsoft Visual Basic that is used to
program
Windows applications and is included with several Microsoft
applications.)
in
some circumstances.

When you name a field, control, or object, it's a good idea to make
sure
the
name doesn't duplicate the name of a property (property: A named
attribute
of
a control, a field, or an object that you set to define one of the
object's
characteristics (such as size, color, or screen location) or an aspect
of
its
behavior (such as whether the object is hidden).) or other element used
by
Microsoft Access; otherwise, your database can produce unexpected
behavior
in
some circumstances. For example, if you refer to the value of a field
called
Name in a table NameInfo using the syntax NameInfo.Name, Microsoft
Access
displays the value of the table's Name property rather than the value
of
the
Name field.

Another way to avoid unexpected results is to always use the ! operator
instead of the . (dot) operator to refer to the value of a field,
control,
or
object. For example, the following identifier explicitly refers to the
value
of the Name field rather than the Name property:

[NameInfo]![Name]



Steve wrote:
Hi,
I had a field name with gaps in, created in my early days ! Never any
problem until now.
I have had need to fill the gaps with _ for it to work in an unbound
text
box code to pull in a value from another form...and of course gaps are
unacceptable !
I am going through all my forms, records, queries etc as it now upsets
them
all. Will take ages ! Is there a quick way of getting access to look
for
all occurences of the old field name and alter it to the new one ?
Cheers
Steve

 

[Klatuu]

2. Not true. One of the files included when you unzip the download is
replace9.hlp
It provides detailed instructions on installing and uninstalling.

5. Yes. You can select by type of object and you can use select specific
objects or use wild cards to select a group of objects by name.

6. Yes. Not specific by every possible property, but I have not had any
problems with it.

It also includes a cross reference capability so you can find unused objects
and create a "where used" list that can also be filtered by object type.

You can also create a list of search values, you don't have to search just
one value per search. It does not have the ability to use wild card
searches, but with the List capability, I have not found that to be a problem.

I have been using this utility for about 2 years without any problems at all.
--
Dave Hargis, Microsoft Access MVP

Thanks Dave and Duane.

one persons review has:-

ReplaceWiz:


1. Ability to specify the objects to search in (forms, reports.).
2. Install and un-install feature.
3. Case sensitive search.
4. Different search options (Any Part of Text, Start of Word, End of
Word, Whole Word, Start of Line, End of Line, Whole Line, Pattern
(WildCard ) Find Only).
5. Ability to chose Selection of objects.
6. Ability to chose selection of properties.
7. Ability to Export, Print and view search results.
8. Ability to utilise system resources
9. Help file.
10. Price $ 45.


Find and Replace:


1. Ability to specify the objects to search in(forms, reports .).
2. No install and un-install facility.
3. Case sensitive search.
4. No different search options.
5. Ability to chose selection of objects???
6. Ability to chose selection of properties???
7. Ability to log find/changes
8. Fast search option
9. Help file.
10. Price $29.


Find and replace has one form or interface which is handy and you can
confirm each find and replace , ReplaceWiz is wizard like (4~5 Pages)
and searches all the objects first then you can chose which to replace
and which to keep.



Not sure what No install and uninstall facility means !.



no uninstall perhaps, but no install !

as for the ????

What is your experience Dave of #2 #5 #6 for FindAndReplace ?



I see there is also a freebie.
http://www3.bc.sympatico.ca/starthere/findandreplace/



I have simple needs, I change a field name and want to update all references
to it with the new name, and ensure all forms, macros, reports, queries run.

For the moment, thats the occasional need...however it would be great to
have the folowing function...:-

I also could do with knowing...which of these will analyse a dbase and let
me know why I get Enter Parameter Value when I thought all was well ?

or why I get #name? in an unbound text window which I had hoped would
display a value from a related form.

Cheers

Steve

Here is a link to a favorite of mine:
http://www.rickworld.com/download.html


--
Dave Hargis, Microsoft Access MVP

Hi,
Thats good to know.
However I could do with a way of finding instances of a changed field
name
and updating that to the new name across the entire access file as I do
find
occurrences of such and all I get is enter parameter value, wish it would
say what I have gone and altered !
I alter a fieldname and need reports and queries and so on to still work,
....dont we all

Steve


Hi Steve,

You can use text with spaces but you must surround with brackets []. I
avoid
because it is cleaner and I use VBA a lot.

Bonnie

http://www.dataplus-svc.com


Here's a blurb out of the Access Help:

Names of fields, controls (control: A graphical user interface object,
such
as a text box, check box, scroll bar, or command button, that lets
users
control the program. You use controls to display data or choices,
perform
an
action, or make the user interface easier to read.), and objects in
Microsoft
Access:

Can be up to 64 characters long.
Can include any combination of letters, numbers, spaces, and special
characters except a period (.), an exclamation point (!), an accent
grave
(`),
and brackets ([ ]).
Can't begin with leading spaces.
Can't include control characters (ASCII values 0 through 31).
Can't include a double quotation mark (") in table, view, or stored
procedure
(stored procedure: A precompiled collection of SQL statements and
optional
control-of-flow statements that is stored under a name and processed as
a
unit. The collection is stored in an SQL database and can be run with
one
call from an application.) names in a Microsoft Access project
(Microsoft
Access project: An Access file that connects to a Microsoft SQL Server
database and is used to create client/server applications. A project
file
doesn't contain any data or data-definition-based objects such as
tables
and
views.).
Although you can include spaces in field, control, and object names,
most
examples in the Microsoft Access documentation show field and control
names
without spaces because spaces in names can produce naming conflicts in
Microsoft Visual Basic for Applications (Visual Basic for Applications
(VBA):
A macro-language version of Microsoft Visual Basic that is used to
program
Windows applications and is included with several Microsoft
applications.)
in
some circumstances.

When you name a field, control, or object, it's a good idea to make
sure
the
name doesn't duplicate the name of a property (property: A named
attribute
of
a control, a field, or an object that you set to define one of the
object's
characteristics (such as size, color, or screen location) or an aspect
of
its
behavior (such as whether the object is hidden).) or other element used
by
Microsoft Access; otherwise, your database can produce unexpected
behavior
in
some circumstances. For example, if you refer to the value of a field
called
Name in a table NameInfo using the syntax NameInfo.Name, Microsoft
Access
displays the value of the table's Name property rather than the value
of
the
Name field.

Another way to avoid unexpected results is to always use the ! operator
instead of the . (dot) operator to refer to the value of a field,
control,
or
object. For example, the following identifier explicitly refers to the
value
of the Name field rather than the Name property:

[NameInfo]![Name]



Steve wrote:
Hi,
I had a field name with gaps in, created in my early days ! Never any
problem until now.
I have had need to fill the gaps with _ for it to work in an unbound
text
box code to pull in a value from another form...and of course gaps are
unacceptable !
I am going through all my forms, records, queries etc as it now upsets
them
all. Will take ages ! Is there a quick way of getting access to look
for
all occurences of the old field name and alter it to the new one ?
Cheers
Steve