Ucase question...

[jazz123]

We have a situation where mail sent to Canada using the mail merge is being returned because it's not in all caps. I've used the following code in each fields after update:

Me.ActiveControl = UCase(Me.ActiveControl)

What I'd like to do is make it so only the Canadian addresses are in upper case. This would be determined by what is in the state field. AB, BC, etc. Any U.S. address would need to be in proper case where the first letter of each word is capitalized.

Can this be done?

 

[Newman]

Change this

Me.ActiveControl = UCase(Me.ActiveControl)

for that

If strState = "BC" or [...] then
    Me.ActiveControl = UCase(Me.ActiveControl)
Else
    Me.ActiveControl = StrConv(Me.ActiveControl, vbProperCase)
End if

And, in the AfterUpdate of the state field, add this

If Me.ActiveControl= "BC" or [...] then
    Me.strAddress = UCase(Me.strAddress)
    Me.strCity = UCase(Me.strCity)
    Me.strPostalCode = UCase(Me.strPostalCode)
    [...]
Else
    Me.strAddress = StrConv(Me.strAddress, vbProperCase)
    Me.strCity = StrConv(Me.strCity, vbProperCase)
    Me.strPostalCode = StrConv(Me.strPostalCode, vbProperCase)
    [...]
End if

 

[jazz123]

The only question I have is this...

If strState = "AB" or "BC" or "etc." then
    Me.ActiveControl = UCase(Me.ActiveControl)
Else
    Me.ActiveControl = StrConv(Me.ActiveControl, vbProperCase)
End if

Is that the way it's supposed to be entered? It doesn't like that. It takes be to the debugger. The name of the state field is "State" not "strState". Is the strState correct?

 

[trucktime]

State

Try this:

If Me.State = "AB" or Me.State= "BC" or "etc." then
Me.ActiveControl = UCase(Me.ActiveControl)
Else
Me.ActiveControl = StrConv(Me.ActiveControl, vbProperCase)
End if

 

[jazz123]

This is what I've got in the After Update of the Address_1 field.

Private Sub Address_1_AfterUpdate()
If Me.State = "AB" Or Me.State = "BC" Or Me.State = "MB" Or Me.State = "NB" Or Me.State = "NL" Or Me.State = "NS" Or Me.State = "NT" Or Me.State = "NU" Or Me.State = "ON" Or Me.State = "PE" Or Me.State = "QC" Or Me.State = "SK" Or Me.State = "YT" Then
Me.ActiveControl = UCase(Me.ActiveControl)
Else
Me.ActiveControl = StrConv(Me.ActiveControl, vbProperCase)
End If
End Sub()

I'm getting the following error:

 

[trucktime]

Ucase

The code goes in the After Update Event of the field 'State'.

 

[jazz123]

If I put only this

Private Sub Address_1_AfterUpdate()
If Me.State = "AB" Or Me.State = "BC" Or Me.State = "MB" Or Me.State = "NB" Or Me.State = "NL" Or Me.State = "NS" Or Me.State = "NT" Or Me.State = "NU" Or Me.State = "ON" Or Me.State = "PE" Or Me.State = "QC" Or Me.State = "SK" Or Me.State = "YT" Then
Me.ActiveControl = UCase(Me.ActiveControl)
Else
Me.ActiveControl = StrConv(Me.ActiveControl, vbProperCase)
End If
End Sub()

In the After Update of the state field, there isn't anything to specify which fields you want this rule to apply to. Amy I just being dumb or am I missing something?

 

[trucktime]

Ucase

Sorry, my fault. This goes in the After Update Event of the field State
Try this:

If Me.State = "AB" Or Me.State = "BC" Or Me.State = "MB" Or Me.State = "NB" Or Me.State = "NL" Or Me.State = "NS" Or Me.State = "NT" Or Me.State = "NU" Or Me.State = "ON" Or Me.State = "PE" Or Me.State = "QC" Or Me.State = "SK" Or Me.State = "YT" Then
Me.Address_1 = UCase(Me.Address_1)
Else
Me.Address_1 = StrConv(Me.Address_1, vbProperCase)
End If

 

[jazz123]

I really appreciate you help with this. It doesn't like this code.

If Me.State = "AB" Or Me.State = "BC" Or Me.State = "MB" Or Me.State = "NB" Or Me.State = "NL" Or Me.State = "NS" Or Me.State = "NT" Or Me.State = "NU" Or Me.State = "ON" Or Me.State = "PE" Or Me.State = "QC" Or Me.State = "SK" Or Me.State = "YT" Then

 

[trucktime]

Ucase

I have tried in my db, and it works fine for me.
Should work in any Access version.

Perhaps you can post your db (without data), and I'll have a look.

 

[jazz123]

It's too large to upload. Here is the link. http://www.dhdesign.biz/database2.zip

Let me know what you think.

 

[trucktime]

UCase

Sorry, can't unzip the file.
It tells me it's an unknown format.

 

[jazz123]

Sorry. I've fixed it. Try the new link above.

 

[trucktime]

Ucase

I downloaded the db.
In the Form DRAFTS (Kodi) I put the code in the After Update Event of the Field
State, and it works fine. The Field Address_1 gets formatted as needed.
What error are you getting?

Tip: use a lookup table for the field State.

Some advice.
It is good practice not to use spaces and special characters in field names,
use an underscore instead.
It is also useful to have meaningful fieldnames, instead of Field 1 etc.
This may come in handy especially when you have to debug.

Form names usually have the prefix 'frm', like frmAddressbook.
For Queries it is qry, and rpt for Reports.

You may want to check the link below.
There is a lot of useful information, including articles about normalisation,
naming conventions and such.

http://www.microsoft-accesssolutions.co.uk/index.htm

Good Luck.

 

[jazz123]

The form that I'm working on is "Lost Baggage Settlements". Did you happen to try on that one?

 

[trucktime]

Ucase

OK, tried that form.
The problem was that the field name was incorrect, something like Text 117.
Rename the Combo Box to cboState and then put this code in the After Update.

If Me.cboState = "AB" Or Me.cboState = "BC" Or Me.cboState = "MB" Then
Me.Address_1 = UCase(Me.Address_1)
Else
Me.Address_1 = StrConv(Me.Address_1, vbProperCase)
End If


Hope it works now.

 

[trucktime]

Ucase

This also underlines the remark I made earlier.
Giving all fields, object names etc. meaningful names can save you a lot
of headache later.

Good Luck.

 

[jazz123]

Works great! I really do appreciate your help. Also for the input regarding the field names.

 

[trucktime]

Ucase

You're welcome.