Solved

force lower case

Posted on 2010-11-16
14
671 Views
Last Modified: 2012-05-10
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.

0
Comment
Question by:mreid3847
  • 5
  • 4
  • 4
  • +1
14 Comments
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 100 total points
ID: 34150274


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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34150302
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
 
LVL 75
ID: 34150338
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34150354
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
 

Author Comment

by:mreid3847
ID: 34150363
MX
I tested your suggestion and I get the same results, the data is stored in the table in all caps.
Thanks
Misty
0
 
LVL 75
ID: 34150402
"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
 

Author Comment

by:mreid3847
ID: 34150577
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 75
ID: 34150624
"unbound form"
oops ... well, the BU event will not trigger in an Unbound form.  So ... how are you saving records now?

mx
0
 

Author Comment

by:mreid3847
ID: 34150670
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
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 250 total points
ID: 34150701
Change it to LCase in this code, e.g.

rsSharedUser!Name = LCase(Me.txtUserName)
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34150722
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34150723
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
 

Author Closing Comment

by:mreid3847
ID: 34150770
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
 
LVL 75
ID: 34150839
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

This article will show you how to use shortcut menus in the Access run-time environment.
This is about my first experience with programming Arduino.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now