Access 2003 - Country Code Not Like Canada or USA

[Dave Brydon]

Access 2003: I currently apply my Postal/Zip Code Format based off the
Country Selected; the way I do it is by checking on the country; using the
forms "On Current" event procedure, then I use additional code on my
Postal/Zip Code field, using the fields "On Got Focus" event (Code Below) to
manipulate the input mask.

This procedure works well; however, I now wish to have a longer Value List
of countries instead of using "Other", but I certainly do not wish to apply
a postal code format for each.

The question is; where I currently use "Case Other" on the forms "On Current
event, and the fields On Got Focus, "ElseIf PCountry = "Other" Then".. How
can I change this to reflect all the other countries Not Like Canada or The
US.

Thanks in advance,

Dave

' ---------- Code Starts ----------
Private Sub Form_Current()

Dim Cancel As Integer
Dim strTitle As String
Dim strMsg As String

If Len([txtPPostalCode]) > 0 Then

Select Case PCountry
Case "Canada"
[txtPPostalCode].Format = "@@@ @@@"
Case "USA"

If Val([txtPPostalCode]) > 99999 Then
[txtPPostalCode].Format = "!@@@@@-@@@@"
Else
[txtPPostalCode].Format = "!@@@@@"
End If

Case "Other"
[txtPPostalCode].Format = ""

End Select

End If

End Sub
' ---------- Code Ends ----------
' ---------- Code Starts ----------
Private Sub txtPPostalCode_ GotFocus()
If PCountry = "Canada" Then
[txtPPostalCode].InputMask = ">L0L\ 0L0;;_"
Else

If PCountry = "USA" Then
[txtPPostalCode].InputMask = "00000-9999;;_"
ElseIf PCountry = "Other" Then
[txtPPostalCode].InputMask = ""
End If

End If

End Sub
' ---------- Code Ends ----------

[Bob Quintal]

"Dave Brydon" <db*****@ns.sym patico.ca> wrote in
news:og******** *************@u rsa-nb00s0.nbnet.nb .ca:

Access 2003: I currently apply my Postal/Zip Code Format
based off the Country Selected; the way I do it is by checking
on the country; using the forms "On Current" event procedure,
then I use additional code on my Postal/Zip Code field, using
the fields "On Got Focus" event (Code Below) to manipulate the
input mask.

This procedure works well; however, I now wish to have a
longer Value List of countries instead of using "Other", but I
certainly do not wish to apply a postal code format for each.

The question is; where I currently use "Case Other" on the
forms "On Current event, and the fields On Got Focus, "ElseIf
PCountry = "Other" Then".. How can I change this to reflect
all the other countries Not Like Canada or The US.

Thanks in advance,

Dave

Replace Case "Other" with Case Else
Bob Quintal


' ---------- Code Starts ----------
Private Sub Form_Current()

Dim Cancel As Integer
Dim strTitle As String
Dim strMsg As String

If Len([txtPPostalCode]) > 0 Then

Select Case PCountry
Case "Canada"
[txtPPostalCode].Format = "@@@ @@@"
Case "USA"

If Val([txtPPostalCode]) > 99999 Then
[txtPPostalCode].Format = "!@@@@@-@@@@"
Else
[txtPPostalCode].Format = "!@@@@@"
End If

Case "Other"
[txtPPostalCode].Format = ""

End Select

End If

End Sub
' ---------- Code Ends ----------
' ---------- Code Starts ----------
Private Sub txtPPostalCode_ GotFocus()
If PCountry = "Canada" Then
[txtPPostalCode].InputMask = ">L0L\ 0L0;;_"
Else

If PCountry = "USA" Then
[txtPPostalCode].InputMask = "00000-9999;;_"
ElseIf PCountry = "Other" Then
[txtPPostalCode].InputMask = ""
End If

End If

End Sub
' ---------- Code Ends ----------

[Dave Brydon]

Bob Quintal wrote:

Replace Case "Other" with Case Else

Hi Bob; I entered "Case Else", but was immediately prompted for an "End
Statement"?

Just curious; By changing the code to "Case Else, and leaving "Other", will
this recognize, say, Spain, as Other, or how will this work.
At first I only had three choices, "Canada, USA, or Other" to select from;
however, I now have Canada, USA, as the first choices, with Canada as the
default, and a complete list of countries after the two top choices.

Essentially, what I'm after is: if any country other then Canada, or the USA
are selected, I don't what an input mask, simply accept the entered data as
is.

[Reggie]

Don't think you did the case else correctly and yes this will give you the
results you are looking for. If the country is anything other then Canada
or US then there will me no format:
Private Sub Form_Current()

Dim Cancel As Integer
Dim strTitle As String
Dim strMsg As String

If Len([txtPPostalCode]) > 0 Then
Select Case PCountry
Case "Canada"
[txtPPostalCode].Format = "@@@ @@@"
Case "USA"
If Val([txtPPostalCode]) > 99999 Then
[txtPPostalCode].Format = "!@@@@@-@@@@"
Else
[txtPPostalCode].Format = "!@@@@@"
End If
Case Else
[txtPPostalCode].Format = ""
End Select
End If
End Sub
--
Reggie

www.smittysinet.com
----------
"Dave Brydon" <db*****@ns.sym patico.ca> wrote in message
news:Qa******** *************@u rsa-nb00s0.nbnet.nb .ca...

Bob Quintal wrote:

Replace Case "Other" with Case Else
Hi Bob; I entered "Case Else", but was immediately prompted for an "End
Statement"?

Just curious; By changing the code to "Case Else, and leaving "Other",

will this recognize, say, Spain, as Other, or how will this work.
At first I only had three choices, "Canada, USA, or Other" to select from;
however, I now have Canada, USA, as the first choices, with Canada as the
default, and a complete list of countries after the two top choices.

Essentially, what I'm after is: if any country other then Canada, or the USA are selected, I don't what an input mask, simply accept the entered data as is.

[Dave Brydon]

Reggie,

Thank you for your solution; however, I now think the issue is with the "On
Got Focus" for the field, as that is where I actually apply the input mask
(see code below.

I wonder how to deal with the"= Other" Then.... portion of the code; any
additional guidance would be welcome!

Cheers,

Dave

'---------- Code Starts ----------
Private Sub txtPPostalCode_ GotFocus()
If PCountry = "Canada" Then
[txtPPostalCode].InputMask = ">L0L\ 0L0;;_"
Else

If PCountry = "USA" Then
[txtPPostalCode].InputMask = "00000-9999;;_"
ElseIf PCountry = "Other" Then
[txtPPostalCode].InputMask = ""
End If

End If

End Sub
'---------- Code Ends ----------
"Reggie" <no**********@s mittysinet.com> wrote in message
news:qM******** ************@co mcast.com...

Don't think you did the case else correctly and yes this will give you the
results you are looking for. If the country is anything other then Canada
or US then there will me no format:
Private Sub Form_Current()

Dim Cancel As Integer
Dim strTitle As String
Dim strMsg As String

If Len([txtPPostalCode]) > 0 Then
Select Case PCountry
Case "Canada"
[txtPPostalCode].Format = "@@@ @@@"
Case "USA"
If Val([txtPPostalCode]) > 99999 Then
[txtPPostalCode].Format = "!@@@@@-@@@@"
Else
[txtPPostalCode].Format = "!@@@@@"
End If
Case Else
[txtPPostalCode].Format = ""
End Select
End If
End Sub
--
Reggie

www.smittysinet.com
----------
"Dave Brydon" <db*****@ns.sym patico.ca> wrote in message
news:Qa******** *************@u rsa-nb00s0.nbnet.nb .ca...

Bob Quintal wrote:

Replace Case "Other" with Case Else

Hi Bob; I entered "Case Else", but was immediately prompted for an "End
Statement"?

Just curious; By changing the code to "Case Else, and leaving "Other",

will

this recognize, say, Spain, as Other, or how will this work.
At first I only had three choices, "Canada, USA, or Other" to select from; however, I now have Canada, USA, as the first choices, with Canada as the default, and a complete list of countries after the two top choices.

Essentially, what I'm after is: if any country other then Canada, or the

USA

are selected, I don't what an input mask, simply accept the entered data

as

is.

[Reggie]

Dave have you tried the after update event of the PCountry field/control?

Private Sub PCountry_AfterU pdate()
Dim strCountry As String
strCountry = Me.PCountry

Select Case strCountry
Case "Canada"
Me.[txtPPostalCode].InputMask = ">L0L\ 0L0;;_"
Case "USA"
Me.[txtPPostalCode].InputMask = "00000-9999;;_"
Case Else 'If the country is not Canada or USA not input mask will
be used
Me.[txtPPostalCode].InputMask = ""
End Select
End Sub

--
Reggie

www.smittysinet.com
----------
"Dave Brydon" <db*****@ns.sym patico.ca> wrote in message
news:zc******** *************@u rsa-nb00s0.nbnet.nb .ca...

Reggie,

Thank you for your solution; however, I now think the issue is with the "On Got Focus" for the field, as that is where I actually apply the input mask
(see code below.

I wonder how to deal with the"= Other" Then.... portion of the code; any
additional guidance would be welcome!

Cheers,

Dave

'---------- Code Starts ----------
Private Sub txtPPostalCode_ GotFocus()
If PCountry = "Canada" Then
[txtPPostalCode].InputMask = ">L0L\ 0L0;;_"
Else

If PCountry = "USA" Then
[txtPPostalCode].InputMask = "00000-9999;;_"
ElseIf PCountry = "Other" Then
[txtPPostalCode].InputMask = ""
End If

End If

End Sub
'---------- Code Ends ----------
"Reggie" <no**********@s mittysinet.com> wrote in message
news:qM******** ************@co mcast.com...

Don't think you did the case else correctly and yes this will give you the

results you are looking for. If the country is anything other then Canada or US then there will me no format:
Private Sub Form_Current()

Dim Cancel As Integer
Dim strTitle As String
Dim strMsg As String

If Len([txtPPostalCode]) > 0 Then
Select Case PCountry
Case "Canada"
[txtPPostalCode].Format = "@@@ @@@"
Case "USA"
If Val([txtPPostalCode]) > 99999 Then
[txtPPostalCode].Format = "!@@@@@-@@@@"
Else
[txtPPostalCode].Format = "!@@@@@"
End If
Case Else
[txtPPostalCode].Format = ""
End Select
End If
End Sub
--
Reggie

www.smittysinet.com
----------
"Dave Brydon" <db*****@ns.sym patico.ca> wrote in message
news:Qa******** *************@u rsa-nb00s0.nbnet.nb .ca...

Bob Quintal wrote:

> Replace Case "Other" with Case Else

Hi Bob; I entered "Case Else", but was immediately prompted for an "End Statement"?

Just curious; By changing the code to "Case Else, and leaving "Other",

will

this recognize, say, Spain, as Other, or how will this work.
At first I only had three choices, "Canada, USA, or Other" to select from; however, I now have Canada, USA, as the first choices, with Canada as the default, and a complete list of countries after the two top choices.

Essentially, what I'm after is: if any country other then Canada, or

the USA

are selected, I don't what an input mask, simply accept the entered

data as

is.

[Reggie]

Dave have you tried the after update event of the PCountry field/control?

Private Sub PCountry_AfterU pdate()
Dim strCountry As String
strCountry = Me.PCountry

Select Case strCountry
Case "Canada"
Me.[txtPPostalCode].InputMask = ">L0L\ 0L0;;_"
Case "USA"
Me.[txtPPostalCode].InputMask = "00000-9999;;_"
Case Else 'If the country is not Canada or USA not input mask will
be used
Me.[txtPPostalCode].InputMask = ""
End Select
End Sub

--
Reggie

www.smittysinet.com
----------
"Dave Brydon" <db*****@ns.sym patico.ca> wrote in message
news:zc******** *************@u rsa-nb00s0.nbnet.nb .ca...

Reggie,

Thank you for your solution; however, I now think the issue is with the "On Got Focus" for the field, as that is where I actually apply the input mask
(see code below.

I wonder how to deal with the"= Other" Then.... portion of the code; any
additional guidance would be welcome!

Cheers,

Dave

'---------- Code Starts ----------
Private Sub txtPPostalCode_ GotFocus()
If PCountry = "Canada" Then
[txtPPostalCode].InputMask = ">L0L\ 0L0;;_"
Else

If PCountry = "USA" Then
[txtPPostalCode].InputMask = "00000-9999;;_"
ElseIf PCountry = "Other" Then
[txtPPostalCode].InputMask = ""
End If

End If

End Sub
'---------- Code Ends ----------
"Reggie" <no**********@s mittysinet.com> wrote in message
news:qM******** ************@co mcast.com...

Don't think you did the case else correctly and yes this will give you the

results you are looking for. If the country is anything other then Canada or US then there will me no format:
Private Sub Form_Current()

Dim Cancel As Integer
Dim strTitle As String
Dim strMsg As String

If Len([txtPPostalCode]) > 0 Then
Select Case PCountry
Case "Canada"
[txtPPostalCode].Format = "@@@ @@@"
Case "USA"
If Val([txtPPostalCode]) > 99999 Then
[txtPPostalCode].Format = "!@@@@@-@@@@"
Else
[txtPPostalCode].Format = "!@@@@@"
End If
Case Else
[txtPPostalCode].Format = ""
End Select
End If
End Sub
--
Reggie

www.smittysinet.com
----------
"Dave Brydon" <db*****@ns.sym patico.ca> wrote in message
news:Qa******** *************@u rsa-nb00s0.nbnet.nb .ca...

Bob Quintal wrote:

> Replace Case "Other" with Case Else

Hi Bob; I entered "Case Else", but was immediately prompted for an "End Statement"?

Just curious; By changing the code to "Case Else, and leaving "Other",

will

this recognize, say, Spain, as Other, or how will this work.
At first I only had three choices, "Canada, USA, or Other" to select from; however, I now have Canada, USA, as the first choices, with Canada as the default, and a complete list of countries after the two top choices.

Essentially, what I'm after is: if any country other then Canada, or

the USA

are selected, I don't what an input mask, simply accept the entered

data as

is.

[Dave Brydon]

Reggie,

Thanks again :-o)

Your solutions worked perfectly; exactly how I wished..you've been a great
help!

Hope you have a wonderful day!

Cheers,

Dave

"Reggie" <no**********@s mittysinet.com> wrote in message
news:UJ******** ************@co mcast.com...

Dave have you tried the after update event of the PCountry field/control?

Private Sub PCountry_AfterU pdate()
Dim strCountry As String
strCountry = Me.PCountry

Select Case strCountry
Case "Canada"
Me.[txtPPostalCode].InputMask = ">L0L\ 0L0;;_"
Case "USA"
Me.[txtPPostalCode].InputMask = "00000-9999;;_"
Case Else 'If the country is not Canada or USA not input mask will
be used
Me.[txtPPostalCode].InputMask = ""
End Select
End Sub

--
Reggie

www.smittysinet.com
----------
"Dave Brydon" <db*****@ns.sym patico.ca> wrote in message
news:zc******** *************@u rsa-nb00s0.nbnet.nb .ca...

Reggie,

Thank you for your solution; however, I now think the issue is with the

"On

Got Focus" for the field, as that is where I actually apply the input mask

(see code below.

I wonder how to deal with the"= Other" Then.... portion of the code; any
additional guidance would be welcome!

Cheers,

Dave

'---------- Code Starts ----------
Private Sub txtPPostalCode_ GotFocus()
If PCountry = "Canada" Then
[txtPPostalCode].InputMask = ">L0L\ 0L0;;_"
Else

If PCountry = "USA" Then
[txtPPostalCode].InputMask = "00000-9999;;_"
ElseIf PCountry = "Other" Then
[txtPPostalCode].InputMask = ""
End If

End If

End Sub
'---------- Code Ends ----------
"Reggie" <no**********@s mittysinet.com> wrote in message
news:qM******** ************@co mcast.com...

Don't think you did the case else correctly and yes this will give you the results you are looking for. If the country is anything other then Canada or US then there will me no format:
Private Sub Form_Current()

Dim Cancel As Integer
Dim strTitle As String
Dim strMsg As String

If Len([txtPPostalCode]) > 0 Then
Select Case PCountry
Case "Canada"
[txtPPostalCode].Format = "@@@ @@@"
Case "USA"
If Val([txtPPostalCode]) > 99999 Then
[txtPPostalCode].Format = "!@@@@@-@@@@"
Else
[txtPPostalCode].Format = "!@@@@@"
End If
Case Else
[txtPPostalCode].Format = ""
End Select
End If
End Sub
--
Reggie

www.smittysinet.com
----------
"Dave Brydon" <db*****@ns.sym patico.ca> wrote in message
news:Qa******** *************@u rsa-nb00s0.nbnet.nb .ca...
> Bob Quintal wrote:
>
> > Replace Case "Other" with Case Else
>
> Hi Bob; I entered "Case Else", but was immediately prompted for an "End > Statement"?
>
> Just curious; By changing the code to "Case Else, and leaving "Other", will
> this recognize, say, Spain, as Other, or how will this work.
> At first I only had three choices, "Canada, USA, or Other" to select

from;

> however, I now have Canada, USA, as the first choices, with Canada

as the

> default, and a complete list of countries after the two top choices.
>
> Essentially, what I'm after is: if any country other then Canada, or

the USA
> are selected, I don't what an input mask, simply accept the entered data as
> is.
>
>
>

[Dave Brydon]

Reggie,

Thanks again :-o)

Your solutions worked perfectly; exactly how I wished..you've been a great
help!

Hope you have a wonderful day!

Cheers,

Dave

"Reggie" <no**********@s mittysinet.com> wrote in message
news:UJ******** ************@co mcast.com...

Dave have you tried the after update event of the PCountry field/control?

Private Sub PCountry_AfterU pdate()
Dim strCountry As String
strCountry = Me.PCountry

Select Case strCountry
Case "Canada"
Me.[txtPPostalCode].InputMask = ">L0L\ 0L0;;_"
Case "USA"
Me.[txtPPostalCode].InputMask = "00000-9999;;_"
Case Else 'If the country is not Canada or USA not input mask will
be used
Me.[txtPPostalCode].InputMask = ""
End Select
End Sub

--
Reggie

www.smittysinet.com
----------
"Dave Brydon" <db*****@ns.sym patico.ca> wrote in message
news:zc******** *************@u rsa-nb00s0.nbnet.nb .ca...

Reggie,

Thank you for your solution; however, I now think the issue is with the

"On

Got Focus" for the field, as that is where I actually apply the input mask

(see code below.

I wonder how to deal with the"= Other" Then.... portion of the code; any
additional guidance would be welcome!

Cheers,

Dave

'---------- Code Starts ----------
Private Sub txtPPostalCode_ GotFocus()
If PCountry = "Canada" Then
[txtPPostalCode].InputMask = ">L0L\ 0L0;;_"
Else

If PCountry = "USA" Then
[txtPPostalCode].InputMask = "00000-9999;;_"
ElseIf PCountry = "Other" Then
[txtPPostalCode].InputMask = ""
End If

End If

End Sub
'---------- Code Ends ----------
"Reggie" <no**********@s mittysinet.com> wrote in message
news:qM******** ************@co mcast.com...

Don't think you did the case else correctly and yes this will give you the results you are looking for. If the country is anything other then Canada or US then there will me no format:
Private Sub Form_Current()

Dim Cancel As Integer
Dim strTitle As String
Dim strMsg As String

If Len([txtPPostalCode]) > 0 Then
Select Case PCountry
Case "Canada"
[txtPPostalCode].Format = "@@@ @@@"
Case "USA"
If Val([txtPPostalCode]) > 99999 Then
[txtPPostalCode].Format = "!@@@@@-@@@@"
Else
[txtPPostalCode].Format = "!@@@@@"
End If
Case Else
[txtPPostalCode].Format = ""
End Select
End If
End Sub
--
Reggie

www.smittysinet.com
----------
"Dave Brydon" <db*****@ns.sym patico.ca> wrote in message
news:Qa******** *************@u rsa-nb00s0.nbnet.nb .ca...
> Bob Quintal wrote:
>
> > Replace Case "Other" with Case Else
>
> Hi Bob; I entered "Case Else", but was immediately prompted for an "End > Statement"?
>
> Just curious; By changing the code to "Case Else, and leaving "Other", will
> this recognize, say, Spain, as Other, or how will this work.
> At first I only had three choices, "Canada, USA, or Other" to select

from;

> however, I now have Canada, USA, as the first choices, with Canada

as the

> default, and a complete list of countries after the two top choices.
>
> Essentially, what I'm after is: if any country other then Canada, or

the USA
> are selected, I don't what an input mask, simply accept the entered data as
> is.
>
>
>

[Bob Quintal]

"Dave Brydon" <db*****@ns.sym patico.ca> wrote in
news:Qa******** *************@u rsa-nb00s0.nbnet.nb .ca:

Bob Quintal wrote:

Replace Case "Other" with Case Else
Hi Bob; I entered "Case Else", but was immediately prompted
for an "End Statement"?

You had two code segments, first, the Select Case
statement. I didn't notice that the second segment had if
statements. If you modify the if...elseif block to a select case
block like the first, it will work. Besides, having consistent
constructs will make the relationship much more evident, when you
go to modify it in 10 years.

Bob Quintal


Just curious; By changing the code to "Case Else, and leaving
"Other", will this recognize, say, Spain, as Other, or how
will this work. At first I only had three choices, "Canada,
USA, or Other" to select from; however, I now have Canada,
USA, as the first choices, with Canada as the default, and a
complete list of countries after the two top choices.

Essentially, what I'm after is: if any country other then
Canada, or the USA are selected, I don't what an input mask,
simply accept the entered data as is.