[AccessD] A2K3 Form - hide/show overlaid fields

Hi Bob,

I don't see anything wrong with your code.  However, if you are setting the
Visible property = False, then you can ignore the Enabled property.  In your
case, you can ignore the enabled property altogether.

If you are able to tab into a field, then the visible property will always
be True.

You are using the form's Current event to trigger this code.  Unfortunately,
the Current event will run based on a number of things, some of which you
can't control.  I'd recommend initiating this code some other way.

Put this code into its own procedure named FormatFormName, and call it in
the Form's Load event.  Also call it from any other appropriate event.

HTH,
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bob Gajewski
Sent: Friday, September 17, 2010 10: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

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com