After Update Event not working?

[Negger]

Hi, I simply need to convert a text box to UpperCase, and then store in underlying table (it's a "City" address field,entered in an input form, to use in mailmerge for addressing mail in the UK).
I've found two methods, one using AfterUpdate & "UCase", the other using OnKeyPress & "KeyAscii", neither seem to be having any effect ?
The code is as follows:
AfterUpdateEvent -
Private Sub City_AfterUpdate()
Me.City = UCase(Me.City)
End Sub
(I've also tried this with a "!" instead of the ".")

OnKeyPress -
Private Sub City_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub

I'm a total novice at this so it may well be something very obvious! Can anyone suggest where I'm going wrong ?
Regards

 

[DCrake]

if it is a bound control use the before update event instead

 

[vbaInet]

The other method is not to store it and put this >in the Format property of the textbox.

 

[Negger]

Thank you both for your replies.
I've now put "UCase" in BeforeUpdate as well but still nothing happens ?
(City formfield should store result in Table field "Form", therefore presumably it is bound ?)
Ascii option should still work anyway, any ideas why not ?
I deliberately didn't use the Format Property as, as you point out, it doesn't store the result.
I presume I do need to store the result to show UPPERCASE if I print off hard copies from this data ?
Regards

 

[vbaInet]

I presume I do need to store the result to show UPPERCASE if I print off hard copies from this data ?
Regards

Depends what you're printing. You should be printing a report, not a form. So if you used that in the record source of the report then it will print in Upper Case.

 

[missinglinq]

Putting this kind of code in the control's BeforeUpdate event will pop a runtime error; having it in the AfterUpdate event should work, as should your attempt using the KeyPress event. They both work as intended for me.

The only thing that comes to mind is the possibility that the textbox has been corrupted. I'd delete the control and re-create it.

Linq ;0)>

 

[Negger]

vbaIlet, Yes I presume that if the "City" data is stored as UPPERCASE in the table, then however I access that data e.g. through a report (to print), then the data will always print out as UPPERCASE ?
Missinglinq, I've deleted & re-created the text box, still no joy.
I've tried UPPERCASEing a different field using the Ascii method, but still nothing happens ?
As I said it's probably something very basic that I'm missing ?
Regards

 

[missinglinq]

You're getting no error messages with this?

I guess the next thing you need to look at are your references. The one for the UCase() function may be missing, and you use it n both of your approaches.

If you're not familiar with checking them Doug Steele gives a good explanation here:

http://www.accessmvp.com/DJSteele/AccessReferenceErrors.html

If that doesn't do it I guess you need to zip the file and post it here for us to lay hands on. This explains how:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

BTW, what version of Access are you using?

 

[vbaInet]

vbaIlet, Yes I presume that if the "City" data is stored as UPPERCASE in the table, then however I access that data e.g. through a report (to print), then the data will always print out as UPPERCASE ?

By Record Source I mean in the query that is the record source of your report. Your report should be based on a query.

In a new column in the query, you would put something like:

NewFieldName: UCase([[COLOR=Red][B]TheField[/B][/COLOR]])

So if done this way it will print (and export) as UPPER CASE.

If you use the format property (i.e. >) it will print as Upper Case but probably not export as upper case.

 

[DCrake]

as a test got to the immediate window (Ctrl+G) then type in

?UCase("fred")

do you get FRED

 

[missinglinq]

I'm guessing the answer is going to be NO.

 

[gemma-the-husky]

i am sure you can do it in a after update event

eg - I am sure i have a fixup method for car reg numbers, to capitalise and strip out spaces, in an after update event.

 

[missinglinq]

i am sure you can do it in a after update event

Yes, the other David was mistaken in suggesting using the BeforeUpdate event of the control. As I said, doing so would pop a Runtime error. It's one of those gazillion digit error numbers, but basically said that modifying the value prevents Access from saving it.

I'm inclined to think that the problem comes from the use of the UCase() function which is used in both of the OP's attempts at this. I suspect that there's a missing reference, possibly the Visual Basic for Applications library.

 

[gemma-the-husky]

I just checked - what i do with my car reg numbers is 2 fixes

1. ucase the reg
2. convert any double spaces to single spaces

just to try and make the format consistent.


I think maybe where this gets a bad press is doing it after a FORM update - when it makes the record dirty again!

 

[DCrake]

I very rarely use bound forms, so my suggestion of using the before update event was purely speculative. And soes it really matter if it is held in mixed case when it can be presented in upper case? Also has the OP tried using StrConv() yet?

 

[vbaInet]

Affter trying StrConv() as David suggested, also do VBA.UCase() - AfterUpDate event of control.

If there's a missing reference it would normally throw up a UDF unknown error.

 

[Negger]

Hello to all that have responded, your input has been much appreciated.
I've created a new database & table with just 3 fields to experiment with & they seem to be working OK, I'll play with it a little more to be certain.
I suspect my main Table may be corrupted, it was salvaged following a major PC failure, and I also suspect that I overwrote my back-up with a corrupted version!
The data is badly jumbled anyway (1200 records) with data in wrong fields and wrong records, so I think I will bite the bullet and start from scratch re-creating in a new database.
At least I still have most of the source documents.
Thanks again anyway for all your help, I suspect I may well be back!
Access 2007
XP Pro
User - Novice!