[AccessD] FW: A2K3 Form - hide/show overlaid fields <SOLVED>

Doesn't it figure that it takes me forever to try and work this out, then
type the whole email and bug you folks ... And then 10 seconds later I find
the solution!

I replaced:
If IsNull([EmployeeCountryCodeID]) Or [EmployeeCountryCodeID] = 0 Or
[EmployeeCountryCodeID] = "" Then

With:
If Form.NewRecord = True Then

And it works.

But hey - thanks anyways!!

Best regards,
Bob Gajewski 

-----Original Message-----
From: Bob Gajewski [mailto:rbgajewski at roadrunner.com]
Sent: Friday, September 17, 2010 11:57 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] A2K3 Form - hide/show overlaid fields

Hi Again Everyone

I'm apparently having a series of brain lapses ... I have two pairs of
fields on my form, with each pair overlaying the other ... Depending upon
the value of another field, I want to display one pair and hide the other
<and> prevent the user from tabbing into the hidden fields. The code is at
the end of this email.

----------------------------------------------------------------------------
---
Controlling Field: EmployeeCountryCodeID
	note: value of 38 = Canada, value of 230 = United States

Field Pair #1: EmployeeStateCodeID and EmployeeZipCode

Field Pair #2: EmployeeProvinceCodeID and EmployeePostalCode
----------------------------------------------------------------------------
---

I *seem* to be able to get the show/hide working, but I still have two
problems:

1) When tabbing through the form, the cursor still goes to every field,
including the hidden ones.

>>>> How can I stop tabbing from accessing the hidden fields?

2) When going to a new record, the code crashes with "Run time error '2427':
You entered an expression that has no value.

>>>> How can I get the "IF" code to fire? In debug, the 'If IsNull' line is
the one highlighted as having the problem. 

>>>> I also tried substituting "If acNewRec = True Then", but that failed on
the test - the code advanced to the "Select Case EmployeeCountryCodeID" and
failed there ... Is there a better (and simpler) way to simply test to see
if it is a new record?

Thanks!!
Bob Gajewski


PS - I have the same code in the EmployeeCountryCodeID_AfterUpdate() module.

CODE BEHIND FORM

Private Sub Form_Current()
If IsNull([EmployeeCountryCodeID]) Or [EmployeeCountryCodeID] = 0 Or
[EmployeeCountryCodeID] = "" Then
    EmployeeStateCodeID.Visible = False
    EmployeeStateCodeID.Enabled = False
    EmployeeProvinceCodeID.Visible = False
    EmployeeProvinceCodeID.Enabled = False
    EmployeeZipCode.Visible = False
    EmployeeZipCode.Enabled = False
    EmployeePostalCode.Visible = False
    EmployeePostalCode.Enabled = False
Else
    Select Case EmployeeCountryCodeID
        Case 38   ' Country = CA
            EmployeeStateCodeID.Visible = False
            EmployeeStateCodeID.Enabled = False
            EmployeeProvinceCodeID.Visible = True
            EmployeeProvinceCodeID.Enabled = True
            EmployeeZipCode.Visible = False
            EmployeeZipCode.Enabled = False
            EmployeePostalCode.Visible = True
            EmployeePostalCode.Enabled = True
        Case 230  ' Country = US
            EmployeeStateCodeID.Visible = True
            EmployeeStateCodeID.Enabled = True
            EmployeeProvinceCodeID.Visible = False
            EmployeeProvinceCodeID.Enabled = False
            EmployeeZipCode.Visible = True
            EmployeeZipCode.Enabled = True
            EmployeePostalCode.Visible = False
            EmployeePostalCode.Enabled = False
        Case Else
            EmployeeStateCodeID.Visible = False
            EmployeeStateCodeID.Enabled = False
            EmployeeProvinceCodeID.Visible = False
            EmployeeProvinceCodeID.Enabled = False
            EmployeeZipCode.Visible = False
            EmployeeZipCode.Enabled = False
            EmployeePostalCode.Visible = False
            EmployeePostalCode.Enabled = False
    End Select
End If
End Sub