make all text upper case in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have seen all the other messages regarding this subject from 6/22/05 - they
helped with the definitions, however it is not staying in my event procedure
box when I save the form and it wants to open up an additional module window
then it does not now were to pull the event

I need to change some of the text that has been inputted already and then
anything I enter from now on!!

Also I need to do this for the whole form can I do that all at once or do I
have to do it per field ???

Thank you in advance for any help you may be able to provide me.

Sophia
 
Sophia,

If you copy and paste this into a module and run it a couple of times I
think you will see how to call if from anywhere in your database. You can use
the function in an update query to update all of the existing data in your
database and then call it anytime a user inputs data. Let me know if you need
more help.
-- Take Care & God Bless ~ SPARKER ~

Private Sub HowToUseThisFunction()
Dim pstrMyTextFromAnywhere As String
pstrMyTextFromAnywhere = "MyExampleText"
MsgBox "When passed through the function" & vbCrLf & _
"MyExampleText" & vbCrLf & _
"now becomes: " & vbCrLf & _
"" & MakeThisTextUpperCase(pstrMyTextFromAnywhere) & _
"", vbInformation, "Example vbUpperCase:"
End Sub

Public Function MakeThisTextUpperCase(pstrText As String) As String
MakeThisTextUpperCase = StrConv(pstrText, vbUpperCase)
End Function
___________________________________________________________________
 
Thank you so much for your help on this - I really appreiate it..... I am
still having trouble though.. if you have a little more time?

Let me explain exactly what I am doing and maybe you can see what step I am
missing

I am going into my form properties and under the "after update" I am adding
an event procedure and bringing up the module that you gave me - it is giving
me an error - I then tried the other that was working for me yesterday

Private Sub First Name_AfterUpdate()
Me!First Name = UCase(Me!First Name)
End Sub

just in the First Name field properties - not working today!!! I cannot see
were I can do an update query either....... Can I do it from the Table to
make it cover the whole database

I have been working with Access for about 6 1/2 years now but never on the
module level... I truly understand all of the other underlying procedures
from tables to macros but my fear is I am not getting this because I don't
now everything about how modules work - my experiance is you need to now this
to make anything in work....I also realize this should have been set up while
the database was being created but unfortunately I was not here at the time
so I am trying to work backwards to make this work for what my supervisors
want.

If you can help much appreciated - I can also go through help on my computer
and see what if anything it will give me.

thank again in advance

Sophia
 
Sophia said:
Thank you so much for your help on this - I really appreiate it.....
I am still having trouble though.. if you have a little more time?

Let me explain exactly what I am doing and maybe you can see what
step I am missing

I am going into my form properties and under the "after update" I am
adding an event procedure and bringing up the module that you gave me
- it is giving me an error - I then tried the other that was working
for me yesterday

Private Sub First Name_AfterUpdate()
Me!First Name = UCase(Me!First Name)
End Sub

just in the First Name field properties - not working today!!! I
cannot see were I can do an update query either....... Can I do it
from the Table to make it cover the whole database

I have been working with Access for about 6 1/2 years now but never
on the module level... I truly understand all of the other underlying
procedures from tables to macros but my fear is I am not getting this
because I don't now everything about how modules work - my experiance
is you need to now this to make anything in work....I also realize
this should have been set up while the database was being created but
unfortunately I was not here at the time so I am trying to work
backwards to make this work for what my supervisors want.

If you can help much appreciated - I can also go through help on my
computer and see what if anything it will give me.
Click to expand...

You've been using Access for over 6 years and you don't know that
field/control names with spaces in them need to be surrounded by square
brackets?

Me![First Name] = UCase(Me![First Name])
 
Sophia,
I have plenty of time for you. For the first part updating all of the
existing data in your database... please give me the name of a table and the
field that you want to update. I will give you the update SQL that you can
paste into a query in design view "SQL-View" and once you see it and run it I
am sure you will begin to understand. For the second part to handle the user
input... Are you using unbound forms and then appending the data after the
user clicks save with some code you have written or are you using a form that
is bound directly to a table / or query so that they are writing directly
into the table? If this is too confusing and you have the means, you are
welcome to zip up a sample stripped down version of your database for me to
look at. And I can return to you the answers you seek. -- Take Care & God
Bless ~ SPARKER ~
 
Thank you so much for your help :)

I was able to put in the right code to have anything entered from now on
come as caps - it is just everything that is already in there that was not
entered correctly... there is about 1/3 of my database in lower case....

the name of my table is "Touch Essentials " I actually need to update all
the fields in my database - there is 16 fields... but if you can tell me how
to do it with the first field I can follow through to all the other fields -
the first field name is: "First Name"
are you talking about taking an already existing query and using a SQL
specific menu choice.....? Can I apply one to all or do I need to put in in
the Criteria for ech field.

Once again I appreciate your help and look forward to your reply.

I have happily learned quite a few more things about this wonderful database
just trying to figure out this little thing - thank you for your help on that
as well.

Sophia
 
Sophia,
you are welcome. I enjoy helping you. I have three functions for you today.
Open your database and create a brand new module and then copy and paste this
code into it. The Private Sub StartUpperCase calls the other 3 functions to
search out all data in the table named "Touch Essentials" you can at anytime
replace that with any other table name or even write some code to loop
through all of the tables in your database... So paste this into a new module
and run it. It will uppercase all of the text in the table named.
_________________________________________________________________________________________________________
Private Sub StartUpperCase()
Call UpperCaseTable("Touch Essentials")
End Sub
Public Function UpperCaseTable( _
pstrTableName As String)
Dim objTdefs As TableDefs
Dim objTdef As TableDef
Dim pstrColumnName As String
Dim lngFldCount As Long
Set objTdefs = CurrentDb.TableDefs
Set objTdef = objTdefs(pstrTableName)
For lngFldCount = 0 To objTdef.Fields.Count - 1
pstrColumnName = objTdef.Fields(lngFldCount).Name
Call UpperCaseColumn(pstrTableName, pstrColumnName)
Next lngFldCount
End Function
Private Function UpperCaseColumn( _
pstrTableName As String, _
pstrColumnName As String)
Dim daoDbs As DAO.Database
Dim strSql As String
Set daoDbs = CodeDb
strSql = _
"UPDATE [" & pstrTableName & "] " & _
"SET [" & pstrTableName & "]." & pstrColumnName & _
" = UpperCaseText([" & pstrColumnName & "]);"
Debug.Print strSql
daoDbs.Execute strSql, dbSeeChanges
strSql = ""
daoDbs.Close
End Function
Public Function UpperCaseText( _
pstrText As String) As String
UpperCaseText = StrConv(pstrText, vbUpperCase)
End Function
__________________________________________________________________________________________________________
Take Care & God Bless ~ SPARKER ~
 
I just wanted to say thank you soooooooooooo very much -

I made the new module and did a couple other updates for other things and my
database looks great !!!!!
My associates are very happy as well.

Thank you tons and tons - I am totally grateful!

I will let you know if I need any more help

Take Care and have a Wonderful Day

Sincerely

Sophia

sparker said:
Sophia,
you are welcome. I enjoy helping you. I have three functions for you today.
Open your database and create a brand new module and then copy and paste this
code into it. The Private Sub StartUpperCase calls the other 3 functions to
search out all data in the table named "Touch Essentials" you can at anytime
replace that with any other table name or even write some code to loop
through all of the tables in your database... So paste this into a new module
and run it. It will uppercase all of the text in the table named.
_________________________________________________________________________________________________________
Private Sub StartUpperCase()
Call UpperCaseTable("Touch Essentials")
End Sub
Public Function UpperCaseTable( _
pstrTableName As String)
Dim objTdefs As TableDefs
Dim objTdef As TableDef
Dim pstrColumnName As String
Dim lngFldCount As Long
Set objTdefs = CurrentDb.TableDefs
Set objTdef = objTdefs(pstrTableName)
For lngFldCount = 0 To objTdef.Fields.Count - 1
pstrColumnName = objTdef.Fields(lngFldCount).Name
Call UpperCaseColumn(pstrTableName, pstrColumnName)
Next lngFldCount
End Function
Private Function UpperCaseColumn( _
pstrTableName As String, _
pstrColumnName As String)
Dim daoDbs As DAO.Database
Dim strSql As String
Set daoDbs = CodeDb
strSql = _
"UPDATE [" & pstrTableName & "] " & _
"SET [" & pstrTableName & "]." & pstrColumnName & _
" = UpperCaseText([" & pstrColumnName & "]);"
Debug.Print strSql
daoDbs.Execute strSql, dbSeeChanges
strSql = ""
daoDbs.Close
End Function
Public Function UpperCaseText( _
pstrText As String) As String
UpperCaseText = StrConv(pstrText, vbUpperCase)
End Function
__________________________________________________________________________________________________________
Take Care & God Bless ~ SPARKER ~


Sophia said:
Thank you so much for your help :)

I was able to put in the right code to have anything entered from now on
come as caps - it is just everything that is already in there that was not
entered correctly... there is about 1/3 of my database in lower case....

the name of my table is "Touch Essentials " I actually need to update all
the fields in my database - there is 16 fields... but if you can tell me how
to do it with the first field I can follow through to all the other fields -
the first field name is: "First Name"
are you talking about taking an already existing query and using a SQL
specific menu choice.....? Can I apply one to all or do I need to put in in
the Criteria for ech field.

Once again I appreciate your help and look forward to your reply.

I have happily learned quite a few more things about this wonderful database
just trying to figure out this little thing - thank you for your help on that
as well.

Sophia
Click to expand...
Click to expand...
 
You are very welcome!
I am happy to have helped you.
Please do not hesitate to ask for more!
Take Care & God Bless ~ SPARKER ~


Sophia said:
I just wanted to say thank you soooooooooooo very much -

I made the new module and did a couple other updates for other things and my
database looks great !!!!!
My associates are very happy as well.

Thank you tons and tons - I am totally grateful!

I will let you know if I need any more help

Take Care and have a Wonderful Day

Sincerely

Sophia

sparker said:
Sophia,
you are welcome. I enjoy helping you. I have three functions for you today.
Open your database and create a brand new module and then copy and paste this
code into it. The Private Sub StartUpperCase calls the other 3 functions to
search out all data in the table named "Touch Essentials" you can at anytime
replace that with any other table name or even write some code to loop
through all of the tables in your database... So paste this into a new module
and run it. It will uppercase all of the text in the table named.
_________________________________________________________________________________________________________
Private Sub StartUpperCase()
Call UpperCaseTable("Touch Essentials")
End Sub
Public Function UpperCaseTable( _
pstrTableName As String)
Dim objTdefs As TableDefs
Dim objTdef As TableDef
Dim pstrColumnName As String
Dim lngFldCount As Long
Set objTdefs = CurrentDb.TableDefs
Set objTdef = objTdefs(pstrTableName)
For lngFldCount = 0 To objTdef.Fields.Count - 1
pstrColumnName = objTdef.Fields(lngFldCount).Name
Call UpperCaseColumn(pstrTableName, pstrColumnName)
Next lngFldCount
End Function
Private Function UpperCaseColumn( _
pstrTableName As String, _
pstrColumnName As String)
Dim daoDbs As DAO.Database
Dim strSql As String
Set daoDbs = CodeDb
strSql = _
"UPDATE [" & pstrTableName & "] " & _
"SET [" & pstrTableName & "]." & pstrColumnName & _
" = UpperCaseText([" & pstrColumnName & "]);"
Debug.Print strSql
daoDbs.Execute strSql, dbSeeChanges
strSql = ""
daoDbs.Close
End Function
Public Function UpperCaseText( _
pstrText As String) As String
UpperCaseText = StrConv(pstrText, vbUpperCase)
End Function
__________________________________________________________________________________________________________
Take Care & God Bless ~ SPARKER ~


Sophia said:
Thank you so much for your help :)

I was able to put in the right code to have anything entered from now on
come as caps - it is just everything that is already in there that was not
entered correctly... there is about 1/3 of my database in lower case....

the name of my table is "Touch Essentials " I actually need to update all
the fields in my database - there is 16 fields... but if you can tell me how
to do it with the first field I can follow through to all the other fields -
the first field name is: "First Name"
are you talking about taking an already existing query and using a SQL
specific menu choice.....? Can I apply one to all or do I need to put in in
the Criteria for ech field.

Once again I appreciate your help and look forward to your reply.

I have happily learned quite a few more things about this wonderful database
just trying to figure out this little thing - thank you for your help on that
as well.

Sophia



:

Sophia,
I have plenty of time for you. For the first part updating all of the
existing data in your database... please give me the name of a table and the
field that you want to update. I will give you the update SQL that you can
paste into a query in design view "SQL-View" and once you see it and run it I
am sure you will begin to understand. For the second part to handle the user
input... Are you using unbound forms and then appending the data after the
user clicks save with some code you have written or are you using a form that
is bound directly to a table / or query so that they are writing directly
into the table? If this is too confusing and you have the means, you are
welcome to zip up a sample stripped down version of your database for me to
look at. And I can return to you the answers you seek. -- Take Care & God
Bless ~ SPARKER ~


:

Thank you so much for your help on this - I really appreiate it..... I am
still having trouble though.. if you have a little more time?

Let me explain exactly what I am doing and maybe you can see what step I am
missing

I am going into my form properties and under the "after update" I am adding
an event procedure and bringing up the module that you gave me - it is giving
me an error - I then tried the other that was working for me yesterday

Private Sub First Name_AfterUpdate()
Me!First Name = UCase(Me!First Name)
End Sub

just in the First Name field properties - not working today!!! I cannot see
were I can do an update query either....... Can I do it from the Table to
make it cover the whole database

I have been working with Access for about 6 1/2 years now but never on the
module level... I truly understand all of the other underlying procedures
from tables to macros but my fear is I am not getting this because I don't
now everything about how modules work - my experiance is you need to now this
to make anything in work....I also realize this should have been set up while
the database was being created but unfortunately I was not here at the time
so I am trying to work backwards to make this work for what my supervisors
want.

If you can help much appreciated - I can also go through help on my computer
and see what if anything it will give me.

thank again in advance

Sophia



:

Sophia,

If you copy and paste this into a module and run it a couple of times I
think you will see how to call if from anywhere in your database. You can use
the function in an update query to update all of the existing data in your
database and then call it anytime a user inputs data. Let me know if you need
more help.
-- Take Care & God Bless ~ SPARKER ~

Private Sub HowToUseThisFunction()
Dim pstrMyTextFromAnywhere As String
pstrMyTextFromAnywhere = "MyExampleText"
MsgBox "When passed through the function" & vbCrLf & _
"MyExampleText" & vbCrLf & _
"now becomes: " & vbCrLf & _
"" & MakeThisTextUpperCase(pstrMyTextFromAnywhere) & _
"", vbInformation, "Example vbUpperCase:"
End Sub

Public Function MakeThisTextUpperCase(pstrText As String) As String
MakeThisTextUpperCase = StrConv(pstrText, vbUpperCase)
End Function
___________________________________________________________________

:

I have seen all the other messages regarding this subject from 6/22/05 - they
helped with the definitions, however it is not staying in my event procedure
box when I save the form and it wants to open up an additional module window
then it does not now were to pull the event

I need to change some of the text that has been inputted already and then
anything I enter from now on!!

Also I need to do this for the whole form can I do that all at once or do I
have to do it per field ???

Thank you in advance for any help you may be able to provide me.

Sophia
Click to expand...
Click to expand...
Click to expand...
 
Back
Top