[AccessD] A2K3 Form - hide/show overlaid fields

Everything Dan said, but additionally my understanding is that controls on top of other controls 
causes speed issues.  I would suggest rethinking how to do this whole thing.

John W. Colby
www.ColbyConsulting.com

On 9/17/2010 12:12 PM, Dan Waters wrote:
> 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
>