By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,491 Members | 1,868 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,491 IT Pros & Developers. It's quick & easy.

Access 2003 - Country Code Not Like Canada or USA

P: n/a
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 ----------


Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
"Dave Brydon" <db*****@ns.sympatico.ca> wrote in
news:og*********************@ursa-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 ----------



Nov 13 '05 #2

P: n/a
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.

Nov 13 '05 #3

P: n/a
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.sympatico.ca> wrote in message
news:Qa*********************@ursa-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.

Nov 13 '05 #4

P: n/a
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**********@smittysinet.com> wrote in message
news:qM********************@comcast.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.sympatico.ca> wrote in message
news:Qa*********************@ursa-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.


Nov 13 '05 #5

P: n/a
Dave have you tried the after update event of the PCountry field/control?

Private Sub PCountry_AfterUpdate()
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.sympatico.ca> wrote in message
news:zc*********************@ursa-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**********@smittysinet.com> wrote in message
news:qM********************@comcast.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.sympatico.ca> wrote in message
news:Qa*********************@ursa-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.



Nov 13 '05 #6

P: n/a
Dave have you tried the after update event of the PCountry field/control?

Private Sub PCountry_AfterUpdate()
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.sympatico.ca> wrote in message
news:zc*********************@ursa-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**********@smittysinet.com> wrote in message
news:qM********************@comcast.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.sympatico.ca> wrote in message
news:Qa*********************@ursa-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.



Nov 13 '05 #7

P: n/a
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**********@smittysinet.com> wrote in message
news:UJ********************@comcast.com...
Dave have you tried the after update event of the PCountry field/control?

Private Sub PCountry_AfterUpdate()
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.sympatico.ca> wrote in message
news:zc*********************@ursa-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**********@smittysinet.com> wrote in message
news:qM********************@comcast.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.sympatico.ca> wrote in message
news:Qa*********************@ursa-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.
>
>
>



Nov 13 '05 #8

P: n/a
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**********@smittysinet.com> wrote in message
news:UJ********************@comcast.com...
Dave have you tried the after update event of the PCountry field/control?

Private Sub PCountry_AfterUpdate()
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.sympatico.ca> wrote in message
news:zc*********************@ursa-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**********@smittysinet.com> wrote in message
news:qM********************@comcast.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.sympatico.ca> wrote in message
news:Qa*********************@ursa-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.
>
>
>



Nov 13 '05 #9

P: n/a
"Dave Brydon" <db*****@ns.sympatico.ca> wrote in
news:Qa*********************@ursa-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.


Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.