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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can also do this at the text (or combo) box level like so:
Private Sub txtSomeFieldName_AfterUpda
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
Private Sub txtSomeFieldName_AfterUpda
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
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.
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.
ASKER
MX
I tested your suggestion and I get the same results, the data is stored in the table in all caps.
Thanks
Misty
I tested your suggestion and I get the same results, the data is stored in the table in all caps.
Thanks
Misty
"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
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
ASKER
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
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
"unbound form"
oops ... well, the BU event will not trigger in an Unbound form. So ... how are you saving records now?
mx
oops ... well, the BU event will not trigger in an Unbound form. So ... how are you saving records now?
mx
ASKER
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("S
rsSharedUser.AddNew
rsSharedUser!PasswordAdapt
rsSharedUser!LocaleIDUniqu
rsSharedUser!OrganizationS
rsSharedUser!UniqueName = Me.txtUniqueName
rsSharedUser!Name = Me.txtUserName
rsSharedUser!EmailAddress = Me.txtEmail
rsSharedUser.Update
rsSharedUser.Close
Set rsSharedUser = Nothing
End Sub
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("S
rsSharedUser.AddNew
rsSharedUser!PasswordAdapt
rsSharedUser!LocaleIDUniqu
rsSharedUser!OrganizationS
rsSharedUser!UniqueName = Me.txtUniqueName
rsSharedUser!Name = Me.txtUserName
rsSharedUser!EmailAddress = Me.txtEmail
rsSharedUser.Update
rsSharedUser.Close
Set rsSharedUser = Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
ASKER
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.
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.
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
mx
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