alter function

Hi, using SQL2005. I want to replace a function and can't because the 
function is being referenced by an object. I have tried to drop it first and 
then create the function. This has the same error result. 

Is there a way to ignore any referenced object and drop a function (and then 
re-create it).  Alternatively any way to ignore referenced object and alter a 
function.

Any ideas or recommendations appreciated :-)

Many thanks,
Jonathan
0
Utf
3/15/2010 1:11:01 AM
sqlserver.programming 1873 articles. 0 followers. Follow

6 Replies
836 Views

Similar Articles

[PageSpeed] 21

Hi
Havv you created a UDF with SCHEMA BINDING  option?

"Jonathan" <Jonathan@discussions.microsoft.com> wrote in message 
news:FE620C93-73AB-46A4-8ECF-0D139C64D60C@microsoft.com...
> Hi, using SQL2005. I want to replace a function and can't because the
> function is being referenced by an object. I have tried to drop it first 
> and
> then create the function. This has the same error result.
>
> Is there a way to ignore any referenced object and drop a function (and 
> then
> re-create it).  Alternatively any way to ignore referenced object and 
> alter a
> function.
>
> Any ideas or recommendations appreciated :-)
>
> Many thanks,
> Jonathan 


0
Uri
3/15/2010 7:31:05 AM
Jonathan (Jonathan@discussions.microsoft.com) writes:
> Hi, using SQL2005. I want to replace a function and can't because the 
> function is being referenced by an object. I have tried to drop it first
> and then create the function. This has the same error result. 
> 
> Is there a way to ignore any referenced object and drop a function (and
> then re-create it).  Alternatively any way to ignore referenced object
> and alter a function. 
 
You don't say what the other object is, which is worrisome, because that 
indicates that you don't care about the database integrity.

Let's say that the function is referred to by a persisted (or indexed)
column. If you were permitted to change the function, the values stored
with the table would become invalid. Say that you have:

    compcol AS dbo.yourfunc(othercol) AS PERSISTED

Currently, yourfunc(8) returns 16, and you now change it so that it returns
17. The stored values would now be incorrect. That is of course entirely
impermissible.

So if you need to change the function in this example, you first need to
drop the column that refers to it.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
0
Erland
3/15/2010 9:00:28 AM
Hi Erland,

I have a script that is transfering database objects to another database. 
That is, dropping views, functions and stored procedures and then creating 
replacements. I can accept that I am probably doing this incorrectly. I guess 
I naively thought that a script that steps through the database objects and 
use objScript  FROM [dbo].[tblObjects]; your recommendations for a better 
method are appreciated.

Regarding the specific function that cannot be deleted, I want to update the 
function in another database. It is used by calculated columns in a couple of 
tables. To drop these columns my script would have to identify them and I 
don't know how to do this. I'm trying to learn as fast as I can. But for now 
my required knowledge and skills seem to be a moving target...

Many thanks,
Jonathan

"Erland Sommarskog" wrote:

> Jonathan (Jonathan@discussions.microsoft.com) writes:
> > Hi, using SQL2005. I want to replace a function and can't because the 
> > function is being referenced by an object. I have tried to drop it first
> > and then create the function. This has the same error result. 
> > 
> > Is there a way to ignore any referenced object and drop a function (and
> > then re-create it).  Alternatively any way to ignore referenced object
> > and alter a function. 
>  
> You don't say what the other object is, which is worrisome, because that 
> indicates that you don't care about the database integrity.
> 
> Let's say that the function is referred to by a persisted (or indexed)
> column. If you were permitted to change the function, the values stored
> with the table would become invalid. Say that you have:
> 
>     compcol AS dbo.yourfunc(othercol) AS PERSISTED
> 
> Currently, yourfunc(8) returns 16, and you now change it so that it returns
> 17. The stored values would now be incorrect. That is of course entirely
> impermissible.
> 
> So if you need to change the function in this example, you first need to
> drop the column that refers to it.
> 
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> .
> 
0
Utf
3/15/2010 7:51:01 PM
Try reading:

How to: View SQL Dependencies (SQL Server Management Studio)
http://msdn.microsoft.com/en-us/library/bb630261.aspx

-- 
Jay Konigsberg
SQL Server DBA in Sacramento, CA
http://www.linkedin.com/in/jaykonigsberg

Live in Sacramento, CA?
Join the Sacramento SQL Server User Group on LinkedIn
http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg



"Jonathan" <Jonathan@discussions.microsoft.com> wrote in message 
news:CA0EC2D0-FDE1-4456-BDC6-FD4A5434CBAF@microsoft.com...
> Hi Erland,
>
> I have a script that is transfering database objects to another database.
> That is, dropping views, functions and stored procedures and then creating
> replacements. I can accept that I am probably doing this incorrectly. I 
> guess
> I naively thought that a script that steps through the database objects 
> and
> use objScript  FROM [dbo].[tblObjects]; your recommendations for a better
> method are appreciated.
>
> Regarding the specific function that cannot be deleted, I want to update 
> the
> function in another database. It is used by calculated columns in a couple 
> of
> tables. To drop these columns my script would have to identify them and I
> don't know how to do this. I'm trying to learn as fast as I can. But for 
> now
> my required knowledge and skills seem to be a moving target...
>
> Many thanks,
> Jonathan
>
> "Erland Sommarskog" wrote:
>
>> Jonathan (Jonathan@discussions.microsoft.com) writes:
>> > Hi, using SQL2005. I want to replace a function and can't because the
>> > function is being referenced by an object. I have tried to drop it 
>> > first
>> > and then create the function. This has the same error result.
>> >
>> > Is there a way to ignore any referenced object and drop a function (and
>> > then re-create it).  Alternatively any way to ignore referenced object
>> > and alter a function.
>>
>> You don't say what the other object is, which is worrisome, because that
>> indicates that you don't care about the database integrity.
>>
>> Let's say that the function is referred to by a persisted (or indexed)
>> column. If you were permitted to change the function, the values stored
>> with the table would become invalid. Say that you have:
>>
>>     compcol AS dbo.yourfunc(othercol) AS PERSISTED
>>
>> Currently, yourfunc(8) returns 16, and you now change it so that it 
>> returns
>> 17. The stored values would now be incorrect. That is of course entirely
>> impermissible.
>>
>> So if you need to change the function in this example, you first need to
>> drop the column that refers to it.
>>
>> -- 
>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>> .
>> 


0
Jay
3/15/2010 8:27:37 PM
Jonathan (Jonathan@discussions.microsoft.com) writes:
> I have a script that is transfering database objects to another
> database. That is, dropping views, functions and stored procedures and
> then creating replacements. I can accept that I am probably doing this
> incorrectly. I guess I naively thought that a script that steps through
> the database objects and use objScript  FROM [dbo].[tblObjects]; your
> recommendations for a better method are appreciated. 

You can use the Generate Scripts feature in Mgmt Studio, although I'm
not entirely convinced of its robustness.

Personally, I prefer to have my code under version control, and update
the database from scripts, which partly are generated, and partly
hand-crafted.

Many people use SQL Compare from Red Gate and are very happy with that.

> Regarding the specific function that cannot be deleted, I want to update
> the function in another database. It is used by calculated columns in a
> couple of tables. To drop these columns my script would have to identify
> them and I don't know how to do this. I'm trying to learn as fast as I
> can. But for now my required knowledge and skills seem to be a moving
> target... 
 
I would say that this is quite an advanced scenario, and I am a little
curious whether a tool like Red Gate's actually handles this. One problem
is that it is not entirely obvious what should happen. Do you want all
columns to be recalculated? Well, if you do not, you probably should
not change the function...

This query will list all computed columns that depends on your 
function, and which of them that are persisted:


   select o.name, o2.name, c.name, cc.is_persisted
   from   sys.objects o
   join   sys.sql_dependencies d on o.object_id = d.object_id
   join   sys.objects o2 on d.referenced_major_id = o2.object_id
   join   sys.columns c on d.column_id = c.column_id
                       and d.object_id = c.object_id
   left join   sys.computed_columns cc on c.object_id = cc.object_id
                                      and c.column_id = cc.column_id
   where  o2.name = 'yourfunc'  

However, the query will probably display a column that is only indexed,
but not persisted as non-persisted, and such a column will also prevent
the function from being altered.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
3/15/2010 10:43:00 PM
Hi Erland,

I will investigate your suggestions and then I am sure that I will have 
further questions.

many thanks,
Jonathan

"Erland Sommarskog" wrote:

> Jonathan (Jonathan@discussions.microsoft.com) writes:
> > I have a script that is transfering database objects to another
> > database. That is, dropping views, functions and stored procedures and
> > then creating replacements. I can accept that I am probably doing this
> > incorrectly. I guess I naively thought that a script that steps through
> > the database objects and use objScript  FROM [dbo].[tblObjects]; your
> > recommendations for a better method are appreciated. 
> 
> You can use the Generate Scripts feature in Mgmt Studio, although I'm
> not entirely convinced of its robustness.
> 
> Personally, I prefer to have my code under version control, and update
> the database from scripts, which partly are generated, and partly
> hand-crafted.
> 
> Many people use SQL Compare from Red Gate and are very happy with that.
> 
> > Regarding the specific function that cannot be deleted, I want to update
> > the function in another database. It is used by calculated columns in a
> > couple of tables. To drop these columns my script would have to identify
> > them and I don't know how to do this. I'm trying to learn as fast as I
> > can. But for now my required knowledge and skills seem to be a moving
> > target... 
>  
> I would say that this is quite an advanced scenario, and I am a little
> curious whether a tool like Red Gate's actually handles this. One problem
> is that it is not entirely obvious what should happen. Do you want all
> columns to be recalculated? Well, if you do not, you probably should
> not change the function...
> 
> This query will list all computed columns that depends on your 
> function, and which of them that are persisted:
> 
> 
>    select o.name, o2.name, c.name, cc.is_persisted
>    from   sys.objects o
>    join   sys.sql_dependencies d on o.object_id = d.object_id
>    join   sys.objects o2 on d.referenced_major_id = o2.object_id
>    join   sys.columns c on d.column_id = c.column_id
>                        and d.object_id = c.object_id
>    left join   sys.computed_columns cc on c.object_id = cc.object_id
>                                       and c.column_id = cc.column_id
>    where  o2.name = 'yourfunc'  
> 
> However, the query will probably display a column that is only indexed,
> but not persisted as non-persisted, and such a column will also prevent
> the function from being altered.
> 
> 
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 
> .
> 
0
Utf
3/16/2010 2:18:01 AM
Reply:

Similar Artilces:

Blank Out Function Totals
I have the following function in cell E7 in a spreadsheet: =D7-C7 Now, if there is no data in D7 and C7, then 00:00 appears in cell E7 as it has no data to work against. What I want to do is to blank out the cells with functions in them so that they appear empty, unless initial data is entered. So in the above case, I don't want anything to appear in cell E7 until cells D7 and C7 have been populated. Any ideas? Tools>Options>View, uncheck Zero values -- Kind regards, Niek Otten "Neil Greenough" <scrivomc@hotmail.com> wrote in message news:mFiif.82137$...

VB UML model from function
Hy How can I build an uml model from a VB 6 function? Thanx 4 any help There is no support to create a UML model using a VB6 function It can however reverse-engineer a VB project into a UML diagram -- -ronnie [MS] This posting is provided "AS IS" with no warranties, and confers no rights. "Papp Gy�rgy" <pappgyorgy@digicart.hu> wrote in message news:eEG6bJQeDHA.1716@TK2MSFTNGP10.phx.gbl... > Hy > > How can I build an uml model from a VB 6 function? > > Thanx 4 any help > > ...

Calling a function in excel
I created a function in the Visual Basic Editor in Excel but am unable to call it from an individual cell in Excel. I am using "=function name(argument1, argumaent2, argument3)" when I try to call it. I am getting the error "$NAME?". Any ideas? Hi The function should be i a standard module (insert menu), not in a worksheet module or anywhere else. And it must not be Private as in "Private Function XYZ() as Long" And you are of course entering it without the quotes ? -- HTH. Best wishes Harald Followup to newsgroup only please "Tom" <tfreem1...

Copy/Paste Functions
I use 2 computers (1 running Vista SP2, the other running XP SP3) from home to remotely connect to my work server. I use a Citrix Presentation Server Client to accomplish this. Once connected, I run several programs including MS Office 2003. The XP machine is fine, but the Vista machine gives me fits. The main problem is that I cannot copy/paste on the Vista machine from the Citrix server app I am using to Word-within-Citrix. No matter how many times I press Print Screen, it acts as it there is nothing available to paste and the Paste function on the Edit menu is grayed out. In fact, I...

If Function Limitation
How can one have more than 7 If Functions nested, I have the need to create more than 7 If Functions, how can I go around this problem, I consulted the Help Section and it states: "Up to seven IF functions can be nested as value_if_true and value_if_false arguments" Your help is most appreciated! In practically every instance I run across where you need more than 7 IF statements, the problem can be solved by constructing a Lookup table of values to be matched and their corresponding return value. Example: Col_A Col_B Apple 1 Berry 100 Cat 11 Date 21 Eel 35 Fruit 106 Grate ...

Altering font sizes
I am currently trying to resize the font throughout the whole of a Publisher document, but am having significant trouble acheiving it. Due to the nature of the document, it has well over 50 separate text boxes. I discovered recently that the document (a diagram) would have to be extended, so was intending to shrink it all slightly to fit in the extra parts. Having selected it all (Ctrl-A), grouped it, and shrinking it slightly, the text no longer fits within many of the text boxes. When i went to change the font size, all the font parts within the formatting toolbar were grayed out, a...

NBText function
Dear all I have NBTEXT function for the first time and can't get it to work. When I have entered the formula and press Enter, the cell goes completely blank??? All I want to do is convert a number into text format. I have downloaded the add-in function but this crashes my laptop every time I use it. Am I doing something wrong?? Thanks. Louise What add-in is this function in? -- Jim Rech Excel MVP the add-in i was told to use in a previous posting is morefunc.dll. Thanks for any help you can offer. >-----Original Message----- >What add-in is this function in? >...

Altering Cursors
Hello, I have some toolbar buttons in my dialog DLL app and two of them are zoom-in and zoom-out. Is there an MFC class like CWaitCursor that can display a zoom-in/zoom-out cursor? Thank you so much. Victor. You will have to change the cursor yourself. Here is an example of the how to change cursors, load the cursor in the m_CursorHand variable: (It's a comment for how to solve a problem of another problem, but it does explain changing cursors nicely) http://www.codeguru.com/Cpp/controls/buttonctrl/comments.php/c2069/?thread=26628 AliR. "victorsk" <victorsk@discussio...

MAX/MIN functions on cells formatted as Percent.
Hello, I am able to use MAX/MIN functions on cells formatted as Numbers, however not on cells formatted as Percent. How can "some" function be used to convert Percent to Numbers so that MAX/MIN function can be used ? Peter No difference, percentage = numbers unless you use text, if so copy an empty cell, select the percentage and do edit>paste special and add That will convert it as well, otherwise format as percentage -- Regards, Peo Sjoblom "Swiatkowski Peter" <swiadek@comcast.net> wrote in message news:y5ednWMaV_pXuCnenZ2dnUVZ_sSdnZ2d@comcast.com......

Function Help Needed
Greetings; I have a spreadsheet that I have put a check box on. If the box is checked (TRUE), I want an function to be performed in another cell. If the box is unchecked (FALSE), I want the value of the cell to just either be blank or zero. I used the function dialog box and it took with no errors, but nothing happens when I check the box. If I type the word 'TRUE' or 'FALSE' in the cell, the function works just fine. However, I want to use the check box. How do I go about doing this? Many TIA! James Have you linked the check box to a cell where you have ma...

message date/time altered when copying from public folder
I have noticed that if we copy mail messages from public folders to users own mailboxes, the time is off by a few minutes to a few hours. If a message arrives late at night, the copied mail may even have the next day's date. We have mail-enabled public folders, and users copy mail to their inboxes from there, but this date/time change is bothering. Do the headers contain different time stamps, and does Outlook read them differently to the Public folders? Any ideas? Jo On Sun, 1 Oct 2006 15:31:18 +0900, "Jo" <gsc@gol.com> wrote: >I have noticed that if we copy mail...

Relative reference within INDIRECT function
Hi, Gaurav Raveshia, Jim Thomlinson and Dave Peterson (Thank you!) helped me to use the INDIRECT function in order to use the contents of a cell as part of a reference to another cell: =INDIRECT("'" & A2 & "'!C7") where A2 contains the name of another worksheet and C7 is the cell on that worksheet that I would like to reference. The problem is that the main reason for needing this was to create a summary worksheet simply by copying and pasting the formula. However, although the A2 will change correctly to B2 when copied to the next column, C7 does not. Ho...

Printer Driver Function
This is a multi-part message in MIME format. ------=_NextPart_000_0052_01C3EAAB.46F4EE70 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello I want to create a "print driver function" for print-to-email For eg.=20 I create a document in Excel, or Lotus 123 or any other program which = has the ability to print. I preview it to make sure it is OK. I select = as a printer: "print to email".=20 =20 Then properties button pops up and when I press it I have a choice of = file formats, dpi and compression. The idea is to p...

alter function
Hi, using SQL2005. I want to replace a function and can't because the function is being referenced by an object. I have tried to drop it first and then create the function. This has the same error result. Is there a way to ignore any referenced object and drop a function (and then re-create it). Alternatively any way to ignore referenced object and alter a function. Any ideas or recommendations appreciated :-) Many thanks, Jonathan Hi Havv you created a UDF with SCHEMA BINDING option? "Jonathan" <Jonathan@discussions.microsoft.com> wrote in messag...

excel function to give the area of a circle?
I'm working my way through learning to build spreadsheets. How would I create the function to give the area of a circle. I know the formula A=PI*r(squared) but don' know the excel functions. I tried "=(PI)*(Power('diameter cell'/2),2)" but that wouldn't work. With the radius in A1: =PI()*A1^2 Or, with the diameter in A1: =PI()*(A1/2)^2 -- Kind regards, Niek Otten Microsoft MVP - Excel "bhoward425" <bhoward425@discussions.microsoft.com> wrote in message news:563DF18C-62D9-4B08-A529-7ADF00353088@microsoft.com... > I...

A mix of functions ?
I have a function on my spreadsheet that I use in two places to give a number, what I now want is to then get a return of the maximum value (I then will want a similar version that returns the minimum value). These are the two functions I am trying to work with: e.g. =VLOOKUP(B2,'Tn05-01_18-06'!$A$1:$B$171,2,FALSE) and =VLOOKUP(B2,'Tn05-01_06-18'!$A$1:$B$171,2,FALSE) (there are subtle differences between the two) or =2&(D2<0)+1-1&RIGHT("000"&ABS(D2)*10,3) and =2&(F2<0)+1-1&RIGHT("000"&ABS(F2)*10,3) (again subtle differences)...

Excel Function Help
Hi All I have names in column A When placing mouse over the name it reveals a mailto:emailaddress@email.com I need the formula to reveal these email address in another column. I have tried =Hyperlink (A1) =Address (A1) =GetAddress (A1) Please help Mike Ippolito See here: http://www.ozgrid.com/VBA/HyperlinkAddress.htm On 28/12/09 8:37 AM, in article C75D3DD7.13B7%mike@themlmheadhunter.com, "Mike Ippolito" <mike@themlmheadhunter.com> wrote: > Hi All > > I have names in column A When placing mouse over the name it reveals a > ma...

Altered meeting always becomes recurring
We're on an Outlook 2007 / Exchange 2008 setup. Here's what happens: 1. Person sends a New Meeting Request for, say, Monday at 9 AM. Everything works properly and the entry appears as it should on the calendar. 2. Person later decides to change the meeting to, say, Tuesday at 10 AM. So they open the original entry on the calendar (from step #1 above) and changes the date to Tuesday and the time to 10 AM, then sends the update. 3. The meeting appears in the new location on the calendar OK but it has been changed into a recurring event. I've verified that the r...

Looking for a function...MIN IF ?
Hello, Here is my problem : On my sheet, I have to 2 columns of n lines. The first column contains n numbers. The second one contains n boolean values ('true' or 'false'). I just want to find the minimum of the values of the first column for which the corresponding value (in the second column) is 'true'. Is there any conditional function like 'SUMIF()' that does that search or any other way to do that ? I have Excel 2002. Thanks for any help ! -- Emeric =MIN(IF(K2:K20,J2:J20)) where K is the column of TRUEs and J is the numbers. It is an array formula, ...

Is there any a "unique" function performs on CStringArray or CStringList?
Is there any a "unique" function performs on CStringArray or CStringList? I know there is an algorithm function "unique" performing on containers in Standard C++ library. Is there a similar function in MFC? What am I doing now to avoid adding same strings to container is: CStringList strList; ..... if (strList.Find("somestring") == NULL) strList.AddTail("somestring"); But I realized it may be an inefficient way. Any good sugestion? I would have used a CMap for this. Then, when all insertions were done, I might consider converting it to a CArray...

Altering SP
Can GP stored procedures be altered. How safe is that. In general you should not alter procs created by GP. There is a possibility that the proc could be altered in a hotfix or upgrade. You may not notice that the proc has changed till much later when you find that the proc is not working as you had meant it Intead, find a way to call your own proc using vba or a trigger. HS "Asma" <asma_twinmos@hotmail.com> wrote in message news:1183294932.796908.67450@w5g2000hsg.googlegroups.com... > Can GP stored procedures be altered. How safe is that. > ...

VBScript String Clean function
This article provides a function written in VBScript which removes illegal characters from a string. The illegal characters are specified in an array at the beginning of the function. The function is passed the string which may contain illegal characters, which then processes the string to remove or replace any illegal characters. The code also allows you to specify the character or word which will replace specific illegal characters. The illegal characters provided in the function are included for processing strings which will become the filename of a document or list item in Sh...

Help with IF Function
Hello Everybody I need an If function for the next task in Column H: Cell H4=F4/G4; H5=F5/G5 and so on. The Problem is that some of the cells in column G have values "0" and "1" and I want in this case the value in the cells of column H just to be equal of the value in cells of column "F". For example if G5=0 or if G5=1 the value in H5 must be the same as the value in F5 But if G5 is not equal to "0" or "1" The value in H5 is F5/G5. I tried next function in cell H4 with no success =if( G4<=1,F4,F4/G4) Any help is highly greeted. Tha...

2007 to 2003 functionality
I have excel 2007 and transferred a file to a user that only has 2003. When trying save this in the 2003 format the compatibity checker gives me 42 formulas that have significant loss of functionality. All of the formulas have the IFERROR function in them. Is there a fix for this problem? It casuse all 42 of thes formulas to put the #name error in the field. One of the formulas looks like this. Did the IFERROR function even exist in 2003 version? =IFERROR(HLOOKUP(F7,$D$36:$N$40,5,FALSE),"") The IFERROR function is new to 2007, and is not available in earlier ...

Global Keypress Function
Hi, I want to write a function that will capture every keystroke that I type and capitalize it? I already do do this in certian text boxes. Private Sub AdditionalEasementsCoOwnerFirstName_KeyPress(KeyAscii As Integer) Char = Chr(KeyAscii) KeyAscii = Asc(UCase(Char)) End Sub But I have many other text boxes and do not really want to input this code to all of them. Just seems like there should be an easier way to do this. Thanks. Hi, you can you form's KeyPress event, plus you can set Key Preview property of the form to True -- Best regards, ___________ ...