Uppercase to Mixed Case Edit in Field

[sara]

I have a table where a few of the users entered vendor names ALL IN
UPPER CASE. I have created forms to edit the data, but I can't seem to
allow changing JOE SMITH to Joe Smith.

What to I have to do to have the user change the name, but keep the
key? Overall, I think any "edits" simply add a new record, rather than
change the existing record.

The form is simple - Choose the vendor from the drop-down list.
After Update, a new field displays: Vendor: [and the chosen vendor in
the field]

If the user wishes to Add a new vendor or Delete the current one, they
choose a button on the bottom of the screen and that works just fine.

The "edit" or "Change" is what doesn't work.

Don't know what code to post, so I didn't post any.

Thanks.

Sara

[gumby]

This will force the name into "Joe Smith"
Add it to the field under the afterupdate property.
Private Sub FirstName_After Update()

Me.FirstName = StrConv(FirstNa me, 3) ' Research StrConv Function for
more info on number to use

End Sub

Private Sub LastName_AfterU pdate()

Me.LastName = StrConv(LastNam e, 3) ' Research StrConv Function for
more info on number to use

End Sub

Private Sub MiddleInitial_A fterUpdate()

Me.MiddleInitia l = StrConv(MiddleI nitial, 3) ' Research StrConv
Function for more info on number to use

End Sub


Some code that will capitalize the first letter but not force the rest
of the letters to be lowercase.

Here it is:

Private Sub LastName_AfterU pdate()

Dim strFirstLetter As String

Dim strRightName As String

Dim strWholeName As String

strFirstLetter = Left(LastName, 1)

strRightName = Mid(LastName, 2, 49)

strWholeName = UCase(strFirstL etter) + strRightName

Me.LastName = strWholeName

End Sub

sara wrote:

I have a table where a few of the users entered vendor names ALL IN
UPPER CASE. I have created forms to edit the data, but I can't seem to
allow changing JOE SMITH to Joe Smith.

What to I have to do to have the user change the name, but keep the
key? Overall, I think any "edits" simply add a new record, rather than
change the existing record.

The form is simple - Choose the vendor from the drop-down list.
After Update, a new field displays: Vendor: [and the chosen vendor in
the field]

If the user wishes to Add a new vendor or Delete the current one, they
choose a button on the bottom of the screen and that works just fine.

The "edit" or "Change" is what doesn't work.

Don't know what code to post, so I didn't post any.

Thanks.

Sara

[sara]

Thanks, gumby. Maybe I'm not being clear.

I have 2 problems:

1. The field is ONE field: Vendor. It may have a company name (ABC
Corp) or it may have a person's name (JOE SMITH). I want to end up
with [ABC Corp] and [Joe Smith] by allowing the user to edit.

2. Every time I try to change, the system tells me I'm trying to add a
duplicate record. I want to change JOE SMITH to Joe Smith, so I'm not
even sure the after update code is the right thing to do.

Here's the code I wrote, and it does execute, but I get a dup message
(I don't allow the same vendor name to be added).

Private Sub txtVendor_After Update()
' This will force "JOE SMITH" into "Joe SMITH"

Dim strVendor As String
Dim strFirstLetter As String
Dim strRightName As String
Dim strWholeName As String

strVendor = Me.txtVendor
strFirstLetter = Left(strVendor, 1)
strRightName = Mid(strVendor, 2, 49)
strWholeName = UCase(strFirstL etter) + strRightName
Me.txtVendor = strWholeName
End Sub

Thanks

Sara



gumby wrote:

This will force the name into "Joe Smith"
Add it to the field under the afterupdate property.
Private Sub FirstName_After Update()

Me.FirstName = StrConv(FirstNa me, 3) ' Research StrConv Function for
more info on number to use

End Sub

Private Sub LastName_AfterU pdate()

Me.LastName = StrConv(LastNam e, 3) ' Research StrConv Function for
more info on number to use

End Sub

Private Sub MiddleInitial_A fterUpdate()

Me.MiddleInitia l = StrConv(MiddleI nitial, 3) ' Research StrConv
Function for more info on number to use

End Sub


Some code that will capitalize the first letter but not force the rest
of the letters to be lowercase.

Here it is:

Private Sub LastName_AfterU pdate()

Dim strFirstLetter As String

Dim strRightName As String

Dim strWholeName As String

strFirstLetter = Left(LastName, 1)

strRightName = Mid(LastName, 2, 49)

strWholeName = UCase(strFirstL etter) + strRightName

Me.LastName = strWholeName

End Sub

sara wrote:

I have a table where a few of the users entered vendor names ALL IN
UPPER CASE. I have created forms to edit the data, but I can't seem to
allow changing JOE SMITH to Joe Smith.

What to I have to do to have the user change the name, but keep the
key? Overall, I think any "edits" simply add a new record, rather than
change the existing record.

The form is simple - Choose the vendor from the drop-down list.
After Update, a new field displays: Vendor: [and the chosen vendor in
the field]

If the user wishes to Add a new vendor or Delete the current one, they
choose a button on the bottom of the screen and that works just fine.

The "edit" or "Change" is what doesn't work.

Don't know what code to post, so I didn't post any.

Thanks.

Sara

[pietlinden]

Wait, you didn't use the vendor's actual name as a primary key, did
you? BAD design. Very bad. I mean, how many Smiths are in the phone
book? That's just asking for trouble. Your first relationship will
work okay, but what happens when you really do have a second "John
Smith". Make the primary key an autonumber, and then base the
relationships on that. If you want to convert the text to propercase,
use something like this:

?strconv("david ortiz",vbProper Case)
David Ortiz

Of course, if you have exceptions to the "first letter capitalized"
rule, you're SOL.

[sara]

No, I did not use the vendor's name as a key, but I did set a unique
index. The table is simple: VendorKey (autonumber), VendorName and
VendorStatus (Active or Deleted).

We do not have the same vendor name twice (haven't in almost 50
years), and even if we somehow did, we'd have to give it a unique name
so the executives would know which vendor sold us which items. They
get reports by vendor name.

SO, the problem still remains that I am not doing what is necessary to
update a record, but rather have (somehow) set it up so that every
action on the screen is seen by the system as a new record, not an
update.

We do have exceptions to the first letter Cap rule, as I mentioned -
ABC Corp, for example, needs to be ABC Corp (not Abc Corp), but Joe
Smith shouldn't be JOE SMITH. The users have to decide what should be
caps and what shouldn't and enter the name properly when they set up
any new vendors (which is rarely).

Now, however, we have over 1000 vendors, and many are all caps. I'm
trying to figure out how to allow editing to simply change the case to
be what we have agreed upon, but the system sees it as a new record,
with the same value.

Any thoughts? Anyone?

thanks
Sara
pi********@hotm ail.com wrote:

Wait, you didn't use the vendor's actual name as a primary key, did
you? BAD design. Very bad. I mean, how many Smiths are in the phone
book? That's just asking for trouble. Your first relationship will
work okay, but what happens when you really do have a second "John
Smith". Make the primary key an autonumber, and then base the
relationships on that. If you want to convert the text to propercase,
use something like this:

?strconv("david ortiz",vbProper Case)
David Ortiz

Of course, if you have exceptions to the "first letter capitalized"
rule, you're SOL.

[Fred Zuckerman]

"sara" <sa*******@yaho o.comwrote in message
news:11******** **************@ b28g2000cwb.goo glegroups.com.. .

I have a table where a few of the users entered vendor names ALL IN
UPPER CASE. I have created forms to edit the data, but I can't seem to
allow changing JOE SMITH to Joe Smith.

What to I have to do to have the user change the name, but keep the
key? Overall, I think any "edits" simply add a new record, rather than
change the existing record.

The form is simple - Choose the vendor from the drop-down list.
After Update, a new field displays: Vendor: [and the chosen vendor in
the field]

If the user wishes to Add a new vendor or Delete the current one, they
choose a button on the bottom of the screen and that works just fine.

The "edit" or "Change" is what doesn't work.

Don't know what code to post, so I didn't post any.

Thanks.

Sara

If the control on the form is bound and unlocked, then the users should be
able to change it. But I suspect that you have it locked so nobody
accidently changes it. Am I correct?
If so, then unlock the control and put the following code in the
BeforeUpdate Event:

'''(CAUTION - air code)
' If the user only changes the case of the characters then old & new values
will match
If Me.VendorName <Me.VendorName. OldValue Then
Msgbox "You attempted to change the vendor name"
Cancel = True
Me.Undo
Exit Sub
Endif

Good Luck,
Fred Zuckerman

[sara]

2 Questions, Fred:

1. The control is bound to field Vendor, and named txtVendor. It is
unlocked. Does it matter if it is showing in the combo box where the
user selected the vendor in the first place? The cboVendor is bound
inm column 1 to vendor key, but I do display the field Vendor. Should
I blank that out? Does it matter?

2. I want the user to change the case, not the value. Will the <>
allow case change, but not value?

I thought this was going to be SO easy!!! Was I ever wrong!

Thanks for the help.
Sara
Fred Zuckerman wrote:

"sara" <sa*******@yaho o.comwrote in message
news:11******** **************@ b28g2000cwb.goo glegroups.com.. .

I have a table where a few of the users entered vendor names ALL IN
UPPER CASE. I have created forms to edit the data, but I can't seem to
allow changing JOE SMITH to Joe Smith.

What to I have to do to have the user change the name, but keep the
key? Overall, I think any "edits" simply add a new record, rather than
change the existing record.

The form is simple - Choose the vendor from the drop-down list.
After Update, a new field displays: Vendor: [and the chosen vendor in
the field]

If the user wishes to Add a new vendor or Delete the current one, they
choose a button on the bottom of the screen and that works just fine.

The "edit" or "Change" is what doesn't work.

Don't know what code to post, so I didn't post any.

Thanks.

Sara

If the control on the form is bound and unlocked, then the users should be
able to change it. But I suspect that you have it locked so nobody
accidently changes it. Am I correct?
If so, then unlock the control and put the following code in the
BeforeUpdate Event:

'''(CAUTION - air code)
' If the user only changes the case of the characters then old & new values
will match
If Me.VendorName <Me.VendorName. OldValue Then
Msgbox "You attempted to change the vendor name"
Cancel = True
Me.Undo
Exit Sub
Endif

Good Luck,
Fred Zuckerman

[Fred Zuckerman]

I'm sorry I don't understand the setup. You have a combo control AND a text
control, and they're both displaying the Vendor field?

Anyway, in answer to your 2nd question, the <(not equal) operator is
opposite of the = (equal) operator. The statement of "Jones" <"JONES" is
false. So using the <operator to cancel, will allow users to change case
without changing the value.

Good Luck,
Fred Zuckerman
"sara" <sa*******@yaho o.comwrote in message
news:11******** **************@ p79g2000cwp.goo glegroups.com.. .

2 Questions, Fred:

1. The control is bound to field Vendor, and named txtVendor. It is
unlocked. Does it matter if it is showing in the combo box where the
user selected the vendor in the first place? The cboVendor is bound
inm column 1 to vendor key, but I do display the field Vendor. Should
I blank that out? Does it matter?

2. I want the user to change the case, not the value. Will the <>
allow case change, but not value?

I thought this was going to be SO easy!!! Was I ever wrong!

Thanks for the help.
Sara
Fred Zuckerman wrote:

"sara" <sa*******@yaho o.comwrote in message
news:11******** **************@ b28g2000cwb.goo glegroups.com.. .

I have a table where a few of the users entered vendor names ALL IN
UPPER CASE. I have created forms to edit the data, but I can't seem

to

allow changing JOE SMITH to Joe Smith.
>
What to I have to do to have the user change the name, but keep the
key? Overall, I think any "edits" simply add a new record, rather

than

change the existing record.
>
The form is simple - Choose the vendor from the drop-down list.
After Update, a new field displays: Vendor: [and the chosen vendor

in

the field]
>
If the user wishes to Add a new vendor or Delete the current one, they
choose a button on the bottom of the screen and that works just fine.
>
The "edit" or "Change" is what doesn't work.
>
Don't know what code to post, so I didn't post any.
>
Thanks.
>
Sara

If the control on the form is bound and unlocked, then the users should

be

able to change it. But I suspect that you have it locked so nobody
accidently changes it. Am I correct?
If so, then unlock the control and put the following code in the
BeforeUpdate Event:

'''(CAUTION - air code)
' If the user only changes the case of the characters then old & new

values

will match
If Me.VendorName <Me.VendorName. OldValue Then
Msgbox "You attempted to change the vendor name"
Cancel = True
Me.Undo
Exit Sub
Endif

Good Luck,
Fred Zuckerman

[sara]

I got it! Now I can edit and change case and it keeps the same key.

The set up: I have the user choose a vendor from the drop-down list.
This is for editing, or so they can first make sure the vendor isn't in
the list before they go and add it (to prevent duplicates). I wasn't
setting the combo box to blank in the AfterUpdate event - I just
display the name field for editing.
Thanks
sara

Fred Zuckerman wrote:

I'm sorry I don't understand the setup. You have a combo control AND a text
control, and they're both displaying the Vendor field?

Anyway, in answer to your 2nd question, the <(not equal) operator is
opposite of the = (equal) operator. The statement of "Jones" <"JONES" is
false. So using the <operator to cancel, will allow users to change case
without changing the value.

Good Luck,
Fred Zuckerman
"sara" <sa*******@yaho o.comwrote in message
news:11******** **************@ p79g2000cwp.goo glegroups.com.. .

2 Questions, Fred:

1. The control is bound to field Vendor, and named txtVendor. It is
unlocked. Does it matter if it is showing in the combo box where the
user selected the vendor in the first place? The cboVendor is bound
inm column 1 to vendor key, but I do display the field Vendor. Should
I blank that out? Does it matter?

2. I want the user to change the case, not the value. Will the <>
allow case change, but not value?

I thought this was going to be SO easy!!! Was I ever wrong!

Thanks for the help.
Sara
Fred Zuckerman wrote:

"sara" <sa*******@yaho o.comwrote in message
news:11******** **************@ b28g2000cwb.goo glegroups.com.. .
I have a table where a few of the users entered vendor names ALL IN
UPPER CASE. I have created forms to edit the data, but I can't seem

to

allow changing JOE SMITH to Joe Smith.

What to I have to do to have the user change the name, but keep the
key? Overall, I think any "edits" simply add a new record, rather

than

change the existing record.

The form is simple - Choose the vendor from the drop-down list.
After Update, a new field displays: Vendor: [and the chosen vendor

in

the field]

If the user wishes to Add a new vendor or Delete the current one, they
choose a button on the bottom of the screen and that works just fine.

The "edit" or "Change" is what doesn't work.

Don't know what code to post, so I didn't post any.

Thanks.

Sara
>
If the control on the form is bound and unlocked, then the users should

be

able to change it. But I suspect that you have it locked so nobody
accidently changes it. Am I correct?
If so, then unlock the control and put the following code in the
BeforeUpdate Event:
>
'''(CAUTION - air code)
' If the user only changes the case of the characters then old & new

values

will match
If Me.VendorName <Me.VendorName. OldValue Then
Msgbox "You attempted to change the vendor name"
Cancel = True
Me.Undo
Exit Sub
Endif
>
Good Luck,
Fred Zuckerman