force lower case

Windows XP, Access 2003, Novice Access and VB user.

I have a form that has unbound controls.  When a user types information into various text boxes I am using the click event of a button to write data to different tables.  

The information stored in the tables at a later date has to be exported to be used as input data for an ASP hosted system.  The ASP hosted system is looking for and requires lower case data.

My question is how do I force lower case on my form so that it is then stored in my table as lower case?

In the property box I tried setting the format of each text box control to format <

I also tried on the KeyPress event of the form the following:
Private Sub Form_KeyPress(KeyAscii As Integer)
    'Convert to lower case
    KeyAscii = Asc(LCase(Chr(KeyAscii)))
End Sub

And visually on the form if I test these and I have my caps lock on an type they are converted to lower case.  Yet when I open my table the data is in upper case.

What do I need to do in order for the data to be stored in the table as lower case?  If possible I would like to do this at the form level and not have to add an additional step of using an update query.

Thanks in advance.

mreid3847Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
cyberkiwiConnect With a Mentor Commented:
Change it to LCase in this code, e.g.

rsSharedUser!Name = LCase(Me.txtUserName)
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:


Try using the Form BeforeUpdate event

Private Sub Form_BeforeUpdate (Cancel As Integer)

   Me![SomeFieldName] = LCase(Me.txtSomeFieldName)

   ' and so on

End Sub

mx
0
 
Jeffrey CoachmanMIS LiasonCommented:
The format property will only control how the data is "Displayed".
So your observation is correct.

In your case it seems like you will have to literally "Replace" the Upper case characters with lower case.
Most likely using the Replace() Function.

I am going home now.

I am sure an expert will be along to help with the exact syntax.

;-)

JeffCoachman
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You can also do this at the text (or combo) box level like so:


Private Sub txtSomeFieldName_AfterUpdate()
   Me.txtSomeFieldName = LCase(Me.txtSomeFieldName)
End Sub

This will immediately convert the text entry to lower case, as well as redisplay it in the text box.

mx
0
 
cyberkiwiCommented:
Without changing your table/form design, and without using macros, you can create a query for each table that needs exporting, such as

tblToExport - create query qryTblToExport

select numcol1, datecol2, lcase(txtcol3) as txtcol3, lcase(txtcol4) as txtcol4
from tblToExport

And use the query as the source for the export instead of the table.
LCase turns all the data into lower case.
0
 
mreid3847Author Commented:
MX
I tested your suggestion and I get the same results, the data is stored in the table in all caps.
Thanks
Misty
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"I tested your suggestion and I get the same results,"
Can you post exactly what you 'tested' ... because I can assure you that using the Form BU event will work ...

Also re:

"the data is stored in the table in all caps."
Are you looking directly in the table ... and if so, do you maybe have a Format such that it is 'displaying' the data in Upper case?

mx
0
 
mreid3847Author Commented:
MX
I can't post the DB because of the information contained in it, some of it is sensitive.  I have included a screen shot.

The screen shot has my unbound form in design view and also in form view with data entered.  The screen shot also has my table after I have written to it.

I tested it with the unbound text field named txtUniqueName, which is the first field on my unbound form.

The code I have on the form BeforeUpdate is:
Private Sub Form_BeforeUpdate(Cancel As Integer)
   Me!txtUniqueName = LCase(Me.txtUniqueName)
End Sub

SCREENSHOT.doc
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"unbound form"
oops ... well, the BU event will not trigger in an Unbound form.  So ... how are you saving records now?

mx
0
 
mreid3847Author Commented:
MX

On the click event of the button on the form I do the following for each table I need to push data to:

Private Sub WriteRecords_Click()
 Dim rsSharedUser As DAO.Recordset
    Set rsSharedUser = CurrentDb.OpenRecordset("SharedUser")
        rsSharedUser.AddNew
        rsSharedUser!PasswordAdapter = "PasswordAdapter1"
        rsSharedUser!LocaleIDUniqueName = "en_US"
        rsSharedUser!OrganizationSystemID = "[Buyer]"
        rsSharedUser!UniqueName = Me.txtUniqueName
        rsSharedUser!Name = Me.txtUserName
        rsSharedUser!EmailAddress = Me.txtEmail
        rsSharedUser.Update
        rsSharedUser.Close
    Set rsSharedUser = Nothing
 End Sub
0
 
cyberkiwiCommented:
Although I still think there is merit to using queries that guaranee lower case output regardless of what data you have, may import, or will enter via datasheet directly etc that don't pass through your unbound form code. What if someone decided tomorrow that bound forms are better?
0
 
cyberkiwiCommented:
Although I still think there is merit to using queries that guaranee lower case output regardless of what data you have, may import, or will enter via datasheet directly etc that don't pass through your unbound form code. What if someone decided tomorrow that bound forms are better?
0
 
mreid3847Author Commented:
Thanks cyber that did what I needed it to do in the context of what I am working with currently and it did allow me to store the data in lowercase in the table.

And you bring up a good point about using a query to do the export.  I am crunched on time currently but I am going to revisit that suggestion.

I threw together a bound form quickly and did test MX solution on the BU and it did work when I used a bound form.  But for the scope of this project I will not be changing the current form to a bound form.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Sorry, I was on an important phone call with IT.  Looks like someone beat to the same solution what I would have posted ... in another 10 seconds :-)

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.